Report on Users Not assigned to a Group

Sam Ogden · ‎03-10-2017

We need to find the best way to see which users are in the system that are not assigned to any group. Is there a way to report on this?

I've looked at making a report against the sys_user table but then can't find a column for group to report against?

Any help would be greatly appreciated.

Thanks

Mike Allen · ‎03-10-2017

I would do a background script, something like:

var user = new GlideRecord('sys_user');

user.addActiveQuery();

user.query();

while(user.next()){

var grmember = new GlideRecord('sys_user_grmember');

grmember.addQuery('user', user.sys_id);

grmember.query();

if(!grmember.hasNext()){

gs.print(user.getDisplayValue());

}

View solution in original post

Harish KM · ‎03-10-2017

Check this Reporting on users with roles in groups

Regards
Harish

Marc A Love · ‎03-10-2017

If you were using the ODBC connection you could also do something like this:

Select

OAUSER.sys_user.name

From

OAUSER.sys_user

Where

OAUSER.sys_user.sys_id not in

(Select OAUSER.sys_user_grmember.user

From OAUSER.sys_user_grmember)

We do something similar quarterly in our audits...

ML

gyedwab · ‎03-14-2017

With Explore Analytics, you can do this without code in real time.

You create one pivot report on the group table with all the groups:

You create another pivot report on the group member table, showing the number of users per each group:

Then you use the Explore Analytics 'mash-up' feature to combine the two reports:

The result will look like this, with all of the groups, and the number of users associated to them (and blank for ones with no users):

Then, all you need to do is use the Explore Analytics 'output filter' feature to show only the groups with no users:

Because Explore Analytics queries ServiceNow in real time, you can now present that information in dashboards or in a scheduled job with the real-time data.

JosephW1 · ‎05-21-2020

I believe you can accomplish this with Related List Conditions. Just rebuild the report in this screenshot.

I ran it, got one number of results.

Then, I added one of the users in the list of results to a group.

Finally, I ran it again and got exactly 1 less result as last time. I think it's working. I hope that helps, have a wonderful day!

Sonam5 · ‎09-23-2020

Hi,

By using a simple related list filter condition it worked.

under condition-> Group.include members is false as shown in the figure.