Every once in a while, I run into a situation where I need a list of distinct values from a table. The latest situation I ran into for this, I needed to get a distinct list of every user that had an open Project Task.
The easiest way to do this of course is to go to a list view, and sort by the field you care about. For example:
Grouping Project Tasks by Assigned To
For my purposes though, I wanted to send out scripted email notifications to everyone that had an open task. As a result, I needed this information available to me through scripting, which caused me to look for a different solution.
The answer came in the form of “GlideAggregate”. This is a Service-Now custom JavaScript object that functions very similar to the popular “GlideRecord”, with a few key differences.
GlideAggregate is built to provide the functionality of gathering basic statistics — counts, averages, maximums, and minimums. However, by employing a little creativity, we can use this power to grab all the unique values of a certain field on a table.
Consider this example below that I recently used:
var ga = new GlideAggregate(“pm_project_task”);
ga.addQuery(“active”, “true”);
ga.addQuery(“assigned_to”, “!=“, “”);
ga.addAggregate(“count”);
ga.orderByAggregate(“count”);
ga.groupBy(“assigned_to”);
ga.query();
while(ga.next()) {
var taskCount = ga.getAggregate(“count”);
gs.print(“User: “+ga.assigned_to.name+” - Number of Project Tasks: “+taskCount);
}
The above code does the following things:
- Counts how many project tasks are assigned to each user.
- Filters out project tasks that aren’t assigned to anyone.
- Prints out to the screen the user’s name, and how many project tasks are assigned to them.
Running that code as a Background Script, gives me the following output:
Background Script Output
Hopefully you find this useful! In the near future I’ll be posting a few other related “How-Tos”:
- Using Background Scripts
- Dynamic Weekly Digest Emails
- More GlideAggregate Examples
Categories: Developers, Scripting Tips
GarrettNow 
Reblogged this on ServiceNow: SOAP Journal.
LikeLike
Hi Garrett,
This is very useful, thanks! Have a quick question.
Say, I need to pull up a list of incidents that were opened today, on an hourly basis i.e. x incidents opened between 1:00 AM – 02:00 AM, etc, addTrend(‘field_name’, ‘hour’) gets me the results. The question is how do I get the hour slot in the result set. Tried ga.getAggregate(‘trend’, ‘hour’) but it did not work.
I’d appreciate any pointers.
incidentsPerHour();
function incidentsPerHour() {
var ga = new GlideAggregate(‘incident’);
var query=”sys_created_onBETWEENjavascript:gs.dateGenerate(‘2014-03-30′,’00:00:00’)@javascript:gs.dateGenerate(‘2014-03-30’,’23:59:59’)”;
ga.addEncodedQuery(query);
ga.addAggregate(‘COUNT’);
ga.groupBy(‘mailbox’);
ga.query();
var arr = [];
while(ga.next()){
arr.push(“trendField = ” + ga.getAggregate(‘COUNT’) + ‘ mailbox=‘ + ga.mailbox);
}
gs.print(“test”);
gs.print(arr.join(‘\n’));
}
Thanks,
Teja.
LikeLike
Hi Teja,
Very intriguing problem there. I’ve never used the ‘addTrend’ function in the past — so I don’t have an outright solution for you, but I can at least give a hint:
After playing around with it for about 30 minutes, I’ve managed to determine that if I limit my query to a single day (like you have above), and then I use “addTrend(‘sys_created_on’, ‘hour’)”, each time I use “ga.next()” — I get the count total for the next hour, and using “getValue”, I can figure out what hour it’s reporting.
The magic word was “timeref” — I figured this out by doing a “getEncodedQuery()”, and noticing that it had “order_by=timeref”. Note that you *must* use “getValue” to grab timeref. Otherwise it will give you undefined.
Example:
incidentsPerHour();
function incidentsPerHour() {
var ga = new GlideAggregate('incident');
var query="sys_created_onBETWEENjavascript:gs.dateGenerate('2014-03-30','00:00:00')@javascript:gs.dateGenerate('2014-03-30','23:59:59')";
ga.addEncodedQuery(query);
ga.addAggregate('COUNT');
ga.groupBy('mailbox');
ga.addTrend('sys_created_on', 'hour');
ga.query();
var arr = [];
while(ga.next()){
gs.print("trendField = " + ga.getAggregate('COUNT') + ' - mailbox=' + ga.mailbox + " - hour: "+ga.getValue("timeref"));
}
}
LikeLike
Thanks much Garrett!! You’ve saved me a lot of time plodding through endless lists.
LikeLike