Back populate metrics

[gaidem]

I recently had a challenge to create metrics for tickets that existed before the metric was defined/created. I was able to achieve this by running a background script checking the audit history of records and inserting the metric based on when the ticket was created and when the audit record had created to calculate the duration. You can modify this script as needed, but it's a basis for how this can be done:

//find incidents that are client confirmed
var x = new GlideRecord('incident');
x.addQuery('u_resolution_status','2');
x.query();
while(x.next()){ //record found, now find out when it was set to client confirmed and create the metric
var y = new GlideRecord('sys_audit'); //this is the audit table
y.addQuery('documentkey',x.sys_id); //find the audit record for this incident
y.addQuery('fieldname','u_resolution_status'); //find the audit record against the resolution status field
y.addQuery('newvalue','2'); //find the record where the resolution status was set to client confirmed
y.query();
if(y.next()){
var z = new GlideRecord('metric_instance'); //this is the metric table
z.initialize();
z.id = x.sys_id; //set the metric against the incident
z.definition = '35edf981c0a808ae009895af7c843ace'; //this is the metric definition, as there are all sorts of metrics
z.table = 'incident'; //set the table to incident
z.start = x.sys_created_on; //set the start of the metric to when the incident was created
z.end = y.sys_created_on; //set the end to when the audit table shows the value was set to client confirmed
  z.duration = gs.dateDiff(x.sys_created_on.getDisplayValue(), y.sys_created_on.getDisplayValue()); //this is used to calculate the difference between the 2 dates and set the duration
z.insert(); //metric created, now start at the while statement for the next record until all records are queried
}}

[psiek]

Hi,

Thanks a lot for this.

Regards,

[DubeyN]

Hi Matt,

I have modified this script to back fill metrics for my custom application. I have below 2 question:

(1) You used z.start = x.sys_created_on; and z.end = y.sys_created_on; to get the start time and end time to be populated in the metrics record. In this case start time will be start of the record for each and every status. But in normal metrics table start time is the time when record move to particular status. Is it possible to get modify this code to get the start time when status move to that status.

(2) sys_audit table have field update count which says how many time record moved to old value to new value. In this case if we run above query we will not get all status in the metrics table, it will only show first status change values not for others. Is there is any way to get the list of all update count in metrics table.

ND

[Bhavana20]

Hello,

I have also the same question, can you please help me on this if you found the solution?

Thanks,

Bhavana

[MarioP1]

Hi gaidem,

Our Requirement : Calculate business duration between the time incident state was resolved/closed to the time - emergency change request was opened related to incident. I was able to create a metric for this and it works as expected for new emergency change records.

Do you have suggestion on how to get above for historical records without using the audit table?

Thanks