Script to count user roles

[#1]

Hi,

I am trying to put together a script that will get a count of all the user roles and then add the summaries to a new table, but I am getting stuck on the first step, and that is getting the data out the "sys_user_has_role" table. I have used the example aggregation script on the wiki and just changed the table and columns. It appears that the COUNT is not doing what it should.

Any help will be appreciated.

Thanks

The script looks like:

var newRrec = new GlideRecord('sys_user_has_role');
newRrec.addAggregate('COUNT', 'role.name');
newRrec.query();
while (newRrec.next()) {
var role = newRrec.role.name;
var roleCount = newRrec.getAggregate('COUNT', 'role.name');
gs.log("The are currently " + roleCount + " users " + role);
}

The result of the log looks like:

The are currently undefined users itil
The are currently undefined users itil
The are currently undefined users admin.. and a whole lot more.

[#2]

What function would I use to group different roles together and then add them up, and somewhere along the way I need to remove the duplicate users when the roles are grouped??

Thanks

[gtk]

can you give code to count user with groups

example: there are 3 users with no groups

there are 3 users with groupA

there are 6 users with groupB