Reporting – Average Time Metrics

General Add comments
by:

For reporting purposes, averages are always good. They can help you find out if you are performing at a reasonable level and hitting your SLA goals or if you’re missing your SLA goals. Out-of-the-box, Service Now provides some nice metrics and the ability to create custom metric definitions.
However, sometimes they just don’t cut it.
For example, the average time spent per change phase could be a very nice metric.
Unfortunately, the standard “Field value duration” method that ServiceNow provides out-of-the-box, does not always perform the way you’d like. For instance, when you want to measure the average time per change phase and the record returns to phase 1 after moving to phase 2, a second metric instance will be created for phase 1. This effectively lowers the average time spent in phase 1 and thus results in a false average. A second concern with the “Field value duration” method is that you can’t measure the time spent in the last phase (because the last phase is never closed).
In order to measure the average time, we need to create two separate metrics.
We start with the first metric. Please note that if you choose type “Script calculation”, you should still carefully consider which field you use. The metric will only be triggered when the selected field is changed and the selected field has to be audited.



The code:
[cc lang=”javascript”]
getPreviousChangePhase();

function getPreviousChangePhase() {
var hist = new GlideRecord(“sys_audit”);
hist.addQuery(“tablename”, current.sys_class_name);
hist.addQuery(“documentkey”, current.sys_id + “”);
hist.addQuery(“fieldname”, “u_change_phase”);
hist.orderByDesc(“record_checkpoint”);
hist.query();

if (hist.next()) {
if (hist.next()) {
var previousID = getChangePhase(hist.newvalue);
checkMetrics(previousID, hist.sys_created_on);
} else {
var gr = new GlideRecord(‘change_request’);
gr.addQuery(‘sys_id’, current.sys_id);
gr.query();
if(gr.next()){
var previousID = getChangePhase(hist.oldvalue);
checkMetrics(previousID, gr.sys_created_on);
}
}
}
}
[/cc]
This function checks the audit log for entries and passes the variables on to other functions.
If you can go two steps back, you get the new value, if there is only one record, you get the old value. (This is required in order to be able to measure the first phase).

[cc lang=”javascript”]
function getChangePhase(value) {
var gr = new GlideRecord(‘sys_choice’);
gr.addQuery(‘element’, ‘u_change_phase’);
gr.addQuery(‘table’, ‘change_request’);
gr.addQuery(‘value’, value);
gr.query();

if (gr.next()) {
return gr.label;
}
}
[/cc]
This function returns the display value instead of the value (optional, but looks better in the actual report).
[cc lang=”javascript”]
function checkMetrics(previousID, startDate) {
var mtc = new GlideRecord(‘metric_instance’);
mtc.addQuery(‘id’, current.sys_id + ”);
mtc.addQuery(‘table’, current.getTableName());
mtc.addQuery(‘field’, ‘u_change_phase’);
mtc.addQuery(‘value’, previousID);
mtc.query();

if (mtc.next()) {
var Start = new GlideDateTime(startDate);
var End = new GlideDateTime(current.sys_updated_on);
mtc.duration.setDateNumericValue(mtc.duration.dateNumericValue() + (End.getNumericValue() – Start.getNumericValue()));
mtc.update();

} else {
createMetric(previousID, startDate);
}
}
[/cc]
This function checks if a metric record already exists for this change / phase combination.
If it exists, it will sum the two records, else it will create a new metric record.

[cc lang=”javascript”]
function createMetric(previousID, startDate) {
var mi = new MetricInstance(definition, current);
var gr = mi.getNewRecord();
gr.start = startDate;
gr.end = current.sys_updated_on;
gr.duration = gs.dateDiff(startDate, current.sys_updated_on, false);
gr.value = previousID;
gr.calculation_complete = true;
gr.insert();
}
[/cc]
This function creates a new metric instance.

Now, in order to be able to measure the time spent in the last phase, we need to create a second metric definition. Please note that to measure the time spent in the last phase, the metric uses the date/time the change entered the last phase and the date/time the state changes to Closed (3) as the end time.
This is basically the same metric definition, with some small differences.
First, this metric only creates a record when the change is closed.
Second, the record is created using the sys_id of the first metric definition.

So do not forget to change the definition’s sys_id to your particular sys_id!

[cc lang=”javascript”]
if (current.state == 3){
getPreviousChangePhase();
}

function getPreviousChangePhase() {
var hist = new GlideRecord(“sys_audit”);
hist.addQuery(“tablename”, current.sys_class_name);
hist.addQuery(“documentkey”, current.sys_id + “”);
hist.addQuery(“fieldname”, “u_change_phase”);
hist.orderByDesc(“record_checkpoint”);
hist.query();

if (hist.next()) {
var previousID = getChangePhase(hist.newvalue);
checkMetrics(previousID, hist.sys_created_on);
}
}

function getChangePhase(value) {
var gr = new GlideRecord(‘sys_choice’);
gr.addQuery(‘element’, ‘u_change_phase’);
gr.addQuery(‘table’, ‘change_request’);
gr.addQuery(‘value’, value);
gr.query();

if (gr.next()) {
return gr.label;
}
}

function checkMetrics(previousID, startDate) {
var mtc = new GlideRecord(‘metric_instance’);
mtc.addQuery(‘id’, current.sys_id + ”);
mtc.addQuery(‘table’, current.getTableName());
mtc.addQuery(‘field’, ‘u_change_phase’);
mtc.addQuery(‘value’, previousID);
mtc.query();

if (mtc.next()) {
var Start = new GlideDateTime(startDate);
var End = new GlideDateTime(current.sys_updated_on);
mtc.duration.setDateNumericValue(mtc.duration.dateNumericValue() + (End.getNumericValue() – Start.getNumericValue()));
mtc.update();

} else {
createMetric(previousID, startDate);
}
}

function createMetric(previousID, startDate) {
var gr = new GlideRecord(“metric_instance”);
gr.initialize();
gr.table = ‘change_request’;
gr.id = current.sys_id;
gr.definition = ‘2d160a31edf57c009a9a67085bd6aa3f’; // CHANGE THIS TO YOUR METRICS SYS_ID!!!!!
gr.start = startDate;
gr.end = current.sys_updated_on;
gr.duration = gs.dateDiff(startDate, current.sys_updated_on, false);
gr.value = previousID;
gr.calculation_complete = true;
gr.insert();
}
[/cc]

Now, to actually report on this metric, I used the following configuration:

This should create a nice report on the average time spent per change phase! (also applicable on e.g. change state)

If you have any questions, you can email me at .img[at].img.

Leave a Reply