Based on the use case you have provided, the below is the sample solution:
Find Approval Groups with Only One or No Members
The approval groups are just groups (sys_user_group), and their members are in the sys_user_grmember table.
Report: Groups with 0 or 1 Member
Use this GlideAggregate:
var ga = new GlideAggregate('sys_user_grmember');
ga.groupBy('group');
ga.addHaving('COUNT', 'user', '<=', '1');
ga.query();
while (ga.next()) {
var group = new GlideRecord('sys_user_group');
group.get(ga.getValue('group'));
gs.print('Group: ' + group.name + ' - Member Count: 1');
}
To include 0-member groups, you'll need to loop through all groups and compare against the sys_user_grmember table:
var allGroups = new GlideRecord('sys_user_group');
allGroups.query();
while (allGroups.next()) {
var memberCheck = new GlideRecord('sys_user_grmember');
memberCheck.addQuery('group', allGroups.sys_id);
memberCheck.query();
if (!memberCheck.hasNext()) {
gs.print('Group with NO members: ' + allGroups.name);
}
}
You can combine both into a single report if you want to generate a list of:
Groups with 0 or 1 members
Along with group name, sys_id, and member names/emails (if any)
Exporting/Reporting Options
Use Scheduled Jobs or Reports to run these regularly.
Add results to a custom table if you want history.
Send output to email or Slack/Teams notifications using integrations.
Task How
| Workflows with only user X as approver | Query sysapproval_approver, group by sysapproval, count = 1 |
| Approval groups with ≤1 member | Query sys_user_grmember, group by group, having count ≤1 |
| Approval groups with 0 members | Loop through sys_user_group and check if sys_user_grmember exists |
We can use this script logic into a Scheduled Script Execution (Scheduled Job or fix scrip or background scrip) in ServiceNow, if we are using in scheduled job it can run regularly and alert your team of:
Workflows where a single user (like the offboarded one) is the only approver
Approval groups that have no or only one member
Create the Scheduled Script
Go to System Definition > Scheduled Jobs
Click New, then choose "Scheduled Script Execution"
Name it something like: Detect Lone Approvers and Understaffed Groups
Set Run to daily/weekly/etc. as needed
Paste the script below into the Script field
(function() {
var userSysId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'; // Replace with the offboarded user's sys_id
var loneApprovals = [];
var groupWarnings = [];
// PART 1: Find workflows where offboarded user is the ONLY approver
var approvalAgg = new GlideAggregate('sysapproval_approver');
approvalAgg.addQuery('approver', userSysId);
approvalAgg.groupBy('sysapproval');
approvalAgg.addHaving('COUNT', 'approver', '1');
approvalAgg.query();
while (approvalAgg.next()) {
var approvalId = approvalAgg.getValue('sysapproval');
var approval = new GlideRecord('sysapproval_approver');
if (approval.get('sysapproval', approvalId)) {
loneApprovals.push('Only approver: ' + approval.approver.name + ' on request: ' + approval.sysapproval.display_value);
}
}
// PART 2: Approval groups with only 1 member
var groupAgg = new GlideAggregate('sys_user_grmember');
groupAgg.groupBy('group');
groupAgg.addHaving('COUNT', 'user', '<=', 1);
groupAgg.query();
while (groupAgg.next()) {
var groupId = groupAgg.getValue('group');
var group = new GlideRecord('sys_user_group');
if (group.get(groupId)) {
groupWarnings.push('Group "' + group.name + '" has only one member.');
}
}
// PART 3: Approval groups with NO members
var allGroups = new GlideRecord('sys_user_group');
allGroups.query();
while (allGroups.next()) {
var memberCheck = new GlideRecord('sys_user_grmember');
memberCheck.addQuery('group', allGroups.sys_id);
memberCheck.query();
if (!memberCheck.hasNext()) {
groupWarnings.push('Group "' + allGroups.name + '" has NO members.');
}
}
// COMPILE OUTPUT
var body = '';
body += ' Lone Approver Warnings \n\n';
body += loneApprovals.length ? loneApprovals.join('\n') : ' No lone approvals found.\n';
body += '\n\n Group Membership Warnings \n\n';
body += groupWarnings.length ? groupWarnings.join('\n') : ' All groups have sufficient members.\n';
// LOG TO SYSTEM LOG
gs.info(body);
// (Optional) Send email
var mail = new GlideEmailOutbound();
mail.setSubject(" Approver & Group Membership Audit");
mail.setBody(body);
mail.setFrom("no-reply@yourcompany.com");
mail.setTo("itops@yourcompany.com"); // replace with your team distro
mail.send();
})();
Output
