Using GlideAggregate to get counts of data grouped by multiple fields.

garyopela · ‎08-23-2022

We all know how to use GlideAggregate, right?

Here's an example:

var ga = new GlideAggregate('cmdb_sam_sw_install');
ga.addAggregate('COUNT');
ga.addQuery('discovery_source', 'ServiceNow');
ga.query();
if (ga.next()){
   gs.print('Count: ' + ga.getAggregate('COUNT'));
}

This will give you a count of how many Software Installs have discovery source of ServiceNow. This is pretty useful. But, sometimes we need to take it a bit further.

Now, let's see how many software installs each and every discovery source has. To do this, we can add an argument to the 'addAggregate' line above. Let's next do this. Let's first get rid of the addQuery from above so that we can pull back all discovery sources. Next, we'll need to add the grouping field, discovery_source, to the addAggregate and getAggregate statements. Finally, we'll need to change the 'if' to a 'while' loop so that we can iterate through the groupings. Don't worry, this won't iterate through ALL of your SW Installs, it'll just iterate through each of the groups (discovery sources).

var ga = new GlideAggregate('cmdb_sam_sw_install');
ga.addAggregate('COUNT', 'discovery_source');
ga.query();
while (ga.next()){
   gs.print('Discovery Source: ' + ga.getDisplayValue('discovery_source') + ' Count: ' + ga.getAggregate('COUNT', 'discovery_source'));
}

Here's what the output would look like in my test instance:

*** Script: Discovery Source: BigFix Count: 1
*** Script: Discovery Source: SCCM Count: 1
*** Script: Discovery Source: ServiceNow Count: 2

Now that's great and all, right? It's super fast, you don't have to use getRowCount(), but... it has it's downside. You are only grouping on one field. So, let's add some code! I've also created more test data rows in my Software Installs table so we can see the results. Here the key thing is that we got rid of the argument we passed to addAggregate and getAggregate, and instead we are using the groupBy that you might have used before with GlideRecord.

var ga = new GlideAggregate('cmdb_sam_sw_install');
ga.addAggregate('COUNT');
ga.groupBy('installed_on');
ga.groupBy('discovery_source');
ga.query();
while (ga.next()){
   var str = ga.getAggregate('COUNT') > 1 ? ' installs' : ' install'; //bothers me to have plural noun when it should be singular :)
   gs.print(ga.getDisplayValue('installed_on') + ' has ' + ga.getAggregate('COUNT') + str + ' for ' + ga.getValue('discovery_source'));
}

Here's what this would now give us:

*** Script: *ANNIE-IBM has 1 install for ServiceNow
*** Script: *ASSET-IBM has 1 install for ServiceNow
*** Script: *BETH-IBM has 1 install for BigFix
*** Script: *BOW-IBM has 3 installs for BixFix
*** Script: *BOW-IBM has 1 install for SCCM
*** Script: *BOW-IBM has 1 install for ServiceNow
*** Script: *BUD-IBM has 1 install for SCCM

That is pretty good, right? This makes it much easier to analyze large sets of data. You can add in additional groupBy conditions if you need to, and whenever you iterate with the while loop, it'll treat each unique combination of all of the groupBy fields as a row, and then you'll get a row count for each iteration. I've used this to validate how many software installs we have grouped by prod_id, discovery_source, and a few other fields.

Here is another example where we query incident table to get the counts of incidents, grouped by Category and Subcategory:

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT');
ga.groupBy('category');
ga.groupBy('subcategory');
ga.query();
while (ga.next()){
   gs.print(ga.getDisplayValue('category') + '|' + ga.getDisplayValue('subcategory') + '|' + ga.getAggregate('COUNT'));
}

This gives us the sample results of:

*** Script: Database|DB2|56
*** Script: Database|Memory|13
*** Script: Database|Oracle|52
*** Script: Database|MS SQL Server|55
*** Script: Hardware|null|6
*** Script: Hardware|CPU|22
*** Script: Hardware|Disk|26
*** Script: Hardware|external storage|24
*** Script: Hardware|Internal Application|1
*** Script: Hardware|Keyboard|16
*** Script: Hardware|Memory|6
*** Script: Hardware|Monitor|30
*** Script: Hardware|Mouse|21
*** Script: Hardware|pc|24

This is just the start of it. You could potentially format the results in a CSV format, then create a CSV file and attach it somewhere useful.

Hope this helps! Let me know if it does, or if you have more questions on topics such as this!

Thanks,

Jr

FAHAD ASLAM · ‎06-09-2023

in my Tokyo version its gave me error

Glide Aggregate

this is not a function

garyopela · ‎06-10-2023

Hey @FAHAD ASLAM would you be able to copy and paste your line where you are creating your GlideAggregate variable? Make sure that you don't have a space in between Glide and Aggregate. It should be GlideAggregate.

Thanks,

Jr

jessa23 · ‎08-17-2023

Hello, this is really helpful however, I had an error of

Skip invalid GroupBy condition: ORDERBYdomain

I was trying to group it by domain.

garyopela · ‎08-21-2023

Hey @jessa23 would you mind sharing some of your code with me so I can look at it? Some DB engines will require orderby on a groupby, or maybe it's the other way around, it's been awhile since I did much database work. But make sure that domain is a valid field on the table that you are querying. It sounds like you are trying to GroupBy the domain, but the field may not be there.

Thanks!

Visnu1517 · ‎01-26-2025

Hi @garyopela

How to get the no of groups when we try to group the records using GlideAggregate?

For the below sample output from your post

*** Script: Database|DB2|56
*** Script: Database|Memory|13
*** Script: Database|Oracle|52
*** Script: Database|MS SQL Server|55
*** Script: Hardware|null|6
*** Script: Hardware|CPU|22
*** Script: Hardware|Disk|26
*** Script: Hardware|external storage|24
*** Script: Hardware|Internal Application|1
*** Script: Hardware|Keyboard|16
*** Script: Hardware|Memory|6
*** Script: Hardware|Monitor|30
*** Script: Hardware|Mouse|21
*** Script: Hardware|pc|24

There are 14 groups in the sample output, I want to get this number directly using any method on GlideAggregate. Is there any direct method to get this number?

garyopela · ‎01-27-2025

Hey @Visnu1517 that's a great question! I've had to do this many times. I just use a counter and increment it inside the while loop. I haven't found a more eloquent way to do it. But the following additions will accomplish what you ask:

var ga = new GlideAggregate('cmdb_sam_sw_install');
ga.addAggregate('COUNT');
ga.groupBy('installed_on');
ga.groupBy('discovery_source');
ga.query();

var noOfGroups = 0;

while (ga.next()){
	noOfGroups += 1;
   var str = ga.getAggregate('COUNT') > 1 ? ' installs' : ' install'; //bothers me to have plural noun when it should be singular :)
   gs.print(ga.getDisplayValue('installed_on') + ' has ' + ga.getAggregate('COUNT') + str + ' for ' + ga.getValue('discovery_source'));
}

gs.info('Number of groups: ' + noOfGroups);