How to retrieve a list of employees up to 5 levels - script include

[YenGar]

Hello all,

I've created a script include to retrieve the users that can report up to a VP (could be 5 layers up) and use it in a dynamic filter but the query is super slow and it causes records to load up very slowly. I am trying to update it in a way that is not so hard on performance but cannot come up with a good way of doing it. Can you experts please suggest what I should change in this script to allow it to be used in a dynamic filter but not compromise performance? Or suggest a better way to handle this? 

script - it is client callable to be used in the condition builder

getManagerEmployees: function(){
		//function getSubordinates(){
		var userList = [];
		var maxLevel = 5; 
		var level = 1;
		var manager = gs.getUserID();
		if(manager)
			recurseUsers(manager,level,maxLevel,userList);
		return userList; 

		function recurseUsers (manager, level,maxLevel,userList) {
			var grUser2 = new GlideRecord('sys_user');
			grUser2.addQuery('manager',manager);
			grUser2.addQuery('active', true);
			//grUser2.addAggregate('COUNT');
			grUser2.query();
			while(grUser2.next()) {
				userList.push(grUser2.getValue('sys_id'));
				if (level+1 <= maxLevel ) {
					recurseUsers(grUser2.getValue('sys_id'),level + 1,maxLevel,userList);
				}
			}
			return userList;
		} 

	},

 Any help is appreciated!

thank you, 

Yen

[Bert_c1]

Create an index on sys_user, that includes 'manager' and 'active'.  See:

https://docs.servicenow.com/search?q=Create%20a%20table%20index

[YenGar]

thank you for your response Bert. I did index the fields but noticed no significant difference. I decided to re-write the script at a group level instead of querying the users table. The query runs faster at the group level but I did open up a case with Servicenow to understand why the script include is being triggered on load when it is not being directly called via a dynamic filter. 

[Bert_c1]

Hi YenGar,

You are not incrementing the 'Level' variable in your script in the while loop (I see that now, it is being incremented). So that reads each record in sys_user that meets your conditions.  And I don't think you need to have 'return userList;' in your function, you pass the array reference in the call to 'recurseUsers()'. Maybe post the entire script, what you posted is partial.  And I doubt you'll be a difference answer from Support on this. Test your script logic in Scripts background. add 'gs.info();' lines to debug what is being returned.  anyway, the follows seems to work in Scripts Background

//function getSubordinates(){
var userList = [];
var maxLevel = 5; 
var level = 1;
//var manager = gs.getUserID();
gs.info('My user ID = ' + gs.getUserID());
var manager = '5137153cc611227c000bbd1bd8cd2005';  //fred.luddy
if(manager)
	recurseUsers(manager,level,maxLevel,userList);
//return userList;
gs.info('manager: ' + manager + ' has managers: ' + userList);

function recurseUsers (manager, level,maxLevel,userList) {
	var grUser2 = new GlideRecord('sys_user');
	grUser2.addQuery('manager',manager);
	grUser2.addQuery('active', true);
	//grUser2.addAggregate('COUNT');
	grUser2.query();
	gs.info("Found " + grUser2.getRowCount() + " sys_user records with manager = " + manager);
	while(grUser2.next()) {
		userList.push(grUser2.getValue('sys_id'));
		if (level+1 <= maxLevel ) {
			recurseUsers(grUser2.getValue('sys_id'),level + 1,maxLevel,userList);
		}
	}
//	return userList;
} 

returns:

*** Script: My user ID = 6816f79cc0a8016401c5a33be04be441
*** Script: Found 2 sys_user records with manager = 5137153cc611227c000bbd1bd8cd2005
*** Script: Found 0 sys_user records with manager = 0a826bf03710200044e0bfc8bcbe5d7a
*** Script: Found 0 sys_user records with manager = b282abf03710200044e0bfc8bcbe5d28
*** Script: manager: 5137153cc611227c000bbd1bd8cd2005 has managers: 0a826bf03710200044e0bfc8bcbe5d7a,b282abf03710200044e0bfc8bcbe5d28

However the two array elements returned show the two users whose manager is fred luddy in my demo data. The script logic seems to work.

[Bert_c1]

Hi @YenGar ,

Maybe the recursive function is the problem, the java call stack grows with that.  Try:

getSubordinates();

function getSubordinates(){
var userList = [];
var maxLevel = 5; 
var level = 1;
//var manager = gs.getUserID();
gs.info('My user ID = ' + gs.getUserID());
var manager = '5137153cc611227c000bbd1bd8cd2005';  //fred.luddy
if(manager)
	recurseUsers(manager,level,maxLevel,userList);
//return userList;
gs.info('manager: ' + manager + ' has subordinates: ' + userList);

function recurseUsers (manager, level,maxLevel,userList) {
	var userList1 = [];
	var userList2 = [];
	var userList3 = [];
	var userList4 = [];
	var userList5 = [];
	var grUser1 = new GlideRecord('sys_user');
	grUser1.addQuery('manager',manager);
	grUser1.addQuery('active', true);
	grUser1.query();
	gs.info("Found " + grUser1.getRowCount() + " sys_user records with manager = " + manager);
	while(grUser1.next()) {
		gs.info('user 1: ' + grUser1.name);
		userList1.push(grUser1.getValue('sys_id'));
		userList.push(grUser1.getValue('sys_id'));
	}
	// level 2 subordinates
	gs.info("Checking level 2 users: " + userList1);
	for (i=0; i<userList1.length; i++) {
		var grUser = new GlideRecord('sys_user');
		grUser.addQuery('manager', userList1[i]);
		grUser.addQuery('active', true);
		grUser.query();
		gs.info("Found " + grUser.getRowCount() + " sys_user records with manager = " + userList1[i]);
		while(grUser.next()) {
			userList2.push(grUser.getValue('sys_id'));
			userList.push(grUser.getValue('sys_id'));
		}
	}
	// level 3 subordinates
	gs.info("Checking level 3 users: " + userList2);
	for (i=0; i<userList2.length; i++) {
		var grUser = new GlideRecord('sys_user');
		grUser.addQuery('manager', userList2[i]);
		grUser.addQuery('active', true);
		grUser.query();
		gs.info("Found " + grUser.getRowCount() + " sys_user records with manager = " + userList2[i]);
		while(grUser.next()) {
			userList3.push(grUser.getValue('sys_id'));
			userList.push(grUser.getValue('sys_id'));
		}
	}
	// level 4 subordinates
	gs.info("Checking level 4 users: " + userList3);
	for (i=0; i<userList3.length; i++) {
		var grUser = new GlideRecord('sys_user');
		grUser.addQuery('manager', userList3[i]);
		grUser.addQuery('active', true);
		grUser.query();
		gs.info("Found " + grUser.getRowCount() + " sys_user records with manager = " + userList3[i]);
		while(grUser.next()) {
			userList4.push(grUser.getValue('sys_id'));
			userList.push(grUser.getValue('sys_id'));
		}
	}
	// level 5 subordinates
	gs.info("Checking level 5 users: " + userList4);
	for (i=0; i<userList4.length; i++) {
		var grUser = new GlideRecord('sys_user');
		grUser.addQuery('manager', userList4[i]);
		grUser.addQuery('active', true);
		grUser.query();
		gs.info("Found " + grUser.getRowCount() + " sys_user records with manager = " + userList4[i]);
		while(grUser.next()) {
			userList5.push(grUser.getValue('sys_id'));
			userList.push(grUser.getValue('sys_id'));
		}
	}
	
//	return userList;
}
}

(The above can be 'cleaned up'.) And see if performance is improved.