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

Willem · ‎09-30-2020

Code looks fine. Have you checked the table with this query? Do you see 100 records?

Perhaps the added Query model.u_field = 1111111 is not returning the expected records (count)?

Tested this script with dot walked condition:

count.addQuery('model_category.allow_as_master','true');

Result:

So best bet is to check the query you are using.

Dead Blade · ‎09-30-2020

Hi Willem, yes I have went to the model table list and filtered by "1111111". Results are 100.

As a test I used

count.addQuery('model_category.allow_as_master','true');

as well.

Results:

Time: 0:00:02.080 id: *****[glide.31] (connpid=61167) for: SELECT count(*) AS recordcount FROM ((alm_hardware alm_hardware0  INNER JOIN alm_asset alm_asset0 ON alm_hardware0.`sys_id` = alm_asset0.`sys_id` )  LEFT JOIN cmdb_model_category cmdb_model_category1 ON alm_asset0.`model_category` = cmdb_model_category1.`sys_id` )  WHERE cmdb_model_category1.`allow_as_master` = 1 /* ****, gs:587299861BE75C10874AC9DA234BCB49, tx:298391c61ba75c10874ac9da234bcbdc */ 
*** Script: This count is empty: null
*** Script: This count is working: 322032
*** Script: count
*** Script: 
----------------------------------------------------------

Just does not seem to work with

count.addQuery('model.u_field','1111111');

Willem · ‎09-30-2020

Is the u_field a reference? then you have to do something like:

'model.u_field.name', '111111111' or whichever field on the u_field referenced record contains the 11111111.

Dead Blade · ‎09-30-2020

The u_field is a string field.

Willem · ‎09-30-2020

How many records do you see if you go to:

yourinstance.service-now.com/alm_hardware_list.do?sysparm_query=model.u_field=1111111

If that shows a 100 records, then there is something wrong.

If that shows 0 records, you need to double check your query. Because then the query returns 0 results.