Summing metric instance durations

[jcmings]

I have a custom duration field set up on my case table and I want to grab & sum all of the metric_instance records for that case when it is in the "Ready" or "Work in Progress" statuses. I'm able to GR query metric_instance for the correct record, but am struggling to sum together the duration fields. I've looked at quite a few Community articles and haven't found one that works yet. Does anyone have any ideas?

To illustrate what I'm looking for:

• Metric Instance Table:
  • Case A was in Ready for 5 min
  • Case A was in Work in Progress for 5 min
  • Case A was in Ready (again) for 5 min
  • Case A is now in Closed
• Case table:
  • Custom_Total_Duration_Field: 15 min <---- this is what I'm trying to set, but am failing with

I've been using an After Update BR that triggers when State changes to Closed, Closed Complete, or Closed Incomplete. I have to use Metric Instance because we do not want to account for when the case is in Draft or Suspended (otherwise I would just take Closed Date minus Opened Date).

Thanks!

[Bert_c1 · Accepted solution]

Hi,

The following will give you total seconds, that can be converted to days, hours, minutes, seconds.

var casemi = new GlideRecord('metric_instance');
casemi.addEncodedQuery('id=20e76753b7b023002bc49a91ee11a947');  // my test record
casemi.query();
var durSeconds = 0;
while (casemi.next()) {
//	if ((casemi.value != '') && (casemi.value != 'Closed')) {
	if ((casemi.value != '') && (casemi.calculation_complete)) {
		gs.info("ID: " + casemi.id.getDisplayValue() + ", value: " + casemi.value + ", start: " + casemi.start + ", end: " + casemi.end);
		var caseDur = new DurationCalculator();
		caseDur = new DurationCalculator();
		caseDur.calcScheduleDuration(casemi.start, casemi.end);
		var secs = caseDur.getSeconds();
		var totalSecs = caseDur.getTotalSeconds();
		gs.info("***SCHEDULE DURATION: SECS=" + secs + " TOTALSECS=" + totalSecs);
		durSeconds += secs;
	}
}
gs.info("total duration values: " + durSeconds);

Decide if logic for 'value' meets your needs, adjust as desired. The key here is using the DurationCalculator:

https://developer.servicenow.com/dev.do#!/reference/api/utah/server_legacy/c_DurationCalculatorAPI?n...

good luck.

[Bert_c1 · Accepted solution]

Hi,

The following will give you total seconds, that can be converted to days, hours, minutes, seconds.

var casemi = new GlideRecord('metric_instance');
casemi.addEncodedQuery('id=20e76753b7b023002bc49a91ee11a947');  // my test record
casemi.query();
var durSeconds = 0;
while (casemi.next()) {
//	if ((casemi.value != '') && (casemi.value != 'Closed')) {
	if ((casemi.value != '') && (casemi.calculation_complete)) {
		gs.info("ID: " + casemi.id.getDisplayValue() + ", value: " + casemi.value + ", start: " + casemi.start + ", end: " + casemi.end);
		var caseDur = new DurationCalculator();
		caseDur = new DurationCalculator();
		caseDur.calcScheduleDuration(casemi.start, casemi.end);
		var secs = caseDur.getSeconds();
		var totalSecs = caseDur.getTotalSeconds();
		gs.info("***SCHEDULE DURATION: SECS=" + secs + " TOTALSECS=" + totalSecs);
		durSeconds += secs;
	}
}
gs.info("total duration values: " + durSeconds);

Decide if logic for 'value' meets your needs, adjust as desired. The key here is using the DurationCalculator:

https://developer.servicenow.com/dev.do#!/reference/api/utah/server_legacy/c_DurationCalculatorAPI?n...

good luck.

[jcmings · Accepted solution]

Thank you @Bert_c1 ! This was very helpful. For those that come along in the future, I wanted to share the code I added to update the duration.

To start, I added a short delay in my (scoped) business rule. When testing, I noticed that metric_instance and my case record updated simultaneously, and my most recent metric_instance record would not get recorded.

 After the delay, I added Bert's code, changing the queries to match what I was looking for (my metric definition, the two states I wanted to capture, and calculation complete = true).

Edit: I ended up changing my business rule to exist on metric_instance and fire after insert of a record containing the Value "Close". Previously, I had a wait condition in my business rule on the case table, but the three second delay I set did not account for longer delays in the event queue (where the magic happens behind the scenes to actually trigger a metric_instance record insert), meaning sometimes the BR wouldn't work as intended. Since the record creation on metric_instance is the real trigger we're looking for, it makes the most sense for the BR to live on there.

Inside the BR, I used Bert's code. Recursion is not an issue here because the GlideRecord Query doesn't actually update or insert anything. 

I then used the following script to calculate the duration (shoutout to my colleague). Since duration really is just the difference between two dates, I set two variables equal to the same dates, and then added the value Bert's code calculated to the second (after converting to milliseconds). 

var milisec = (durSeconds * 1000); 
	var gdt_start = new GlideDateTime("1980-01-01 00:00:00"); 
	var gdt_end = new GlideDateTime("1980-01-01 00:00:00"); 
	
	gdt_end.add(milisec); 
	
	var dur = GlideDateTime.subtract(gdt_start, gdt_end);

Below that,  I wrote a GlideRecord Query on my case table, looking for the sys_id of the case that is equal to the current metric's ID (not sys_id, but ID). In the gr.next() container, I set my duration field equal to dur and then did a gr.update()! Since this is an after BR, it's fine to call that update function.

Hope this helps anyone down the line!

[Smashley]

Hi @jcmings, could you possibly share the GlideRecord query you wrote to get the matching Case sys_id? I'm super stuck at this point. Thanks!!

[jcmings]

Hi @Smashley, sure thing, see below. 

// HR Case GlideRecord Query and Update
	var hrgr = new GlideRecord('sn_hr_core_case');
	hrgr.addEncodedQuery('stateIN3,4,5'); //Closed Complete, Closed Incomplete, Closed-Transf
	hrgr.addQuery('sys_id', current.id); //HR sys ID = current metric ID
	hrgr.query();
	if (hrgr.next()) { // "If" because there will only be one HR case record
		hrgr.u_total_resolution_time = dur;
		hrgr.update();
	}