Is a way to find all ACTIVE Groups with membership that are ALL Inactive

[Muradz1234567]

I want to determine and create a report on all active groups that their memberships is completely INACTIVE. 

[Ankur Bawiskar]

@Muradz1234567 

are you saying you want list of groups which are active and in those groups all members are inactive?

[Muradz1234567]

Yes. For example say active "Group A" has 10 members but ALL of them are Inactive. 

[Ankur Bawiskar · Accepted solution]

@Muradz1234567 

this will work

1) create a script include which is client callable and classless

function inactiveUserCount() {
	var arr = [];
	var group = new GlideRecord('sys_user_group');
	group.addActiveQuery();
	group.query();
	while (group.next()) {
		var group_member = new GlideAggregate('sys_user_grmember');
		group_member.addEncodedQuery('group.sys_id=' + group.sys_id);
		group_member.query();
		var member_count = group_member.getRowCount();

		if (member_count > 0) {
			var active_member = new GlideAggregate('sys_user_grmember');
			active_member.addEncodedQuery('group.sys_id=' + group.sys_id + '^user.active=false');
			active_member.query();
			var active_member_count = active_member.getRowCount();
			if (active_member_count == member_count) {
				arr += (group.sys_id + ',');
			}
		}
	}
	return arr;
}

then call that in list report on sys_user_group table like this

This worked for me

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