Glide Record Aggregate "Distinct"

[wac]

Hello everyone,

I am having troubles to perform a Disctinct-Query using Glide. I want to perform a query on the Software-Instances table. The goal is to get exactly one record-entry per Computer (installed_on) which meets a certain condition. e.g. Software-Install-Count less then 10.

What I get with the script below is a list of all computers in "order" which meet the requirement. Of course this requirement can be matched multiple times on some computer.

There for a want to perform a distinct query on computer (installed_on) or as Service-Now seems to offer a group_by.

If I try:

var gr = new GlideRecord(cmdb_software_instance);

gr.addEncodedQuery(some_filter_query);

gr.orderBy(current.u_field);

gr.groupBy('installed_on');

gr.query();

It is not working. Am i missing something?

Any ideas?

Cheers,

Chris

[ccajohnson]

I tried your sample script (changing the sys_id of the installed_on value) and was successful in returning one name. It is unclear as to how it is failing. Verify that your encoded query is correct by replicating the same query on the Software Instance table and copying the query for comparison.

I am curious as to what your query is. Based upon your original description, I guessed that you are looking for is Product Name Install count < 10. Here is what I used and it returned a list:

	var gr = new GlideAggregate('cmdb_software_instance');
	gr.addEncodedQuery('software.install_count<10');
	gr.groupBy('installed_on');
	gr.query();
	while(gr.next()){
	gs.log(gr.installed_on.name);
	}

[johnram]

The ServiceNow Wiki content is no longer supported. Updated information about this topic is located here: GlideAggregate

Visit http://docs.servicenow.com for the latest product documentation

[MGanon]

if GlideAggregate is for count, sum, min, & max, would GlideAggregate even work for a distinct list of text or alphanumerical data that don't need a count?

[sabell2012]

Chris:

You might try an alternative approach:

ServiceNow Admin 101: You Too Can Do DISTINCT Queries Using GlideRecord

Steven.

[MGanon]

If you still have the issue, I combined the 2 approaches: https://community.servicenow.com/community?id=community_question&sys_id=df874b13db0cc81013b5fb2439961975

My approach might not work for large queries though as identified here: https://community.servicenow.com/community?id=community_question&sys_id=57319fe9dbdcdbc01dcaf3231f961981&view_source=searchResult