Hi,
Do not use history table. Instead use a metric to serve your purpose.
Follow the steps.
1. Check out the OOB metric for incident table named "Assigned to Duration". This metric calculates the duration for how much time the incident was assigned to a particular person.
2. Now your requirement is to get the time stamp when the assigned to field was changed first time. For this create a copy of the "Assigned to Duration" metric. In script area paste below script. This will create the record only once for each incident in metric_instance table.
createMetric();
function createMetric() {
var mi = new MetricInstance(definition, current);
if (mi.metricExists()){
return;}
else{
var gr = mi.getNewRecord();
gr.insert();
}
}
3. That's it! From now onwards it will start to capture the metrics. Create report on incident_metric table(it is database view actually) and use below filters.
//Change the definition with the name of your metric.
4. Add below highlighted field to report to get the time stamp when the field was changed.
Mark my response correct if it worked for you. Ask for help if required.
You can query the sys_history_line table and run a query like this:
Might not be 100% accurate because the ticket could be passed around a few times and left blank. There's a way to query the table via script include and get back the first entry per INC. I can help if you need assistance
Saloni-
The other responses are correct, technically you should not report on history tables. In fact, SN blocks the ability to run queries on these tables (ex: creating reports on the sys_history_line table.
You can use sys_history_line table to run report and use condition like below. But ServiceNow doesn't recommend running report on historic tables as this will be huge data load on ServiceNow platform. It can cause performance issues
Hope this helps you out. If so, please hit the "Correct answer" button.
