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

jaheerhattiwale · ‎05-31-2023

@Dolly M Please use below code in the script include function to get the sys ids of all the groups with inactive members.

getGroupWithInactiveMembers: function(){

var groupsWithInactiveMembers = [];

var groupGr = new GlideRecord("sys_user_group");

groupGr.query();

while(groupGr.next()){

var grpMemberGr = new GlideRecord("sys_user_grmember");

grpMemberGr.addQuery("group="+groupGr.sys_id.toString());

grpMemberGr.addActiveQuery();

grpMemberGr.query();

if(!grpMemberGr.hasNext()){

groupsWithInactiveMembers.push(groupGr.sys_id.toString());

}

return groupsWithInactiveMembers.toString();

},

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

jaheerhattiwale · ‎05-31-2023

@Dolly M Did you try this code? This code shouldn't take much time for query. Please try once.

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

Dolly M · ‎05-31-2023

Hi @jaheerhattiwale

Yes, addActiveQuery doesn't works for sys_user_grmember as active field is not present on this table.

jaheerhattiwale · ‎05-31-2023

@Dolly M oh yes. Please try the below updated code

getGroupWithInactiveMembers: function(){

var groupsWithInactiveMembers = [];

var groupGr = new GlideRecord("sys_user_group");

groupGr.query();

while(groupGr.next()){

var grpMemberGr = new GlideRecord("sys_user_grmember");

grpMemberGr.addQuery("group="+groupGr.sys_id.toString());

grpMemberGr.addQuery('user.active=true');

grpMemberGr.query();

if(!grpMemberGr.hasNext()){

groupsWithInactiveMembers.push(groupGr.sys_id.toString());

}

return groupsWithInactiveMembers.toString();

},

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

Thanks @jaheerhattiwale - I tried this code but taking lot of time as it will iterate through each record in sys_user_grmember. The system became unresponsive resulting in error message No response from server.

Can it be done via Database view ?