Calculate total 'on hold' duration of incident and add it as a custom field in the form

[MaryJohn]

Hi,

We have a requirement to display the total time spent in 'on hold' state of an incident and display its value as a custom field in the incident form. I tried creating a 'duration' type field with calculated value using below script, but it does not seem to display the total duration calculated. However, when i use the same script on a 'string' type field, it populates the value. Can someone please help in identifying why 'duration' type field isn't displaying the value or where I am going wrong and how can I fix this.  The duration is ideally required to be populated in day/hour/min format so its easily understandable by users. 

Script :

(function calculatedFieldValue(current) {

var dur=0;

var gr = new GlideRecord('metric_instance');

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

gr.addEncodedQuery('definition.name=On Hold Duration^calculation_complete=true');

gr.query();

while(gr.next())

{

dur=dur+gr.duration.dateNumericValue()/(60*60*1000); //duration converted from millisecs to hours

}

return dur;

})(current);

[Dr Atul G- LNG]

Hi @MaryJohn 

There is a field on the SLA table which capture the on-hold duration and total onhold, try to use that.

[MaryJohn]

Thanks for replying @Dr Atul G- LNG ,

I tried to find the 'total onhold' field in sla table (both task sla and incident sla table), i am unable to find it. Could you please elaborate. Also, any idea why my code is not adding the total duration to the custom field in incident form i created?

[MaryJohn]

Thanks for replying @Dr Atul G- LNG 

I tried to find ' total onhold' field as you suggested in both incident sla and task sla tables, but unable to find it. Could you please elaborate let me know where i can find it.
Also, any idea why my code is not displaying the total value in the custom field I created?
Thank you!

[MaryJohn]

Thanks for replying @Dr Atul G- LNG,

I tried to find 'total onhold' field on both incident SLA and task SLA tables, but I am unable to find it.
Could you please elaborate. Also, any idea why my code is not displaying the total duration in the custom field I created, are there any corrections required?