Finding a user's Director/VP using VIP flag

[alan_lowrance]

I used to post these blogs on just the North Texas User Group, but seems I can't do anything but post a question there now, so here I am!

Business Case: For certain expensive software or other requests we need to send an approval to the person's manager, or manager's manager, or manager's manager's manager... as far up the chain until we find someone who is flagged as a VIP on the user table.  Either the VIP list is updated manually, or you have a Business Rule that flags them when chosen as the head of a department.

If the latter, and you have a standardized list of departments that HR isn't always changing and renaming every few months, you could easily link the new Director/VP field to the user's department.dept_head field.  But let's say you need to actually recursively search up the chain of managers until you find one that's a VIP and populate them into the field instead.  I would create a u_director or whatever named field you wanna use on the sys_user table and since it's just an empty reference field right now, there's no harm in trying to populate everyone's new field using a Background Script.  For those who don't know what a ServiceNow Background Script is, it's raw server-side javascript like what you put in a Business Rule or Scheduled Job, but you have to elevate your permissions to security_admin because it would allow you to just delete all records on a table or remove journal entries, or a number of other naughty things if you abused it.

In this case though, we'll use a Background Script to test our logic and then can copy-paste the working bit into a Business Rule to keep everything up to date after we get it working.

Background Script:

var allemps = new GlideRecord('sys_user');
allemps.addActiveQuery();
allemps.addQuery('source','!=',''); //only run on LDAP sourced users
allemps.query();
while (allemps.next()){
	if (allemps.manager.nil() || (allemps.vip && allemps.title.indexOf('Admin Assistant')>-1))
		allemps.u_director = allemps.manager; //admin assistants are probably flagged as VIP but want their manager being the director
	else {
		var oneup = allemps.manager;
		while (oneup != 'found')
			oneup = isVIP(oneup);
	}
allemps.update();
}

function isVIP(sysid){
	var person = new GlideRecord('sys_user');
	if (person.get(sysid)){
		if (person.vip || person.manager.nil()){
			allemps.u_director = sysid;
			return 'found'; //exit loop
		}
		else
			return person.manager;
	}
	else
		return 'found'; //another exit loop just in case
}

And if that worked correctly, you just need to use most of that in a Business Rule to keep everything up-to-date as Manager field changes, the person themselves become a VIP, or upon first insert of a new user.  So the condition would be: current.operation() == 'insert' || current.manager.changes() || current.vip.changes()

(function executeRule(current, previous /*null when async*/) {
	if (current.manager.nil() || (current.vip && current.title.indexOf('Admin Assistant')>-1))
	current.u_director = current.manager;
	else {
		var oneup = current.manager;
		while (oneup != 'found')
			oneup = isVIP(oneup);
	}
	function isVIP(sysid){
		var person = new GlideRecord('sys_user');
		if (person.get(sysid)){
			if (person.vip){
				current.u_director = sysid;
				return 'found';
			}
			else
				return person.manager;
		}
		else
			return 'found';
	}
})(current, previous);

The only problems we've run into are that if your manager or anyone between you and the previous VIP becomes a VIP, it won't know to update everyone under them so instead of this business rule you could just run the first Background Script every Sunday or something to keep it all pretty much up to date as long as promotions are happening toward the end of weeks.

In summary, it's easier to just keep your department heads updated and use those instead, but hopefully you can use this example of Background Scripts, Business Rules, Scheduled Jobs, etc. as a thought exercise and play around with looping functions.

[Liju John1]

//requestor='userName';
requestor=gs.getUserName();
var manager="";
var usr= new GlideRecord('sys_user');
usr.get('user_name', requestor);
usr.query();
if(usr.next()){
manager=usr.manager;
if(manager)
var myVPemail= getmanagerstitle(manager);
gs.print(myVPemail +" is the VP");
}
//answer=vptitle;
function getmanagerstitle(mgr,levels){
var usr= new GlideRecord('sys_user');
usr.get('sys_id',mgr);
usr.query();
if(usr.next()){
var isVP = usr.title.indexOf("VP, ");
if (isVP == 0){
gs.print(usr.email +" - "+usr.title);
return usr.email;
}
else{
gs.print(usr.email +" - "+usr.title);
return getmanagerstitle(usr.manager);
}
}
}