Group Member Counts Reports
Posted by Mark Stanger in Reporting Wednesday, 21 July 2010 06:32 2 Comments
O
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…
//Query the sys_user_group table for all groups
var grp = new GlideRecord('sys_user_group');
grp.query();
while(grp.next()){
//Query for the number of group members
var grpm = new GlideAggregate('sys_user_grmember');
grpm.addQuery('group', grp.sys_id);
grpm.addAggregate('COUNT');
grpm.query();
var groupMembers = 0;
if(grpm.next()){
groupMembers = grpm.getAggregate('COUNT');
grp.u_group_members = groupMembers;
}
//Query for the number of active group members
var grpma = new GlideAggregate('sys_user_grmember');
grpma.addQuery('group', grp.sys_id);
grpma.addQuery('user.active', true);
grpma.addAggregate('COUNT');
grpma.query();
var activeGroupMembers = 0;
if(grpma.next()){
activeGroupMembers = grpma.getAggregate('COUNT');
grp.u_active_group_members = activeGroupMembers;
}
//Update the group record with the new counts
grp.update();
}
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.
Thanks for the share.
I got a similar requirements, but I solved it by creating a Business Rule on the “sys_user_grmember” table. When a new record is created, I increment the group variable. And do the opposite when the record is deleted
.
thanks
Sylvain
If you need the counts to be accurate up to the second, then that’s the way to do it for sure. The scripting is pretty similar, but will run once for each record updated or deleted. The thing to remember (which you already know) is that if you remove or add 50 people from a group the entire script runs 50 times for that single action! Usually that isn’t an issue, but depending on how up-to-date you need the information you might go the scheduled job route instead.
For the scenario given above, you would actually need two business rules (one on the ‘sys_user_grmember’ table for group member counts and one on the ‘sys_user’ table for active user counts).
If you’ve got a script to share you’re welcome to post it in a comment here! It might help someone else with the same issue.