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?

[Dubz]

GlideAggregate would be the method i would go for, can you post up an example of the code you've tried using?

A slightly more clumsy way would be to add all the users to an array and then you can just check the length.

var arr = [];
var gr = new GlideRecord('cmdb_ci');
gr.addQuery('assigned_to', '!=', '');
gr.query();
while(gr.next()){
arr.push(gr.getUniqueValue());
}

var uniqueArr = new ArrayUtil().unique(arr);
gs.print('There are ' + uniqueArr.length + ' users with a CI assigned');

[damianfell]

I was trying to avoid making a huge array, but that's looking like the most likely way I can see at the moment.

[damianfell]

Here's the current script from the count gauge:

var querystring='install_status=10^assigned_toISNOTEMPTY^sys_class_name=cmdb_ci_pc_hardware^ORsys_class_name=u_cmdb_ci_mobile_device';
var count = new GlideAggregate('cmdb_ci');
count.addEncodedQuery(querystring);
count.addAggregate('COUNT(DISTINCT','assigned_to');
count.setGroup(false);
count.query();
var usernum=0;
if (count.next())
usernum = count.getAggregate('COUNT(distinct','assigned_to');
answer = usernum;

[Dubz]

Tried out a few permutations on this, couldn't figure out how to get a count of just the users rather than the amount CI's the are assigned to. Another option other than the big array, which still isnt very elegant but seems to work, is just to increment a counter yourself:

var count = new GlideAggregate('cmdb_ci');
count.addQuery('assigned_to','!=','');
count.groupBy('assigned_to');
count.query();
var userCount = 0;

while (count.next()){
userCount++;
}
gs.print(userCount);