Simplifying GlideAggregate – Real Use Cases & Scripts 💡

[Nilay_Potdukhe]

GlideAggregate:-

GlideAggregate enables you to easily create database aggregation queries. The scoped GlideAggregate class is an extension of Glide Record and provides database aggregation (COUNT, SUM, MIN, MAX, AVG) queries. This functionality can be helpful when creating customized reports or in calculations for calculated fields. The Glide Aggregate class works only on number fields

Real-World Scenarios Using GlideAggregate:- 

1. Auto-Creation of Problem Record Based on Incident Volume

Automatically creates a Problem record if more than 5 incidents are logged against the same CI within the last hour

var incidentCheck = new GlideAggregate('incident');

// Filter for incidents linked to a specific CI in the last 60 minutes

incidentCheck.addQuery('cmdb_ci', '0fa9680d3790200044e0bfc8bcbe5d4c');

incidentCheck.addQuery('opened_at', '>=', gs.minutesAgoStart(60));

incidentCheck.addAggregate('COUNT');

incidentCheck.query();

// If incident count exceeds threshold, create a Problem record

if (incidentCheck.next() && parseInt(incidentCheck.getAggregate('COUNT')) > 5) {

    var problem = new GlideRecord('problem');

    problem.initialize();

    problem.short_description = 'Multiple incidents reported for CI: ' + incidentCheck.cmdb_ci.getDisplayValue();

    problem.cmdb_ci = '0fa9680d3790200044e0bfc8bcbe5d4c';

    problem.insert();

}

2. Detecting Duplicate Users by Email in sys_user Table:-

This script identifies duplicate user records based on the email address

var userCheck = new GlideAggregate('sys_user');

// Count users grouped by email

userCheck.addAggregate('COUNT', 'email');

userCheck.addActiveQuery();

// Filter for emails appearing more than once

userCheck.addHaving('COUNT', '>', 1);

userCheck.query();

// Log duplicate email addresses

while (userCheck.next()) {

 gs.info("Duplicate Email Found: " + userCheck.email);

}

3. Count Active Incidents Grouped by Priority Using GlideAggregate

var incidentPriorityStats = new GlideAggregate('incident');

// Filter for active incidents only

incidentPriorityStats.addActiveQuery();

// Group by priority and count each group

incidentPriorityStats.addAggregate('COUNT', 'priority');

incidentPriorityStats.query();

// Display the count per priority level

while (incidentPriorityStats.next()) {

    var priorityLabel = incidentPriorityStats.priority.getDisplayValue();

    var priorityCount = incidentPriorityStats.getAggregate('COUNT', 'priority');

    gs.info("Priority: " + priorityLabel + " | Active Incidents: " + priorityCount);

}

4. Count Active Incidents Grouped by Category Using GlideAggregate

var incidentStats = new GlideAggregate("incident");

// Filter only active incidents

incidentStats.addActiveQuery();

// Group results by category and count each group

incidentStats.addAggregate('COUNT', 'category');

incidentStats.query();

// Display the count per category

while (incidentStats.next()) {

    var categoryName = incidentStats.category.getDisplayValue();

    var categoryCount = incidentStats.getAggregate('COUNT', 'category');

    gs.info("Category: " + categoryName + " | Active Incidents: " + categoryCount);

}

5. Retrieve Total Incident Count Using GlideAggregate

var incidentCount = new GlideAggregate('incident');

// Add COUNT aggregation to retrieve total number of incidents

incidentCount.addAggregate('COUNT');

// Execute the query

incidentCount.query();

// Output the result if available

if (incidentCount.next()) {

    gs.info("Total Incidents: " + incidentCount.getAggregate('COUNT'));

}