Create a Glide Aggregate query with multiple GroupBy and Having Clause

[rajendraksingh]

HI,

I want to implement create a Glide Record to implement the following query

/*

SELECT                               cmdb_ci, type, metric_name, resource, Count(*) AS cnt, MAX(sys_created_on) as last_occurence

FROM                                   em_alert

WHERE                               em_alert.incident IS NOT NULL

GROUP BY                   cmdb_ci, type, metric_name, resource

HAVING                             last_occurence > (Now() - 3 days) AND cnt >= 5

*/

To implement this I wrote the following GlideAggregate statement

var count = new GlideAggregate('em_alert');

count.addNotNullQuery('incident');

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

count.addAggregate('COUNT');

count.groupBy('cmdb_ci');

count.groupBy('type');

count.groupBy('metric_name');

count.groupBy('resource');

count.query();

var currentLessNDays   = new GlideDateTime();

currentLessNDays.addDaysUTC(-3); // calculate time for previous 3 days

while (queryOutput.next()) {

var cnt = count.getAggregate('COUNT');

var last_occur = count.getAggregate('MAX', 'sys_created_on');

var currentLessNDays   = new GlideDateTime();

currentLessNDays.addDaysUTC(-3); // calculate time for previous 3 days

//HAVING last_occurence > (Now() - 3 days) AND cnt >= 5 Implementing Having Clause

if( parseInt(cnt,10) >= 5 && last_occur >= currentLessNDays ){               //parseInt(number,radix) by default it takes octal

                                                gs.log( "CMDB_CI = " + count.cmdb_ci.sys_id.toString());

                                                gs.log( "Resource = " +count.resource);

                                                gs.log( "Metric = " +count.metric_name);

                                                gs.log( "Type = " +count.type.sys_id.toString());

                                                gs.log( "Count = " +count.getAggregate('COUNT'));

                                                gs.log( "Last Occur = " + count.getAggregate('MAX', 'sys_created_on'));

                                      }

}

Is there any issue in the query? I am not getting correct result.

Thanks

Rajendra

[gertjankrab]

Hi Rajendra,

you can add "count.addHaving('COUNT', '>=', 5);" befor you do the query().

And maybe a typo: while (queryOutput.next()) needs to be while (count.next())?

Gertjan