GlideAggregate: grouping by multiple columns

[tahnalos]

We have a requirement to provide a count of tickets based on customer and ticket categorization.  Normally, we would opt for a pivot report, but because we have over 35 categories, we're looking to aggregate in a script instead.  Because we have a piece of software that will interpret data, the objective is to present the data in a format that will be sent to the software to be interpreted.

My main question is the use of GlideAggregate.  How do we set up this script so we can do a count based on two separate criteria, customer and categorization?

[rajneeshbaranwa · Accepted solution]

You can create two Glideaggregate loop to get this information. Something like below.

var ga = new GlideAggregate('incident');
ga.addEncodedQuery('active=true^assigned_toISNOTEMPTY');
ga.addAggregate('count','assignment_group');
ga.query();
while (ga.next())
	{
	
var ga1 = new GlideAggregate('incident');
		ga1.addEncodedQuery('active=true^assigned_toISNOTEMPTY^assignment_group=' + ga.assignment_group);
		ga1.addAggregate('count','assigned_to');
		ga1.query();
		
		while (ga1.next())
			{
				
				
				gs.print('number of inc for assignment group' + ga.assignment_group.getDisplayValue() + ' and assigned to ' + ga1.assigned_to.getDisplayValue() + ' is ' + ga1.getAggregate('count','assigned_to'));
			}
		
		
		
		
	}

[VaranAwesomenow]

Extending this to CMDB CI duplicate counts

// duplicates can exist across the classes
var level1Field = 'u_primary_source_id';
var level2Field = 'sys_class_name';
var commonFilter = 'sys_created_byISNOTEMPTY^';
var sDebug = '';
var counter = 0;
var ga = new GlideAggregate('cmdb_ci');
ga.addEncodedQuery(commonFilter + level1Field + 'ISNOTEMPTY');
ga.addAggregate('count', level1Field);
ga.groupBy(level1Field);
ga.addHaving('COUNT', '>', '1');
ga.query();
while (ga.next()) {
var ga1 = new GlideAggregate('cmdb_ci');
ga1.addEncodedQuery(commonFilter + level1Field + 'ISNOTEMPTY' + "^" + level1Field+ '=' + ga[level1Field]);
ga1.addAggregate('count', level2Field);
ga1.groupBy(level2Field);
//ga1.addHaving('COUNT', '>', '1');

ga1.query();

while (ga1.next()) {
counter++;
sDebug += ga[level1Field] + "|" + ga1[level2Field] + "|" + ga1.getAggregate('count', level2Field) + "\n";
}
}
sDebug += " Total duplicates=" + counter;
gs.info(sDebug);