How to identify groups where the group manager is NOT a group member

[SarahC1]

Hi all,
I am attempting pull a list of all groups where the group manager is NOT a group member, but I'm beginning to wonder if this is possible? Its straightforward enough to identify groups where the manager is a member of a specific group but I cannot seem to find a way to find the reverse.
Currently trying to achieve this with a database view but its not something I've used previously. 
The tables I'm concerned with are sys_user_group and sys_user_grmember.
Grateful for any tips or pointers, even if to confirm this is not something that is possible!

[Hemanth M1]

Hi @SarahC1 ,

Have you tried reporting on your Database view where group member user != manager field and group by group name field.

[Anil Lande]

Hi,

You can run below script to find the groups where manager is not a member of group.

var groups = [];
var grp = new GlideRecord('sys_user_group');
grp.addNotNullQuery('manager');
grp.query();
while(grp.next()){
     var grMember = new GlideRecord('sys_user_grmember');
     grMember.addQuery('user',grp.manager.toString());
     grMember.addQuery('group',grp.getUniqueValue());
     grMember.query();
     if(!grMember.hasNext()){
             groups.push(grp.getUniqueValue());
             gs.info('Manager : '+grp.manager.getDisplayValue()+' is not member of Group : '+grp.getDisplayValue());
      }
}
gs.info('Groups sys_id : '+groups.toString());

[SarahC1]

Many thanks Anil, what syntax should I use to call this in a report builder please?

[SarahC1]

Many thanks Anil, what is the correct syntax to call this in the report builder?