How to get list of user who have only ITIL role?

[Santosh Kallim1]

Hello Experts,

I have 20K user's, there multiple roles added to each user's

There are some user's, only have ITIL role, there are no roles added to these users except ITIL, so I need to get the count of those users and sys_id through scripting, 

can anybody help me please? 

[Krosenthal]

You can also just go to the user table and filter by role is ITIL...at least in our instance I've been able to do that. I've also had ServiceNow provide a list of those users.

[Akash Gurram1]

Hello Santosh,

Since you need the count and sys_ids of users with ONLY ITIL role, you can use the below script.
Here first I am grouping all the records in the 'sys_user_has_role' table and of those, I am checking the records with count 1. Then if they have the role itil, I am storing it in the array only_itil_role.

var gr = new GlideAggregate('sys_user_has_role'); 
gr.addAggregate('count');
gr.orderByAggregate('count');
gr.groupBy('user');
gr.query();

only_itil_role = [];
while(gr.next()){
    var roleCount = gr.getAggregate('count');
    if(roleCount == 1){
        var roleGr = new GlideRecord('sys_user_has_role');
        roleGr.addQuery('user', gr.user);
        roleGr.query();
        while(roleGr.next()){			
			if(roleGr.role == '282bf1fac6112285017366cb5f867469'){
				only_itil_role.push(roleGr.user);
			}
        }
    }	
}
gs.print("Count of users with ONLY ITIL role is: "+only_itil_role.length);
gs.print("Their sys_ids are :");
for(i in only_itil_role){
	gs.print(only_itil_role[i]);
}

Please mark my answer correct/helpful if it works,

Regards,
Akash

[DrewW]

I would change this part of your script slightly, I think it will be quicker.

    if(roleCount == 1){
        var roleGr = new GlideRecord('sys_user_has_role');
        roleGr.addQuery('user', gr.user);
        roleGr.addQuery('role', '282bf1fac6112285017366cb5f867469'); //sys_id of itil role.
        roleGr.query();
        if(roleGr.next()){
            only_itil_role.push(roleGr.getValue("user"));
        }
    }

EDIT: You also need to do a getValue on the user field or you will end up with the same value in every element of the array.

[Santosh Kallim1]

It still return the overall count ..

[DrewW]

Ok, so I added a having clause to Akash's script.  This means only the people who have 1 role will be checked.  It tested fine in my instance

var gr = new GlideAggregate('sys_user_has_role'); 
gr.addAggregate('count');
gr.orderByAggregate('count');
gr.addHaving('count', '=', 1)
gr.groupBy('user');
gr.query();

only_itil_role = [];
while(gr.next()){
    var roleGr = new GlideRecord('sys_user_has_role');
    roleGr.addQuery('user', gr.user);
    roleGr.addQuery('role', '282bf1fac6112285017366cb5f867469'); //sys_id of itil role.
    roleGr.query();
    if(roleGr.next()){
        only_itil_role.push(roleGr.getValue("user"));
    }
}
gs.print("Count of users with ONLY ITIL role is: "+only_itil_role.length);
gs.print("Their sys_ids are :");
for(i in only_itil_role){
	gs.print(only_itil_role[i]);
}