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

arielgritti · ‎04-09-2021

Hi @jfreise

Thanks for your post. I tried it but didn't work for me.

Taking advantage of your idea, I created a after insert/update/delete Business Rule on sys_user_grmember table to count members and update the group. I added the field u_group_members to sys_user_group table and update the include_members field too.

Take a look:

(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);

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