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