GlideAggregate

Recently, I gave an interview, where I was asked to write a script in scripts - background to print priority-wise count of all incident records. And I could not do it. Primarily because I did never use this API for anything else but getting the COUNT. So this is the time for doing what I did not do and probably you do not as well. So let us dig what this API is.

If you did not get what exactly I was asked to do, follow this steps:

Navigate to Incident > All

Right-click the column Priority and select Group By Priority.

Your window will display something like this:

Here, You will notice:

Groups are named for the values of the field selected for grouping. For example, in our case, since we grouped by Priority, each group is a Priority's display name.
The record count for each group appears next to the group name/Priority.
The total number of items in the list (all groups/Priorities combined) appears near the paging controls in the list.

I had to write a script to print the output as shown in the last screenshot above. If you want to read more about "Grouped lists" you can find it here.

What is 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.

Getting the number of records for GlideRecord query

One of the frequently used use case is finding number of records for a a GlideRecord query. This can be achieved using getRowCount for a GlideRecord query, which is not a efficient solution if no operation is performed with the returned records. Alternatively, use of GlideAggreggate can improve the performance significantly. Let us see how to script both the ways:

getRowCount

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideRecord("incident")
grInc.query()
gs.info(grInc.getRowCount())

You should see the output similar to the following:

Now, Navigate to Incident > All

Right-click the value New for column State and select Show Matching.

Your window will display something like below, Observe that the condition State = New has been added to the filter and the total number of records with this filter:

Right-click the end of the filter breadcrumb and select Copy query from the context menu:

Now, let us modify the script as below to use encoded query copied above to filter the records:

var grInc = new GlideRecord("incident")
grInc.addEncodedQuery("state=1")
grInc.query()
gs.info(grInc.getRowCount())

Copy the above script to scripts - background, and click Run script button:

You should see the output similar to the following:

This is the traditional way of getting number of records for a specific filter. You can dig documentation to know more about Copy query & Quick filters.

GlideAggregate

There is alternate way of scripting the same using GlideAggregate that can improve the performance significantly and is more efficient:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.addEncodedQuery("state=1")
grInc.addAggregate("COUNT")
grInc.query()
if (grInc.next()) {
  gs.info(grInc.getAggregate("COUNT"))
}

You should see the output similar to the following:

In the above script,

addAggregate() method is used to ask needed information.
getAggregate() method gets the value of an aggregate from the current record.

Group records

Now, we can get back to our initial requirement:

Navigate to Incident > All
Right-click the column Priority and select Group By Priority.
Your window will display something like this:
Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.addAggregate("COUNT")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} ({1})", [
    grInc.getDisplayValue("priority"),
    grInc.getAggregate("COUNT"),
  ])
}

You should see the output similar to the following:

In the above script, groupBy() provides the name of a field to use in grouping the aggregates. Alternatively, we can use the second parameter of addAggregate() method to achieve the same result:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.addAggregate("COUNT", "priority")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} ({1})", [
    grInc.getDisplayValue("priority"),
    grInc.getAggregate("COUNT", "priority"),
  ])
}

You should see the output similar to the following:

getTotal()

Let us modify our script to introduce another method, getTotal(), which returns the number of records by summing an aggregate:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.addAggregate("COUNT", "priority")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} ({1})", [
    grInc.getDisplayValue("priority"),
    grInc.getAggregate("COUNT", "priority"),
  ])
}
gs.info(grInc.getTotal("COUNT"))

You should see the output similar to the following:

groupBy() and COUNT

At this point you should ask if there are two ways to group, what is the difference between them? Let us modify the script a bit to use both groupBy() and COUNT together:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.groupBy("state")
grInc.addAggregate("COUNT", "priority")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} & State: {1} ({2})", [
    grInc.getDisplayValue("priority"),
    grInc.getDisplayValue("state"),
    grInc.getAggregate("COUNT", "priority"),
  ])
}
gs.info(grInc.getTotal("COUNT"))

You should see the output similar to the following:

Here We did print number of records for each priority grouped by state. Let us verify the results:

Navigate to Incident > All

Right-click the column Priority and select Group By Priority.

Your window will display something like this:

