ver the last couple of weeks I’ve seen the same type of question a few times. The questions center on group membership reporting and usually look something like the following…
“How can I generate a report showing which groups in my system have no users?”
“How can I generate a report showing which groups have no active users?”
“Is it possible to report on the number of active users in a group?”
The biggest challenge in answering these questions is not around reporting on active users, but in reporting on groups that have no users. How do you bring up a report of records that don’t exist? When designing your Service-now implementation, its always important to understand the end reporting goals that correspond to each particular process. Reporting on groups with no members is a good example of a scenario where you’ll need to perform some sort of calculation and then capture that calculation somewhere where the data can be reported on.
The questions above can be answered by doing the following…
1- Create 2 new integer fields on the Group (‘sys_user_group’) table — one called ‘Group members’ and one called ‘Active group members’. These fields will store the count of each of these group metrics and will be populated by a scheduled script job at a specified interval.
2- Create a new Scheduled Job entry (‘System Definition’ -> Scheduled Jobs) to automatically run a script of your choosing and use the following script…
var grp = new GlideRecord('sys_user_group');
//Query for the number of group members
var grpm = new GlideAggregate('sys_user_grmember');
var groupMembers = 0;
groupMembers = grpm.getAggregate('COUNT');
grp.u_group_members = groupMembers;
//Query for the number of active group members
var grpma = new GlideAggregate('sys_user_grmember');
var activeGroupMembers = 0;
activeGroupMembers = grpma.getAggregate('COUNT');
grp.u_active_group_members = activeGroupMembers;
//Update the group record with the new counts
The script will run at the interval you specify and populate counts on each group record for the number of group members and also the number of active group members. Once you have that information, its very simple to create reports based on the group table that display the needed information.