How to know groups without members

[arielgritti]

Hi community

Today I needed to answer this question: How we know which groups haven't members?

Looking in the community I found helpful posts about it and now I shared my implementation to help other mates with the same question

Step 1

I added a field in the sys_user_group table called u_group_members.

Step 2
I created a BR after insert/update/delete in the sys_user_grmember table:

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    var grpm = new GlideAggregate('sys_user_grmember');
    grpm.addQuery('group', current.group);
    grpm.addAggregate('COUNT');
    grpm.query();
    var groupMembers = 0;

    if (grpm.next()) {
        groupMembers = grpm.getAggregate('COUNT');
    }

    var grGroup = new GlideRecord("sys_user_group");
    grGroup.addQuery("sys_id", current.group);
    grGroup.query();
    if (grGroup.next()) {
        grGroup.u_group_members = groupMembers;
	grGroup.include_members = true;
		
	grGroup.update();
    }


})(current, previous);

Step 3

I created a fix script to update all the groups with the "count members".

var grToUpd = 'xxxxxxxxxxxxxxxxxxx'; // sys_user_group sys_id for individual update
var individualUpd = false;
var groupsToUpd = 0;
var groupsUpdated = 0;
var debug = true;

var grGroup = new GlideRecord("sys_user_group");
if (individualUpd) {
    grGroup.addQuery("sys_id", grToUpd);
}
grGroup.query();
groupsToUpd = grGroup.getRowCount();

if (debug){
	gs.log('Groups to update: ' + groupsToUpd);
}

while (grGroup.next()) {
	
	if (debug) {
		gs.log('Updating group: ' + grGroup.getDisplayValue());
	}

    var grpm = new GlideAggregate('sys_user_grmember');
    grpm.addQuery('group', grGroup.sys_id);
    grpm.addAggregate('COUNT');
    grpm.query();
    var groupMembers = 0;

    if (grpm.next()) {
        groupMembers = grpm.getAggregate('COUNT');
		
		if (debug) {
			gs.log('Group members: ' + groupMembers);
		}
    }

    if (groupMembers > 0) {
        grGroup.u_group_members = groupMembers;
        grGroup.include_members = true;

        grGroup.update();
		
		if (debug) {
			gs.log('Group updated');
		}
		groupsUpdated = groupsUpdated + 1;
    }
	else {
		grGroup.u_group_members = 0;
        grGroup.include_members = false;
		
		grGroup.update();		
	}
	
	if (debug) {
		gs.log('Groups updated: ' + groupsUpdated);
	}

}

With this solution you can query the groups in two ways to know if they have members:

1. u_group_members > 0
2. include_members = true

Please mark helpful, correct or bookmark if I helped you.
This action will help other members with similar issues to find a solution.
Thanks
Ariel

[Lucas Vieites]

Hey Ariel,

would this other solution work for you? I've been playing around with reporting lately and found the Related list conditions feature in the reporting module very useful.

For your example, I would:

1. Create a report on the [sys_user_group] table
• Add any filter you need, for example: [Active] [is] [true])
2. Open the Related list conditions under the filter area and select Group Members
• This should be a related list in an OOB system
3. Change the condition to "Equal to 0 selected table records are related to a record on"
4. Run the report

In the following screenshot I used a List type report and added a few columns:

I hope this helps users who don't have access to scripting on their instance.

Kind regards,
Lucas Vieites
P.S. If my reply helped you, please mark it as helpful or correct so other community members can benefit from this information.

[arielgritti]

Hi Lucas,

This definitively is better than scripting. I was trying with the related list in the report but I couldn't solve it. With your help, I'll try again! This was my first option mate.

Thanks,

Ariel

[arielgritti]

Hi Lucas again!

Works! 

I can't belive how I didn't see the "equals to 0" condition 😞

Thanks mate!

Ariel

[Allen Andreas]

Hi,

Yeah, great call out, because as nice as this article is, it's not needed if you can avoid doing all this scripting.

Thanks!

[arielgritti]

Hi @Allen A 

Totally agree with you. What @Lucas Vieites says was my first option, but I couldn't fix it in my first attempt 😞 for that reason, I tried with code, but mates, use the OOTB solution, always is better than write code!

Thanks,

Ariel

[Deepak Forum]

Hi All,

I see people are struggling to set Equal to 0, please see the snip.

[Sooraj_Singh_SN]

we can also run below background script to get groups with zero members 

var groupGR = new GlideRecord('sys_user_group');
//groupGR.addActiveQuery();
groupGR.query();


var zeroMemberGroups = [];


while (groupGR.next()) {
  var groupID = groupGR.getUniqueValue();
  
 
  var groupMembersGR = new GlideRecord('sys_user_grmember');
  groupMembersGR.addQuery('group', groupID);
  groupMembersGR.query();
  
  
  if (!groupMembersGR.hasNext()) {
    zeroMemberGroups.push(groupGR.getValue('name'));
  }
}


gs.info("Groups with zero members: " + zeroMemberGroups.join(', '));
gs.info(zeroMemberGroups.length);

[Devarsh Ganesh]

Please go though the below code.

var count=0;//to count how many groups are not having the members

var countpm=0;//to count how many groups are  having the members

var group_name=[];

var group =new GlideRecord('sys_user_group');

group.query();

while(group.next())

{

    var grm=new GlideRecord('sys_user_grmember');

    grm.addQuery('group',group.sys_id);

    grm.query();

    if(grm.next())

    {

    countpm=countpm+1;

    }

    else

    {

   // gs.print("Member not present:"+group.name);

    group_name.push(group.name.toString());// You can store sys_id also but you have to convert it to string because the array stores string values

    count=count+1;

    }

}

gs.print('No members'+count);

gs.print("groups with no members");

for(var i=0;i<count;i++)

{

 gs.info(group_name[i]);

}

If it is helpful for you then please give a comment and let me know

[Trish Mac]

@Lucas Vieites  I found this today and your solution was perfect as is.  It gave me exactly what I was looking for.

Thanks very much!

Trish