Find the group with No members in it

[pandeyved]

Hi All I am writing the code to find the group with zero members in it, and I have achieved this also by using Glide record in While Loop, but I think it's not recommended way of doing it.

Here's my code,

var groupGr = new GlideRecord('sys_user_group');
groupGr.addActiveQuery(); 

groupGr.query();
while (groupGr.next()) {
var groupId = groupGr.sys_id.toString();
var groupName = groupGr.name.toString();

var memberGr = new GlideRecord('sys_user_grmember');
memberGr.addQuery('group', groupId); 
memberGr.query();
if (!memberGr.hasNext()) {
gs.info("Group with active manager and no members: " + groupName);
}
}

Please suggest some best way of achieving it 
Thanks

[Sohail Khilji]

Hi @pandeyved ,

Why dont you use GlideAggregate  in your script instead of GlideRecord ? 

var ga = new GlideAggregate('sys_user_grmember');
ga.addAggregate('COUNT');
ga.groupBy('group');
ga.addHaving('COUNT', '=', '0');

[Harish Bainsla]

You can use glide aggregate 

var groupGr = new GlideAggregate('sys_user_group');
groupGr.addActiveQuery();
groupGr.addAggregate('COUNT', 'sys_user_grmember', 'group');
groupGr.groupBy('sys_id', 'name');
groupGr.query();
while (groupGr.next()) {
var groupId = groupGr.getValue('sys_id');
var groupName = groupGr.getValue('name');
var memberCount = parseInt(groupGr.getAggregate('COUNT', 'sys_user_grmember', 'group'));

if (memberCount == 0) {
gs.info("Group with active manager and no members: " + groupName);
}
}

if you get answer please mark helpful and accept solution

[JamesEcoStratus]

If I understand your question, you could also try this approach.

You can write a script in ServiceNow to find groups with no members by querying the sys_user_grmember table and checking which groups have no associated members. Here's a sample script that accomplishes this:

This script does the following:

Initializes an array called emptyGroups to store the names of groups with no members.

Queries the sys_user_grmember table to find active groups with no associated members. It groups the results by the group field and uses COUNT to filter for groups with a member count of 0.

Iterates through the result set and adds the names of empty groups to the emptyGroups array.

Finally, it prints the list of empty groups or a message indicating that no empty groups were found.

You can adapt this script to your specific needs, such as saving the list of empty groups to a record, sending notifications, or taking other actions as required.

Good Luck,

James