find the group who has itil role and group member is empty

Raj12341
Tera Contributor

find the group who has itil role and group member is empty.

for this I have written a script , but it is not working.

// Define the ITIL roles to check
var itilRoles = ['itil', 'itil_admin'];

// Get all groups
var groups = new GlideRecord('sys_user_group');
groups.query();

// Iterate through each group
while (groups.next()) {
    // Check if the group has ITIL roles
    var hasItilRoles = true;
    for (var i = 0; i < itilRoles.length; i++) {
        if (!groups.hasRole(itilRoles[i])) {
            hasItilRoles = false;
            break;
        }
    }

    // Check if the group has no members
    var hasMembers = new GlideRecord('sys_user_grmember');
    hasMembers.addQuery('group', groups.sys_id);
    hasMembers.query();
   
    if (hasItilRoles && !hasMembers.hasNext()) {
        // Print or log the group information
        gs.info("Group with ITIL roles and no members: " + groups.name);
    }
}
thanks, please help me in this.
1 ACCEPTED SOLUTION

Tony Chatfield1
Kilo Patron

Hi, I could not quite understand the approach you were taking with your code, but I think a better solution would be to start by querying sys_group_has_role table, filtering for groups based on the role(s) you are checking for, then loop through the results. Perhaps something like this.

//An array of Roles to query or you can hard code the query.
var myRoles = ['itil', 'itil_admin'];
var myQuery = 'role.nameIN' + myRoles.toString();

//An arry for results
var groupWithNoMembers = [];

//Query Group Roles table
var groupRec = new GlideRecord('sys_group_has_role');
groupRec.addEncodedQuery(myQuery);
groupRec.query();

while (groupRec.next()) {
//Loop through results and check group member table
    var myId = groupRec.group.sys_id;

    var userRec = new GlideRecord('sys_user_grmember');
    userRec.addQuery('group.sys_id', myId);
    userRec.setLimit(1);
    userRec.query();
    
    //If no result then the group has no members, so push group into results array.
    if(!userRec.next()) {
        groupWithNoMembers.push(groupRec.group.name);
    }
}

gs.info('Results ' + groupWithNoMembers.toString());

View solution in original post

1 REPLY 1

Tony Chatfield1
Kilo Patron

Hi, I could not quite understand the approach you were taking with your code, but I think a better solution would be to start by querying sys_group_has_role table, filtering for groups based on the role(s) you are checking for, then loop through the results. Perhaps something like this.

//An array of Roles to query or you can hard code the query.
var myRoles = ['itil', 'itil_admin'];
var myQuery = 'role.nameIN' + myRoles.toString();

//An arry for results
var groupWithNoMembers = [];

//Query Group Roles table
var groupRec = new GlideRecord('sys_group_has_role');
groupRec.addEncodedQuery(myQuery);
groupRec.query();

while (groupRec.next()) {
//Loop through results and check group member table
    var myId = groupRec.group.sys_id;

    var userRec = new GlideRecord('sys_user_grmember');
    userRec.addQuery('group.sys_id', myId);
    userRec.setLimit(1);
    userRec.query();
    
    //If no result then the group has no members, so push group into results array.
    if(!userRec.next()) {
        groupWithNoMembers.push(groupRec.group.name);
    }
}

gs.info('Results ' + groupWithNoMembers.toString());