Foundation data - Support Group clean up

[vinnybehring]

Hi,

Does anyone know how to verify whether a support group has had no activity on the platform during the last six months (or any other specified period) and could therefore be a candidate for deactivation?

Thanks in advance.

[Vikram Reddy]

Hi @vinnybehring,

There's no out of the box "last activity" field sitting on Group [sys_user_group], so you have to derive it from the task table, since every ticket type (incident, problem, change_request, sc_task, and so on) extends task and carries the assignment_group field. A few ways to get there depending on whether you want a one-off list or something ongoing:

1. Report approach: build a report on Group [sys_user_group], add a related list condition on Task->Assignment group, and set it to none (or count = 0) for records with sys_updated_on or opened_at on or after 6 months ago. That surfaces every group with zero task activity in the window without touching a script.
2. Script approach: run a GlideAggregate against task, grouped by assignment_group, filtered to records updated in your window. Whatever active group in sys_user_group doesn't show up in that aggregate is a candidate. Something like:

var gr = new GlideAggregate('task');
gr.addAggregate('COUNT');
gr.groupBy('assignment_group');
gr.addQuery('sys_updated_on', '>=', gs.daysAgoStart(180));
gr.query();
while (gr.next()) {
    gs.print(gr.assignment_group.getDisplayValue() + ': ' + gr.getAggregate('COUNT'));
}

1. Check group membership too: a group with no active members in sys_user_grmember is dead weight regardless of ticket history, and it's a cheap check to run alongside the activity query.
2. Don't forget approvals: Group approval [sysapproval_group] is a separate table from task-level assignment, so a group can look quiet on assignment_group activity and still be actively used as an approver group. Worth a quick separate check before you deactivate anything.

If this is going to be a recurring housekeeping exercise rather than a one-time cleanup, and you're licensed for Performance Analytics, it's worth building this as an indicator on a dashboard instead of re-running the script every quarter.

Thank you,
Vikram Karety
Octigo Solutions INC