How to extract only users belonging to certain groups (Groups) from the users on the sys_user table

[Yuta3553]

Hello All,Please help me I'm a begineer of servicenow developer.

I currently want to extract rows from the users table (sys_user) that meet the following conditions.

I would be grateful if someone could tell me how to do this.

Conditions:.
1. domain belongs to the right domain: TOP/Default
2.Active is "True".
3. the user belongs to the Groups listed on the right: MSA TEAM 

Thank you in advance.

[Paul Deonarine3 · Accepted solution]

Can you please try with below script, 

var gr = new GlideRecord('sys_user');
gr.addQuery('domain', 'TOP/Default');
gr.addQuery('active', true);
gr.addQuery('GROUPS', 'CONTAINS', 'MSA TEAM');
gr.query();

while (gr.next()) {
  // do something with each record
  var firstName = gr.getValue('first_name');
  var lastName = gr.getValue('last_name');
  var email = gr.getValue('email');
  // ...
}

In this code snippet, we create a new GlideRecord object for the sys_user table and add three queries using the addQuery method. The first query checks for the domain, the second for the active status, and the third for the group membership.

After adding all the queries, we call the query method to run the query and fetch the matching records. Finally, we loop through the records using the next method and extract the values of the desired fields using the getValue method.

You can modify this code to suit your specific needs, such as adding more queries or extracting different fields.

[Paul Deonarine3 · Accepted solution]

Can you please try with below script, 

var gr = new GlideRecord('sys_user');
gr.addQuery('domain', 'TOP/Default');
gr.addQuery('active', true);
gr.addQuery('GROUPS', 'CONTAINS', 'MSA TEAM');
gr.query();

while (gr.next()) {
  // do something with each record
  var firstName = gr.getValue('first_name');
  var lastName = gr.getValue('last_name');
  var email = gr.getValue('email');
  // ...
}

In this code snippet, we create a new GlideRecord object for the sys_user table and add three queries using the addQuery method. The first query checks for the domain, the second for the active status, and the third for the group membership.

After adding all the queries, we call the query method to run the query and fetch the matching records. Finally, we loop through the records using the next method and extract the values of the desired fields using the getValue method.

You can modify this code to suit your specific needs, such as adding more queries or extracting different fields.

[Ankur Bawiskar]

@Yuta3553 

it should be an easy task.

query all users with that domain and active and then for each user check if that user is member of that group

what script did you start from your side?

[Yuta3553]

@Ankur Bawiskar 
Hi,Thanks for your prompt reply!!

This is my current script,However, this script does not extract the data well.
Could you please tell me the solution?

Regard,

[Ankur Bawiskar]

@Yuta3553 

please share script and not screenshot