GLIDEAGGREGATE SERVICENOW | USECASE IMPLEMENTATION | COMPLETE TUTORIAL

Introduction

Hello friends, welcome back to my YouTube channel, Basico ServiceNow Learning. In today's video, we are going to talk about one of the ServiceNow APIs called GlideAggregate. We will discuss all the GlideAggregate functions in depth, supported by use cases. By the end of this video, you will have a good understanding of all the GlideAggregate functions and their aggregation capabilities in detail.

What is GlideAggregate?

GlideAggregate is an extension of GlideRecord and provides the capability to perform aggregations like min, max, count, sum, average, and more. This API is primarily used for calculations in ServiceNow.

Key Aggregations in GlideAggregate:

• min: Finds the minimum value.
• max: Finds the maximum value.
• count: Counts the number of records.
• sum: Sums up the values of a particular field.
• average: Calculates the average value of a field.
• group_concat: Concatenates values and can eliminate duplicates.

Use Cases

We will explore four use cases to understand the practical implementation of GlideAggregate:

1. Find the count of active incidents.
2. Determine the number of incidents belonging to each category (Group by Category).
3. Use of sum, average, min, and max in cmdb_ci table.
4. Display all distinct class values in cmdb_ci table records as a comma-separated string.

Use Case 1: Find the Count of Active Incidents

Before starting with GlideAggregate, let's look at how GlideRecord can be used to find the total count of incidents.

Using GlideRecord for Counting:

var count = new GlideRecord('incident');
count.query();
var totalCount = count.getRowCount();
gs.info(totalCount); // Outputs total count of incidents.

Using GlideAggregate for Counting:

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.addQuery('active', true);
count.query();
if (count.next()) (
  gs.info(count.getAggregate('COUNT'));
)

Use Case 2: Determine the Number of Incidents Belonging to Each Category

Grouping by Category:

var count = new GlideAggregate('incident');
count.addAggregate('COUNT', 'category');
count.query();
while (count.next()) (
  gs.info('Category: ' + count.category + ' Count: ' + count.getAggregate('COUNT', 'category'));
)

Use Case 3: Use of Sum, Average, Min, and Max in cmdb_ci Table

Sum Calculation:

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('SUM', 'cost');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('SUM', 'cost'));
)

Average Calculation:

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('AVG', 'cost');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('AVG', 'cost'));
)

Min Calculation:

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('MIN', 'cost');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('MIN', 'cost'));
)

Max Calculation:

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('MAX', 'cost');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('MAX', 'cost'));
)

Use Case 4: Display All Distinct Class Values in cmdb_ci Table Records

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name'));
)

Keywords

• GlideAggregate
• ServiceNow API
• Aggregation Functions
• Incident Count
• Group By Category
• Sum, Average, Min, Max
• Group Concat
• Distinct Class Values

FAQ

Q1: What is GlideAggregate in ServiceNow? A: GlideAggregate is an extension of GlideRecord in ServiceNow, which provides the capability to perform aggregation functions such as min, max, count, sum, and average.

Q2: How do I find the count of active incidents using GlideAggregate? A: You can initialize a GlideAggregate object, add the 'COUNT' aggregate, apply the filter for active incidents, and then query the table:

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.addQuery('active', true);
count.query();
if (count.next()) (
  gs.info(count.getAggregate('COUNT'));
)

Q3: Can I use GlideAggregate to determine the number of incidents in each category? A: Yes, you can use the addAggregate function with COUNT and the groupBy function for the 'category' field.

Q4: How can I perform sum, average, min, and max aggregations in a table? A: You can use the addAggregate function with 'SUM', 'AVG', 'MIN', or 'MAX' as required.

Q5: How to get all distinct class values in a cmdb_ci table? A: You can use the addAggregate function with 'GROUP_CONCAT_DISTINCT':

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name');
count.query();
if (count.next()) (
  gs.info(count.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_class_name'));
)

I hope this article was helpful. Please like, share, and subscribe to the channel. If you have any questions or feedback, feel free to comment below. Thank you, and have a nice day!