How to build a report to find out if the Group manager is also a group member of the same group.

[tengkuzul]

Hi Everybody,

Context: Group management is a mess and we are doing some data clean up.

I work with reporting and would like to make a report to find if the group manager is also in the same group member list.

I have tried in tables below but cant find any result

[sys_user_group]
[sys_user_grmember]

Many thanks for any idea.

[Ankur Bawiskar]

@tengkuzul 

Did you try to use database view?

[Ankur Bawiskar]

@tengkuzul 

this background script will give same result and list down the groups

Run this as admin in background script

try{
	var groupArray = [];

	var groupRec = new GlideRecord('sys_user_group');
	groupRec.addEncodedQuery("managerISNOTEMPTY");
	groupRec.query();
	while(groupRec.next()){
		var gr = new GlideRecord("sys_user_grmember");
		gr.addQuery("group", groupRec.getUniqueValue());
		gr.addQuery("user", groupRec.manager);
		gr.query();
		if(gr.hasNext())
			groupArray.push(groupRec.getValue('name'));
	}
	gs.info("Groups where group manager is also a member: " + groupArray);
}
catch(ex){
	gs.info(ex);
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Tai Vu]

Hi @tengkuzul 

Let's try the below query.

URL: https://<instance_name>/sys_user_grmember_list.do?sysparm_query=userSAMEASgroup.manager

Let me know if it works for you!

Cheers,

Tai Vu

[tengkuzul]

Hi,

Thanks for the answer.

This sorta helps but now it leaves me to figure out which groups manager is not in the group member list.