Query all managers, add to group

[snoviello]

I am looking to automatically populate a group in ServiceNow composed of all users who are people managers (i.e. they appear in anyone's 'manager' field). I'd like to utilize a scheduled job that runs, queries all users who appear in the manager field, and adds them to this group. There are not a ton of users in our environment - and this would run off-hours; so I'm not too concerned with runtime.

Unfortunately, I am not allowed to bring in groups from Active Directory at this time - this would have been the easy path, as I could have just leveraged the 'People Managers' group from there.

I think I may be overcomplicating the ask - as I keep getting lost in the weeds while trying to accomplish this.

Any help here would be greatly appreciated.

[VaranAwesomenow · Accepted solution]

Dear Steve,

I made a script similar to what you are looking for. This should add all people managers to the group. You need to update the group sys_id in the groupid variable

var grUser = new GlideRecord('sys_user');

grUser.addEncodedQuery('manager!=NULL');

grUser.query();

while(grUser.next()) {

gs.print ("reached while");

addToGroup(grUser.manager);

}

function addToGroup(userManager) {

var groupId = '9aee1b7e0f33030067a7cd8ce1050e80'; // update the correct group sys_id

var query = 'group=' +groupId + '^user=' + userManager;

var grMember = new GlideRecord('sys_user_grmember');

grMember.addEncodedQuery(query);

grMember.query();

if(!grMember.next()) {

grMember.newRecord();

grMember.group = groupId;

grMember.user = userManager;

grMember.insert();

}

}

Please let me know if this works.

Part 2 of the solution would be to cleanup the group to ensure that if any existing members are not managers anymore those user records should be removed from the group.

Thanks
Anil

[snoviello]

Thanks a ton, Anil.

For cleanup, I've also added a function to this job:

deleteMembers(groupId); << Added at the top of the script

function deleteMembers(groupId){

  var member = new GlideRecord('sys_user_grmember');

  member.addQuery('group',groupId);

  member.query();

  while( member.next()) {

  member.deleteRecord();

  }

}

Seems to do the job of removing all members each night - and then the script you've written will import the list of current users.

Thanks again,

Steve

[VaranAwesomenow]

Dear Steve,

Apologies, I just found a small bug in my code. It ends up creating duplicates in the group membership if you have a user as manager for more than one user. I am fixing it up now.

[VaranAwesomenow]

Here is the final script which does both create group member records and maintenance of the group

var groupId = '9aee1b7e0f33030067a7cd8ce1050e80';

checkExistingMembers(groupId);

addMembersToGroup(groupId);

function checkExistingMembers(groupId) {

var isManager = false;

var query = 'group=' +groupId;

var grMember = new GlideRecord('sys_user_grmember');

grMember.addEncodedQuery(query);

grMember.query();

while(grMember.next()) {

isManager = checkIfManager(grMember.user);

if(isManager == false) {

grMember.deleteRecord();

}

}

}

function checkIfManager(userManager) {

var return_flag = false;

var query = 'manager=' + userManager + '^active=true';

var grUser = new GlideRecord('sys_user');

grUser.addEncodedQuery(query);

grUser.query();

if(grUser.next()) {

return_flag = true;

}

return return_flag;

}

function addMembersToGroup(groupId) {

var query =   'manager!=NULL^active=true';

var grUser = new GlideRecord('sys_user');

grUser.addEncodedQuery(query);

grUser.query();

while(grUser.next()) {

addToGroup(grUser.manager, groupId);

}

}

function addToGroup(userManager, groupId) {

var query = 'group=' +groupId + '^user=' + userManager;

var grMember = new GlideRecord('sys_user_grmember');

grMember.addEncodedQuery(query);

grMember.query();

if(!grMember.next()) {

grMember.newRecord();

grMember.group = groupId;

grMember.user = userManager;

grMember.insert();

}

}