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 · Accepted solution]

1. I think why this will be happening because your group contains both roles [ in related list filtering with what not is been challenging for me ] but here is what I can suggest 

  in while loop put a check ( if condition ) to see if this group does not contain your required role/conditions etc. Then only push to the array.

2. once you have groupNames variable, you can run a glideRecord query on table 'sys_user_group' to get the required data by iterating over the array(groupNames) using for loop

OR

in while loop only like we are doing gr.group.name, you can get other details from that particular record like gr.group.manager/gr.group.manager.name/gr.group.email & store it in a different array.

As you are in need to store multiple attributes of a record, better use JSON to make data accessible in an easy way.

[Aish1]

I am having similar issue when i am trying to find list of users who is not a part of any group but still has a role that is inherited as true.Below is the script I am writing but not able to find the correct output:

var roles = new GlideRecord('sys_user_has_role'); 
roles.addQuery('inherited', true); 
roles.query(); 

while(roles.next()) 
{ 
var userGR = new GlideRecord('sys_user_grmember'); 
userGR.addQuery('user','!=',roles.user); 
userGR.query(); 
while(userGR.next()) 
{ 

userGR.getRowCount(); 

} 

}