Find number of group members that have a specified role

[Nicholas Cerill]

I am looking for assistance how I can create a script that will show us the number of users who are part of a specific sys_user_group who have a sys_user_role. 

groups does not appear to be a filterable field for sys_user, so I feel that a custom script is my only way of handling this. 

[Nicholas Cerill]

Thank you Hitoshi. As i mentioned. my issue is not finding finding which users belong to a group, it is finding which users belong to a group AND have a specific role

[Hitoshi Ozawa]

How about creating a database view joining sys_user_grmember table with sys_user_has_role table?

Sample output

[Kartik Sethi]

Hi @Nicholas Cerillo 

You can follow the suggestions given by @Hitoshi Ozawa as the Database view seems to be the optimum solution. In case you really need a script then you can go for the below-provided code which you can run in Background - Script or Fix Script

Script:

var requiredRole = 'itil';
var requiredGroup = 'Software';


//Cannot use GlideAggregate as addJoinQuery is a GlideRecord method
var groupMemberGr = new GlideRecord('sys_user_grmember');
var roleGr = groupMemberGr.addJoinQuery('sys_user_has_role', 'user', 'user');

//Add Base table condition and Join table condition
groupMemberGr.addEncodedQuery('group.name=' + requiredGroup);
roleGr.addCondition('role.name', requiredRole);

//Query
groupMemberGr.query();
gs.print(groupMemberGr.getRowCount());

//Print Members
while(groupMemberGr.next())
    gs.print(groupMemberGr.user.getDisplayValue());

Remark: Since we are using GlideRecord's method ► getRowCount method this will lead to a performance impact on the system as there can be a huge number of records that needs to be queried. There is no similar function "addJoinQuery" in GlideAggregate hence Database view seems to be a better approach.

Tried on PDI:
Group - Software
Role - itil
6 members and 5 have itil role

---

Please mark my answer as correct if this solves your issues!

If it helped you in any way then please mark helpful!

Thanks and regards,

Kartik