How to run a distinct count on user records with the same name

[damianfell]

I have an unusual problem, I need to generate a unique count of users with devices assigned.

On the face of it this is simply a distinct count of the assigned_to field on the cmdb_ci table,

However irrespective of whether I use the distinct count function in the reporting engine, or a glideAggregate query in a scripted count gauge, the numbers are too low.

It would appear that both these methods are doing their distinct calculation based on the display field of the assigned_to field, not the sysid of the referenced record.

Thus having two users in the user table with the name "Jon Davis" but different userID's and sys_id's becomes a count of 1 rather than 2.

In an organisation of circa 30,000 staff we have in the region of 250 reasonable duplicate names.

Can anyone suggest a method to do a distinct count of the records referenced rather than the display fields?

[damianfell]

David - that looks like the best bet, I've just tried the massive array and it timed out after 5 minutes (probably that last array function trying to deduplicate 30,000 users across 100,000 devices).

Using a grouped glide aggregate and incrementing the count should mean most of the heavy lifting is done in the back-end by MysQL rather than javascript.

[damianfell]

That did the trick - Gauge updated in seconds, and the numbers match my offline crunching of them.

For reference for anyone using a count gauge rather than background scripts, the code I finally used was:

var querystring='install_status=10^assigned_toISNOTEMPTY';
var count = new GlideAggregate('cmdb_ci');
count.addEncodedQuery(querystring);
count.groupBy('assigned_to');
count.query();
var userCount = 0;

while (count.next()){
userCount++;
}
answer = userCount;

[Dubz]

Glad that's working, just got the below working as well, I think you basically had it, just needed to get rid of the if statement! I imagine it's more efficient that the count incrementor.

if this is now resolved please do mark an answer correct to close it down.

var count = new GlideAggregate('cmdb_ci');
count.addQuery('assigned_to','!=','');
count.setGroup(false);
count.addAggregate('COUNT(DISTINCT','assigned_to');
count.query();
var agg;
count.next();
agg = count.getAggregate('COUNT(DISTINCT','assigned_to');

gs.print(agg);

[damianfell · Accepted solution]

I marked your previous answer as correct, this code still counts two people with the same name as one, so it's still using the user display value, not the user sys_id for the distinct count.

However amending your last code sample to the following does result in the correct answer:

var count = new GlideAggregate('cmdb_ci');
count.addQuery('assigned_to','!=','');
count.setGroup(false);
count.addAggregate('COUNT(DISTINCT','assigned_to.sys_id');
count.query();
var agg;
count.next();
agg = count.getAggregate('COUNT(DISTINCT','assigned_to.sys_id');

gs.print(agg);

[asifnoor]

Hi,

Try this code. This will give you count of records for each assigned_to.

var gr = new GlideAggregate('cmdb_ci'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('assigned_to'); //Group aggregate by the 'assigned_to' field
gr.query();
while(gr.next()){
   var osCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the assigned to and count 
   gs.print('Distinct Device count per user: ' + gr.assigned_to.getDisplayValue() + ': ' + osCount);
}

Mark the comment as a correct answer and helpful once worked.