Calculating Metrics on Tickets before the Metric definition was created

Carl Fransen1 · ‎03-26-2018

Hi All,

I've created a number of metric definitions for HR Cases - most are 'field value calculation' where we just measure the state a case was in, the assignee or the assignment group. I've also created one 'Script Calculation' to find the time difference between 'New' and 'Completed', which is just a copy of the Incident OOTB one, see below:

// CJF 16-03-2018 Added metric to capture how log it takes to complete a HR Case - copied from Incident Script
// variables available
// current: GlideRecord -  target incident
// definition: GlideRecord -  (this row)
var s = current.state;
if (s == 2)
  createMetric();

function createMetric() {
  var mi = new MetricInstance(definition, current);
  if (mi.metricExists()) 
    return; 

  var gr = mi.getNewRecord();
  gr.start = current.sys_created_on;
  gr.end = current.sys_updated_on;
  gr.duration = gs.dateDiff(gr.start.getDisplayValue(), gr.end.getDisplayValue());
  gr.calculation_complete = true;
  gr.insert();
}

These all work fine and are measuring things as needed, however this only calculate for the new HR Cases - any existing cases are not included. We went live back in July last year and keen to get all the historical information included as well.

Is there any way (maybe a background script) I can get the existing cases to create metric instances for the metric?

I had a look at this post, but my scripting skills aren't upto translating this to my needs.

Can anyone out there help?

Thanks

Carl.

Dennis R · ‎03-27-2018

Okay, I'm going to tackle the business duration thing first, because it's actually the easier of the two since I have that one already running in many of my own metric definitions. I'll work on the fix script to insert and update the other metrics retroactively as soon as I can, so stay tuned.

STRONG caveat: Please, please, please make sure you test any code you get from here, including mine, in a lower instance before blasting it out to production!!!

I'm going to assume that you have a schedule entry [cmn_schedule] defined called "Company Business Hours". If you don't, you'll need to create one and add... well, your company business hours, of course, to it as a repeating time span. You can get as fancy as you want to with this and add holidays and whatnot if you want. If you want to call your schedule something else, feel free to set the name at the top of the script below, or even better, feed the sys_id of the schedule directly into the new GlideSchedule call so that if you rename your schedule, it won't screw up your script.

But one important note because this is hopelessly confusing to a lot of my coworkers: Business duration "days" are NOT the same as calendar days! Here's an example:

A Brief Diversion: Business Durations

Let's say that your business day runs from 9:00am until 5:00pm, which is eight hours long. If you see a business duration of 2 days, 9 hours, and 31 minutes that started at, say, 2:37pm on Tuesday, when is the end of that time span? Most people would guesstimate around 4:00pm on Friday, but this is not even close. The answer is at 4:08pm a week from Thursday, almost a week later!

How's that? Because 2 business days is actually 48 business hours, plus 9 hours and 31 minutes, which comes to 57 business hours, 31 minutes. Since a business day is eight hours long, that works out to 7 calendar days, plus an hour and 31 minutes. Since 7 calendar days will always span a weekend, that adds another two calendar days to the total, making it 9 days, 1 hour, 31 minutes in calendar time.

The Guts: The Metric Definition Script

With that having been said, here's the script that should work to record the business duration. Note that I didn't use the MetricInstance script include because, frankly, I don't like it. It really needs a way to get an existing metric instance without creating a new one, but I don't like modifying out-of-the-box script includes, so when I deal with metrics, I very rarely use the MetricInstance script include and populate the fields manually. I've commented the script so that hopefully everything is kind of self-explanatory, but if you have any questions, by all means, let me know. If you grok this script, then when I write up the retroactive metric population script, it will be a lot more easy to see what I'm doing.

/**
 * Creates or updates the HR Case Completion metric based on the time a case
 * is opened until it is closed (state is 2).
 */
