AVG value being calculated is incorrect

[divvi_vamsi]

I am trying to run a glideAggregate for Average value on syslog_transaction table for response_time for last few minutes. But i observe the value being returned is the MAX value instead of AVG of all the records within that time range.

Not sure what is being missed. Below is the script:

var count = new GlideAggregate('syslog_transaction');

count.addQuery('sys_created_on','>=', gs.minutesAgo(2));

count.addAggregate('AVG', 'response_time');

count.query();

while(count.next()) {

  var avg= count.getAggregate('AVG', 'response_time');

}

gs.log('Response time Average is '+ avg);

Thanks
Divya

[Shishir Srivast]

Hi Divya,

I think you need to addAggregate for MAX and MIN as well and have groupby on type. Please try below code if it helps.

var count = new GlideAggregate('syslog_transaction');

count.addQuery('sys_created_on','>=', 'javascript:gs.minutesAgo(2)');

count.addAggregate('MIN', 'response_time');

count.addAggregate('MAX', 'response_time');

count.addAggregate('AVG', 'response_time');

count.groupBy('type');

count.query();

while(count.next()) {

  var avg = count.getAggregate('AVG', 'response_time');

  gs.log(count.type + 'Response time Average is '+ avg);

}

[Kalaiarasan Pus]

You haven't done any grouping of records to get the average. Try using groupBy function in your script.

http://wiki.servicenow.com/index.php?title=GlideAggregate#gsc.tab=0

[divvi_vamsi]

i need an avg value for the entire column values of response time and within a specific time, so that shouldnt be categorized under a particular thing. Just like the value of a list calculations in a list view.

Thanks
Divya

[Shishir Srivast]

I think, you need to use groupBy(), i was also trying without groupBy() but wan't able to get the result in background script. You can try to get the avg based upon groupBy for last 2 minutes and then add them up like below code (anyways average will be same if you add them for different groupBy) to get the final average. Hope this helps.

var count = new GlideAggregate('syslog_transaction');

count.addQuery('sys_created_on','>=', 'javascript:gs.minutesAgo(1)');

count.addAggregate('AVG', 'response_time');

count.groupBy('type');

count.query();

var final_avg = 0;

while(count.next()) {

var avg = count.getAggregate('AVG', 'response_time');

gs.log('Response time Average is :         '+ avg);

final_avg =+ parseFloat(final_avg ) + parseFloat(avg);

}

gs.log('final_avg:     ' + final_avg);