Script query in sys_user_group table using addJoinQuery from another table sys_group_has_role doesn't print the right results

[bbf3562]

Hello guys,

I am doing the script query in scheduled job to to print a list of groups that are not HR groups. I want to filter out the HR group that have Type is human_resources OR Group role have contain "sn_hr". Here is my query script,

var groupGr = new GlideRecord('sys_user_group');
var groupGrRole = groupGr.addJoinQuery('sys_group_has_role', 'sys_id', 'group');
groupGr.addEncodedQuery('active=true^typeNOT LIKE7a5370019f22120047a2d126c42e705c^u_validation_count<5^managerISNOTEMPTY^manager.emailISNOTEMPTY^manager.active=true^manager.notification=2');

groupGrRole.addCondition('role.name', 'NOT LIKE', 'sn_hr');
groupGr.query();
gs.print("Count in group table: " + groupGr.getRowCount());
while(groupGr.next()){
    gs.print(groupGr.getValue('name'));
    
}

The problem is that the result was incorrect because it have 2 more unwanted HR group in the list that have "sn_hr" role but doesn't have type is human_resource. It look like the rest of other excluded HR groups have BOTH type = human_resources and role contain "sn_hr". It seems like my script query do exclude group that have BOTH instead of either one of them. Does anyone know how to correct my script to also exclude 2 unwanted HR groups that have one of these 2 requirements? Here is 1 of  2 HR groups example that doesn't have both,

 

[RatneshTSN]

Would you try using encoded query in gliderecord

First open https://<instanceName>.service-now.com/sys_group_has_role_list.do

Make sure it has columns Group,Group Type,Role: if not you need to add by configuring List Layout

then configure required column as below

then add Group Type in filter condition

Make sure you replace filter condition value as per requirement And OR condition as which suits you best

Now click run & then right click on query to copy encoded query

Your encoded query may look like this depending on values you selected:

group.typeLIKE74af88c6c611227d0066386e74dc853d^role.name=catalog

Now use Glide Record Encoded query & pass this query. See if it give the results accordingly or not.

Let me know the results.

Thanks-Ratnesh

[bbf3562]

I just tried your method and the number result it not the same as correct result. For example, your method got my result 1807 but the correct result is 1641. I think it because of duplicate group names that have more than 1 roles in sys_group_has_role. How can you sort it out but remove the duplicate group names?

[RatneshTSN]

Hi,

You can use it as below, let me if it helps!

//Pass encoded query
var encQuery = 'group.typeLIKE74af88c6c611227d0066386e74dc853d^role.name=itil^ORDERBYgroup';

//Initialize array to store group names
var groupNameArray= [];

var gr = new GlideRecord('sys_group_has_role');
gr.addEncodedQuery(encQuery);
gr.query(encQuery);
while(gr.next()){
 
  //Store Group-name to array
  groupNameArray.push(gr.group.name.toString());
  
}

gs.print("Initial Stored Array:\n" + groupNameArray);

//Function to get unique values from given array
function uniqueArrayValues(value, index, self) { 
    return self.indexOf(value) === index;
}

// call filter method on array & pass uniqueArrayValues as callback-function
var groupNames = groupNameArray.filter(uniqueArrayValues);

gs.print("After filtering duplicate Array:\n" + groupNames);

[bbf3562]

Hi I tried this method and got result of 1648. So it 7 more group names than the original result.

2 questions,

1. When I compare why I have 7 more results, there are HR groups that have group type human_resources AND role that contain "sn_hr" that shouldn't be show in the list. It should remove group that have either or both that have group type is human_resources and role contain with "sn_hr". Here is screenshot example of 1 of group that shouldn't be in list,

2. How can I use groupNames variable to put in loop to get group name to pull out information like group manager name, contact info, etc?

Here is my update script,

var encQuery = 'group.typeNOT LIKE7a5370019f22120047a2d126c42e705c^ORrole.nameNOT LIKEsn_hr^group.active=true^group.u_validation_count<5^group.managerISNOTEMPTY^group.manager.active=true^group.manager.emailISNOTEMPTY^group.manager.notification=2^ORDERBYgroup';

//Initialize array to store groups names
var groupNameArray = [];

var gr = new GlideRecord('sys_group_has_role');
gr.addEncodedQuery(encQuery);
gr.query();

while(gr.next()){
	//store group name to array
	groupNameArray.push(gr.group.name.toString());
	
}

//gs.print("Initial Stored Array:\n"+ groupNameArray);

//Function to get unique values from given array
function uniqueArrayValues(value, index, self){
	return self.indexOf(value) === index;
}

//call filter method on array & padd uniqueArrayValue as callback-function
var groupNames = groupNameArray.filter(uniqueArrayValues);


gs.print("after filtering duplicate Array:\n"+ groupNames);
gs.print("results "+ groupNames.length);