Need 'COUNT' to return records with zero entries in script

[David Casper]

I'm querying the cmdb_ci class table in order to pull all CIs by class. I'm using COUNT but it is leaving out Classes with zero CIs. 

Below is my current script. Thanks!

var gr = new GlideAggregate('cmdb_ci');
gr.addAggregate('COUNT');
gr.orderbyAggregate('COUNT');
gr.groupBy('sys_class_name');
gr.query();
while(gr.next()){
	var classCount = gr.getAggregate('COUNT');
	gs.print(gr.sys_class_name + ' ' + classCount);
}

[Chuck Tomasi]

That's because GlideAggregate is only counting records. It's a bit like saying "See all the houses in this city? Count the number and group them by color". If there are no polka dot houses, you won't get a count because that's not out there to physically count.

What you need is a list of all classes, then say "How many records does each class have?" (i.e. what color houses are you specifically interested in counting?)

I suggest using TableUtils - getTableExtensions to get the list of tables that extend from cmdb_ci like this. It returns an array which you can then use to loop over GlideAggregate()

var table = new TableUtils("cmdb_ci");
var list = table.getTableExtensions();

for (var i = 0; i < list.length; i++) {
  // Do your GlideAggregate count for records on each list[i] here
}

Note: TableUtils is Global only

[David Casper]

Great analogy! lol I had a feeling that is what it was doing. I've not heard of the TableUtils, I'll make sure to look into that and l let you know how it goes. Thanks for the response!

[David Casper]

So I tried the simplest approach without success. I'm assuming this will run through the background scripts, but coding is not my strongest suite. Suggestions? 

var table = new TableUtils('cmdb_ci');
var list = table.getTableExtensions();
for (var i = 0; i < list.length; i++) { 
	var gr = new GlideAggregate('cmdb_ci');
gr.addAggregate('COUNT');
gr.orderbyAggregate('COUNT');
gr.groupBy('sys_class_name');
gr.query();
while(gr.next()){
	var classCount = gr.getAggregate('COUNT');
	gs.print(gr.sys_class_name + ' ' + classCount);
}
}

[Chuck Tomasi · Accepted solution]

It would go more like this... 

var tu = new TableUtils('cmdb_ci');
var tables = tu.getTableExtensions();
var list = tables.toArray();
gs.info('length=' + list.length);
for (var i = 0; i < list.length; i++) { 
	var gr = new GlideAggregate(list[i]);
	gr.addAggregate('COUNT');
	gr.query();
	if (gr.next()){
		var classCount = gr.getAggregate('COUNT');
		gs.print(list[i] + ' ' + classCount);
	}
}

The thing to note is that some items may get counted twice. For example if cmdb_ci_server has 40 items, that class also includes for example, 10 Unix servers from cmdb_ci_unix_server. There's really no way to get around this because of the way table extensions work unless you can absolutely guarantee that all CIs are in the "end tables" only, then you could add a couple lines to the code to say "If this table has extensions, then don't count it" and you'll only get those end-tables, but I find very few people have a CMDB without some CIs in a parent class somewhere.