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.

[DeeMendoza]

Hi Ashish! This works perfectly. The use of GlideAggregate to count users who appear more than once in the filtered table is exactly what I needed to accomplish my goal. Thank you!

[Nishant8]

Hello @DeeMendoza, You are querying a right table. You can include below in your encoded query that should work fine.

// Add a query to find users who belong to both Group A and Group B
grUser.addEncodedQuery("group.name=GroupA^ORgroup.name=GroupB");

P.S: with this approach, you might get unique key violation error if user, which you are trying to add to Group C, is already part of Group C. you can handle this by using if block, if required.

[DeeMendoza]

Hi Nishant! Thanks for your input. I had already considered this query/filter, but it ends up showing users who belong to either Group A or Group B. While it does include users who belong to both groups, I'm specifically trying to get users who belong to both Group A and Group B