Create a Metric and calculate historical data

by

I came across a topic a couple of days ago where I wanted to create a new metric. This is actually not really rocket science and very easy to do but then I was wondering how can I calculate the data for already existing tickets.

Calculate the Time difference in Change Requests

My goal was to create a metric that calculates the time difference between the Planned End Date and the Actual End Date in a change request. The reason for this is we wanted to check how good our planning was done and if we can fit the deadlines we gave to our business partners.

First of all, we had to create the metric definition:

  1. Go to Metrics > Definitions.
  2. Click New.

I’ve decided to create a metric with a script calculation to be able to fulfill our requirements.

  • Requirement 1:
    Calculate the difference between Planned End Date and Actual End Date to identify an average of days we are over/underestimating.
  • Requirement 2:
    Mark changes as “Planned Date Overdue” if the duration of requirement 1 is greater 2 days.
  • Requirement 3:
    Calculate the metric also for historical data.
  • Requirement 4:
    Calculate only when the state changes to “Closed”.

The following code is used in the calculation and it fulfills three of our requirements.

// Requirement 4: State = Closed
if (current.state == 3) {
    createMetric();
}

function createMetric() {
    var mi = new MetricInstance(definition, current);
	// Check if a metric already exists and do not create another metric record
	if (mi.metricExists()){
		return;
	}
	
	// Generate metric record
    var gr = mi.getNewRecord();
    gr.planned = current.end_date;
    gr.actual = current.work_end;
	
	// Requirement 1: Calculate Time Difference between planned and actual and write value into duration field of metric
	var dur = gs.dateDiff(gr.planned.getDisplayValue(), gr.actual.getDisplayValue(),false);
    gr.duration = dur;
	
	// Requirement 2: Split time difference value to only get the days back. If duration > 2 days set value to Planned Date Overdue.
	var durrDays = dur.split(' ');
	if(durrDays[0] > 2){
		gr.value = 'Planned Date Overdue';
	}
	else{
		gr.value = 'OK';
	}
	
	// Complete calculation and create metric
    gr.calculation_complete = true;
    gr.insert();
}

Calculate metrics for historical data

We now have implemented a metric that calculates always when a change request is closed. Because we have already historical data in our change requests we wanted to feed that metric with this data. To do this simply create a fix-script that calls your metric calculation

  1. Go to System Definition > Fix Scripts
  2. Click New.
Fixed script to calculate metric for historical data

The script calculates the data for everything that is part of our GlideRecord query. In this case I tested it on my personal developer instance therefore I used gs.quartersAgo(). You can of course use any kind of date/time value here.

// Requirement 3: Calculate for historical data
var records = new GlideRecord('change_request');

//Get all change requests that were closed a couple of quarters ago. Can also be something else...
records.addQuery('closed_at', '>', gs.quartersAgo(100));
records.query();

while (records.next()) {

    //Trigger metric.update with the change request, send the trigger parameters
    gs.eventQueue('metric.update', records, '[state]', records.sys_mod_count, 'metric_update');

}

Now just run your fix script and you will get metrics created from your historical records. You can then use that data in reporting to visualize how well you planned your changes!

Leave a Comment