GlideAggregate - Get Unique Records Easily!

[Allen Andreas]

GlideAggregate - Get Unique Records Easily!

Recently, I was in a scenario where I needed to find the number of distinct (unique) records by user. Within the platform, you can use the "Group by" feature in list view to accomplish this, but I needed to be able to do this in a script. When trying to get a count of records, it's always recommended to use the GlideAggregate API. A lot of people will avoid using this API because the getRowCount() method for GlideRecord is much easier, but it's not the leading practice way to get record count.

When looking at the documentation for the GlideAggregate API and specifically the addAggregate() method, you'll see "agg" parameter options such as: AVG, COUNT, MAX, MIN, SUM, etc. For COUNT, that is merely the count of records not a unique count, so that wouldn't work for my use case.

After conducting a bit of research, I came across numerous Community forum posts about getting the results from GlideAggregate, then putting them into an array, then using the ArrayUtil API and the unique() method to accomplish what I was looking to do however, I then came across another Community forum post from 2015 by Travis Toulson, which not only gave an example of the script, but also explained how they came across this undocumented feature (thank you, kind soul).

To get to the point, there's an additional "agg" parameter you can use called: "COUNT(DISTINCT" -- yes...it's spelled correctly and yes it does include that random ( in the name. You can use something like:
---
ga.addAggregate('COUNT(DISTINCT', 'user');
ga.setGroup(false);
---
To get the distinct count of whatever field you supply, in my case I was looking for users, and then set the setGroup() method to false (otherwise, you'll be given a list of users).

So, the above avoids having to use additional scripting to accomplish this and keeps it all within the GlideAggregate API functionality.

My final script for looking at a comma-separated list of specific roles to find the number of distinct (unique) users that had them was:

var ga = new GlideAggregate('sys_user_has_role');
ga.addQuery('user.active', true);
ga.addEncodedQuery("role.nameINitil,admin"); //example list of roles
ga.addAggregate('COUNT(DISTINCT', 'user');
ga.setGroup(false);
ga.query();
ga.next();
var count = ga.getAggregate('COUNT(DISTINCT', 'user');

Tldr: GlideAggregate has an additional "agg" parameter you can use within the addAggregate method called: "COUNT(DISTINCT". This allows you to get the distinct (unique) count of a specific field.

If you enjoy ServiceNow content like this, please follow on LinkedIn and consider subscribing to my ServiceNow focused YouTube channel: Allenovation!