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.

[Yuta3553]

@Ankur Bawiskar 

Sorry for incovenince.here it is.

//generate value from "sys_user"table
var gr = new GlideRecord("sys_user");
gr.addQuery("active","=", true);
gr.addQuery("sys_domain","=", domain);
gr.orderBy("last_login");
gr.orderBy("user_name");
gr.query();

//generate value from "sys_user_grmember"table
this.QUERY_USER ="group=1a6ff4e92f8561106a1655272799b6d0";//Group="MSA TEAM"
var us = new GlideRecord("sys_user_grmember");
us.addEncodedQuery(this.QUERY_USER);
us.query();

//Extract only those that match your criteria
while(gr.next()){
if (us.getvalue("user")==gr.getvalue("name"));{
var body = [];
body.push(gr.getDisplayValue("user_name"));
body.push(gr.getDisplayValue("name"));
body.push(gr.getDisplayValue("last_login"));
}

return body;

Regards.

[Ankur Bawiskar · Accepted solution]

@Yuta3553 

try this

this.QUERY_USER ="group=1a6ff4e92f8561106a1655272799b6d0";//Group="MSA TEAM"

var arr = [];

//generate value from "sys_user"table
var gr = new GlideRecord("sys_user");
gr.addActiveQuery();
gr.addQuery("sys_domain", domain);
gr.orderBy("last_login");
gr.orderBy("user_name");
gr.query();
while(gr.next()){

	//generate value from "sys_user_grmember"table
	var us = new GlideRecord("sys_user_grmember");
	us.addEncodedQuery(this.QUERY_USER);
	us.addQuery("user", gr.getUniqueValue())
	us.query();
	//Extract only those that match your criteria
	if(us.next()){
		var body = {};
		body["use_name"] = gr.getValue("user_name");
		body["name"] = gr.getValue("name");
		body["last_login"] = gr.getValue("last_login");
		arr.push(body);
	}
}
return JSON.stringify(arr);