How do I create a Dynamic Group from sys_user table?

[andrew_b]

I would like the ability to create a group in ServiceNow that is dynamically updated based on a user attribute. 

Since I have all my users in our sys_user table, I would like to setup a business rule that runs a query that populates a group in ServiceNow based on an attribute. The end goal is to make three individual dynamic groups that contain faculty, staff and students . (FYI: It's dynamic becasue my sys_user table is updated via a transform map that pulls from our Active Directory) 

Table: sys_user
Field: u_user_type
Type: reference
Reference: u_user_affiliation
Reference Qual: u_active=true^EQ
Max Length: 40

 Possible Values: faculty, staff or student

My assumption is that I would need to make a business rule on the sys_user table to check for the field u_user_type and has the attribute faculty. 

Here is where I need some help. I'm not a developer for service now but I have a some  programming experience. I hate to farm this job out so I'm determined to figure this out for myself. I need some help understanding how to accomplish this task. I have looked at business rules and I think that's the direction that I need to go but I get lost when setting it up. 

Any help would be greatly appreciated. 

Here is what I have put together for the query so far... 

var target = new GlideRecord('sys_user');
target.addQuery('u_user_type',faculty);
target.query(); // Issue the query to the database to get relevant records
while (target.next()) {
// add code here to process the incident record
}

[Allen Andreas]

Hi,

Not farming it out for a consultant to get paid for it, but farming it here for free help, haha...anyways...

You're on the right path, but this business rule would need to be on the sys_user table and set it for insert and update checkboxes, this can run after (that's a checkbox selection too and appears after you click the "advanced" link).

You'd want to set the condition that the user type changes (this determines when this particular business rule runs) and then in the script tab (again..only shows once advanced has been chosen)..you'd want to query your group membership table (sys_user_grmember) and then insert records there...adding them to a group.

So then you'd query in the script like:

var gr = new GlideRecord('sys_user_grmember');
gr.initialize();
if (current.u_user_type == 'sys_id_of_student') {
gr.group = 'sys_id_of_student_group';
gr.user = current.sys_id;
gr.insert();
} else if (current.u_user_type == 'sys_id_of_faculty') {
gr.group = 'sys_id_of_faculty_group';
gr.user = current.sys_id;
gr.insert();
}

Then just rinse and repeat for staff or any other types you have...that should get you started though pretty close to done.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[jasonjones]

Allen,

Thanks for your help. 

The script below works as a fix script, but seems to not work as a scheduled job.

//Query all users in the 'Staff' type
var rec = new GlideRecord('sys_user');
rec.addQuery('u_user_type', '72e223efdbc522407bcdfb37bf96194b');
rec.addNotNullQuery('department');
rec.addQuery('company', 'c575e135db1ce20015adf1fcbf96190d');
rec.addQuery('active', 'true');
rec.query();
while(rec.next()){
  //Create a new group relationship record for this user
  var rec1 = new GlideRecord('sys_user_grmember');
  rec1.initialize();
  rec1.user = rec.sys_id;
  rec1.group.setDisplayValue('cba1e38cdbbbff0015adf1fcbf9619ef');
  rec1.insert();
}

Below is also one that I was testing by filtering different departments into different groups, but it was erroring out as a fix script.

//Query all users in the 'Faculty' type
var rec = new GlideRecord('sys_user');
rec.addQuery('u_user_type', '81e2a3efdbc522407bcdfb37bf961901');
rec.addQuery('department=e74be92fdb88620015adf1fcbf9619ee^ORdepartment=bc3ba12fdb88620015adf1fcbf961909');
rec.addQuery('company', 'c575e135db1ce20015adf1fcbf96190d');
rec.addQuery('active', 'true');
rec.query();
while(rec.next()){
  //Create a new group relationship record for this user
  var rec1 = new GlideRecord('sys_user_grmember');
rec1.initialize();
if (current.department =='bc3ba12fdb88620015adf1fcbf961909') {
  rec1.group.setDisplayValue('f4a1238cdbbbff0015adf1fcbf9619df');
  rec1.user = rec.sys_id;
  rec1.insert();
} else if (current.department == 'e74be92fdb88620015adf1fcbf9619ee') {
  rec1.group.setDisplayValue('cba1e38cdbbbff0015adf1fcbf9619ef');
  rec1.user = rec.sys_id;
  rec1.insert();
}
}

Any assistance is helpful.

Thanks.

Jason

[Robert T]

Finding this years later and sending out a prayer here:

This script worked magic for when the conditions of update/insert are met.

1. Is there an inverse of this script you could detail (i.e., if a user leaves that "type" and goes to some other type -- even a type not defined in the else statements -- then remove the user from that group).  ... So if a student leaves the institution, and no longer has a u_user_type, or has one of "alumni" or something, is there a way to notice this and remove that student from the group?

2. How do I fire off this rule to run on all current users (1 time), then have it run with updates/inserts?

Many thanks,

--Robert

[Alok Das]

Hi Andrew,

Yes it can be done with the Business Rule on "sys_user" Table as suggested by Allen, I would like to add few things,

Creating a Scheduled job would be a best Approach, in the perspective of system performance. Business Rule will be triggered on each and every single record insert or update, so I would suggest of scheduled record instead of Business Rule.

Below is the script for scheduled job:

usertable();
function usertable(){
	var user = new GlideRecord('sys_user');
	user.addEncodedQuery('sys_updated_onRELATIVEGE@hour@ago@24');//picks the records which were updated in last 24 hours.
	user.query();
	while(user.next()){
		if(current.u_user_type=='faculty'){
			updategroupuser(user.sys_id, 'groupsysid1');//replace the groupsysid1 with group sys_id
		}
		if(user.u_user_type=='staff'){
			updategroupuser(current.sys_id, 'groupsysid2');//replace the groupsysid2 group sys_id
		}
		if(user.u_user_type=='student'){
			updategroupuser(current.sys_id, 'groupsysid3');//replace the groupsysid3 group sys_id
		}
	}
}
//this function adds the user to a group if user is not a member of that particular group 
function updategroupuser(user,group){
	var table = new GlideRecord('sys_user_grmember');
	table.addQuery('user',user);
	table.addQuery('group',group);
	table.query();
	if(!table.next()){
		table.initialize();
		table.setValue('user',user);
		table.setValue('group',group);
		table.insert();
	}
}

Please mark my answer Correct/Helpful based on the impact.

Regards,

Alok