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

Carl Fransen1 · ‎08-27-2020

Hi vikrami,

Firstly you'll need to ensure you update the script with the actual 'metric_instance' name (not sys_id) you are trying to update, you can't use the name 'metric_instance', it doesn't refer to anything.

Secondly you'll also need to use the actual sys_id of the schedule you want to use, again you can't use the sys_id I provided as this is unique to my system.

Then you'll need to create a script for each Metric Instance you want to calculate the empty values for.

I don't know how to do for 10 test records, only that you could clone to your Dev and run the script across the metric instances, or export all your values and then run the script, if incorrect then just delete and load them back in. There is probably a way to do this via scripting, but thats not something I am clever enough to do myself, you could always check with Mike Patel, he has been really helpful to my queries over the years.

Cheers

Carl.

VShine · ‎08-27-2020

@Carl Fransen

I am sorry to keep bugging you!

I am trying to populate Duration field & not business duration. That is reason I have commented code for Business schedule.\

Should I be changing highlighted "metric_instance" with name of Metric instance?

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

Carl Fransen1 · ‎08-27-2020

My apologies - this refers to the table itself where it grabs the metric instances from.

Below is the scheduled job I run to grab these regularly and complete.

//  CJF Script from Community which grabs all records in the 'metric_instance' table
//  Then checks if calculation has ended, if true, calculates the business duration usign the 'New Zealand' schedule.
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('sys_id of schedule to use for calculation');
	// Get duration based on schedule
	gr.business_duration = gsBusiness.duration(gr.start.getGlideObject(), gr.end.getGlideObject());
	gr.setWorkflow(false); 
	gr.update();
}