GlideAggregate

What It Is

GlideAggregate is a specialized GlideRecord subclass that translates directly to SQL aggregate functions at the database layer. While GlideRecord queries bring individual records into memory for processing, GlideAggregate pushes the computational work down to the database itself, returning only the calculated results. This architectural difference means the platform can count 50,000 incidents or sum financial values across thousands of records without the memory overhead and processing time that would crush a standard GlideRecord loop.

This API executes exclusively on the server side—there's no client-side equivalent because aggregate operations require direct database access that browsers cannot provide. You'll use it in Business Rules, Script Includes, Scheduled Scripts, and REST APIs, but never in Client Scripts or UI Policies. The underlying mechanism leverages the application server's database connection pool to execute optimized SQL queries with GROUP BY clauses, aggregate functions, and proper indexing strategies that ServiceNow's query optimizer can work with effectively.

Without GlideAggregate, you cannot efficiently count large datasets, calculate running totals, or generate summary statistics without hitting transaction timeouts or memory limits. I've seen developers try to count 10,000+ records with GlideRecord.getRowCount() only to watch their scripts fail with exceeded execution time errors. The difference between a 30-second timeout and a 200-millisecond response time often comes down to using the right tool for aggregate operations—and GlideAggregate is frequently that tool.

Administrators use GlideAggregate primarily in scheduled reports and data cleanup scripts where they need counts and totals across large tables. Developers leverage it in integrations, custom applications, and performance-critical Business Rules where real-time calculations matter. Architects design around it when building scalable solutions that need to maintain sub-second response times even as data volumes grow from thousands to millions of records.

GlideAggregate sits between standard GlideRecord queries and raw database operations in ServiceNow's data access hierarchy. Like GlideRecord, it respects Access Control Lists and business rules, but like direct SQL, it processes data at the database layer rather than in application memory. It shares syntax patterns with GlideRecord for filtering (addQuery()) but extends into aggregate-specific methods (addAggregate(), groupBy()) that map directly to SQL aggregate functions and GROUP BY clauses.

How It Works Under the Hood

When you execute a GlideAggregate query, ServiceNow's Object-Relational Mapping (ORM) layer translates your method calls into native SQL aggregate queries with proper WHERE clauses, GROUP BY statements, and aggregate functions like COUNT(), SUM(), and AVG(). The platform's query optimizer applies available indexes, executes the query against the underlying database (MySQL, Oracle, or SQL Server depending on your instance), and returns only the calculated results rather than individual record data.

The key architectural advantage lies in data movement—or the lack thereof. A standard GlideRecord query requesting 10,000 incidents transfers those records from database to application server memory, then processes them one by one. A GlideAggregate query transfers only the final count or sum, regardless of whether it calculated across 100 records or 100,000. This dramatically reduces network traffic between database and application tiers while leveraging the database engine's native optimization for aggregate operations.

The Request Lifecycle

1. Script execution reaches your GlideAggregate instantiation and method calls (addQuery(), addAggregate(), etc.) within the application server's Rhino JavaScript engine
2. The query() method triggers ServiceNow's ORM to construct native SQL with aggregate functions, applying ACL filters and table inheritance rules automatically
3. The database engine executes the SQL query using available indexes, performing aggregation at the storage layer and returning only calculated results
4. ServiceNow receives the aggregated data and makes it available through the GlideAggregate object's next() and getAggregate() methods for your script to consume
5. Your script processes the results, typically a small number of rows containing grouped data and calculated aggregates rather than thousands of individual records

The Core Pattern

// Basic GlideAggregate pattern for counting and summing
// Always instantiate with target table name
var ga = new GlideAggregate('incident');

// Add filters just like GlideRecord - these become WHERE clauses
ga.addQuery('state', 'IN', '1,2,3'); // Only open incidents
ga.addQuery('priority', '<=', '3'); // High priority only

// Define what to calculate - this becomes the aggregate function
ga.addAggregate('COUNT'); // Most common - just count matching records

// Execute the query - this sends SQL to database
ga.query();

