Occasionally you may find yourself needing to write a script that needs to look at more than one row to get its answer. For instance, perhaps you need a count of all the Dell servers in a particular datacenter. Or maybe you need to know the average amount of memory installed on certain servers, or which servers have the most memory. Or maybe somebody is asking you a crazy question like "How much storage capacity do we have in the datacenter?"
You could, of course, write a little script that does any of these things — but that little script will have to read each record involved in the calculation, which means that every one of those records will have to be pulled out of the database and into your ServiceNow instance. If the calculation involves thousands of records, this is rather inefficient.
Wouldn't it be nice if the database could do this work for you? If you're familiar with SQL, you may know about the "aggregate functions" (COUNT, SUM, AVG, MIN, and MAX) that do exactly these sorts of things. They're called aggregate functions because the operate on an "aggregation" (collection) of records, not just one record. But how can you use an aggregate function from ServiceNow scripting? Why, with a GlideAggregate, of course!
Here's how you might get some statistics on memory in your Dell servers using the usual scripting techniques:
var gr = new GlideRecord('cmdb_ci_server');
gr.addQuery('manufacturer.name', 'Dell');
gr.query();
var count = 0;
var sum = 0
var min = 999999999;
var max = 0;
while (gr.next()) {
var ram = gr.ram - 0;
count++;
sum += ram;
if (ram < min) min = ram;
if (ram > max) max = ram;
}
gs.log('Total: ' + sum);
gs.log('Num: ' + count);
gs.log('Average: ' + (sum/count));
gs.log('Min: ' + min);
gs.log('Max: ' + max);
That works great, but when I run it against a CMDB with 10,000 Dell servers in it...it takes a long time.
Here's how to do the same thing with a GlideAggregate:
var ga = new GlideAggregate('cmdb_ci_server');
ga.addAggregate('SUM', 'ram');
ga.addAggregate('COUNT', null);
ga.addAggregate('AVG', 'ram');
ga.addAggregate('MIN', 'ram');
ga.addAggregate('MAX', 'ram');
ga.addQuery('manufacturer.name', 'Dell');
ga.setGroup(false);
ga.query();
if (ga.next()) {
gs.log('Total: ' + ga.getAggregate('SUM', 'ram'));
gs.log('Num: ' + ga.getAggregate('COUNT', null));
gs.log('Average: ' + ga.getAggregate('AVG', 'ram'));
gs.log('Min: ' + ga.getAggregate('MIN', 'ram'));
gs.log('Max: ' + ga.getAggregate('MAX', 'ram') + '\n');
}
Not only is the code simpler, but it executes much faster, and entirely in the database. Win!
A few notes:
- The COUNT aggregate doesn't have to have a field name. If you supply a null (as I did, above) for the name, then it simply counts every record (like a COUNT(*) in SQL). If you supply a name, then it counts all the non-null occurrences of that field.
- I called .setGroup(false) above to turn off grouping, which is on by default. Grouping allows you to get statistics for multiple groups of records in a single operation. See the script below for an example.
- The ServiceNow wiki has documentation for GlideAggregate
Here's a script that will get memory statistics for each server manufacturer:
var ga = new GlideAggregate('cmdb_ci_server');
ga.addAggregate('SUM', 'ram');
ga.addAggregate('COUNT', null);
ga.addAggregate('AVG', 'ram');
ga.addAggregate('MIN', 'ram');
ga.addAggregate('MAX', 'ram');
ga.setGroup(true);
ga.groupBy('manufacturer');
ga.query();
while (ga.next()) {
gs.log('Total: ' + ga.getAggregate('SUM', 'ram'));
gs.log('Num: ' + ga.getAggregate('COUNT', null));
gs.log('Average: ' + ga.getAggregate('AVG', 'ram'));
gs.log('Min: ' + ga.getAggregate('MIN', 'ram'));
gs.log('Max: ' + ga.getAggregate('MAX', 'ram') + '\n');
}
Note that this is an almost trivial change from the GlideAggregate script above, and is far easier than trying to do the same thing with GlideRecord...
4 Comments
