Report on Managers who do not have delegates set up

[juliekirkeeide]

Hello. I am using the reporting module (tried classic UI and New) to try to build a report showing which managers of groups do not have a delegate set up. I'm not having any luck. 

Looking for Groups / Managers / Delegate = False

In the classic UI I cannot figure out how to pull in data from a Related List

In the new UI, I cannot figure out which table to use that would allow me to pull in a related list that would result in showing all the groups, who the manager is and list the delegate or <empty> if they don't have a delegate set up.  

Again, I'm not concerned with managers (users) who do have delegates set up but only with those who do not.

Any tips appreciated!

Thank you,

Julie 

[SanjivMeher]

I am sorry. We actually have a custom field delegate which we use. As Saurabh suggested, you can create a database view between sys_user_group and sys_user_delegate and use that.

The other option is script

ex. you can run the below script in background script

var gr = new GlideRecord('sys_user_group');
gr.addActiveQuery();
gr.query();
while (gr.next())
{
if (gr.manager!='')
{
var dl = new GlideRecord('sys_user_delegate');
dl.addQuery('user',gr.getValue('manager'));
dl.query();
if (dl.next())
{
gs.info('Delegate for '+gr.getDisplayValue('manager') +' is '+dl.getDisplayValue('delegate'));
}
else
{
gs.info('Delegate not found for '+gr.getDisplayValue('manager') )
}
}
}

[Saurabh Gupta]

Hi,
You can create a database view using sys_user_delegate and sys_user_group table and use it in reporting.

Please mark the answer as correct, If I answered your query. It will be helpful for others who are looking for similar questions.

Regards
Saurabh