Groups without Users Report

[Mark_Didrikson]

Hello,

Is it possible to create a report of Groups that do not have Users assigned to them?

Thanks!

[Aaron40]

Having a business rule running on your group table isn't a bad idea to track membership counts. That'd make it possible to run reports instead of constantly rerunning scripts to calculate the groups.

I like that approach.

[jfreise · Accepted solution]

To do it this way, create a new field on the group form named Group Members, type=integer. Go to the dictionary for this field and make it read-only
Create a business rule named "Count Group Members" on sys_user_group table, when: before, update only and use this code to do the counting:

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

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

Then you can do reporting on group membership counts.

[Mark_Didrikson]

Thank you! This is great! Very helpful!

[Aaron40]

If you don't have a modified table and business rule, this will get you a list of all groups and a list of all group memberships.

All that needs to be done is comparing the two lists.

var allGroups = [];
var memGroups = [];

var mem = new GlideRecord('sys_user_grmember');
mem.query();
while (mem.next())
{
    var name = mem.group.name + "";
    if (!memGroups.indexOf(name))
    {
       memGroups.push(name);
    }
}


var grp = new GlideRecord('sys_user_group');
grp.query();

while (grp.next())
{
      allGroups.push(grp.name + "");
}


gs.log(allGroups);
gs.log(memGroups);

[sowmyab]

Hi,

I tried using Business rule but Group Members value is not populating automatically.

Need to update the every group manually then only value is updating.

Can you please help me out how can i get values automatically on Group member field???

Best Regards,

Sowmya