How to create a metric report on Change Request to calculate the duration between all the state?

[Shantharao]

Hi All,
How to create a metric report on Change Request to calculate the duration between all the states?
How to calculate the average time between the Assess to Schedule state?

Thanks

[Shruti]

Hi,

Navigate to Metrics-> Definitions

Create a new metric

For reporting, create a list report on 'metric_instance' table and filter it by the new metric definition 

[Shantharao]

Hi Shruthi,

Thanks for the response, I am getting individual state duration but I want to get the average time taken between Assess and Scheduled phase duration

Thanks in advance for your inputs on the above scenario

[Shruti]

Hi @Shantharao 

Create a metric definition and use below script and replace 'aeba94c59711f1103afb3d400153af44' with the sys_id of metric definition you created. Create a new change request record and update the state to test it

if (current.state == -4) {
    createMetric();
} else if (current.state == -2) {
    updateMetric();
}

function createMetric() {
    var mi = new MetricInstance(definition, current);
    if (!mi.metricExists()) {
        var v_gr1 = mi.getNewRecord();
        v_gr1.start = current.sys_updated_on;
        v_gr1.value = current.getDisplayValue('state');
        v_gr1.calculation_complete = false;
        v_gr1.insert();
        return;
    }
}

function updateMetric() {
    var mi = new MetricInstance(definition, current);
    if (mi.metricExists()) {
        var v_gr2 = new GlideRecord('metric_instance');
        v_gr2.addQuery('id', current.sys_id);
        v_gr2.addQuery('definition', 'aeba94c59711f1103afb3d400153af44');  // replace 'aeba94c59711f1103afb3d400153af44' with the sys_id of metric definition you created
        v_gr2.addQuery('calculation_complete', false);
        v_gr2.orderByDesc('sys_created_on');
        v_gr2.query();

        if (v_gr2.next()) {
            v_gr2.end = current.sys_updated_on;
            v_gr2.value = current.getDisplayValue('state');
            v_gr2.duration = gs.dateDiff(v_gr2.start.getDisplayValue(), v_gr2.end.getDisplayValue());
            v_gr2.calculation_complete = true;
            v_gr2.update();
        }
        return;
    }
}

[Shantharao]

Hi Shruti,

Thank you for the response there is one more scenario client is asking for
For example, when a change is rejected change will move back into the New phase and duplicate metric instances are generated for a unique change request record,
So they want a report to calculate the cumulative duration average time spent in the Asess / Authorize / Schedule phases like that

Screenshot for your reference