// Process results - typically only one row for simple aggregates
if (ga.next()) {
    var incidentCount = ga.getAggregate('COUNT');
    gs.log('High priority open incidents: ' + incidentCount);
    
    // Use the count for business logic, reports, or integrations
    return parseInt(incidentCount);
}

var IncidentMetrics = Class.create();
IncidentMetrics.prototype = {
    
    // Reusable method for getting incident counts by assignment group
    getIncidentCountByGroup: function(groupSysId, stateFilter) {
        var ga = new GlideAggregate('incident');
        
        // Filter by assignment group - core business requirement
        ga.addQuery('assignment_group', groupSysId);
        
        // Optional state filter with sensible default
        if (stateFilter) {
            ga.addQuery('state', stateFilter);
        }
        
        // Group by assignment group name for readable results
        ga.groupBy('assignment_group.name');
        ga.addAggregate('COUNT');
        
        ga.query();
        
        // Return structured data for consumption by other scripts
        var results = [];
        while (ga.next()) {
            results.push({
                group: ga.getValue('assignment_group.name'),
                count: parseInt(ga.getAggregate('COUNT'))
            });
        }
        return results;
    },
    
    type: 'IncidentMetrics'
};

Real-World Scenarios

Service Catalog Cost Analysis

Financial teams need monthly reports showing total costs by department for Service Catalog requests. A GlideRecord approach would timeout on large datasets, but GlideAggregate handles the calculation efficiently at the database layer.

// Calculate total Service Catalog costs by department for last month
var startDate = new GlideDateTime();
startDate.addMonths(-1);
startDate.setDayOfMonth(1);

var endDate = new GlideDateTime(startDate);
endDate.addMonths(1);

var ga = new GlideAggregate('sc_req_item');

// Filter to completed requests from last month
ga.addQuery('state', '3'); // Closed Complete
ga.addQuery('opened_at', '>=', startDate.getValue());
ga.addQuery('opened_at', '<', endDate.getValue());
ga.addNotNullQuery('price'); // Only items with pricing

// Group by requesting department for breakdown
ga.groupBy('request.requested_for.department.name');
ga.addAggregate('SUM', 'price'); // Sum the price field
ga.addAggregate('COUNT'); // Also count items per department

ga.query();

while (ga.next()) {
    var dept = ga.getValue('request.requested_for.department.name') || 'Unknown';
    var totalCost = parseFloat(ga.getAggregate('SUM', 'price'));
    var itemCount = parseInt(ga.getAggregate('COUNT'));
    
    gs.log('Department: ' + dept + ', Cost: $' + totalCost.toFixed(2) + ', Items: ' + itemCount);
}

Watch for null values in price fields—they'll skew your aggregations. Always use addNotNullQuery() on fields you're summing, and handle department references that might not exist through proper null checking in your results processing.

SLA Performance Dashboard

Operations teams need real-time visibility into SLA breach counts by priority and assignment group. This data feeds executive dashboards that update every few minutes, requiring sub-second query performance.

// Fast SLA breach counting for dashboard widgets
function getSLABreachMetrics(priorityFilter) {
    var metrics = {};
    
    // Count active SLA breaches by priority and group
    var ga = new GlideAggregate('task_sla');
    ga.addQuery('active', 'true');
    ga.addQuery('has_breached', 'true');
    
    // Filter by priority if specified
    if (priorityFilter) {
        ga.addQuery('task.priority', priorityFilter);
    }
    
    // Group by priority and assignment group for detailed breakdown
    ga.groupBy('task.priority');
    ga.groupBy('task.assignment_group.name');
    ga.addAggregate('COUNT');
    
    ga.query();
    
    while (ga.next()) {
        var priority = ga.getValue('task.priority') || '5';
        var group = ga.getValue('task.assignment_group.name') || 'Unassigned';
        var breachCount = parseInt(ga.getAggregate('COUNT'));
        
        if (!metrics[priority]) metrics[priority] = {};
        metrics[priority][group] = breachCount;
    }
    
    return metrics;
}

Change Management Risk Assessment

