Groups without Users Report

Mark_Didrikson · ‎11-20-2013

Hello,

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

Thanks!

jfreise · ‎11-20-2013

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.

View solution in original post

Aaron40 · ‎11-20-2013

I haven't seen a report before but I've used a background script to query the group table and the group membership table and removed all the matches.. ending up with a list of groups that have no members.

I've seen others do this in the past, too. But I haven't seen anyone create a report on it.

Mark_Didrikson · ‎11-20-2013

Thanks Aaron! Can you provide the SQL you used for your Background Script?

Pritu · ‎04-06-2022

can you please provide a background script for this

jfreise · ‎11-20-2013

You can create a new List report for Group (sys_user_group) and set the filter where "Group Members is 0". Alternatively, you can create a new list report on the Group (sys_user_group) table and group by Group Members. This will show the groups that have zero members in addition to empty group members, and groups with one user.\
Strike that 🙂 we created a custom field "Group Members" integer field to provide a count of its members (via a business rule). This makes it easy to report on group member counts.