Get Unique Values with GlideAggregate

[Lukasz Bojara]

Recently I had to check what are the unique values for one field in a table. The table had over 5000 records and at least 400 unique values. For a quick check, I could just group the list view by this filed and read the number. The issue is that I needed a comma-separated list of those values. So I have created a function that I can use on any table by just changing the input parameters.

After doing the same kind of scripting over and over again I have identified 4 conditions:

• it will need a table name parameter
• it will need a field name that I need the values form parameter
• it will need a possibility to filter the initial list parameter
• it needs to return an array of unique value

The best solution, especially from the performance point of view is to use GlideAggregate as it is best suited for counting and that is in fact what we will do. In overall the function will use GlideAggregate to count the record and group them by the chosen field, this way there will be only one row returned for each unique value and this unique value will be added to the array that will be returned.

Here is how the function looks like:

/**
  * @function getUniqueValues
  * @description Get unique values for one field.
  * @param table {string} - name of the table to be queried
  * @param groupingAttribute {string} - name of the field that the unique values are needed
  * @param encodedQuery {string} - encoded query to pre-filter the results
  * @returns {array} array of unique values, empty array if not found
  *
  */
function getUniqueValues(table,groupingAttribute,encodedQuery){
	var uniqueValues = [];
	
	var ga = new GlideAggregate(table);
	ga.addAggregate('COUNT');
	ga.groupBy(groupingAttribute);
	ga.addHaving('COUNT','>','0'); // get only values where count is more than 1
	
	// check if encoded query has been provided, if yes the it will be added to the GlideAggregate object
	if(encodedQuery != undefined) {
		ga.addEncodedQuery(encodedQuery);
	} 

	
	ga.query();
	
	while (ga.next()) {
		// check if the row is for a unique value and not for an overall count
		if(ga.getDisplayValue(groupingAttribute)){
			uniqueValues.push(ga.getDisplayValue(groupingAttribute)); // add the value to the array
		}
	}
	
	return uniqueValues;
}

And here is an example of use, get the list of groups that have active incidents on them:

var test = getUniqueValues('task','assignment_group','active=true');

gs.debug(test);

With a result:

[
  "Application Security",
  "CAB Approval",
  "Catalog Request Approvers > $1000",
  "Change Management",
  "Database",
  "Database San Diego",
  "Database Security team ",
  "Email Server Support Group",
  "Endpoint Security",
  "Hardware",
  "Help Desk",
  "Incident Management",
  "ITSM Engineering",
  "Network",
  "NY DB",
  "Openspace",
  "Problem Solving",
  "Procurement",
  "Service Desk",
  "Software",
  "Unix Support Group",
  "Vulnerability Response",
  "Windows Server support "
]

This function can be used with combination to many use cases, especially when you need a list of comma-separated values for your IN query.

[Daniel Oderbolz]

Thanks, that is a good function. 

Only one thing should be changed - the line 

ga.addHaving('COUNT','>','0'); // get only values where count is more than 1

Should read

ga.addHaving('COUNT','>','1'); // get only values where count is more than 1

Best
Daniel

[Phonsie Hevey1]

That is so useful, thanks so much for posting here.