AVG value being calculated is incorrect

divvi_vamsi · ‎07-06-2017

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 · ‎07-07-2017

Hi Divya,

Can you please try below code. I tried in Background Script and seems working. Basically I have applied below formula.

Total avg: [(avg of type A* Number count of Type A) + (avg of type B * Number count of Type B)] / (Number count of Type A + Number count of Type B)

var count = new GlideAggregate('syslog_transaction');

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

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

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

count.groupBy('type');

count.query();

var final_avg = 0;

var cnt = 0;

var avg = 0;

var total_val = 0;

var total_count = 0;

while(count.next()) {

total_count =+ parseInt(total_count) + parseInt(count.getAggregate('COUNT', 'response_time'));

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

cnt = count.getAggregate('COUNT', 'response_time');

total_val =+ parseFloat(total_val) + (parseFloat(avg) * parseInt(cnt));

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

gs.log(count.type + ' Response time count is : '+ cnt);

}

gs.log('***************************************************');

gs.log('total val :' + total_val);

gs.log('total count :' + total_count);

final_avg =+ parseFloat(total_val) / parseInt(total_count);

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

Hope this helps.

View solution in original post

Shishir Srivast · ‎07-10-2017

Hi Divya,

Hope provided script helped you to achieve the requirement.

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

If you are viewing this from the community inbox you will not see the correct answer button. If so, please review https://community.servicenow.com/docs/DOC-5601

divvi_vamsi · ‎07-13-2017

So as per my findings in total, the initial question by me: AVG being calculated is incorrect is due to two mistakes from my side:

GroupBy isn't used for a particular column

If GroupBy isn't needed then setGroup(false) needs to be included in the code.

Missing this concept led me to confusion. The later version of this function is to get column values from the column function so that lot of redundancy can be avoided when we wanted to calculate the same values for multiple columns. Below is the updated code for the same:

function getAvgOfCol(col_name) {

var grA = new GlideAggregate('syslog_transaction');

grA.addQuery('sys_created_on','>=', gs.hoursAgo(1));

grA.addAggregate ('AVG', col_name);

grA.setGroup(false); //no need to group by any column when set to false (for aggreagate methods)

grA.query();

var avg = 0;

if (grA.next()) { //use if instead of while as it checks the condition just once, which is what we require for calculating aggregate;

avg = grA.getAggregate("AVG",col_name); //col_name value from calling function

if (avg > 0) {

gs.print(avg);

}

else {

gs.print('NaN?');

}

return avg;

}

Just by understanding these concepts was able to eliminate all other complicated codes.

Thanks
Divya

Shishir Srivast · ‎07-13-2017

Thank you Divya for the details. Learned interesting concept today.