Calculating Metrics on Tickets before the Metric definition was created

[Carl Fransen1]

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]

Oh man, I love these questions because I've had to do this several times myself. 🙂

In a nutshell, you're going to have to query the audit history to pull the information from the table and create the metric instance records manually based on the timestamps of the activities in that history. So let's roll up our sleeves and get started.

I have a few questions first, though. You say that you're collecting metrics on how long an HR case takes to go from "New" to "Completed". I'm guessing from the context of the code snippet that the value 2 corresponds to the "Completed" state. What is the value of the "New" state?

Also, what is the name of the table for your HR cases? And before we get too deeply into this, is that table being audited? (That is, if you open up an HR case record, can you pull up its history by clicking on the form system menu and selecting History > List from the popup?) If it's not being audited, then we can't really go any further.

Last but not least, are you also going to want to store the business duration in addition to the calendar duration? Right now you're not collecting that, but if you want to, we can account for it retroactively and get your current script set up to do it as well.

--Dennis R

[Carl Fransen1]

Hi Dennis,

Thanks for your reply and willingness to help, appreciate it.  Firstly  yes I can see the 'History > List' from the case, so this seems to tick off the first hurdle.

The base table I'm running this off is the HR Case table, or sn_hr_core_case.  Thankfully the way SN have configured HR with the CoE, it automatically runs the metrics across all my other tables, which is nice.  However the other tables are listed below:

sn_hr_core_case_operations, sn_hr_core_case_relations, sn_hr_core_case_payroll, sn_hr_core_case_total_rewards, sn_hr_core_case_workforce_admin and u_sn_hr_core_case_remuneration_mobility_hr_reporting.

New State value is '1' and yes 'Completed' is '2'.

I do want to capture business duration, but wasn't sure how to script it - so help with that would be great for the metric and the historical records, if possible.

My other metric, as stated, are 'field value calculation' types, nothing out of the ordinary, I'm capturing 3 on the 'assignment group', 'assignee' and 'status' fields.  These i also need to try and capture all the historical information for.  I notice also that these standard new metrics the business duration is also not being calculated - i thought this might have been automatic, but alas.

Thanks

Carl.

[Dennis R · Accepted solution]

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

[Carl Fransen1]

Hi Dennis.

One question before I start testing this script - is this a separate background script I need to run, a new metric or a script I add to my existing metric? 

Also FYI - I need to move some of the metrics to my dev instance so this will take some time to generate data fo me to test with.

Thanks and have a great Easter.

Carl.