Auto Creation of Users

[#1]

Hi

I have a table in the DB called EMPLOYEE. From this, I want to automatically create new users in the SYS_USER table.

So, how can I maintain SYS_USER (insert update delete) based on changes in EMPLOYEE? I suppose it will have to be some kind of scheduled import job.

I hear that Business Rules is not the way to go.

Cheers
Dave

[#2]

My processes can update user information in EMPLOYEE.

A certain process needs to add the users to sys_user. I can't directly add them, your add_user (or whatever it might be called) process needs to add them. I can't assign sys_id's, and other automatically populated fields. Your add_user procedure should be able to do it. I want to harness your add_user procedure to take the information from my EMPLOYEE table, and add the users to Service Now.

[john_roberts]

How about using a JDBC Import Set to run an import daily from the external table?
Is this a hosted instance or on-premise?
Is the employee table on the same server?

[RobWoodbyrne]

You're going to need a unique identifier in the EMPLOYEE table - something like employee id that will be the same in your EMPLOYEE table and in our sys_user table. Then you could write a global business rule similar to below and schedule the script to run in an automated fashion. This will probably put a huge load on your system since it's performing full table scans so you'd want to make sure it runs in off hours and I'd recommend listening to the other suggestions - this is a very last resort and could cause an application server ourtage. This is only an example... John's suggestion is a better way to go, but so you don't think we are not answering the question... here you go: 🙂 The following wiki will tell you how to schedule scripts:

http://wiki.service-now.com/index.php?title=Automate_and_Schedule_Common_Activities

getEmployeeRecord();

function getEmployeeRecord(){
var ge = new GlideRecord('EMPLOYEE');
ge.query();
while(ge.next()){
 var upd = updateSysUser(ge.employee_id,ge.first_name,ge.last_name,ge.phone);
 if(upd = 'notupdated'){
  insertSysUser(ge.employee_id,ge.first_name,ge.last_name,ge.phone);
 }
 }
}

function updateSysUser(id,fname,lname,phone){
 var u = 'notupdated';
 var ue = new GlideRecord('sys_user');
 ue.addQuery('user_name',id);
 if(ue.next()){
 ue.first_name = fname;
 ue.last_name = lname;
 ue.phone = phone;
 ue.update();
 u = 'updated';
 }
return u;
}

function insertSysUser(id,fname,lname,phone){
 var iu = new GlideRecord('sys_user');
 iu.user_name = id;
 iu.first_name = fname;
 iu.last_name = lname;
 iu.phone = phone;
}


function deleteSysUser(id){
var du = new GlideRecord();
du.addQuery('user_name',id);
du.query();
if(du.next()){
 du.deletRecord();
 }
}

[CapaJC]

How, specifically, is EMPLOYEE being populated/maintained? System Import Sets? Scheduled Imports? Some other way?

If there's a Transform Map in the process somewhere updating EMPLOYEE, a transform map has a "run business rules" checkbox that would run the target table's business rules.

Business rules will only run on GlideRecord inserts/updates/deletes. If you're populating EMPLOYEE through some other back-end means, you'll need to schedule a script to do what you want.

[#6]

Yes, the table will be update by a periodic stored proc in the Database, not by Glide/Service-Now.

But yes, a script that runs daily is something I want to do. Can you please give me an example of a script that will add users based on external information.