Calculate Average Business Elapsed Time

[jonathangilbert]

Hi, 

I have a requirement to work out the average "business elapsed time" which is found on the task_sla table for the last 7 days against each sla definition's and then update a field on the associated SLA Definition with the average showed as a duration.

I found the below script on the forum that works when using the incident "resolve time" on the incident table, which I run as a scheduled job:-

var gr = new GlideRecord('incident');

gr.addEncodedQuery("numberSTARTSWITHINC^resolved_atONLast

7

days@javascript:gs.beginningOfLast7Days()@javascript:gs.endOfLast7Days()^priority=3");

gr.query();

var count = 0;

var sum = 0;

while (gr.next()) {

var ram = gr.calendar_stc - 0;

count++;

sum += ram;

}

var avg = (sum/count/60);

//gs.log('Total: ' + sum);

//gs.log('Num: ' + count);

//gs.log('Average: ' + (sum/count/60));

var time = avg;

var result = Math.floor(time/24/60) + ":" + Math.floor(time/60%24) + ':' + Math.floor(time%60)+':'+Math.floor((time%60)%60);

var art = new GlideRecord('contract_sla');

art.addEncodedQuery("sys_id=baa52649db64b20089f4fda41d96192a");

art.query();

while (art.next()) {

art.u_average_7_day_resolution_time = result;

art.update();

}

But when I update the script to run on the task_sla table and use the "business elapsed time" (business_duration) field, the script just returns NaN against count and average

var gr = new GlideRecord('task_sla');

gr.addEncodedQuery("sla=baa52649db64b20089f4fda41d96192a^u_resolvedONLast

7

days@javascript:gs.beginningOfLast7Days()@javascript:gs.endOfLast7Days()");

gr.query();

var count = 0;

var sum = 0;

while (gr.next()) {

var ram = gr.business_duration - 0;

count++;

sum += ram;

}

var avg = (sum/count/60);

//gs.log('Total: ' + sum);

//gs.log('Num: ' + count);

//gs.log('Average: ' + (sum/count/60));

var time = avg;

var result = Math.floor(time/24/60) + ":" + Math.floor(time/60%24) + ':' + Math.floor(time%60)+':'+Math.floor((time%60)%60);

var art = new GlideRecord('contract_sla');

art.addEncodedQuery("sys_id=baa52649db64b20089f4fda41d96192a");

art.query();

while (art.next()) {

art.u_average_7_day_resolution_time = result;

art.update();

}

Both the "incident resolve time" and "business elapsed time" are OOB fields and are both "durations"

attatched is the error as a text file when testing the script as a "background script"

Any ideas 

[Sebas Di Loreto]

I think you have an error on your encoded query... what is u_resolved in the task_sla table?

It should be something like task.resolved_at

var gr = new GlideRecord('task_sla');

gr.addEncodedQuery("sla=baa52649db64b20089f4fda41d96192a^u_resolvedONLast

7

days@javascript:gs.beginningOfLast7Days()@javascript:gs.endOfLast7Days()");

gr.query();

[jonathangilbert · Accepted solution]

Hi Sebastian

Thanks for the reply. I have actually got my script working by updating one of the lines of code from:- 

var ram = gr.business_duration - 0;

to 

var ram = gr.business_duration.dateNumericValue() / 1000 - 0;

This takes the duration value and converts it to milliseconds. The rest of my script then works as expected

Many Thanks anyway for trying to help