(function evaluateMetric(current, definition) {
    var scheduleName = 'Company Business Hours';
    
    // Try to fetch an existing metric, if one exists
    var grMetric = new GlideRecord('metric_instance');
    grMetric.addQuery('id', current.getUniqueValue());
    grMetric.addQuery('definition', definition.getUniqueValue());
    grMetric.query();
    
    if (!grMetric.next()) {
        // If the metric doesn't exist, create it
        grMetric = new GlideRecord('metric_instance');
        grMetric.newRecord();
        grMetric.table = 'sn_hr_core_case';
        grMetric.field = 'state';
        grMetric.definition = definition.getUniqueValue();
        grMetric.id = current.getUniqueValue();
        grMetric.start = current.opened_at;
        grMetric.calculation_complete = false;
    }

    // Regardless of whether this is a new record or an existing one, we want
    // to update the field values to reflect the current state.
    grMetric.field_value = current.getValue('state');
    grMetric.value = current.getDisplayValue('state');

    if (current.state == '2') { // Completed
        var gdtStart = new GlideDateTime(current.opened_at);
        var gdtEnd = current.closed_at.nil()
                ? new GlideDateTime()
                : new GlideDateTime(current.closed_at);
        
        // Calculate calendar duration
        var gdurCalendar = GlideDateTime.subtract(gdtStart, gdtEnd);
        
        // Calculate business duration
        var gdurBusiness = new GlideDuration(gdurCalendar);
            // If we can't find a valid schedule, use the calendar duration
        
        // If you know the sys_id of your schedule, it would probably be a bit
        // more efficient and less risky (in case your schedule name changes)
        // to replace the following five lines and use it directly in a couple
        // of lines such as:
        //
        // var gsBusiness =
        //         new GlideSchedule('0123456789abcdef0123456789abcdef');
        // gdurBusiness = gsBusiness.duration(gdtStart, gdtEnd);
        
        var grBusiness = new GlideRecord('cmn_schedule');
        if (grBusiness.get('name', scheduleName)) {
            var gsBusiness = new GlideSchedule(grBusiness.getUniqueValue());
            gdurBusiness = gsBusiness.duration(gdtStart, gdtEnd);
        }
        
        grMetric.calculation_complete = true;
        grMetric.end = gdtEnd;
        grMetric.duration = gdurCalendar;
        grMetric.business_duration = gdurBusiness;
    }
    
    grMetric.isNewRecord() ? grMetric.insert() : grMetric.update();
})(current, definition);

Now that you have the business duration recorded, you can provide averages in reports and show trends in performance analytics widgets that takes out weekends and (optionally) holidays when people aren't working on stuff in their calculations, woot!

You can go nuts with this kind of stuff if you want. For example, while our company hours are officially 9:00am - 5:00pm Eastern, our Change Management team actually works 8:00am - 6:00pm (10 business hours) every day. They wanted their OLAs and metrics to reflect that expanded time, so I have a schedule called "Change Management Hours" against which most of their OLAs and metrics are tracked instead of the standard company hours.

Hope this helps,

--Dennis R

View solution in original post

VShine · ‎08-27-2020

@ Carl Fransen What about the "Calculating Duration for records before Metrics Definition was created"? DId you get answer on that?

Carl Fransen1 · ‎08-27-2020

Yes I did - it's in in one of my other posts:

https://community.servicenow.com/community?id=community_question&sys_id=14cb581ddbdd1f083882fb651f96...

It's about half-way down, a post from Mike Patel - see below, there are some other posts after that which correct an error so make sure you read those.

var gr = new GlideRecord("metric_instance");
gr.addEncodedQuery('end!=NULL^business_duration=NULL');
gr.autoSysFields(false); // so that the records don't have system updates
gr.query();
while(gr.next()) {
	var gsBusiness =new GlideSchedule('dcf6c8fedb5d7e007d1bfa0dbf9619db');
	// Get duration based on schedule
	gr.business_duration = gsBusiness.duration(gr.start.getGlideObject(), gr.end.getGlideObject());
	gr.setWorkflow(false); 
	gr.update();
}

You can also try this one if my link doesn't work for you:

https://community.servicenow.com/community?id=community_question&sys_id=33d0d7a9dbdcdbc01dcaf3231f9619e9

Cheers

Carl.

VShine · ‎08-27-2020

@Carl Fransen

Thank you so much for your help.

I am trying to update Duration with this background script..... We have lot of Metrics in Metric definition (global & HR scope). I only wanted to run for HR scope....but when I am adding encoded query with "definition name", I am not getting any older records.

var gr = new GlideRecord("metric_instance");
var count = new GlideRecord("metric_instance")
gr.addEncodedQuery('end!=NULL^duration=NULL');
count.setLimit(10);
gr.autoSysFields(false); // so that the records don't have system updates
gr.query();
while(gr.next()) {
//var gsBusiness =new GlideSchedule('dcf6c8fedb5d7e007d1bfa0dbf9619db');
// Get duration based on schedule
var duration;
gr.duration = gs.duration(gr.start.getGlideObject(), gr.end.getGlideObject());
gr.setWorkflow(false);
gr.update();
}

I want to test run on 10 records first..... how do I validate that 10 records are updated? I am getting this in Script execution block.

Mike Patel · ‎08-27-2020

try running below

var gr = new GlideRecord("metric_instance");
gr.addEncodedQuery('end!=NULL^duration=NULL');
gr.setLimit(10);
gr.autoSysFields(false); // so that the records don't have system updates
gr.query();
while (gr.next()) {
    //var gsBusiness =new GlideSchedule('dcf6c8fedb5d7e007d1bfa0dbf9619db');
    // Get duration based on schedule
    var duration;
    gr.duration = gs.duration(gr.start.getGlideObject(), gr.end.getGlideObject());
    gr.setWorkflow(false);
    gr.update();
}

VShine · ‎08-28-2020

@ Mike Patel

Script working perfect...Its calculating total Duration for all HR cases. Where we are mentioning Metric definition? Is there a way we can calculate different field value metrics for all existing cases.