How to query for users by filtering on a specific sys_user_group name?

[Todd O]

I have a basic need to I could whip out in a minute with plain old SQL. But, I'm having trouble doing the same thing in GlideRecord joins.   Here is my goal...

I have a sys_user_group named "Todd Group."   I basically want to find all users that are part of that group. Therefore, I believe I need to join the three tables that make up this relationship. That is, sys_user_group, sys_user_grmember, and sys_user.   I followed the documentation on how to do a join and came up with the below code. The problem is that it's pulling all the users when I only have 2 assigned in this group.   Any help is appreciated. Thank you.

Todd

var gr = new GlideRecord('sys_user');
grSUGM = gr.addJoinQuery('sys_user_grmember'); // <-- join to the association table
grSUG = grSUGM.addJoinQuery('sys_user_group'); // <-- join to the sys_user_group that holds the group name
grSUG.addCondition('name', 'Todd Group'); // <-- filter on the sys_user_group table by the group name
gr.query();
while (gr.next()) {
   gs.info('Here is a user: ' + gr.name + ', ' + gr.sys_id); // <-- Print all users, more than just the 2 I have in "Todd Group."
}

[Brian Dailey1]

Hi Todd,

Actually, all you need to do is query the many-to-many table that relates users to groups, [sys_user_grmember].

var gr = new GlideRecord('sys_user_grmember');

gr.addQuery('group=(insert sys_id of Todd Group));

gr.query();

var users = '';

while(gr.next()){

          users += gr.user.name + ",";

}

gs.log("Users found in Todd Group: " + users);

As an example, this will generate a comma-separated list of the user name's, but you could pull whatever info you wanted (sys_id, etc.).

Give that a try.

-Brian

Edited: to follow your sample code closed using 'name'.

[Todd O]

Hi Brian,

I ran the above query but I'm still getting all the user records from the entire sys_user table. I see the Pradeep's suggestion below is working. Thank you for taking the time and I'm sure a little tweaking and this one would work too.

Todd

[Brian Dailey1]

It's probably the addQuery() statement (once you insert your group's sys_id)...   I didn't have the closing quote on the query string.   An invalid query string will return all records unless you specify otherwise.

-Brian

[Pradeep Sharma · Accepted solution]

Hi Todd,

Here you go.

var gr = new GlideRecord('sys_user_grmember');   
 // <-- join to the association table   
 // <-- join to the sys_user_group that holds the group name   
gr.addQuery('group.name', 'Todd Group'); // <-- filter on the sys_user_group table by the group name   
gr.query();   
while (gr.next()) {   
   gs.info('Here is a user: ' + gr.user.getDisplayValue()); // <-- Print all users, more than just the 2 I have in "Todd Group."   
}