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

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: 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,