How do I get the list of people managers for the users present in sys_user table

[Shreya2]

How do I get the number of  people mangers from the sys_user table without creating another field and populating it via a Business Rule/Scheduled Job? I have an LDAP integration set up, and want to report out the total number of managers in the org. I have tried looking for "Title" field based on many community articles but that filed is empty for me.

[johnfeist]

Hi Shreya,

What you are looking for is a simple SQL query, but no so easy with ServiceNow reporting.  Probably the simplest way to get your number is create a report something like manager, last Name, First Name where Manager is not empty and then group by manager.  You will see your total number at the bottom of the report.

There are a number of open suggestions to ServiceNow to give us greater flexibility in creating reports and what you are looking for is a common need and request.

Hope that helps.

:{)

Helpful and Correct tags are appreciated and help others to find information faster

[Lukasz Bojara · Accepted solution]

Hi,

The sys_user table has an attribute called manager. If you need to get only the number then you can write a script using the GlideAggregate. Have a look at this script:

function getUniqueValues(table,groupingAttribute,encodedQuery){
	var uniqueValues = [];
	
	var ga = new GlideAggregate(table);
	ga.addAggregate('COUNT');
	ga.groupBy(groupingAttribute);
	ga.addHaving('COUNT','>','0'); // get only values where count is more than 1
	
	// check if encoded query has been provided, if yes the it will be added to the GlideAggregate object
	if(encodedQuery != undefined) {
		ga.addEncodedQuery(encodedQuery);
	} 

	
	ga.query();
	
	while (ga.next()) {
		// check if the row is for a unique value and not for an overall count
		if(ga.getDisplayValue(groupingAttribute)){
			uniqueValues.push(ga.getDisplayValue(groupingAttribute)); // add the value to the array
		}
	}
	
	return uniqueValues;
}


var test = getUniqueValues('sys_user','manager').length;

gs.debug(test);

I used my function for this, you can find the description in my article. If you will remove the length property, you will have an array of all managers that have at least 1 user assigned to them.

If my answer helped you in any way, please then mark it as helpful. If this solved your case please mark it as a correct answer. Thank You.

Best regards,

Łukasz

[Shreya2]

Hi Lukasz,

Thanks for the response. The script did help me get the number of managers when the function is called in a background script. 

How do I get that in a report though? 

I tried with the filter condition as 

Sys ID is javascript:getUniqueValues('sys_user','manager','active=true').length;

Should I be putting the filter on some other field?

[Shreya2]

Thanks Łukasz,

That worked 🙂

Called the function in the report and it gave me the output that I expected.