How To Use GlideAggregate In ServiceNow – A Complete Guide

Many developers, even seasoned ones, don’t have a truly solid grasp on using GlideAggregate.

Most developers will get something like gr.getRowCount(), to find a tables row count, which is usually fine to use, especially in a single use background script.

But there are performance reasons why you shouldn’t be using getRowCount() frequently, and instead of this, you should earn how to use GlideAggregate.

In ServiceNow, GlideAggregate is a class that allows developers to perform aggregate functions, such as SUM, COUNT, MIN, and MAX, on table data. This is useful for retrieving summary information from tables, such as the total number of records, the minimum or maximum value of a field, or the sum of values in a field.

A ServiceNow developer might use GlideAggregate for a variety of reasons, such as to calculate statistics or summary information for a report, to perform data analysis and identify trends or patterns, or to retrieve subsets of data that meet specific criteria. Using GlideAggregate can be more efficient than retrieving all of the records in a table and performing the aggregate functions in the application code.

For example, a developer might use GlideAggregate to retrieve the total number of open incidents in a specific priority range, the average age of open incidents, or the sum of the estimated effort values for all open tasks. This information can then be used to generate reports or display summaries on a dashboard.

Below, I’ll show you how GlideAggregate works, a very common example (finding duplicate user names) and then I’ll finish off by showing you some common GlideAggregate methods. Learning GlideAggregate will give you a huge edge in ServiceNow development.

A GlideAggregate Script Example

var gr = new GlideAggregate('table_name');
gr.addAggregate('COUNT', 'field_name');
gr.query();
while (gr.next()) {
  var count = gr.getAggregate('COUNT', 'field_name');
  gs.info(count);
}

In this example, a GlideAggregate object is created for the specified table, and an aggregate function is added to count the number of records in the table based on the specified field. The query() method is then used to execute the query, and the next() and getAggregate() methods are used to iterate over the results and retrieve the count.

You can also add additional filter criteria to the query using the addQuery() method, and add other aggregate functions using the addAggregate() method. For more information and examples, you can refer to the ServiceNow documentation for the GlideAggregate class.

Find Duplicate User Names Or Records In A Table

To find duplicate user names in a table in ServiceNow, you can use a GlideAggregate query with a COUNT aggregate function, combined with a HAVING clause to filter the results. Here is an example script that you can use:

var gr = new GlideAggregate('table_name');
gr.addAggregate('COUNT', 'user_name');
gr.addHaving('COUNT', 'user_name', '>', 1);
gr.query();
while (gr.next()) {
  var userName = gr.get('user_name');
  var count = gr.getAggregate('COUNT', 'user_name');
  gs.info(userName + ': ' + count);
}

In this script, a GlideAggregate object is created for the specified table, and an aggregate function is added to count the number of records with the same user_name value. The addHaving() method is then used to add a condition to the query, specifying that only records with a count greater than 1 should be included in the results. This filters the query to only include user names that appear more than once in the table.

The query() method is used to execute the query, and the next() and get() and getAggregate() methods are used to iterate over the results and retrieve the user_name and count values. These values can then be used to display or process the duplicate user names.

This script can be easily modified to find duplicates based on other fields, or to perform other aggregate functions on the data. For more information and examples, you can refer to the ServiceNow documentation for the GlideAggregate class.

Common GlideAggregate Methods

Here is a table of some common GlideAggregate methods in ServiceNow, along with a brief description of each:

These methods can be used in combination to perform various aggregate functions on table data and retrieve the results. For more information and examples, you can refer to the ServiceNow documentation for the GlideAggregate class.