Generate/get users wherein their group membership is only for a specific group

[ss123]

Hi All,

Just wanted to ask if it's possible to generate / get the list of users wherein their group membership is only for a specific group using a script?

For example: User 1, User 2 and User 3 are members of Group A , which is their ONLY group membership.

Thank you.

[Community Alums · Accepted solution]

Hi @ss123 

If you are looking for list of users who are part of only one group, then below is the script.

var gr = new GlideRecord('sys_user');

gr.addActiveQuery();

gr.query();

while(gr.next()){

    var gr1 = new GlideRecord("sys_user_grmember");

    gr1.addQuery('user', gr.sys_id);

    gr1.query();

    gr1.next();

    if(gr1.getRowCount() == 1)

        gs.print(gr.email+" - "+gr1.group.name);

}

Hope this helps.

Regards

Saranesh

[Medi C]

Hi @ss123,

Here are 2 function that you can use as per your requirements:

Function 1: (Query the users table and return the results as an array of JSON, where each object contains username and the only one group)

function getUsersWithOnlyOneGroup() {
    var usersWithOneGroup = [];

    var grUsers = new GlideRecord('sys_user');
    grUsers.query();

    while (grUsers.next()) {
        var userID = grUsers.sys_id.toString();

        // Check group membership count
        var grMembership = new GlideRecord('sys_user_grmember');
        grMembership.addQuery('user', userID);
        grMembership.query();


        if (grMembership.next() && grMembership.getRowCount() === 1) { // Only one group
			var obj = {
				"Username" : grUsers.user_name.toString(),
				"Group": grMembership.group.name.toString()
			};
            usersWithOneGroup.push(obj);
        }
    }

    return usersWithOneGroup;
}

Usage:

var result = getUsersWithOnlyOneGroup();
gs.info('Users who belong to only one group: ' + JSON.stringify(result));

Function 2: (Check for only users within a given group sys_id if they are members of that only group)

function getUsersWhoAreOnlyMemberOfGroup(targetGroup) { // Replace with your group's Sys ID

    var userList = [];

    var grUsers = new GlideRecord('sys_user_grmember');
    grUsers.addQuery('group', targetGroup);
    grUsers.query();

    while (grUsers.next()) {
        var userID = grUsers.user + "";
        // Check if the user has more than one group membership
        var gaCheck = new GlideAggregate('sys_user_grmember');
        gaCheck.addAggregate("COUNT");
        gaCheck.addQuery('user', userID);
        gaCheck.query();
        if (gaCheck.next() && gaCheck.getAggregate("COUNT") == "1") {
            // Ensure the user is only in the target group
            userList.push(grUsers.user.user_name + "");
        }

    }

    gs.info('Users who are ONLY members of Group with Sys ID: ' + targetGroup + ' → ' + userList.join(', '));
};

Usage:

getUsersWhoAreOnlyMemberOfGroup("d625dccec0a8016700a222a0f7900d06") //Replace with your group sys_id

[Medi C]

Hi @ss123,

Thanks for marking my response as helpful, I believe my solution would also work for your requirements. ServiceNow Community allows multiple answers to be selected as Correct Solutions.