Sooner or later every ServiceNow developer needs a number: active incidents per assignment group, open changes per CI, average reassignments per category. The first instinct is a GlideRecord loop with a counter. It works in a developer instance with 40 incidents and becomes the slowest thing on the page in production with 400,000. GlideAggregate is the tool built for this, and interviewers like it because using it well shows you think about where work happens: in the database or in your script.
Counting the expensive way
Here is the loop version of “active incidents per group”:
var counts = {};
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.query();
while (gr.next()) {
var group = gr.assignment_group.getDisplayValue() || '(no group)';
counts[group] = (counts[group] || 0) + 1;
}Every matching incident is fetched from the database, turned into a GlideRecord object and walked in JavaScript, only to increment a number. With 25,000 active incidents that’s 25,000 rows to produce a dozen totals. Calling gr.getRowCount() for a plain count has the same problem: the rows were still queried. Both patterns are what reviewers flag.
The GlideAggregate version
GlideAggregate extends GlideRecord and adds SQL-style aggregation: AVG, COUNT, GROUP_CONCAT, GROUP_CONCAT_DISTINCT, MAX, MIN, STDDEV and SUM. You describe the grouping and the aggregates, and the database returns one row per group. An illustrative script (the pattern follows the documented API; it wasn’t run against an instance for this article):
var ga = new GlideAggregate('incident');
ga.addQuery('active', true);
ga.groupBy('assignment_group');
ga.addAggregate('COUNT');
ga.addAggregate('AVG', 'reassignment_count');
ga.query();
while (ga.next()) {
var group = ga.assignment_group.getDisplayValue() || '(no group)';
var count = parseInt(ga.getAggregate('COUNT'), 10);
var avg = parseFloat(ga.getAggregate('AVG', 'reassignment_count'));
gs.info(group + ': ' + count + ' active, avg reassignments ' + avg.toFixed(2));
}Line by line: the filter goes in with addQuery exactly as on a GlideRecord. groupBy names the grouping field. addAggregate('COUNT') with no field counts rows per group; addAggregate('AVG', 'reassignment_count') averages a numeric field. Each next() moves to the next group, not the next incident, and getAggregate reads the computed value using the same type and field you passed to addAggregate.
For a single total, drop the groupBy and read one row:
var total = new GlideAggregate('incident');
total.addQuery('active', true);
total.addAggregate('COUNT');
total.query();
var activeCount = total.next() ? parseInt(total.getAggregate('COUNT'), 10) : 0;Running the same loop against sample data
To show what comes back, here’s a tiny in-memory stand-in written for this article. MiniAggregate is not ServiceNow code; it mimics the method names so the calling code mirrors the real script, and it rounds averages to two places (the real API’s formatting may differ).
class MiniAggregate {
constructor(rows) { this.rows = rows; this.filters = []; this.groups = []; this.aggs = []; }
addQuery(f, v) { this.filters.push((r) => r[f] === v); }
groupBy(f) { this.groups.push(f); }
addAggregate(type, field) { this.aggs.push([type, field]); }
query() {
const buckets = new Map();
for (const r of this.rows.filter((r) => this.filters.every((fn) => fn(r)))) {
const key = JSON.stringify(this.groups.map((g) => r[g]));
if (!buckets.has(key)) buckets.set(key, []);
buckets.get(key).push(r);
}
this.result = [...buckets.values()].map((rs) => ({ first: rs[0], rs }));
this.i = -1;
}
next() { return ++this.i < this.result.length; }
getValue(f) { return this.result[this.i].first[f]; }
getAggregate(type, field) { // like the real API, values come back as strings
const rs = this.result[this.i].rs;
if (type === 'COUNT') return String(rs.length);
if (type === 'AVG') return (rs.reduce((s, r) => s + r[field], 0) / rs.length).toFixed(2);
}
}
// 25 active incidents: Service Desk 11, Network 9, Database 3, unassigned 2
const groups = [['Service Desk', 11], ['Network', 9], ['Database', 3], ['', 2]];
const incidents = groups.flatMap(([g, n]) =>
Array.from({ length: n }, (_, k) => ({ active: true, assignment_group: g, reassignment_count: (k * 7 + g.length) % 4 })));
incidents.push({ active: false, assignment_group: 'Network', reassignment_count: 0 });
const ga = new MiniAggregate(incidents);
ga.addQuery('active', true);
ga.groupBy('assignment_group');
ga.addAggregate('COUNT');
ga.addAggregate('AVG', 'reassignment_count');
ga.query();
const rows = [];
while (ga.next()) {
const group = ga.getValue('assignment_group') || '(no group)';
rows.push({ group, count: ga.getAggregate('COUNT'), avg: ga.getAggregate('AVG', 'reassignment_count') });
}
rows.forEach((r) => console.log(`${r.group.padEnd(13)} count=${r.count.padStart(2)} avg reassignments=${r.avg}`));
console.log('rows returned to the script:', rows.length, 'instead of', incidents.length);
const asStrings = [...rows].sort((a, b) => (a.count < b.count ? 1 : -1)).map((r) => `${r.group}:${r.count}`);
const asNumbers = [...rows].sort((a, b) => parseInt(b.count, 10) - parseInt(a.count, 10)).map((r) => `${r.group}:${r.count}`);
console.log('busiest first, compared as strings:', asStrings.join(', '));
console.log('busiest first, parsed as numbers: ', asNumbers.join(', '));Service Desk count=11 avg reassignments=1.55
Network count= 9 avg reassignments=1.67
Database count= 3 avg reassignments=1.67
(no group) count= 2 avg reassignments=1.50
rows returned to the script: 4 instead of 26
busiest first, compared as strings: Network:9, Database:3, (no group):2, Service Desk:11
busiest first, parsed as numbers: Service Desk:11, Network:9, Database:3, (no group):2The script handled four rows instead of 26, and the inactive incident was filtered out before grouping. The last two lines are the bug that ships most often: compared as strings, "9" beats "11" because "9" sorts after "1", so the busiest team lands at the bottom of a “top groups” widget.
Three things that trip people up
Results are strings. getAggregate returns a string even for COUNT. Wrap it in parseInt or parseFloat before comparing, sorting or doing arithmetic. "3" + 1 is "31".
Empty values get a group. Incidents with no assignment group come back as their own row with an empty value. That’s often the most important row (unassigned work), so label it rather than letting it print as a blank line, and decide deliberately whether it belongs in the output.
Reference fields group by sys_id. Grouping on assignment_group groups by the referenced record’s sys_id, and you read the name with getDisplayValue(). Two groups both called “Network” in different companies produce two rows with the same label. If the output is keyed by name, one silently overwrites the other; key it by getValue('assignment_group') and display the name.
A smaller one: COUNT with a field name counts the non-null values of that field, which isn’t the same as counting rows. Use the no-argument form when you mean “how many records”.
Filtering groups and bucketing time
SQL’s HAVING clause (keep only groups whose aggregate passes a test) is available as addHaving in the global GlideAggregate API, for example addHaving('COUNT', '>', '10'). The scoped API doesn’t list it, so in a scoped application you filter the grouped rows in script, which is still cheap because there are only as many rows as groups.
For time series, addTrend buckets a date field by an interval such as week or month, which saves you grouping on a formatted date string. And orderByAggregate lets the database sort groups by an aggregate value, so a “top five” list doesn’t need to fetch every group.
When a script isn’t the right tool
If the numbers are for people to look at, a report or a Platform Analytics indicator is usually better than a script: it’s configurable, it respects the viewer’s access, and it doesn’t need a developer to change. Like GlideRecord, GlideAggregate in server script doesn’t apply ACLs, so a script that publishes counts (through a widget or a Scripted REST API) can reveal how many records exist in a table the viewer can’t read. Use GlideAggregate for logic: thresholds, routing decisions, recalculating a stored count in a business rule, or feeding an integration.
Interview practice
A Service Portal widget shows “Top 3 categories this month” using this server script, and it loads slowly and sometimes shows the wrong top category. What would you change?
var gr = new GlideRecord('incident');
gr.addEncodedQuery('opened_atONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()');
gr.query();
var c = {};
while (gr.next()) c[gr.getValue('category')] = (c[gr.getValue('category')] || 0) + 1;
var top = Object.keys(c).sort(function(a, b) { return c[a] < c[b] ? 1 : -1; }).slice(0, 3);Answer
The slowness comes from fetching every incident opened this month just to count them; replace the loop with a GlideAggregate on incident with the same encoded query, groupBy('category') and addAggregate('COUNT'), so the database returns one row per category. Then read the counts with parseInt, because the replacement’s getAggregate values are strings and sorting them as strings would reproduce exactly the “wrong top category” symptom (11 sorts below 9). The current code’s counts are numbers, so the wrong results probably come from somewhere else: incidents with no category all land under the key "null" or an empty string and can take a top slot. Decide whether uncategorised incidents should appear, label them, and filter them out if not.
I’d also sort with orderByAggregate('COUNT') or sort numerically in script and keep only three. Finally, because widget server scripts run as the user but GlideAggregate doesn’t check ACLs, confirm the viewers are allowed to know these counts; if not, restrict the widget or use a report that respects access.
Related reading
- ServiceNow interview questions
- ServiceNow multiple-choice questions
- ServiceNow Scheduled Jobs, a common home for nightly aggregate calculations
- ServiceNow Caching and Invalidation, when a computed number is cached for a dashboard
- GlideAggregate, scoped API (ServiceNow documentation)