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

[jfarrer]

To do queries that group by something you'll need to use GlideAggregate instead of GlideRecord. It's documented here: GlideAggregate - ServiceNow Wiki

[wac]

Hello.

Thank you for the fast reply. This was actually a typo. I did use GlideAggregate, but it is not working.

[jfarrer]

Did you set the aggregate that you wanted and, if needed, ordered by it? Like this:

agg.addAggregate('count','installed_on');

agg.orderByAggregate('count', 'installed_on');

Then after querying, retrieved the aggregate with this:

agg.getAggregate('count','installed_on');

I'm guessing it's a typo as well, but the table name needs to be in quotes.

[wac]

Hello again,

sorry for the late reply. It is not working, though. I started over and properly I am missing something obvious:

Here is what I did:

var gr = new GlideAggregate('cmdb_software_instance');

gr.addEncodedQuery('installed_on=afc474e4bc0e3800389403f23d593cfc');

gr.groupBy('installed_on');

gr.query();

while(gr.next()){

       gs.log(gr.installed_on.name);

}

There is plenty installed on that computer, but the script should only return one record. Or am I mistaken?

Cheers,

Chris