GlideAggregate is a powerful feature in ServiceNow that allows you to perform aggregate functions on large sets of data in a more efficient way than traditional methods. It is an extension of GlideRecord and provides an capability to do aggregation like MIN, MAX, COUNT, SUM, AVG etc. In this blog post, we will explore the capabilities of GlideAggregate and how it can be used to enhance your ServiceNow instance. Below we have shared the video link to demonstrate this API practical use and implementation.

Aggregation meaning: Gathered the information and expressed in statistical analysis.

What is GlideAggregate?

GlideAggregate is a class in ServiceNow that provides a way to perform aggregate functions on a specified set of records. It works similarly to the SQL GROUP BY clause, allowing you to group records based on one or more fields and apply functions like COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT, GROUP_CONCAT_DISTINCT, STDDEV on the grouped data. GlideAggregate can be used in server-side scripting such as business rules, script includes, scheduled jobs or UI actions.

Advantages of using GlideAggregate

Using GlideAggregate instead of traditional methods such as looping through all records and performing operations on each one has several advantages. Here are a few of them:

Performance: GlideAggregate performs much faster on large data sets than looping through each record because it only retrieves the data required for the query.

Reduced memory usage: GlideAggregate uses less memory than traditional methods because it retrieves only the required data for the query.

Improved readability and maintainability: GlideAggregate code is usually shorter and more readable than traditional methods because it uses simpler syntax and eliminates the need for complex loops and conditional statements.

How to use GlideAggregate

To use GlideAggregate, you need to create a new instance of the class and specify the table and fields you want to query. You can then apply grouping and aggregate functions to the data. Here is an example of a GlideAggregate query that retrieves the number of incidents created per day in the last week:

var count=new GlideAggregate('incident');
count.addAggregate('COUNT');
count.groupBy('category');
count.query();
while(count.next()){
    gs.info(count.getDisplayValue('category')+' - '+count.getAggregate('COUNT'));
}

In this example, we create a new instance of GlideAggregate for the incident table, add the COUNT aggregate function to the sys_created_on field, group the data by the sys_created_on field, add a query to retrieve only the last 7 days of data, and then execute the query. Finally, we loop through the results using the next() method and retrieve the aggregate value and the sys_created_on field value for each group.

GlideAggregate Aggregation Examples:

The GlideAggregate class provides several aggregate functions that you can use to calculate values like COUNT, SUM, AVG, MAX, and MIN. In this blog post, we will explore each of these functions and how they can be used in your ServiceNow instance.

COUNT

The COUNT function returns the number of records in the group. For example, if you want to know how many incidents were created in the last week, you can use the COUNT function to count the number of incidents that were created. Execute the below script in background script and check the output.

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

SUM

The SUM function returns the sum of the values in the group. For example, if you want to know the total amount of time spent on incidents in the last week, you can use the SUM function to add up the time spent for each incident. Execute the below script in background script and check the output.

var count=new GlideAggregate('cmdb_ci');
count.addEncodedQuery('manufacturer=b7e9e843c0a80169009a5a485bb2a2b5');
count.addAggregate('SUM','cost');
count.setGroup(false);
count.query();
while(count.next()){
    gs.info(count.getAggregate('SUM','cost'));
}

AVG

The AVG function returns the average value of the group. For example, if you want to know the average resolution time for incidents in the last month, you can use the AVG function to calculate the average resolution time. Execute the below script in background script and check the output.

var count=new GlideAggregate('cmdb_ci');
count.addAggregate('AVG','cost');
count.setGroup(false);
count.query();
while(count.next()){
    gs.info(count.getAggregate('AVG','cost'));
}

MAX

The MAX function returns the maximum value in the group. For example, if you want to know the highest priority of incidents in the last week, you can use the MAX function to find the incident with the highest priority. Execute it in background script and check the output.

var count=new GlideAggregate('cmdb_ci');
count.addEncodedQuery('manufacturer=b7e9e843c0a80169009a5a485bb2a2b5');
count.addAggregate('MAX','cost');
count.setGroup(false);
count.query();
while(count.next()){
    gs.info(count.getAggregate('MAX','cost'));
}

MIN

The MIN function returns the minimum value in the group. For example, if you want to know the lowest priority of incidents in the last week, you can use the MIN function to find the incident with the lowest priority. Execute the below script in background script and check the output

var count=new GlideAggregate('cmdb_ci');
count.addQuery('cost','!=','0');
count.addEncodedQuery('manufacturer=b7e9e843c0a80169009a5a485bb2a2b5');
count.addAggregate('MIN','cost');
count.setGroup(false);
count.query();
while(count.next()){
    gs.info(count.getAggregate('MIN','cost'));
}

GROUP_CONCAT

GROUP_CONCAT will concatenates all non-null values of the group in ascending order and joins them with a comma (',') and returns the result as a String. Below is the example of the same execute it in background script and check the output.

var incidentGA = new GlideAggregate('incident');
incidentGA.addAggregate('GROUP_CONCAT', 'category');
incidentGA.setGroup(false);
incidentGA.query();
while (incidentGA.next()) {
gs.info('GROUP_CONCAT: ' + incidentGA.getAggregate('GROUP_CONCAT', 'category'));
}

GROUP_CONCAT_DISTINCT

GROUP_CONCAT_DISTINCT will concatenates all non-null values of the group in ascending order, removes all the duplicates and joins them with a comma (',') and returns the result as a String. Below is the example of the same execute it in background script and check the output.

var incidentGA = new GlideAggregate('incident');
incidentGA.addAggregate('GROUP_CONCAT_DISTINCT', 'category');
incidentGA.setGroup(false);
incidentGA.query();
while (incidentGA.next()) {
gs.info('GROUP_CONCAT_DISTINCT: ' + incidentGA.getAggregate('GROUP_CONCAT_DISTINCT', 'category'));
}

STDDEV

STDDEV populates standard deviation. Execute the below script in background script and check the output. Currently, I am not sure about the use case scenarios for the same but soon I will be posting the use case scenario of STDDEV.

var test=new GlideAggregate('incident');
test.groupBy('category');
test.addAggregate('STDDEV','sys_mod_count');
test.query();
while(test.next()){
    gs.info(test.getDisplayValue('category')+' - '+test.getAggregate('STDDEV','sys_mod_count'));
}

The GlideAggregate class provides several aggregate functions that you can use to perform calculations on a set of records. By using these functions, you can calculate values like counts, sums, averages, maximums, and minimums. Whether you are a developer or an administrator, understanding the capabilities of GlideAggregate functions can help you make the most of your ServiceNow instance.

Example and Understanding of GlideAggregate Functions:

The GlideAggregate class in ServiceNow provides several functions that can be used to perform aggregate calculations on a set of records. These functions can be used to calculate values like COUNT, SUM, AVG, MAX, and MIN. In this blog post, we will explore each of these functions in detail and provide examples of how to use them in your ServiceNow instance.

addQuery()

The addQuery() function is used to add conditions to the GlideAggregate query. It takes two or three parameters (when we use operators).The field to be queried

The operator for the query (e.g., '=', '>', '<', '>=', '<=', '!=', 'IN', 'NOT IN', etc.)

The value to be compared against