How should I go about writing a script that queries which users belong to two certain groups?

[DeeMendoza]

Hi! I'm trying to write a script to query users who belong to both Group A and Group B, so I can add them to a new group (Group C).

I'm unsure which table to query or how to write the query. I've experimented with filters in the sys_user_grmember table but haven't found a query that meets my requirements.

Maybe I'm looking in the wrong place or going about this all wrong?

Any insight would be appreciated!

This is what I've come up with so far:

var grUser = new GlideRecord("sys_user_grmember");

// Add a query to find users who belong to both Group A and Group B
grUser.addEncodedQuery("//what do i even put here?");
grUser.query();

while (grUser.next()) {
    // Create a new GlideRecord object for the sys_user_grmember table
    var gr = new GlideRecord('sys_user_grmember');
    
    gr.initialize();
    // Set the group to Group C
    gr.group = '//GROUP_C_SYS_ID';
    
    // Set the user to the current user in the loop
    gr.user = grUser.user;
    gr.insert();
}

[Viraj Hudlikar · Accepted solution]

Hello @DeeMendoza 

Sample script something as below:

// Define the group sys_ids
var groupA = 'sys_id_of_group_A';
var groupB = 'sys_id_of_group_B';
var groupC = 'sys_id_of_group_C';

// Define the user sys_id
var userSysId = 'sys_id_of_user';

// Check if the user is in Group A
var userInGroupA = false;
var grA = new GlideRecord('sys_user_grmember');
grA.addQuery('group', groupA);
grA.addQuery('user', userSysId);
grA.query();
if (grA.next()) {
    userInGroupA = true;
}

// Check if the user is in Group B
var userInGroupB = false;
var grB = new GlideRecord('sys_user_grmember');
grB.addQuery('group', groupB);
grB.addQuery('user', userSysId);
grB.query();
if (grB.next()) {
    userInGroupB = true;
}

// If the user is in both Group A and Group B, add them to Group C
if (userInGroupA && userInGroupB) {
    var grC = new GlideRecord('sys_user_grmember');
    grC.initialize();
    grC.user = userSysId;
    grC.group = groupC;
    grC.insert();
    gs.info('User added to Group C: ' + userSysId);
} else {
    gs.info('User is not in both Group A and Group B.');
}

If my response has helped you hit helpful button and if your concern is solved do mark my response as correct.

Thanks & Regards
Viraj Hudlikar.

[Viraj Hudlikar · Accepted solution]

Hello @DeeMendoza 

Sample script something as below:

// Define the group sys_ids
var groupA = 'sys_id_of_group_A';
var groupB = 'sys_id_of_group_B';
var groupC = 'sys_id_of_group_C';

// Define the user sys_id
var userSysId = 'sys_id_of_user';

// Check if the user is in Group A
var userInGroupA = false;
var grA = new GlideRecord('sys_user_grmember');
grA.addQuery('group', groupA);
grA.addQuery('user', userSysId);
grA.query();
if (grA.next()) {
    userInGroupA = true;
}

// Check if the user is in Group B
var userInGroupB = false;
var grB = new GlideRecord('sys_user_grmember');
grB.addQuery('group', groupB);
grB.addQuery('user', userSysId);
grB.query();
if (grB.next()) {
    userInGroupB = true;
}

// If the user is in both Group A and Group B, add them to Group C
if (userInGroupA && userInGroupB) {
    var grC = new GlideRecord('sys_user_grmember');
    grC.initialize();
    grC.user = userSysId;
    grC.group = groupC;
    grC.insert();
    gs.info('User added to Group C: ' + userSysId);
} else {
    gs.info('User is not in both Group A and Group B.');
}

If my response has helped you hit helpful button and if your concern is solved do mark my response as correct.

Thanks & Regards
Viraj Hudlikar.

[DeeMendoza]

This may also work and looks like a more "certain" solution to the problem. Thank you for your input! 🙂

[Viraj Hudlikar]

Hello @DeeMendoza 

Thank you for marking my response as helpful! 😊

I hope your concern has been fully addressed. If it resolves your issue, please consider marking it as the accepted solution. This will ensure others in the community can benefit from the solution too.

As per new community feature you can mark multiple responses as correct.

Thanks & Regards
Viraj Hudlikar.

[AshishKM · Accepted solution]

Hi @DeeMendoza ,

To achieve this, created 3 groups in my PDI Group A, Group B and Group C and added 2 users in Group A and Group B where Abel Tuter is common in both groups. Use the GlideAggregate on Group Membership table for common users.

Now the below script will give the common user from both groups.

var gr = new GlideAggregate('sys_user_grmember');
        gr.addAggregate('COUNT', 'user');
        gr.orderBy('user');
        gr.addQuery('group', '3139bc1ac398665052381c84e401312e').addOrCondition('group', '9449f8dac3d8665052381c84e4013108'); // sys_id of Group A and Group B
        gr.query();
		while ( gr.next()){
			var userCount = gr.getAggregate('COUNT', 'user');
            if (userCount > 1) {
                gs.print('User Name ->'+ gr.user.name);
				var grGR = new GlideRecord('sys_user_grmember');
				grGR.initialize();
				grGR.group = 'e749b09ac3d8665052381c84e40131b4'; // sys_id of Group C
				grGR.user = gr.user;
				grGR.update();
				
            }
		}

Before

After

-Thanks,

AshishKM