Need Script in Metric Definition to calculate mean time to resolve excluding weekends for incidents

Devangi · ‎02-24-2021

Hi,

We have a requirement to create a metric definition script to calculate MTTR of Incidents resolved excluding weekends.

e.g if incident is created on Monday of first week and resolved on Friday of 3rd week. Calculation should exclude 2 weekends. MTTR should be 15 days not 21.

How can we achieve this?

Callum Ridley1 · ‎02-24-2021

Hi Devangi,

The first thing to mention is that a metric definition is not the right place to calculate the MTTR, however you can use the data collected in a Metric to calculate the MTTR.

What you are describing is the ability to calculate a metric based on a schedule. ServiceNow does not support calculation of Metrics based on a schedule out of the box, but it's absolutely possible to produce a reusable Script Include that will allow you to do this.

For anyone who's spent some time working with Metrics, they will probably have come across the MetricInstance Script Include. This Script Include simplifies the process of starting and stopping Metric Instance records. The good news is that we can use this Script Include as a basis for our own implementation that will take a schedule into account.

ServiceNow provide the GlideSchedule API that we can use to calculate the time difference between two date/time values when taking into account a schedule. With this in mind, we can apply a few simple updates to the MetricInstance Script Include that will allow the duration to be calculated against a schedule.

I've called my Script Include ScheduleAwareMetricInstance, but you can call it whatever you like. Here's the starting point of the Script Include.

var ScheduleAwareMetricInstance = Class.create();
ScheduleAwareMetricInstance.prototype = Object.extendsObject(MetricInstance, {

    type: 'ScheduleAwareMetricInstance'
});

First thing to notice here is the use of Object.extendsObject, what this allows us to do is extend the existing MetricInstance Script Include, without having to copy and paste / duplicate code on the platform. The second and very important thing to notice is the lack of an initialize method, this method is already defined in the MetricInstance Script Include and we automatically inherit it when we use Object.extendsObject.

Next we need a way of telling the code which schedule we want the resulting metric instance to calculate against, so we can create a setSchedule method, this will accept the sys_id of a schedule, and we will make sure it is a valid schedule record or log an error to the system log.

var ScheduleAwareMetricInstance = Class.create();
ScheduleAwareMetricInstance.prototype = Object.extendsObject(MetricInstance, {

	setSchedule: function(schedule_id) {
		var scheduleGR = new GlideRecord('cmn_schedule');
		if(scheduleGR.get(schedule_id))
			this.schedule_id = schedule_id;
		else
			gs.error(this.type + ': setSchedule method called with invalid schedule_id ' + schedule_id);
	},
	
    type: 'ScheduleAwareMetricInstance'
});

Now that we've set the schedule we want to calculate the metric against, we just need to do the work of calculating a duration against that schedule. The point that we will be running this calculation is at the point we record the end time of the metric. Looking at the MetricInstance script include, an endDuration method has already been supplied. To keep the api as similar as possible to the existing Script Include, we will override this method by defining it in our own Script Include.

var ScheduleAwareMetricInstance = Class.create();
ScheduleAwareMetricInstance.prototype = Object.extendsObject(MetricInstance, {

	setSchedule: function(schedule_id) {
		var scheduleGR = new GlideRecord('cmn_schedule');
		if(scheduleGR.get(schedule_id))
			this.schedule_id = schedule_id;
		else
			gs.error(this.type + ': setSchedule method called with invalid schedule_id ' + schedule_id);
	},

	endDuration: function() {
		var gr = new GlideRecord('metric_instance');
		gr.addQuery('definition', this.definition.sys_id);
		gr.addQuery('id', this.current.sys_id);
		gr.addQuery('calculation_complete', false);
		gr.query();
		if (!gr.next())
			return;

		gr.end = this.current.sys_updated_on;

		if(gs.nil(this.schedule_id)) {
			// in case the setSchedule method was not called or called with an invalid value, revert to the default calculation
			gr.duration = gs.dateDiff(gr.start.getDisplayValue(), gr.end.getDisplayValue());
		} else {
			// calculate the duration based on the schedule
			var schedule = new GlideSchedule(this.schedule_id);
			var startDate = new GlideDateTime(gr.start);
			var endDate = new GlideDateTime(this.current.sys_updated_on);
			gr.duration = schedule.duration(startDate, endDate);
		}

		gr.calculation_complete = true;
		gr.update();
	},

	type: 'ScheduleAwareMetricInstance'
});

That is all we need in our Script Include, so let's see how we can use this in a Metric Definition script to calculate the duration of the resolution time against the 8-5 weekdays schedule.

(function metricCalculation(definition, current) {
	
	var sami = new ScheduleAwareMetricInstance(definition, current);
	if(!sami.metricExists() && GlideFilter.checkRecord(current, 'state=6')) {
		// this uses the 8-5 weekdays schedule, you may want to create and use a 24 hour weekdays schedule
		sami.setSchedule('08fcd0830a0a0b2600079f56b1adb9ae');
		var metricInstance = sami.getNewRecord();
		metricInstance.setValue('start', current.getValue('sys_created_on'));
		metricInstance.insert();
		sami.endDuration();
	}
	
})(definition, current);

Outputs a Metric Instance with the following values, remember this is only an 8-5 weekdays schedule, you may want to create a 24 hour weekdays schedule. Notice how the duration is only 1 day and 12 hours but there is a 6 day gap between the start and end, this means our work has been successful.

If my answer is correct / helpful, please remember to mark it as such so that others can see the information as easily as possible.

Callum

View solution in original post

Callum Ridley1 · ‎02-24-2021