Now, click the arrow icon next to the Priority 'Critical' to expand the group

Right-click the value In Progress for column State and select Show Matching.

You should see the output similar to the following:

Sorting the result

orderBy()

Let us introduce another method called orderBy(), which provides the name of a field that should be used to order the aggregates.:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.groupBy("state")
grInc.addAggregate("COUNT", "priority")
grInc.orderBy("priority")
grInc.orderBy("state")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} & State: {1} ({2})", [
    grInc.getDisplayValue("priority"),
    grInc.getDisplayValue("state"),
    grInc.getAggregate("COUNT", "priority"),
  ])
}
gs.info(grInc.getTotal("COUNT"))

You should see the output similar to the following:

In the above example, we did sort the list by Priority and state.

orderByAggregate()

Another variation of orderBy() is orderByAggregate(), which orders the aggregates based on the specified aggregate and field. Let us slightly modify our script:

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.groupBy("state")
grInc.addAggregate("COUNT", "priority")
grInc.orderByAggregate("count", "priority")
grInc.query()
while (grInc.next()) {
  gs.info("Priority: {0} & State: {1} ({2})", [
    grInc.getDisplayValue("priority"),
    grInc.getDisplayValue("state"),
    grInc.getAggregate("COUNT", "priority"),
  ])
}
gs.info(grInc.getTotal("COUNT"))

You should see the output similar to the following:

You might have already noticed that the result is now sorted by the number of records.

Count isn't the only function

So far we have seen some examples that utilizes COUNT for understanding GlideAggregate APIs, But Count is not the only thing that we can do with it. We can also use SUM, MAX, MIN, and AVG to get the total sum, maximum number, minimum number, and average, respectively. I would like to quote Andrew Barnes as is from his exteremely useful ServiceNow blog post "Understanding GlideAggregate" which can be found here:

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.

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.addAggregate("AVG", "reassignment_count")
grInc.addAggregate("AVG", "sys_mod_count")
grInc.query()
while (grInc.next()) {
  gs.info(
    "Incidents with priority {0} had {1} average modifications and {2} average reassignments",
    [
      grInc.priority.getDisplayValue(),
      grInc.getAggregate("AVG", "sys_mod_count"),
      grInc.getAggregate("AVG", "reassignment_count"),
    ]
  )
}

You should see the output similar to the following:

addHaving()

addHaving() adds a "having" element to the aggregate. This method is extermely useful when you want to trigger something if record count crosses the defined threshold or to find duplicate records. You can find the awesome post on snprotips blog by Tim Woodruff here regarding same.

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.groupBy("priority")
grInc.addAggregate("COUNT", "sys_mod_count")
grInc.addHaving("COUNT", "sys_mod_count", ">", "10")
grInc.query()
while (grInc.next()) {
  gs.info("Incidents with priority {0} had {1} total modifications", [
    grInc.priority.getDisplayValue(),
    grInc.getAggregate("COUNT", "sys_mod_count"),
  ])
}

You should see the output similar to the following:

The above script outputs the total number of times particular priority incidents modified, if modification count is greater than 10. Feel free to change the value to something less like 1 or more like 30 and re-execute the script to observe the output.

addTrend()

addTrend() method adds a trend for a field. Let us modify the script to display number of incidents by months.

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.addTrend("opened_at", "month")
grInc.addAggregate("COUNT")
grInc.query()
while (grInc.next()) {
  gs.info("{0}: {1}", [grInc.getValue("timeref"), grInc.getAggregate("COUNT")])
}

You should see the output similar to the following:

getQuery()

getQuery() method retrieves the query necessary to return the current aggregate.

Copy the below script to scripts - background, and click Run script button:

var grInc = new GlideAggregate("incident")
grInc.addTrend("opened_at", "month")
grInc.addAggregate("COUNT")
grInc.query()
while (grInc.next()) {
  gs.info("{0}: {1}, Query: {2}", [
    grInc.getValue("timeref"),
    grInc.getAggregate("COUNT"),
    grInc.getQuery(),
  ])
}

You should see the output similar to the following:

How can you learn more?

The best way to learn anything is hands-on exercises. Here is some of the resources which will help provide examples and use cases to furthur enhance your understanding: