JavaScript Tips and Tricks: Aggregates...

A few weeks ago I was talking with one of our customers (I'll call him Joe) who had just finished writing a script that created a little report of how many items of each type were in his CMDB. Joe was quite happy with his script except that it took a long time to run. Here's what Joe had written:

var gr = new GlideRecord('cmdb_ci');
gr.orderBy('sys_class_name');
gr.query();

var name = '';
var count = 0;
while (gr.next()) {
    var thisName = '' + gr.getValue('sys_class_name');
    if (thisName == name)
        count++;
    else {
        if (name != '')
            gs.log(thisName + ': ' + count);
        name = thisName;
        count = 1;
    }
}
gs.log(name + ': ' + count);

Joe did this the hard, slow way — there's a much easier way, one that exploits the capabilities of the database that underlies every Service-now.com instance.

Joe's little script queries the entire CMDB (which, in his case, had almost a half million entries!) and then one-by-one examines every record to see what's in it. It keeps track of the count of each of each kind of CMDB CI very nicely — and over the course of about 40 minutes, it got Joe the answer I wanted. But 40 minutes? Surely a computer could do better than that?!?

So I showed Joe an alternative approach, based on GlideAggregate (documented on our Wiki😞

var ga = new GlideAggregate('cmdb_ci');
ga.addAggregate('COUNT', 'sys_class_name');
ga.query();
while(ga.next()) {
    var result = ga.getAggregate('COUNT', 'sys_class_name');
    gs.log(ga.getValue('sys_class_name') + ': ' + result);
}

The first thing Joe noticed is that my script was a bit shorter. The second thing he noticed is that it only took a couple of seconds to execute. Shorter, simpler, faster — sweet!

GlideAggregates take advantage of the aggregation features built right into modern SQL databases. Computers really are very good at counting!