Script to add users in a group

[palmen]

I have a requirement as follows.

All users who are responsible for a system (u_cmdb_ci_service_system) should belong to the group "ServiceNow - System Responsible".

If you are removed as system responsible you should no longer belong to the group.

If you are added as system responsible for a system you should be added to the group (unless you already are a member, same user can be responsible for more then 1 system).

What is the best solution for this? Create a Business Rule when you change the field System Responsible (u_system_responsible) or have a scheduled script that runs every night?

I started with the scheduled script and it's working fine. Only thing I'm a bit worried about is if you delete/comment row 3 in the script, all users will lose their groups. Since we assign roles by groups they would also lose all their roles which is a very bad thing.

This is my scheduled script

//Remove all users in group ServiceNow - System Responsible
var remove= new GlideRecord('sys_user_grmember');
remove.addQuery('group','=', '4fb4d1866f3842006cd79cef8d3ee468'); //ServiceNow - System Responsible
remove.query();
while(remove.next()){
   remove.deleteRecord();
}

//Add System Responsible to group "ServiceNow - System Responsible"
var system;
var group;
var user;

//Query all users at Company X
user = new GlideRecord('sys_user');
user.addQuery('company', '=', '7c401d5d15d66c40b84c3a16586bdce9'); //Company X
user.query();
while (user.next()) {
   //Query all active systems where user is system responsible
   system = new GlideRecord('u_cmdb_ci_service_system');
   system.addQuery('install_status', '=', '101'); //101 = Active
   system.addQuery('u_system_responsible', '=', user.sys_id);
   system.query();
   if(system.next()) {
       //Create a new group relationship record for this user
       group = new GlideRecord('sys_user_grmember');
       group.initialize();
       group.user = user.sys_id;
       group.group.setDisplayValue('ServiceNow - System Responsible');
       group.insert();
   }
}

Any thoughts about best practice?

Or is there some way to modify my scheduled script to not risk removing all group relations for all users?

[edwin_munoz]

Hello Fredrik,

You are right, there are a couple of errors in the script I posted. I have made the needed adjustments, could you please try once again and let me know the outcome?

var users = new GlideRecord('sys_user_grmember');

users.addQuery('group','4fb4d1866f3842006cd79cef8d3ee468'); //ServiceNow - System Responsible

users.query();

while(users.next()){

       if (! isResponsibleForSystem(users.user)) {

               users.deleteRecord();

       }



}



function isResponsibleForSystem(user) {

       system = new GlideRecord('u_cmdb_ci_service_system');

       system.addQuery('install_status', '101'); //101 = Active

       system.addQuery('u_system_responsible', user.sys_id);

       system.query();

       return system.hasNext();

}

Thanks

[palmen]

Thanks for the updated script, it works fine now.

[edwin_munoz]

Glad it is working Fredrik!

This is a perfect example of why it is important to name the variables correctly, I made the mistake of naming the variable users when in fact I wasn't querying the users table.

[palmen · Accepted solution]

These are the 2 scripts we use.

Business rule to add members and a scheduled script to remove users from the group.

BUSINESS RULE

Table: System [u_cmdb_ci_service_system]

Insert: True

Update: True

When: After

Filter Condition: "System responsible" changes

Script

//Get the new System Responsible

var user = current.u_system_responsible.sys_id;





//Create a new group relationship record for this user   

var rec1 = new GlideRecord('sys_user_grmember');   

rec1.initialize();   

rec1.user = user;   

rec1.group.setDisplayValue('ServiceNow - System Responsible');   

rec1.insert(); 

SCHEDULED SCRIPT

Run: Daily

Time: Hours 00:10:00

Script

var users = new GlideRecord('sys_user_grmember');     

users.addQuery('group','4fb4d1866f3842006cd79cef8d3ee468'); //ServiceNow - System Responsible     

users.query();     

while(users.next()){     

       if (! isResponsibleForSystem(users)) {     

               users.deleteRecord();   

       }         

}     



function isResponsibleForSystem(user) {     

       system = new GlideRecord('u_cmdb_ci_service_system');     

       system.addQuery('install_status', '=', '101'); //101 = Active     

       system.addQuery('u_system_responsible', user.user.sys_id);     

       system.query();     

       return system.hasNext();     

}     

[Kushagra Mehrot]

Hi Fredrik,

I am trying below script to add users in a group, but when this script runs second time it is creating duplicate entries in the grmember table.

var Vquery = "active=true^ user_nameSTARTSWITHae";

var Vuser = new GlideRecord('sys_user');

Vuser.addEncodedQuery(Vquery);

Vuser.query();

var group = 'SSPR - AE';

while(Vuser.next()){

    var Add = new GlideRecord('sys_user_grmember');

Add.initialize();

Add.user = Vuser.sys_id;

Add.group.setDisplayValue('SSPR - AE');

Add.insert();

      }

************************************************************************

To avoid this:

I changed the code to -

var Vquery = "active=true^ user_nameSTARTSWITHae";

var Vuser = new GlideRecord('sys_user');

Vuser.addEncodedQuery(Vquery);

Vuser.query();

var group = 'SSPR - AE';

while(Vuser.next()){

      if(!Vuser.name.isMemberOf(group)){

var Add = new GlideRecord('sys_user_grmember');

Add.initialize();

Add.user = Vuser.sys_id;

Add.group.setDisplayValue('SSPR - AE');

Add.insert();

      }}

But still the duplicate entries are getting created in grmember table. Can you help me out here?

Thanks,

Kush