Create metric definition to calculate total time taken excluding on hold duration

[Anushiya Preeth]

Hi All,

Requirement is to create a metric definition to calculate the total time taken from open to resolve state excluding on hold duration. The on-hold duration is to be considered only when certain 'on hold reasons' are selected where of some them pause the sla and the others don't (I have created a custom metric definition to track on hold duration only for specific on hold reasons). I tried to create the metric definition using task_sla table by adding a query on pause stage but it doesn't seem to work, as the on hold reasons we are looking for doesn't necessarily pause the SLA. Can someone please help me with the code that can fulfil my requirement. Thank you!

[Ankur Bawiskar]

@Anushiya Preeth 

try creating this script include and see if it works from background script and then enhance

var CalculateTotalTime = Class.create();
CalculateTotalTime.prototype = {
    initialize: function() {},

    getTotalTime: function(taskSysId) {
        var totalTime = 0;
        var taskGR = new GlideRecord('task');
        if (taskGR.get(taskSysId)) {
            var openTime = taskGR.opened_at.getGlideObject();
            var resolveTime = taskGR.resolved_at.getGlideObject();
            totalTime = GlideDateTime.subtract(resolveTime, openTime).getNumericValue();

            var onHoldGR = new GlideRecord('task_sla');
            onHoldGR.addQuery('task', taskSysId);
            onHoldGR.addQuery('stage', 'paused');
            onHoldGR.query();
            while (onHoldGR.next()) {
                if (this.isSpecificOnHoldReason(onHoldGR.on_hold_reason)) {
                    var onHoldStart = onHoldGR.start_time.getGlideObject();
                    var onHoldEnd = onHoldGR.end_time.getGlideObject();
                    var onHoldDuration = GlideDateTime.subtract(onHoldEnd, onHoldStart).getNumericValue();
                    totalTime -= onHoldDuration;
                }
            }
        }
        return totalTime;
    },

    isSpecificOnHoldReason: function(onHoldReason) {
        var specificReasons = ['Reason1', 'Reason2', 'Reason3']; // Add your specific on-hold reasons here
        return specificReasons.indexOf(onHoldReason.toString()) !== -1;
    },

    type: 'CalculateTotalTime'
};

background script

var calcTotalTime = new CalculateTotalTime();
var totalTime = calcTotalTime.getTotalTime(incidentRecordSysId);
gs.info(totalTime);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Anushiya Preeth]

Thank you @Ankur Bawiskar, I will try this script. 

[Ankur Bawiskar]

@Anushiya Preeth 

Did that work?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Anushiya Preeth]

Hi Ankur, 
I went ahead and created metric definition which excludes on-hold state duration for the specific on-hold reasons which users can utilize for reporting purpose. I really appreciate your help, thank you!

Providing the script below for reference to anyone looking to create similar metric definition.

var mi = new MetricInstance(definition, current);

var incState = current.incident_state;

var holdReason = current.hold_reason;

// Function to create a new metric instance

function createMetric() {

   var gr = mi.getNewRecord();

   gr.start = current.sys_updated_on;

   gr.value = current.getDisplayValue('incident_state');

   gr.insert();

}

// Function to update the end time and duration of the metric instance

function updateEndTimeMetrics() {

   var gr = new GlideRecord('metric_instance');

   gr.addQuery('id', current.sys_id);

   gr.addQuery('definition', definition.sys_id);

   gr.addQuery('calculation_complete', false);

   gr.query();

   if (gr.next()) {

       gr.end = current.sys_updated_on;

       gr.duration = gs.dateDiff(gr.start.getDisplayValue(), gr.end.getDisplayValue());

       gr.calculation_complete = true;

       gr.update();

   }

}

// Update end time for the existing metric instance if it exists

if (mi.metricExists()) {

    updateEndTimeMetrics();

}

// Create a new metric instance if the incident is not in the "on hold" state with specific reasons

if (!(incState == 3 && (holdReason == 1 || holdReason == 6 || holdReason == 7)) && !(incState >= 6)) {

    createMetric();

}