Automate Quarterly Assignment Group Cleanup Based on Inactivity, Ticket Usage, and CMDB Dependency

[nameisnani]

Requirement Description

We are looking to automate the cleanup of unused Assignment Groups in ServiceNow.
The goal is to reduce stale groups and inactive memberships without impacting active support or CMDB-linked services.

This cleanup should run on a quarterly basis and must follow strict eligibility and exclusion rules.

---

Business Problem

Over time, multiple Assignment Groups become inactive due to:

• No ticket usage

• Inactive or invalid users

• Orphaned groups with no ownership

Manual cleanup is error-prone and inconsistent.
We want a controlled, automated, and auditable approach.

---

Eligibility Conditions (ALL must be true)

An Assignment Group should be considered for cleanup only if all below conditions are met:

1. No Active Members

   • No active records in sys_user_grmember

   • OR group has zero members

2. No Ticket Activity in Last 12 Months

   • No records assigned to the group in:

     • Incident (incident)

     • Request Item (sc_req_item)

     • Catalog Task (sc_task)

     • Change Request (change_request)

   • Time window: last 12 months

3. Inactive / Invalid Users

   • Group members with:

     • Empty user_name

     • No last_login_time

     • OR inactive for more than 12 months

---

Cleanup Actions

For eligible groups:

• Remove inactive users from the group

• Identify groups with:

  • No active members

  • No ticket activity in last 12 months

• Do NOT delete groups automatically

(Deletion, if required, will be reviewed manually.)

---

Mandatory Exclusion Rules (Critical)

Before any cleanup action, the script must verify:

1. CMDB Dependency Check

   • If the Assignment Group is linked to:

     • Application Services

     • CI records in CMDB
       → Exclude from cleanup

2. Protected Groups

   • Groups identified as:

     • SUP

     • APR
       → Must always be retained, even if inactive

---

Execution Model

• Cleanup should run quarterly

• Preferred implementation:

  • Scheduled Script Execution

• Should support:

  • Reporting / dry-run output

  • Safe execution (no blind deletes)

---

Constraints

• ❌ No custom tables allowed

• ❌ No deletion of assignment groups without review

• ✔ Must be production-safe and auditable

• Best-practice approach for implementing this safely

• Script design or pseudo-logic recommendations

• CMDB relationship validation methods

• Suggestions to minimize performance impact

---

can any one please help me the configuartions - steps with example