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

damianfell · ‎05-23-2019

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 · ‎05-23-2019

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);

View solution in original post

damianfell · ‎05-23-2019

Thanks, but I'm not sure that's quite what I'm after, that looks like it will generate a list of OS counts per user, rather than a count of unique users across the table.

asifnoor · ‎05-23-2019

it actually gives you count of unique users only. its grouped by assigned_to and getting the count.

I tweaked another code, hence the variables are misleading.

damianfell · ‎05-23-2019

Aah I see that's a count of devices per user, which might be useful for another part of this exercise, but what I'm looking for is a count of unique users across the whole table, I think I'm going to use the array method that David suggested.

asifnoor · ‎05-23-2019

okie. Could you mark the comment as helpful if this helps.

Sushma24 · ‎10-08-2021

I have my requirement quite similar to this. I have grouped by manager for task table and want value of user's under each manager within while loop just like count value. How can we get that