Aggregate count not working

Go to solution
Dead Blade
Kilo Guru

‎09-30-2020 10:19 AM

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
0 Helpfuls
  • 2,502 Views
1 ACCEPTED SOLUTION

Go to solution
Willem
Giga Sage
Giga Sage
In response to Dead Blade

‎10-06-2020 12:21 PM

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

37 REPLIES 37

Go to solution
Dead Blade
Kilo Guru
In response to Willem

‎10-01-2020 07:54 AM

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=

0 Helpfuls

Go to solution
Willem
Giga Sage
Giga Sage
In response to Dead Blade

‎10-01-2020 08:05 AM

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?

 

Go to solution
Dead Blade
Kilo Guru
In response to Willem

‎10-01-2020 08:32 AM

Following your link I receive 0 results.

0 Helpfuls

Go to solution
Willem
Giga Sage
Giga Sage
In response to Dead Blade

‎10-01-2020 08:35 AM

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.

Go to solution
Dead Blade
Kilo Guru
In response to Willem

‎10-01-2020 08:45 AM

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?

0 Helpfuls