Aggregate count not working

Dead Blade · ‎09-30-2020

Hello All, the aggregate count is not working as expected. Please help. The dot-walk addQuery should return a count of 100 for example, but I am getting 0 results.

		
var count = new GlideAggregate('alm_hardware');   
count.addQuery('model.u_field','1111111');
count.addAggregate('COUNT');  
count.query();

gs.log("This count is empty: "+count.getAggregate('COUNT'));
if(count.next()){

gs.log("This count is working: "  +count.getAggregate('COUNT'));
gs.log('count');
}

Results:

*** Script: This count is empty: null
*** Script: This count is working: 0
*** Script: count
*** Script: 
----------------------------------------------------------

The Second row should be a count of 100

Willem · ‎10-06-2020

To help you, you can run this code:

var usedModels = 0;
var grModel = new GlideRecord('cmdb_model');
grModel.addQuery('u_field', '1111111');
grModel.query();
while (grModel.next()) {
    var grHardware = new GlideRecord('alm_hardware');
    grHardware.addQuery('model', grModel.getUniqueValue());
    grHardware.query();
    if (grHardware.next()) {
        gs.log("The model: " + grModel.getDisplayValue() +  " is used in Hardware");
        usedModels++;
    }

}
gs.log("There are: " + grModel.getRowCount() + ' Models available with 111111');
gs.log("There are: " + usedModels + ' Models used for Hardware with 111111');

Please share the log results.

View solution in original post

Dead Blade · ‎10-01-2020

The model field is a reference field to cmdb_model

Here is the path to the u_field list results:

service-now.com/cmdb_model_list.do?sysparm_query=u_field%3D1111111&sysparm_first_row=1&sysparm_view=

Willem · ‎10-01-2020

Yeah, I understand that. But you are querying the alm_hardware table. So there must be records in the alm_hardware table. That have a model (in the cmdb_model table) which has u_field set to 1111111.

If you followed my link you get that result. Which is not the same as all models that have u_field set to 1111111. Because not all models will be used on the alm_hardware table.

Let me know if you have questions about my explanation.

What is the result when you followed my link?

Dead Blade · ‎10-01-2020

Following your link I receive 0 results.

Willem · ‎10-01-2020

Yes, so there are no alm_hardware CI's that have a model (cmdb_model) with u_field 1111111.

That is why your glide aggregate also returns 0.

You can check this by changing the model of one of the alm_hardware records to a model that has u_field set to 1111111 and run your script again. Then it will return 1.

Dead Blade · ‎10-01-2020

If I run the same query on cmdb_model then I receive the required results.

var count = new GlideAggregate('cmdb_model');   
count.addAggregate('COUNT');
count.addQuery('u_field','1111111');
count.query();

gs.log("This count is empty: "+count.getAggregate('COUNT'));
if(count.next()){

gs.log("This count is working: "  +count.getAggregate('COUNT'));
gs.log('count');
}

*** Script: This count is empty: null
*** Script: This count is working: 100
*** Script: count

I am correcting the previous GlideRecord queries with GlideAggregate per SN Best practices. So I have to use the alm_hardware instead of cmdb_model. Is this possible to get the required results without changing tables in the GlideAggregate?