Change Advisory Boards need historical data on change success rates by type and risk level to make informed approval decisions. This analysis examines thousands of closed changes to calculate success percentages.

// Calculate change success rates for risk assessment
function getChangeSuccessRates(changeType, riskLevel, daysPast) {
    var cutoffDate = new GlideDateTime();
    cutoffDate.addDays(-daysPast);
    
    var ga = new GlideAggregate('change_request');
    ga.addQuery('state', '3'); // Closed
    ga.addQuery('opened_at', '>=', cutoffDate.getValue());
    
    // Filter by type and risk if provided
    if (changeType) ga.addQuery('type', changeType);
    if (riskLevel) ga.addQuery('risk', riskLevel);
    
    // Group by type, risk, and outcome for comprehensive view
    ga.groupBy('type');
    ga.groupBy('risk');
    ga.groupBy('close_code'); // Success vs Failed outcomes
    ga.addAggregate('COUNT');
    
    ga.query();
    
    var results = {};
    while (ga.next()) {
        var type = ga.getValue('type') || 'unknown';
        var risk = ga.getValue('risk') || 'unknown';
        var outcome = ga.getValue('close_code') || 'unknown';
        var count = parseInt(ga.getAggregate('COUNT'));
        
        var key = type + '_' + risk;
        if (!results[key]) results[key] = { successful: 0, failed: 0 };
        
        if (outcome === 'successful' || outcome === 'successful_issues') {
            results[key].successful += count;
        } else {
            results[key].failed += count;
        }
    }
    
    return results;
}

Change request close codes vary significantly between organizations, so validate your success/failure logic against actual data. Consider using addNullQuery() to exclude changes that were cancelled before implementation, as they don't represent true success or failure data for risk calculations.

The Classic Mistake

// Trying to get incident count by state - WRONG
var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT');
agg.groupBy('state');
agg.query();

// This returns undefined/empty - query hasn't executed yet
var totalCount = agg.getAggregate('COUNT');
gs.info('Total incidents: ' + totalCount);

// Even this loop won't work as expected
while (agg.next()) {
    var state = agg.getValue('state');
    var count = agg.getAggregate('COUNT');
    gs.info('State ' + state + ': ' + count + ' incidents');
}

This fails because getAggregate() returns undefined until you call next() to position the cursor on a result row. ServiceNow doesn't execute the aggregate query until the first next() call, so accessing aggregate values beforehand returns nothing. You won't see an error in the logs — just empty variables that make your reports show zeros. The query executes successfully, but you're reading from an unpositioned cursor.

// Get incident count by state - CORRECT
var agg = new GlideAggregate('incident');
agg.addAggregate('COUNT');
agg.groupBy('state');
agg.query();

// Always call next() first to position cursor
while (agg.next()) {
    var state = agg.getValue('state');
    var stateDisplayValue = agg.getDisplayValue('state');
    var count = agg.getAggregate('COUNT');
    
    gs.info('State ' + stateDisplayValue + ' (' + state + '): ' + count + ' incidents');
}

// For single aggregate without groupBy
if (agg.next()) {
    var totalCount = agg.getAggregate('COUNT');
    gs.info('Total incidents: ' + totalCount);
}

Performance Rules

1. Always use addEncodedQuery() instead of multiple addQuery() calls when you have more than 3 conditions. Multiple addQuery() calls over large tables (>50,000 records) can cause query timeouts after 30 seconds.
2. Never use orderBy() on aggregated results. ServiceNow ignores this method on GlideAggregate, wasting CPU cycles and potentially causing memory errors on datasets over 10,000 grouped results.
3. Limit groupBy() to maximum 3 fields. More than 3 grouping columns on tables with over 100,000 records triggers automatic query cancellation by the database governor, returning incomplete results without error messages.
4. Always include indexed fields in addQuery() filters. Queries without indexed conditions on tables over 500,000 records cause full table scans, consuming 8+ GB RAM and crashing browser sessions for other users.
5. Never call getRowCount() on GlideAggregate. This method executes a second, separate query against the database, doubling your execution time and potentially hitting transaction limits in Business Rules.
6. Use setLimit(1000) on any aggregate query that might return more than 5,000 grouped results. Unlimited results consume server memory linearly and will crash the application node when memory exceeds 16GB.
7. Avoid SUM() and AVG() on non-numeric fields. ServiceNow performs implicit type conversion on every row, adding 200-400% overhead to query execution time and generating millions of entries in the application log.
8. Never use GlideAggregate inside while loops or forEach() iterations. Each aggregate query opens a new database connection, and exceeding 100 concurrent connections triggers automatic session termination by the database connection pool.

