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
}}

[Bhavana20]

Hye did you achieve this? if so please help me..

[Jon25]

Thanks for posting your script, I used this for inspiration and have developed the below script to create metrics for Assets from the Audit history.

// define the metric definition sysid to attach the created metric instances
var metric_definition_sysid = '<insert your metric definition sysid here>';

gs.print("Querying asset records...");
var gr_hardware = new GlideRecord('alm_hardware'); //this is the hardware asset table
gr_hardware.addQuery('model_category.name', 'Computer'); //limit to a specific model category
gs.print("Query: " + gr_hardware.getEncodedQuery());
gr_hardware.query(); // get hardware asset records
gs.print("Found " + gr_hardware.getRowCount() + " records.");

//loop each asset we found
gs.print("Checking each asset record for audit history...");
while (gr_hardware.next()) {
    gs.print("START Checking: " + gr_hardware.asset_tag);
	// retrive the audit history for the asset
    var gr_audit = new GlideRecord('sys_audit'); //this is the audit table
    gr_audit.addQuery('fieldname', 'assigned_to'); //find the assigned to audit records
    gr_audit.addQuery('tablename', 'alm_hardware'); //find the audit records just for hardware assets
    gr_audit.addQuery('documentkey', gr_hardware.sys_id); //find the audit records just for this asset
    gr_audit.orderBy('sys_created_on'); //order by date with oldest first
    gr_audit.query();

	gs.print("Found " + gr_audit.getRowCount() + " audit records");

    // Build the completed metric history from audit data

    //create variable for use to store the metric start date. This will be either the asset creation date for the first metric or the previous audit record creation date for future metrics.
    var startdate = gr_hardware.sys_created_on.getDisplayValue();
	
    //loop each audit history for the asset
    while (gr_audit.next()) {
        //Resolve the user display name from the audit sysid value for use in the metric
        var metricvalue = '';
		if (gr_audit.oldvalue) {
           metricvalue = gs.getUser().getUserByID(gr_audit.oldvalue).getDisplayName();
        } 

		// check if a metric already exists so we don't create duplicates
		// timing for metric creation and audit history may not align to the millisecond so we check on same day and value.
		var gr_metric = new GlideRecord('metric_instance'); //this is the metric table
		gr_metric.addQuery('definition', metric_definition_sysid); //limit to a specific metric definition
		gr_metric.addQuery('id', gr_hardware.sys_id); //limit to the same asset
		gr_metric.addQuery('value', metricvalue); //limit to the same value
		var gdt = new GlideDateTime(startdate);
		gr_metric.addQuery('start','ON', gdt.getDate()); //limit to the same date day
		gr_metric.query();
		
		//If a duplicate does not exist, create the record
		if (gr_metric.getRowCount() == 0) {
			gs.print("ADD Metric: " + gr_hardware.asset_tag + ',' + metricvalue + ',' + startdate + ',' + gr_audit.sys_created_on);
			//create the completed metric
			gr_metric = new GlideRecord('metric_instance'); //this is the metric table
			gr_metric.initialize(); //prepare a new record
			gr_metric.id = gr_hardware.sys_id; //set the metric against the asset
			gr_metric.definition = metric_definition_sysid; //this is the metric definition sysid
			gr_metric.table = 'alm_hardware'; //set the table to hardware assets
			gr_metric.field = 'assigned_to'; //set the field to assigned to
			gr_metric.value = metricvalue; //set the new to assigned to value
			gr_metric.start = startdate; //set the start of the metric to when the asset was assigned
			gr_metric.end = gr_audit.sys_created_on; //set the end to when the audit table shows the value was set
			gr_metric.duration = gs.dateDiff(startdate, gr_audit.sys_created_on.getDisplayValue()); //this is used to calculate the difference between the 2 dates and set the duration
			gr_metric.calculation_complete = 1;
			gr_metric.insert(); //metric created
		} else {
			gs.print("IGNORE Metric: Duplicate Found " + gr_hardware.asset_tag + ',' + metricvalue + ',' + startdate);
		}
			
        //update the startdate to the audit record enddate for use in the next metric
        startdate = gr_audit.sys_created_on.getDisplayValue();

    }
	
    //Every record needs an open metric with current data
	// check if a metric already exists so we don't create duplicates
	// timing for metric creation and audit history may not align to the millisecond so we check on same day and value.
	gr_metric = new GlideRecord('metric_instance'); //this is the metric table
	gr_metric.addQuery('definition', metric_definition_sysid); //limit to a specific metric definition
	gr_metric.addQuery('id', gr_hardware.sys_id); //limit to the same asset
	gr_metric.addQuery('value', gr_hardware.assigned_to.getDisplayValue()); //limit to the same value
	gdt = new GlideDateTime(startdate);
	gr_metric.addQuery('start','ON', gdt.getDate()); //limit to the same date day
	gr_metric.query();

	//If a duplicate does not exist, create the record
	if (gr_metric.getRowCount() == 0) {
		gs.print("ADD Metric: " + gr_hardware.asset_tag + ',' + gr_hardware.assigned_to.getDisplayValue() + ',' + startdate + ', Active');
		//create the open metric
		gr_metric = new GlideRecord('metric_instance'); //this is the metric table
		gr_metric.initialize(); //prepare a new record
		gr_metric.id = gr_hardware.sys_id; //set the metric against the asset
		gr_metric.definition = metric_definition_sysid; //this is the metric definition
		gr_metric.table = 'alm_hardware'; //set the table to hardware assets
		gr_metric.field = 'assigned_to'; //set the field to assigned to
		gr_metric.value = gr_hardware.assigned_to.getDisplayValue(); //set the new to assigned to value
		gr_metric.start = startdate; //set the start of the metric to when the asset was assigned
		gr_metric.insert(); //metric created
	} else {
		gs.print("IGNORE Metric: Duplicate Found " + gr_hardware.asset_tag + ',' + gr_hardware.assigned_to.getDisplayValue() + ',' + startdate);
	}
        

}

[VincentLastname]

I'm having trouble using the same script b/c my audit table only goes back 4 months. Unsure why.