How to get the Top 10 values from a table using the GlideAggregate function


Description

The following is an example script that demonstrates how you can determine the top N records from a table.  For example, sometimes you might want to check and see which task has the most journal entries, or the most attachments.  The following script shows how this can be done using the GlideAggregate function.  Getting the top N values from a table can help to identify some possible health issues (eg., tasks wiith an excessive number of worknotes or emails with an excessive number of attachments).

Release or Environment

All

Resolution

topN function

Here is a function called "topN" that takes, as input, the tablename, the fieldname that you want to find the top N values for, and the number representing the number of the top records you want to display:  

function topN(pTable, pColumn, pCount) {
var ga = new GlideAggregate(pTable);
ga.addAggregate('COUNT', pColumn);
ga.orderByAggregate('COUNT', pColumn);
ga.query();
var i = 0;
var stdout = [];
stdout.push('Top ' + pCount + ' ' + pColumn + ' values from ' + pTable + '\n');
while (ga.next() && (i++ < pCount)) {
stdout.push(ga.getValue(pColumn) + ' ' + ga.getAggregate('COUNT', pColumn));
}
gs.print(stdout.join("\n"));
}

Please note that as some tables can become quite large, these functions can take some time to process.

Example Output

// Display the top 10 documentkey values from the sys_audit table
topN('sys_audit', 'documentkey', 10);

// Display the top 10 instance values from sys_email
topN('sys_email', 'instance', 10);

// Display the top 10 element_id values from sys_journal_field
topN('sys_journal_field', 'element_id', 10);

// Display the top 10 table_sys_id from sys_attachment
topN('sys_attachment', 'table_sys_id', 10);

function topN(pTable, pColumn, pCount) {
var ga = new GlideAggregate(pTable);
ga.addAggregate('COUNT', pColumn);
ga.orderByAggregate('COUNT', pColumn);
ga.query();
var i = 0;
var stdout = [];
stdout.push('\nTop ' + pCount + ' ' + pColumn + ' values from ' + pTable + '\n');
while (ga.next() && (i++ < pCount)) {
stdout.push(ga.getValue(pColumn) + ' ' + ga.getAggregate('COUNT', pColumn));
}
gs.print(stdout.join("\n"));
}
*** Script: Top 10 documentkey values from sys_audit

44c93a2dbf2111007c94c0647e07396b 567
f66df211bf0211007c94c0647e0739e3 567
ef1c0673db202300eba776231f96192e 471
fd030171dfa0010068c37a0d3df263c7 82
1c741bd70b2322007518478d83673af3 59
757cd565dbbda70097ee9ee6db96198f 52
cb6c1565dbbda70097ee9ee6db961946 52
cc6c5165dbbda70097ee9ee6db96190e 52
eb4c5d25dbbda70097ee9ee6db961942 52
1c4cd925dbbda70097ee9ee6db96194b 49

*** Script: Top 10 instance values from sys_email

016c5165dbbda70097ee9ee6db9619dc 7
0c7c5565dbbda70097ee9ee6db96192b 7
3c5c5d25dbbda70097ee9ee6db9619f4 7
3e5cdd25dbbda70097ee9ee6db961979 7
627c1965dbbda70097ee9ee6db96194c 7
883e695bdbfa6b00f78b9007db9619bb 7
b27c1965dbbda70097ee9ee6db96198d 7
c95c9d25dbbda70097ee9ee6db96194e 7
d16c9165dbbda70097ee9ee6db961904 7
fb6c1565dbbda70097ee9ee6db9619ea 7

*** Script: Top 10 element_id values from sys_journal_field

1c741bd70b2322007518478d83673af3 12
48f89d7c0b2022003be12da0d5673a98 11
64c04c8c0b3022003be12da0d5673ac0 11
7ca5e98b0b2022003be12da0d5673a67 10
1a3337610b6022003be12da0d5673ae6 9
1e118c8c0b3022003be12da0d5673a86 9
a619f77f0b2022003be12da0d5673a03 9
a9e9c33dc61122760072455df62663d2 9
b94f7bbf0b2022003be12da0d5673a14 9
ca218c8c0b3022003be12da0d5673a9b 9

*** Script: Top 10 table_sys_id values from sys_attachment
7d1915894f12020031577d2ca310c7ae 10
b187ad0d4f12020031577d2ca310c7c9 9
cd0f59c94f12020031577d2ca310c790 8
7b4111894f12020031577d2ca310c70b 7
cfc2c1d54f52020031577d2ca310c797 7
0425d5894f12020031577d2ca310c75c 6
7a134ae54f52020031577d2ca310c70c 6
94e211894f12020031577d2ca310c70f 6
c70615894f12020031577d2ca310c74c 6
48d4e45893c13200bebe7344967ffb07 5

Additional Information

GlideAggregate - Global
https://docs.servicenow.com/csh?topicname=c_GlideAggregateAPI.html&version=latest

GlideAggregate - Scoped
https://docs.servicenow.com/csh?topicname=c_GlideAggregateScopedAPI.html&version=latest