Mastering GlideAggregate API in ServiceNow: A Guide with Examples

[Abhijeet_Pawar]

When I was new to ServiceNow, I found the GlideAggregate API to be a bit confusing. That’s why I decided to write this blog, covering all the essential scenarios and use cases and interview questions, to make it easier for newcomers to understand and use GlideAggregate effectively.Let’s get started!

• What is GlideAggregate?
  GlideAggregate is a ServiceNow server-side API that allows you to perform aggregate queries, making it possible to retrieve summary statistics for a specific table. This API is perfect for scenarios where you need aggregate data without the overhead of retrieving and processing individual records.

Examples:
1]Print How many number of incidents belong to each category.
==>
Approach 1:With Group By Method:
==>

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.groupBy('category');
count.query();
while(count.next())
{
gs.info(count.getDisplayValue('category') + ': ' + count.getAggregate('COUNT'));

}

Approach 2:Without Group By Method:

var count = new GlideAggregate('incident');
count.addAggregate('COUNT','category);
count.query();
while(count.next())
{
gs.info(count.getDisplayValue('category') + ': ' + count.getAggregate('COUNT','category'));

}

2]Print sum of cost from cmdb_ci table.
==>

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('SUM', 'cost');//group by cost and then do addition
count.setGroup(false);//diable group by
count.query();
while(count.next()) {
gs.info(count.getAggregate('SUM', 'cost'));
}

3]Print all incidents opened each quarter/month
==>

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.addTrend('opened_at', 'quarter');//month
ga.query();
while(ga.next()) {
gs.info([ga.getValue('timeref'), ga.getAggregate('COUNT')]);
}

4]Print all Incident created each minute.
==>

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT'); 
ga.addTrend('sys_created_on', 'minute');
ga.query();
while(ga.next()) {
gs.info([ga.getValue('timeref'), ga.getAggregate('COUNT')]);

}

5]Keep Latest record and delete remaining same incident number.
==>

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT', 'number');
//ga.setGroup(false);
ga.query();
while (ga.next()) {
if (ga.getAggregate('COUNT', 'number') > 1) {
var grInc = new GlideRecord('incident');
grInc.addQuery('number', ga.number);
grInc.orderByDesc('sys_created_on');
grInc.query();
grInc.next();

while (grInc.next()) {
grInc.deleteRecord();
}
}
}

6]Use of orderByAggregate(String agg, String fieldName)Method.
==>Orders the aggregates based on the specified aggregate and field.
Ex:
1]Print top 3 callers who has raised maximum incident tiket
==>

var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT', 'caller_id');
agg.orderByAggregate('COUNT', 'caller_id');
agg.setLimit(2);
agg.query();
while (agg.next())
{
gs.print(agg.getDisplayValue('caller_id')+agg.getAggregate('COUNT','caller_id'));


}

7]setAggregateWindow(Number firstRow, Number lastRow)
==>
Limits the number of rows from the table to include in the aggregate query.

Ex:

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addAggregate('COUNT', 'category');
incidentGroup.setAggregateWindow(0, 10);
incidentGroup.query();
while (incidentGroup.next()) {
var incidentCount = incidentGroup.getAggregate('COUNT', 'category');
gs.info('{0} count: {1}', [incidentGroup.getValue('category'), incidentCount]);
}

8]setIntervalYearIncluded(Boolean b)
==>
Sets whether to group results by year for day-of-week trends.
These trends are created using the addTrend() method with the dayofweek time interval.

Ex:

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addEncodedQuery("sys_created_onRELATIVEGT@month@ago@6");
incidentGroup.addTrend('sys_created_on', 'dayofweek');
incidentGroup.addAggregate('COUNT');
incidentGroup.setIntervalYearIncluded(false);
incidentGroup.query();
while (incidentGroup.next()) {
gs.info(incidentGroup.getValue('timeref') + ': ' + incidentGroup.getAggregate('COUNT'))};

9]Print Incident count as per category and sub-category
==>

var incGA= new GlideAggregate('incident');
incGA.addEncodedQuery('category!=NULL^subcategory!=NULL');
incGA.addAggregate('COUNT','subcategory');
incGA.groupBy('category');
incGA.groupBy('subcategory');
incGA.query();
gs.print('category\t\t subcategory\t\t incident count\t')
while(incGA.next()){
gs.print(incGA.category.getDisplayValue() +"\t\t"+incGA.subcategory.getDisplayValue()+"\t\t\t\t"+ incGA.getAggregate('COUNT','subcategory'));
}

• GROUP_CONCAT
  Concatenates all non-null values of the group in ascending order, joins them with a comma (','), and returns the result as a string.
  Ex:
  1]Distinct Class value in single string comma separated
  ==>

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('GROUP_CONCAT', 'sys_class_name');
count.query();
while (count.next()) {
gs.info(count.getAggregate('GROUP_CONCAT', 'sys_class_name'));
}​

• GROUP_CONCAT_DISTINCT
  ==>Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma (','), and returns the result as a string.
  Ex:

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name');
count.query();
while (count.next())
{
gs.info(count.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name'));
}​

If you enjoyed this ServiceNow content and found it helpful, feel free to connect with me on LinkedIn.

Hit the helpful button if you liked it or found it helpful. Bookmark it for further updates!

Stay tuned for more insights, tips, and tutorials on mastering ServiceNow.

Regards,

Abhijeet Pawar.

[Hitoshi Ozawa]

You've posted to the question forum instead of writing a blog. To write a blog, go to the following page and select "Create a Blog Post" under "Article Dashboard" listed on the right side of the page.

https://www.servicenow.com/community/developer-blog/bg-p/developer-blog

[Abhijeet_Pawar]

Hello @Hitoshi Ozawa This was my first time, and I’ll make sure to take care of it next time. Thank you for your guidance!

[Hitoshi Ozawa]

@Abhijeet_Pawar I thought it was great and thought it'll be useful to much more users if it was made into a blog instead of question because questions tend to get forgotten very quickly due to many questions being asked every day. The blog will attract more users to read because it will remain highlighted in the blog section for a longer time.

Keep up the good work.

[kaustubhk245]

Very Helpful Abhijit