GlideAggregate not returning expected results

[Jeff Bascou]

I'm trying to get all of the names used in our CMDB and the count of how many times they're used. (Don't ask why; the back-story is too long). I expected the code below to do that, but it does not return all the names - Note: it does return 100,001 names, counts, and links back to the CMDB with results that match. For example, we have 116,162 CIs names '00', and the code below lists it, with an accurate count, and a link back to the CMDB that returns that many records. But, we also have 86,685 CIs with the name 'NO NAME ASSIGNED' - these are not listed by the code below. Does anyone know why?

var getNames = new GlideAggregate('cmdb_ci');
getNames.addAggregate('COUNT','name');
getNames.query();//get name of every record in cmdb_ci and the count of that name
while(getNames.next()){//loop through all ci names
   writeName = new GlideRecord('u_cmdb_name_duplication');//access the table
   writeName.initialize();//create a record in the table
   var theName = getNames.getValue('name');//get the cis name
   writeName.u_name = theName;//write the cis name into the record
   var countName = getNames.getAggregate('COUNT','name');//get the count of cis with that name
   writeName.u_count = countName;//write the cis count into the record
   writeName.u_link = 'https://'+gs.getProperty('instance_name')+
      '.service-now.com/cmdb_ci_list.do?sysparm_query=name='+theName;//create a link back to cmdb_ci
   writeName.insert();//insert the record
}

[malaisamyj]

Hi Jeff,

try this,

var getNames = new GlideAggregate('cmdb_ci');
getNames.addAggregate('COUNT','name');

getNames.groupBy('name');

getNames.query();//get name of every record in cmdb_ci and the count of that name
while(getNames.next()){//loop through all ci names
   writeName = new GlideRecord('u_cmdb_name_duplication');//access the table
   writeName.initialize();//create a record in the table
   var theName = getNames.getValue('name');//get the cis name
   writeName.u_name = theName;//write the cis name into the record
   var countName = getNames.getAggregate('COUNT','name');//get the count of cis with that name
   writeName.u_count = countName;//write the cis count into the record
   writeName.u_link = 'https://'+gs.getProperty('instance_name')+
      '.service-now.com/cmdb_ci_list.do?sysparm_query=name='+theName;//create a link back to cmdb_ci
   writeName.insert();//insert the record
}

[Jeff Bascou]

same results

[Jeff Bascou · Accepted solution]

The right answer turns out to be: gs.setProperty("glide.db.max.aggregates","800000");. I increase this property when my Script starts, and I set it back to the default (100000) when the Script finishes.

[Pulkesh Haran]

@Jeff Bascou 

Hi,

how we can set this property from the scoped application?

gs.setProperty is not working and giving an error : 

Security restricted: Access to property 'glide.db.max.aggregates' from scope 'GRC: Risk Shared Common Components' has been refused due to the property's cross-scope access policy.

 After creating a cross-scope record ...still its not working.