Query/Script to add user to group based on field in sys_user table

[Robert T]

Hello,

In our sys_user table, the department field changes frequently.

I'd like to have a user be added to a group if department = x, but also have a user be removed from a group if their department changes from x to something else (y).

I am fairly new to SN, and have experimented with a business rule on the sys_user table that interacts with the sys_user_grmember table.  This has had limited success with some buggy behavior.

I'd like to be able to run 1 clean script that updates all users based on their department being x or y, then have an active rule that looks for updates to the department field and associates or removes that group going forward.

Many thanks for any and all help,

--Robert

[Daniel O_Connor]

Hi Robert,

Many ways to do this, including as you identified business rules. And there is a number of scripts you could use or run.

If your new to ServiceNow, would suggest taking a look at flow designer. There is a lot of things you can do in there now, without really having to worry about scripts or coding. It's also a nice, neat, consolidation of workflows and automation as you make more and more. 

I'd suggest doing this in the below steps (Again I don't know what volume of users you have, but sometimes it's best to break updating records down into smaller chunks)

1) Update user groups in batches.

2) Delete users from groups in batches.

3) Create your method to manage updates automatically moving forward.

For step 1 and 2, I'd use background script. This lets you run a script against tables in one go. I think it would work best since your updating existing data in your tables, which might be in high volume (make sure to thoroughly test this)

For step 3, I'd personally look at creating a Flow to manage this as I find them better nowadays than business rules. Mainly just easier to maintain but also you can see executions which catch errors and failures which you can troubleshoot.

Alternatively, if you want to go down the business rule road, you'd need to create one with an AFTER UPDATE condition set, so it trigger after your AD integration updates the user records.

Here is a quick script I put together, that should put all your users from Department X into the group you need. 

//This segment runs a query to return the records in the department
var doc = new GlideRecord('sys_user');
doc.addQuery('department', 'x'); //Put your department name in here instead of X
doc.query();
while(doc.next()){
  //This next section puts the user into the group
  var docc = new GlideRecord('sys_user_grmember');
  docc.initialize();
  docc.user = doc.sys_id;
  docc.group.setDisplayValue('xx'); //Put your group name in here instead of XX
  docc.insert();
}

Would mention, if you search the community site there is tons of threads around this requirement. So you will see a lot of varying ways this is done and managed through scripts, code, flows, business rules, client scripts etc.

Personally, I manage it manually in my instance. And I support over 3000 global users. I've really just never felt it any hassle to do, just get a request to add or remove to a group and then do it manually. Although you've probably now made me interested in looking to my own instance to see if I automated it, how would it look like 

[Robert T]

I'm getting an error at docc.group.setDisplayValue

undefined function

[Daniel O_Connor]

Weird. I see you've replied to Tony, he might be your man if you want to go down a BR route that uses scripts and code.

Coincidentally enough, I got a similar request to yours across my desk this morning. I didn't go the BR route and instead implemented this using a Flow.

Drop a reply if you don't get there with the BR route and I can help you with the Flow option 

[Tony Chatfield1]

Hi, if you could share your existing script (in plain text or xml) the forum would be able to review and provide clearer advise\help diagnosis your codding issue.

At a basic level I would be using an after update BR on sys_user when current.yourField.changes()
and use GlideRecord to lookup\find your matched 'sys_user_grmember' record based on previous.yourFieldValue - and whatever mapping you have between department and group.
Once you have found the old record you delete it.
Then (again based on your department\group mapping) insert a new record based on current.yourFieldValue.

Your post is missing key information IE how/where are the Department and Group fields mapped?
Is this dynamic\based on a reference field or will you need to hard code a lookup in your script?

Below is a general guide (untested)

var myOldMappedGroupSys_id = 'yourMappedGroupBasedOnPreviousValue';
var myNewMappedGroupSys_id = 'yourMappedGroupBasedOnCurrentValue';

var myOldRec = new GlideRecord('sys_user_grmember');
myOldRec.addQuery('user', current.sys_id);
myOldRec.addQuery('group', myOldMappedGroupSys_id);
myOldRec.query();

if(myOldRec.next()) {
myOldRec.deleterecord();
} else {
gs.info('error message as not found');
}

var myNewRec = new GlideRecord('sys_user_grmember');
myNewRec.initialise();
myNewRec.user = current.sys_id;
myNewRec.group = myNewMappedGroupSys_id;

myNewRec.insert();