Side Effects & Platform Behavior

• Does NOT trigger Business Rules, ACLs, or UI Policies. GlideAggregate queries bypass all record-level security and business logic, executing as raw SQL against the database.
• Creates entries in syslog table when queries exceed 10 seconds execution time. These logs include full SQL statements and execution plans, visible to database administrators.
• Writes to sys_db_cache table for query result caching. Results are cached for 5 minutes by default, meaning identical aggregate queries return stale data during that window.
• Ignores field-level Read ACLs completely. Users can aggregate data from fields they cannot normally access, potentially exposing sensitive information in reports and dashboards.
• Updates sys_user_session.last_activity timestamp on every query execution, extending user session timeouts and affecting concurrent user limits.
• Breaks in Service Portal client scripts and Catalog Client Scripts because database connections are not available in the browser context. Generates ReferenceError exceptions.
• Automatically converts reference fields to sys_id values in getValue() calls. Use getDisplayValue() to get human-readable reference field values.
• Creates temporary tables in database when using HAVING conditions on aggregate results. These consume additional disk space and are not automatically cleaned up until database maintenance windows.
• Executes with elevated privileges in scheduled jobs and Business Rules, bypassing table-level ACLs that would normally restrict access to sys_user, sys_user_group, and other security-related tables.
• Fails silently when querying non-existent fields, returning null values without error messages. No validation occurs against the table schema until query execution.

Debugging When It Breaks

The most common failure symptoms are empty results when you expect data, or undefined values from getAggregate() calls. Users typically see blank reports, zero counts in widgets, or "No data available" messages in Performance Analytics. The browser console shows no JavaScript errors because the code executes successfully — it just returns empty results. Server-side, you'll see successful query execution logs with zero rows returned, which makes the problem harder to spot.

For server-side debugging, check System Log > All for entries containing "GlideAggregate" with your table name. Query timeout errors appear as "Database query cancelled" messages with the full SQL statement. Memory issues show up as "OutOfMemoryError" exceptions in the application node logs, usually followed by automatic service restarts. Database connection pool exhaustion generates "Connection refused" errors in syslog_app_node table with timestamps matching your aggregate query execution.

Quick diagnostic checklist:

• Add gs.info(agg.next()) before accessing aggregate values — should return true if query has results
• Test the same query conditions on regular GlideRecord first to verify data exists
• Check if aggregate field names match exactly — typos in getAggregate('COUTN') return null
• Verify field types for SUM/AVG operations using Table Schema or sys_dictionary table lookups
• Enable SQL debugging with gs.log_sql = true to see generated SQL in system logs

Quick Reference

• Always call next() before getAggregate() — aggregate values only exist after positioning cursor on result row
• Use getDisplayValue() for reference fields in groupBy() — getValue() returns sys_id values
• Aggregate function names are case-sensitive: 'COUNT', 'SUM', 'AVG', 'MIN', 'MAX' must be uppercase
• Cannot use orderBy(), setLimit(), or chooseWindow() with grouped results — these methods are ignored
• Only works server-side in Business Rules, Script Includes, Scheduled Jobs, and Background Scripts
• Results are cached for 5 minutes by default — identical queries return same data during cache window
• For single aggregate without grouping, use if(agg.next()) instead of while loop
• Bypasses all record-level security — use gs.hasRole() checks when exposing aggregate data to users
• Add addNotNullQuery() on aggregate fields to exclude null values from SUM/AVG calculations
• Multiple addAggregate() calls in single query are supported — access each with separate getAggregate() calls