Reporting on Incident State Duration Metric

[Ashok10]

Hi there,

I am trying to pull a report on Incident Metric table to find out the incident state durations.When an incident went through a state multiple times, there are multiple rows in the results which is expected.

Is there a way to get only one row for each incident and state combination by summing the durations. If you look at the screenshot there are two rows for Awaiting problem states, I want to combine them into one row. Can somebody help me on this please?

[Gurpreet07]

Hi Sai,

How we implemented this ...like created new field on incident , Total Duration... then created a after update Business Rule on incident and added condition , state changes to closed. In Business rule script we wrote a logic to get all metrics records for incident and sum up and store total duration. Then we used incident table for reporting purpose.

[kkswamy]

Hi Mr Gurpreet,

If you don't mind, can you please share the business rule script and how it is coming in the report after this script.

Regards,

KK.

[ThorS]

I was able to run a background script to perform the summary and provide an output; additionally the script will update your base records with the duration for each value in the field being durat(ed?). 

You should be able to use this for your business rule:

(function() {

    // === Configuration ===

    var metricDefSysId = '1234'; // Replace with your metric_definition sys_id

    var baseTableName = 'x_custombasetable'; // your base table name (lowercase)

    // Helper function to convert duration string (e.g., '1970-01-12 00:05:59') to seconds

    function durationStringToSeconds(durationStr) {

        if (!durationStr)

            return 0;

        var gdt = new GlideDateTime(durationStr);

        var epoch = new GlideDateTime('1970-01-01 00:00:00');

        var diffMs = gdt.getNumericValue() - epoch.getNumericValue();

        return Math.floor(diffMs / 1000);

    }

    // Maps to hold data

    var aggregatedData = {};    // { recordSysId: { _record_number: string, state1: durationDays, ... } }

    var uniqueStates = {};      // { stateName: true }

    var unmatchedIds = [];      // to log sys_ids not found in base table

    // Query metric_instance for the metric definition

    var gr = new GlideRecord('metric_instance');

    gr.addQuery('definition', metricDefSysId);

    gr.query();

    while (gr.next()) {

        var recordSysId = gr.getValue('id'); // Assuming this is sys_id of base record

        if (!recordSysId) {

            gs.info('metric_instance ' + gr.getValue('sys_id') + ' missing id field');

            continue;

        }

        // Parse duration string to seconds

        var rawDuration = gr.getValue('duration');

        var durationSeconds = durationStringToSeconds(rawDuration);

        // Convert seconds to days (float)

        var durationDays = durationSeconds / 86400;

        // Initialize aggregation object if needed

        if (!aggregatedData[recordSysId]) {

            aggregatedData[recordSysId] = {

                _record_number: null

            };

            // Get base record by sys_id

            var recGr = new GlideRecord(baseTableName);

            if (recGr.get(recordSysId)) {

                aggregatedData[recordSysId]._record_number = recGr.getValue('number') || 'UNKNOWN_NUMBER';

            } else {

                aggregatedData[recordSysId]._record_number = 'UNKNOWN';

                unmatchedIds.push(recordSysId);

            }

        }

        // Use 'value' field for the state

        var stateValue = gr.getValue('value') || 'UNKNOWN_STATE';

        uniqueStates[stateValue] = true;

        if (!aggregatedData[recordSysId][stateValue]) {

            aggregatedData[recordSysId][stateValue] = 0;

        }

        aggregatedData[recordSysId][stateValue] += durationDays;

    }

    // Build CSV header

    var stateList = Object.keys(uniqueStates).sort();

    var csvHeader = ['Record Number'].concat(stateList.map(function(s){ return '"' + s + '"'; })).join(',');

    var csvRows = [];

    csvRows.push(csvHeader);

    for (var recId in aggregatedData) {

        if (!aggregatedData.hasOwnProperty(recId)) continue;

        var recData = aggregatedData[recId];

        var row = [];

        row.push('"' + (recData._record_number || '') + '"');

        for (var i = 0; i < stateList.length; i++) {

            var st = stateList[i];

            var dur = recData[st] || 0;

            // Round to 2 decimals

            dur = Math.round(dur * 100) / 100;

            row.push(dur);

        }

        csvRows.push(row.join(','));

    }

    var csvOutput = csvRows.join('\n');

    gs.info('=== Duration (in days) per State per Custom Base Record ===');

    gs.info(csvOutput);

    if (unmatchedIds.length > 0) {

        gs.info('=== Unmatched sys_ids (not found in base table) ===');

        unmatchedIds.forEach(function(id) {

            gs.info(id);

        });

    }

// === Map state names to base record duration field names ===

// Adjust these mappings to your actual state names and corresponding field names on x_custombasetable

var stateToFieldMap = {

    "Work in Progress": "duration_work_in_progress",

    "Complete": "duration_complete"

    // Add all your state-to-field mappings here

};

// Now update each base record with summed durations

for (var recSysId in aggregatedData) {

    if (!aggregatedData.hasOwnProperty(recSysId)) continue;

    var recData = aggregatedData[recSysId];

    // Load the base record

    var recGr = new GlideRecord(baseTableName);

    if (recGr.get(recSysId)) {

        // For each state, set the corresponding duration field

        for (var stateName in stateToFieldMap) {

            if (!stateToFieldMap.hasOwnProperty(stateName)) continue;

            var fieldName = stateToFieldMap[stateName];

            var durationDays = recData[stateName] || 0;

            // Update the field with rounded duration (days)

            recGr.setValue(fieldName, Math.round(durationDays * 100) / 100);

        }

        recGr.update();

    }

}

})();