Avoid Duplicate User Names When Creating Local Accounts

[Matthew Glenn]

Hey All,

I have a catalog item that we'll be occasionally using to create local ServiceNow accounts from within a workflow activity. That part is simple, but I want to be able to detect/avoid duplicate usernames/emails during the automated creation process. That's where I get stuck

The snippet below runs within a workflow activity, and outside of the submission and approval, there should be no human interaction here.

var first = current.variables.first;
var middle = current.variables.middle;
var last = current.variables.last;
//set base alias/username/email
var alias = first.toLowerCase().toString().substring(0,1) + last.toLowerCase();
var username = alias;
var email = alias + '@domain.com';
var grGet = new GlideRecord('sys_user');
grGet.addEncodedQuery('user_name=' + username + '^ORemail=' + email);
grGet.query();
if(grGet.hasNext()){
           alias = first.toLowerCase().toString().substring(0,2)   + last.toLowerCase();
           username = alias;
           email = alias + '@domain.com';         
}

As you can see, I'm doing a simple glide lookup to check for a conflict. If we have a conflict, we include the 2nd letter if the first name. That's as far as I can get. What if we have a conflict with the 2nd letter/option, or the 3rd, etc...?

If we have a Mark Smith, and hire in a Mario Smith, Mario becomes 'masmith'. But what if we later hire a Martha Smith, how do we get her to be 'marsmith? Once the first name runs out (two John Smith's in the company), we then need to roll over to the middle name and follow the same process.

Can anyone help?

[Tim Deniston]

Give this a shot. Basically, we loop through the first+middle combination until an available user/email is found. It does not create a user if it gets all the way through and doesn't have a match.

var first = current.variables.first;
var middle = current.variables.middle;
var last = current.variables.last;
var firstmiddle = first + middle;


var validName = false;


while (validName == false) { //this will be set to TRUE when a valid name is found
   for (var i = 1; i <= firstmiddle.length; i++) {


   //set base username/email   
   var username = firstmiddle.toString().toLowerCase().substring(0, i) + last.toString().toLowerCase();
   var email = username + '@domain.com';


   var grGet = new GlideRecord('sys_user');
   grGet.addEncodedQuery('user_name=' + username + '^ORemail=' + email);
   grGet.query();


   if (!grGet.hasNext()) {
   //username and email are unique
   validName = true;
   current.variables.user_ref = createUser(first, middle, last, email, username);
   break;
   }
   }
   //no valid name found
   break;
}


function createUser(first, middle, last, email, username) {
   var newUser = new GlideRecord('sys_user');
   newUser.first_name = first;
   newUser.middle_name = middle;
   newUser.last_name = last;
   newUser.email = email;
   newUser.user_name = username;


   var userSysID = newUser.insert();
   return userSysID;
}

[Richard101]

I don't like doing so many GlideRecord queries, so I would suggest changing it to only do one.

1. Search the sys_user with the following criteria:
   • Where email starts with first letter of first name
   • Email ends with last name (excluding the domain)
2. Then, you can loop through these values in javascript and compare using more powerful regex and quicker.

[ginesdoleratorm · Accepted solution]

Following the advise of richardr you can also give a try the following code for checking existing users similar to how Tim suggested but limiting the number of queries.

Basically, it just make a query to retrieve all the users that might match the current user name, and build the username avoiding any of them. Additionally, if it cannot find a proper username after trying composing it using the first, middle and last name, it will append a number to it (e.g., JohnSmith3) as a fall back in case there are many users with similar names.

var first = current.variables.first;   
var middle = current.variables.middle;   
var last = current.variables.last;   
var firstmiddle = first + middle;
var user_ref;
var MAX_ATTEMPT = 1000;
var username;

var possibleMatches = findPossibleMatches(first, last);
var validName = false;
for (var i = 1; i <= firstmiddle.length; i++) {
   //set base username/email       
   username = firstmiddle.toString().toLowerCase().substring(0, i) + last.toString().toLowerCase();   
   var email = username + '@domain.com';
   if (!usernameExists(username, email, possibleMatches)) {
       //username and email are unique   
       validName = true;   
       user_ref = createUser(first, middle, last, email, username);   
       break;   
   }
}
if (!validName){
   for (var i = 1; i < MAX_ATTEMPT; i++){
       var usernameNr = username + i;
       var email = usernameNr + '@domain.com';
       if (!usernameExists(usernameNr, email, possibleMatches)){
           user_ref = createUser(first, middle, last, email, usernameNr);
           break;
       }
   }
}

function usernameExists(username, email, possibleMatches){
   return possibleMatches.indexOf(username) > -1 || possibleMatches.indexOf(email) > -1;
}

function createUser(first, middle, last, email, username) {   
gs.log(username);
   var newUser = new GlideRecord('sys_user');   
   newUser.first_name = first;   
   newUser.middle_name = middle;   
   newUser.last_name = last;   
   newUser.email = email;   
   newUser.user_name = username;   
   var userSysID = newUser.insert();   
   return userSysID;   
} 

function findPossibleMatches(first, last){
   var grGet = new GlideRecord('sys_user');
   var firstChar = first.toLowerCase().charAt(0)
   grGet.addEncodedQuery('user_nameSTARTSWITH' + firstChar + '^user_nameLIKE' + last + '^NQemailSTARTSWITH'+ firstChar +'^emailLIKE' + last);   
   grGet.query();
   var possibleMatches = [];
   while (grGet.next()){
       possibleMatches.push(grGet.user_name + "");
       possibleMatches.push(grGet.email + "");
   }
   return possibleMatches;
}

Hope it helps.

[Matthew Glenn]

Worked like a charm. Thank you very much!!