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]

I think the issue is with the field name role.name. I think you just need 'role'. Not 100% sure but give that a try.

[#3]

Tried that already, If you use just the role, you get the role sys id's instead of the name.

[CapaJC]

I think one issue is that you did a new GlideRecord instead of a new GlideAggregate. But I'd start the script with the sys_user_role table (easier for me to conceptualize). Here's my script that worked:



doit();
function doit() {
var gr = new GlideRecord('sys_user_role');
gr.query();
while (gr.next()) {

var count = new GlideAggregate('sys_user_has_role');
count.addAggregate('COUNT');
count.addQuery('role',gr.sys_id);
count.query();
var num = 0;
if (count.next()) {
num = count.getAggregate('COUNT');
if (num > 0)
gs.log("There are " + num + " users with the '" + gr.name + "' role");
}
}

}

[#5]

Yep this worked, thanks.