Metrics - How to calculate the total duration if status changes from one state to other multiple times

[bhargavi7]

Hi all,

How to calculate total duration ,If we change state from Work in progress to closed.

Scenario is State changed from work in progress to suspended, after that it moved to Work in progress again and moved from work in progress to closed.So,it is in work in progress state 2 times.In metrics,it is creating two records .How to show total duration in duration field through metric scripting without creating new field.

I tried all the script which is in community but not working.

Can anyone share any script 

Thanks in advance.

[Gaurav Banga]

Try solution mentioned in the below blog:

https://community.servicenow.com/community?id=community_question&sys_id=c42fbe29db58dbc01dcaf3231f96...

Thanks,

Gaurav

[T_Prashaanth · Accepted solution]

Hi bhargavi ,

This Can be achieved using the following script . Basically from In progress - to - Resolved  and then again if it is moved to In Progress and then to resolved. It will update the same Metric record -

var state = current.state;
if (state == '2' || state == '6') // checking if the state is in Progress or Resolved
timeDurations(state);

function timeDurations(value) {
var mi = new MetricInstance(definition, current);
if (mi.metricExists() && value == '6') { // Triggered when the state changes to resolved and update the duration
var gr = new GlideRecord('metric_instance');
gr.addQuery('id', current.sys_id);
gr.addQuery('definition', '6e87e4d31b03301015cbc991604bcbb1');
gr.orderByDesc('sys_created_on');
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();
}
return;
}

if (!mi.metricExists() && value == '2') { // Triggered when the state changes in Progress for the first time
gs.log('hii');
var gr1 = mi.getNewRecord();
gr1.start = current.sys_updated_on;
gs.log('hii' + current.sys_updated_on);
gr1.calculation_complete = false;
gr1.insert();
return;
}

if (mi.metricExists() && value == '2') { //Triggered when the state changes back to in progress from any other state then update the start time with a value of (Updated or Current Time ) Minus the (Duration)
var gr2 = new GlideRecord('metric_instance');
gr2.addQuery('id', current.sys_id);
gr2.addQuery('definition', '6e87e4d31b03301015cbc991604bcbb1');
gr2.orderByDesc('sys_created_on');
gr2.query();
if (gr2.next()) {
var gdt = new GlideDateTime(current.sys_updated_on.getDisplayValue());
gs.info('Now Time =' + gdt);
var ms = gr2.duration.dateNumericValue();
gs.info('Now Time Duration =' + gr2.duration);
gdt.add(-ms);
gr2.start = gdt.getValue();
gs.info('Now Time Updated =' + gdt.getValue());
gr2.end = '';
gr2.duration = '';
gr2.calculation_complete = false;
gr2.update();
}
return;
}

}

Please , mark my solution as correct , if it is helpful

[bhargavi7]

Hi Prashanth,

Thank you so much for the script.

I have replaced the state values with my requirement values,changed the metric definition sys id with mine in 10th line and tried with field value duration and script evaluation in metric definition.But it is not working.i created new cases and checked but still showing no records to display.This is urgent requirement.Could you please let me know what is the issue

Thanks,

Bhargavi.

[T_Prashaanth]

Hi @bhargavi, Can you please, provide screenshot / paste your code here, so that I can review it once?