Understanding GlideAggregate

#Developer , #GlideAggregate

Andrew Barnes

5 minute read

What is GlideAggregate

Earlier this year, the blog visited the topic of GlideRecord. I shall set my sites on the friend and close relative to GlideRecord, GlideAggregate. GlideAggregate is an extension of GlideRecord. It provides the capability to do aggregation (COUNT, SUM, MIN, MAX, AVG). These operations can be done with regular GlideRecord use, but with GlideAggregate, they are optimized around these use cases and offer significant efficiencies.

Let’s look at an example GlideAggregate setup.

var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT', 'category');
agg.orderBy('category');
agg.addQuery('sys_created_on', '>=', 'javascript:gs.beginningOfLast12Months()');
agg.query();
while (agg.next()) {
    //do things on the results
    var incidentCount = agg.getAggregate('COUNT', 'category');
    gs.info('Display the count {0}', [incidentCount]);
}

The similarities to GlideRecord will stand out if you are familiar with those. The format is very similar. The main addition in this example is the addition of the addAggregate. This function allows us to add one or more aggregates to our results. Any of the aggregate types can be mixed and matched in the same query. It accepts two parameters, the aggregate type, and the field (column) to operate on if needed. Interestingly the orderBy, which is also used in GlideRecords has an additional purpose of performing a group-by for aggregates. The rest of the building for this example is the same process until we get inside the resultant query results in our while loop.

Result

You can see the results of running this. There are five different categories and the counts of the number of records for each of those categories. The grouping happened as a result of the orderBy method that was utilized. Multiple orderBy can be used to subgroup the results into an as granular number of clusters as needed.

var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT', 'category');
agg.orderBy('category');
agg.orderBy('assigned_to');
agg.orderBy('state');
agg.addQuery('sys_created_on', '>=', 'javascript:gs.beginningOfLast12Months()');
agg.query();
while (agg.next()) {
    //do things on the results
    var incidentCount = agg.getAggregate('COUNT', 'category');
    var category = agg.getValue('category');
    var state = agg.getDisplayValue('state');
    var assigned_to = agg.getDisplayValue('assigned_to');
    gs.info('Display the count {0} and category {1}  assigned to {2} and state {3}', [incidentCount, category, assigned_to, state]);
}

Result

Our results show the orderBy caused grouping first on category, then on assigned to, and finally on state. The chained groupBy allows a great deal of flexibility in how and what use cases GlideAggregate can be helpful. The count and groupBy that was performed above is just one of the many uses available. It allows us to build a unique list of values. Often when writing scripts, it can be useful to know what the values of a particular field are, to then use in a GlideRecord process. Now the script will be adjusted to showcase that use case.

Imagine the user has role table (sys_user_has_role) and you would like to find all the users with role ITIL and put them into a group. You could, query the table, loop through all the records, and build your user list to then place in the group. This would require searching and retrieving every record that has the ITIL role. If you have done this, you will know there are many entries for the same user for the same role. GlideAggregate allows for a much faster building of the user list.

var itilUsers, intoGroup, tempUser, group;
itilUsers = new GlideAggregate("sys_user_has_role");
itilUsers.addAggregate("COUNT");
itilUsers.addQuery('role.name', 'itil');
itilUsers.groupBy("user");
itilUsers.query();

while(itilUsers.next()) {
    tempUser = itilUsers.user;
    group = 'dd475dd2db36bb0064dcf5861d96190a';
    intoGroup = new GlideRecord("sys_user_grmember");
    intoGroup.initialize();
    intoGroup.setValue('group', group);
    intoGroup.setValue('user', tempUser);
    intoGroup.insert();
}

Had GlideRecord been used for the users, our resultant script would have run many times slower. The performance difference is due to a large number of results that match compared to the small amount that is being sought. In my instance, I had 59 users that met the criteria, but across more than 800 records. The sub-second results (0.912sec) compared to (7.23sec) show how much faster this was for this simple use case. When looking at tables with even higher numbers, the increase can be more significant.

One frequent use case that can be seen in nearly every customer’s instance: using getRowCount for a GlideRecord query in which no operation is performed with the returned records. Even out of box scripts can suffer from this plight as we can see below.

var priorTasks = new GlideRecord('sn_erm_deployment_task');
priorTasks.addQuery('build_phase', this.gr.sys_id);
priorTasks.query();
if (priorTasks.getRowCount() > 0)
    gs.print('Count ' + priorTasks.getRowCount());

The further script does nothing with priorTasks directly. Thus, the overhead of doing a full query is added to my function. Use of GlideAggreggate here would improve the performance significantly. Our operation above takes 0.261 vs. 0.019 seconds for a smallish data set. When applied to a larger table with 58k returned, the results were 0.917 and 0.027 seconds. An order of magnitude improvement is sizeable and worth working into the practices for your team. There are certainly times when you can and should use getRowCount with your GlideRecord query. Nearly anytime the returned results of the query are being utilized in with a while(object.next()) is acceptable to use getRowCount.

Records GlideRecord GlideAggreggate
589 0.261 0.019
58,345 0.917 0.027
5,568,834 15.5 3.18

Below is the GlideAggregate version

var priorTasks = new GlideAggregate('sn_erm_deployment_task');
priorTasks.addQuery('build_phase', this.gr.sys_id);
priorTasks.addAggregate('COUNT');
priorTasks.query();
priorTasks.next();
gs.print('Count ' + priorTasks.getAggregate('COUNT'));

Count isn’t the only function we can perform with GlideAggregate. Average, Sum, Max, Min are handy when dealing with numbers. Our favorite example table incident has several useful fields that pure counts isn’t wildly useful. Our service desk might want to know the average number of times incidents were modified sys_mod_count or reassigned reassignment_count. The service desk owner can use this information to set a baseline, and then run this every month to see if changes have been an improvement or not. This can be done with performance analytics and reporting but is an example.

var incidentCount = new GlideAggregate('incident');
incidentCount.addAggregate('AVG', 'reassignment_count');
incidentCount.addAggregate('AVG', 'sys_mod_count');
incidentCount.groupBy('category');
incidentCount.query();
while (incidentCount.next()) {
    var category = incidentCount.category.getDisplayValue();
    var reassign_count = incidentCount.getAggregate('AVG', 'reassignment_count');
    var mod_count = incidentCount.getAggregate('AVG', 'sys_mod_count');
    gs.info("Incidents category {0} had {1} average modifications and {2} average reassignments" , [category, mod_count, reassign_count]);
}

Result

Some resources for further examples and use cases:

  1. Steven Bell’s Ask the Expert session
  2. Developer site documentation
  3. Docs Examples
  4. GarrettNows examples

Hopefully, this article has shown you some of the power of GlideAggregate and helps you and your teams utilize this powerful tool in future scripting. Add comments below for your example GlideAggregate uses or provide a use case, and we can update the blog entry with your idea!

Comments