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]

Sorry @MaryJohn 

its pause duration

[Bert_c1]

@MaryJohn 

If you look at how duration fields are stored, you see they are a value the represent number of seconds since '1970-01-01 00:00:00'.  For example:

<duration>1970-01-01 00:15:53</duration>

this value is 15 minutes and 53 seconds.  Duration fields are tricky to work with. Such as adding multiple values as you first posted. The available API is here:

DurationCalculatorAPI

I've tested the following script logic for metric_instance:

var mIns = new GlideRecord('metric_instance');
mIns.addEncodedQuery('definition=db763699833f5e102d24fa96feaad3d4^value=On Hold^calculation_complete=true');
mIns.query();
var sum = 0;
var epoch = new GlideDateTime('1970-01-01 00:00:00');
while (mIns.next()) {
	var dur = mIns.duration.toString();
	gs.info('epoch: ' + epoch + ', dur: ' + dur);
	// need to get total seconds and API uses a schedule
	var dc = new DurationCalculator();
	dc.setSchedule('38fa64edc0a8016400f4a5724b0434b8');		//24x7
	dc.calcScheduleDuration(epoch, dur);
	sum += dc.getTotalSeconds();
	gs.info('total seconds: ' + dc.getTotalSeconds());
	
}
// convert to Duration value
gs.info('sum: ' + sum + ', UTC Offset:' + epoch.getTZOffset());
var TZOffset = epoch.getTZOffset();
epoch.add(sum*1000);
gs.info('sum: ' + sum*1000 + ', TZOffset: ' + TZOffset + ', total: ' + ((sum*1000) + TZOffset));
gs.info('dur: ' + epoch.getDisplayValue());
// now add tz offset
epoch.add(-1*TZOffset);
gs.info('TZ adjusted dur: ' + epoch.getDisplayValue());

// GlideDateTime value to load a Duration field
// var dur = new GlideDateTime(epoch.getDisplayValue());
// return dur;

You can replace my query (lines 1-3) with your own for testing use in a Calculated field definition (uncomment the last two lines). And you can comment out or remove the 'gs.info();' lines used to debug.

[Anushiya Preeth]

Thank you for the solution @Bert_c1. This is really helpful!

[Bert_c1]

You're Welcome @Anushiya Preeth , I hope this is useful to @MaryJohn , it took some time to get it right.  Maybe it'll work in a Calculated field definition, the key is when it desired to run. a Client script/script include can work. a Business rule defined on the metric_instance with appropriate filter conditions can also work. And a fix script using the same logic can work.