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

Dennis R · ‎03-29-2018

It's a drop-in replacement for the metric script you have in the OP.

Sorry I haven't gotten to the fix script to retroactively populate the data, I'll try to do that tonight or tomorrow.

--Dennis R

Carl Fransen1 · ‎04-02-2018

Hi Dennis - thanks for the update - started testing this morning in DEV and it is looking great, so will continue over the next few days to confirm it's all working as expected. Really appreciate this.

Carl Fransen1 · ‎04-15-2018

Hi Dennis,

Hoping you've had a chance to work on the fix script....?

Thanks

Carl.

James Rostron1 · ‎10-15-2019

Hi Dennis,

I know this was some time ago put together but the level of detail and explanation is fantastic.

I'm new to scripting so I just wanted to understand how I can implement the actual script itself. (Schedules are already setup).

Should this be in a business rule or somewhere else?

Many thanks,

James

Carl Fransen1 · ‎05-08-2018

Thanks fo all your help Dennis - I did receive another reply on a different question I asked, which also fixes the historical information for this question.

The script to get this working is in the following thred:

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

Thanks again to all who helped.

Cheers

Carl.