Report on groups containing all inactive members

Dolly M · ‎05-30-2023

Need a report on groups containing all inactive members.

Dolly M · ‎05-31-2023

Thanks all -

Got this working using below code -

inactiveUserCount: function() {
var arr = [];
var group = new GlideRecord('sys_user_group');
group.addEncodedQuery('active=true^typeNOT LIKEd317d8111b537cd096c5a970f54bcbaf');
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;
}

View solution in original post

Devender Kumar · ‎05-31-2023

Hi @Dolly M

Please create a report on Group member table(sys_user_grmember) and query something like this

If my answer help you resolved your issue then please mark correct or helpful.

Regards

Devender

Dolly M · ‎05-31-2023

Hi Devender,

Yes, but I want to query all those groups which only has inactive members.

Ex: Group A contains 4 members > all 4 members are inactive > I want to show this group in my report.

Even if 1 out of 4 is active > then this group should not be shown in the report

jaheerhattiwale · ‎05-31-2023

@Dolly M

1. create report on group (sys_user_group) table.

2. Create a script include and write a function to find out the sys IDs of group which have all inactive members. Add sys IDs to array and return them

3. Create dynamic filter option and use the script include function in it

4. Add the filter in report using dynamic filter option created

Please mark as correct answer if this solves your issue

Please mark the answer as correct or helpful based on impact
ServiceNow Community Rising Star, Class of 2023

Dolly M · ‎05-31-2023

Yes, I am using the same approach, just need to find a way to query all inactive users