Creating and Adding Users to a Group Based on a Variable (Department)

[mtg01]

Hello Guys, this is my first post. I'm new to the platform, but not so new to programming, Anyway, I've pulled over department values to sys_user via LDAP/AD and now need a scheduled task to add users to a group based on their department. I wrote a script that can verify and add based on the name but I want the sys_id from the cmn_department table to match the new group also as it will make adding users possible. Here is my idea so far:

var dept_array = [];
       var dept_name = "";
   var dept_id = "";
       var dept = new GlideRecord("cmn_department");
   dept.addActiveQuery();
   dept.addQuery('name');
   dept.addQuery('sys_id');
       dept.query();

   while(dept.next()){
   dept_name = dept.name.toString();

   dept_id = dept.sys_id.toString();
   //create a department object that holds the name and sys_id.
   var dept_obj = {name: dept_name, id: dept_id}; 
   //store each object literal in an array
   dept_array.push(dept_obj);

   }
   //loop through the array, extract each pair
   for(i = 0; i < dept_array.length; i++){
   //gs.print("the array one at a time " + dept_array[i].name + ", " + dept_array[i].id);
   var group = new GlideRecord('sys_user_group');
   group.addQuery("name", dept_array[i].name);
               group.addQuery("sys_id", dept_array[i].id);   
               group.query();   
               if (group.next()) {   
                       // Ignore if group exists   
                       gs.print("Group " + group.name + " already exists");   
               }else{
                       var group2 = new GlideRecord("sys_user_group");
                       gs.print("group doesn't exist...Creating" + dept_array[i].name + " " + dept_array[i].id + " group");
                       group2.initialize();   
                       group2.name = dept_array[i].name;
                       group2.sys_id = dept_array[i].id;
                       group2.insert();   
               }

}

Goal = get user department, make groups with same name, add users to groups as a scheduled tasks so it updates as people change departments etc.

Any help is greatly appreciated!!

[manikorada · Accepted solution]

Matt,

You have some errors in the script. You can't set the sys_id directly like that, sys_id is auto populated when you insert a record. Also, two records in Service Now can't have same sys_id so you can't query on it.

I have modified your script and you can use something like this:

createGroup();

function createGroup()

{

var dept = new GlideRecord("cmn_department");

dept.query();

while(dept.next()){

  var group = new GlideRecord('sys_user_group');

  group.addQuery("name", dept.name);

  group.query();

  if (group.next()) {

  // Ignore if group exists

  gs.print("Group " + group.name + " already exists");

  }else{

  var group2 = new GlideRecord("sys_user_group");

  gs.print("group doesn't exist...Creating" + dept_array[i].name + " " + dept_array[i].id + " group");

  group2.initialize();

  group2.name = dept.name;

// groupid will give the sys_id of the new group inserted

  var groupid = group2.insert();

  }

}

}

[mtg01]

Yes I know, thanks Mani. It is a bit embarrassing as I was still in the middle of testing things and frustrated. You have answered a main question and that is you cannot create another record that uses the same sys_id. I should have known, and that being the case... My original solution(not this mess) worked as well as can be expected. Oh well, I appreciate the help Mani. I will check out your solution more when I get home and mark your answer correct.

Update, Upon further inspection your script is very close to my original. Well at least it validates I was originally on the right track. Sucks I wasted the afternoon trying to come up with a solution to getting the users into the groups once they are created. Sunday, I set up this script and created the groups but got stuck trying to get users in by department. Since sys_user_grmember uses only sys_Id I was hoping to keep them uniform. Thanks again!

**One thing you did differently than I did was at the bottom you assigned the variable groupid to the new sys_id. That actually solves my problem just fine:) I wasn't aware insert() returned the sys_id. Nice!

**UPDATE**

For those that want to see the final solution here is what I came up with. I appreciate the help Mani but I ended up learning another key aspect of SN and the ability to dot walk from the group member table back to grab the name value. It runs in the middle of the night and updates our groups. Works perfectly. Hope this helps someone

updateUserGroupsFromDepartment();

function updateUserGroupsFromDepartment(){

//create an array to store departments from cmd_departments

        gs.log("/// Start User Departments to Group Sync Routine ///");

var dept_array = [];

var dept = new GlideRecord("cmn_department");

        dept.addActiveQuery();

        dept.query();

        while(dept.next()){

    //fill array with departments by name

  dept_array.push(dept.name.toString());

}

  for(i = 0; i < dept_array.length; i++){

  gs.log("/// Checking for existance of group in sys_user_group via cmn_departments ///");

            var group = new GlideRecord('sys_user_group');  

            group.addQuery('name', dept_array[i]);

            group.query();

            if (group.next()) {

                      // Ignore if group exists

                      gs.log("Group " + group.name + " already exists");

            }else{

      var group2 = new GlideRecord("sys_user_group");

      group2.initialize();

      group2.name = dept_array[i]

      group2.insert();

  gs.log("Group membership created " + group2.name.toString());  

  }

}

gs.log("/// End Group Update ///");

gs.log("/// Start Adding Users to Groups ///");

addUsersToGroup();

}

addUsersToGroup();

function addUsersToGroup() {

var user = new GlideRecord("sys_user");

  user.query();

while(user.next()){

  var user_dept = user.department.name;

  if(user.department.name){

  var gm = new GlideRecord("sys_user_grmember");

  if(!isMember(user_dept, user.name)){

  gs.log("adding " + user.name + " to " + gm.group.name);

  gm.initialize();

  gm.user = user.sys_id;

  gm.group.setDisplayValue(user.department.name);

  gm.insert();

  }

  }

  }

}

//Check if the user is in the group table

function isMember(group_name, user_name) {

  var gr = new GlideRecord("sys_user_grmember");

  gr.addQuery('group.name', group_name);

  gr.addQuery('user.name', user_name);

  gr.query();

  while (gr.next()) {

  userInGroup = true;

  gs.log("Found User as a Group member.");

  return true

  }

  gs.log("User not found as group member.");

  return false;

  }

  gs.log("/// End Adding Users to Groups ///");

.