GlideAggregate return wrong value

[Sharath807]

Hi all , Iam trying to get the count based on the query , but in system records its showing count as 534.

but when i run script in background its showing as 543. did i miss anything?

var totalCount = 0;
var count = new GlideAggregate('cmdb_sam_sw_install');
count.addEncodedQuery("discovery_model.u_software_name=c421a3e387e4d21075920d460cbb350b");

count.addAggregate('COUNT');

count.groupBy('installed_on');

count.query();
while (count.next()) {

totalCount = totalCount+1;

gs.info(count.getAggregate("COUNT")+ "--"+count.getValue("installed_on"));

}
gs.info("Total count: " + totalCount);

[Tai Vu]

Hi @Sharath807 

Could you clarify the expected result from your script above? Are you aiming for one of the following?

1. Getting the total record count with the encoded query: discovery_model.u_software_name=c421a3e387e4d21075920d460cbb350b

2. Retrieving the total count of unique devices from the Installed on [installed_on] field?

From your script, this line below 

gs.info("Total count: " + totalCount);

is giving you the result for the second option, which is the total of unique devices from the Installed on [installed_on] field.

Cheers,

Tai Vu

[Sharath807]

Hi @Tai Vu i need like after applying query and then group by based on installed on , it will show a count right, i need to display that count. is it possible?

[Tai Vu]

Hi @Sharath807 

Just saw the screenshot you provided. You may encounter this issue below.

When doing Group By on a column within the list, the count in the pagination may not match up with t...

There are two main reasons:
The first cause is that if you're grouping by on a column where there are empty values, these rows are grouped but not treated as a row count within the pagination.

The second cause is that if you're grouping by on a column which is a reference field, you can get what appears as duplications within the rows. This is because of the records in that table having the same display name value but are actually different values.

The total count from your script is currently giving the correct result total count of group by.

Cheers,

Tai Vu

[Sharath807]

Hi @Tai Vu   ok.. now i tried to get the count dynamic to all records, but its showing counts only based on the filter ( discovery_model.u_software_name)   not considering goupby (installed on.)

var count = new GlideAggregate('cmdb_sam_sw_install');

count.groupBy('discovery_model.u_software_name');

count.addAggregate('COUNT');

count.query();

while (count.next()) {

    var softwareName = count.getDisplayValue('discovery_model.u_software_name');

    var totalCount = count.getAggregate('COUNT');  

    gs.info("Software: " + softwareName + ", Installations count: " + totalCount);

    var softwareNormGR = new GlideRecord('u_norm_software');

    softwareNormGR.addQuery('u_name', softwareName);  

    softwareNormGR.query();

    if (softwareNormGR.next()) {

        softwareNormGR.u_used_count = totalCount;  

        softwareNormGR.update();  

        gs.info("Updated u_norm_software record for " + softwareName + " with total installations: " + totalCount);

    } else {

        gs.error("No matching u_norm_software record found for the software name: " + softwareName);

    }

}

[Tai Vu]

Hi @Sharath807 

From your script, it seems you're aiming to achieve the following:

1. Count the number of software installations for each Software Discovery Model.

2. Query the Norm Software table and update the Used Count accordingly.

This means the Installed on field isn't necessary for either of these steps. So, we can exclude that from the logic.

Let give my adjustment below a try, which updates the Used Count in the Norm Software table:

var count = new GlideAggregate('cmdb_sam_sw_install');
count.groupBy('discovery_model');
count.addAggregate('COUNT');
count.query();
while (count.next()) {
    var softwareName = count.getDisplayValue('discovery_model');
    var totalCount = count.getAggregate('COUNT');  
    gs.info("Software: " + softwareName + ", Installations count: " + totalCount);
   
    var softwareNormGR = new GlideRecord('u_norm_software');
    softwareNormGR.addQuery('u_name', softwareName);  
    softwareNormGR.query();
    if (softwareNormGR.next()) {
        softwareNormGR.u_used_count = totalCount;  
        softwareNormGR.update();  
        gs.info("Updated u_norm_software record for " + softwareName + " with total installations: " + totalCount);
    } else {
        gs.error("No matching u_norm_software record found for the software name: " + softwareName);
    }
}

Cheers,

Tai Vu