CMDB Relationship Cleanup

[_bhishek]

Hi All,

In CMDB relationship table (cmdb_rel_ci),I have total of 4 Million relationships for which parent is non-operational /retired or child is non-operation/retired .SO we want to clean this data with removing these stale/orphan relationships. Also in for future want to make a automatic process if any parent or child is updated as non-operation /retired ,then respective relationship should be removed .What should be the best practice to deal with this data and Do we have functionality from service now to deal with this clean up process.Please suggest .

[MaxMixali]

Answer — Best Practices for Cleaning and Automating Removal of Stale CMDB Relationships (cmdb_rel_ci)

You are addressing a classic CMDB data hygiene problem: millions of relationships exist where either the parent or child CI is retired or non-operational. The goal is twofold:

1. Clean existing stale relationships (one-time cleanup)

2. Prevent future buildup via automation when a CI changes status.

Here’s how to approach this following ServiceNow CMDB best practices and performance-safe design.

---

🔹

1. One-Time Cleanup of Existing Stale Relationships

Option A – Use CMDB Data Manager (Preferred OOB Approach)

ServiceNow provides CMDB Data Manager (part of the CMDB Workspace) which allows you to:

• Define identification rules for what “stale” or “invalid” means.

• Automate bulk cleanup of relationships that violate those rules.

• Schedule recurring cleanups.

Steps:

1. Navigate to CMDB Data Manager > Policies.

2. Create a Data Manager Policy on the table cmdb_rel_ci.

3. Define filters such as:

Parent Operational Status is Retired OR Child Operational Status is Retired
OR Parent Operational Status is Non-Operational OR Child Operational Status is Non-Operational

1. Action → Delete.

2. Schedule it (e.g., weekly during off-peak hours).

✅ Benefits:

• No scripting required.

• Handles millions of records using batch processing safely.

• Fully supported by ServiceNow.

---

Option B – Scripted Cleanup (if Data Manager not available)

If you don’t have CMDB Data Manager (older release or restricted instance):

1. Create a background script or scheduled job:

// Delete relationships where parent or child is retired or non-operational
var gr = new GlideRecord('cmdb_rel_ci');
gr.addEncodedQuery('parent.operational_statusIN7,8^ORchild.operational_statusIN7,8'); // 7=Non-operational, 8=Retired
gr.query();
var count = 0;
while (gr.next()) {
gr.deleteRecord();
count++;
if (count % 10000 === 0) gs.info('Deleted ' + count + ' stale relationships so far...');
}
gs.info('Completed deleting stale CMDB relationships.');

2. Run it in batches:

• Never delete all 4M at once; limit per run to 50K–100K records for stability.

• Use setWorkflow(false) and autoSysFields(false) for performance.

---

🔹

2. Ongoing Preventive Automation

Option A – Business Rule on CI Class

Create a Business Rule (before or after update) on cmdb_ci table (and extend it to all subclasses using “Applies to extended tables”).

Condition:

When operational_status changes to Retired or Non-Operational → remove relationships.

Script Example:

(function executeRule(current, previous /*null when async*/) {
if (previous.operational_status == current.operational_status)
return; // no change

var nonActiveStatuses = ['7','8']; // 7=Non-Operational, 8=Retired
if (nonActiveStatuses.indexOf(current.operational_status.toString()) == -1)
return;

// Delete parent relationships
var parentRel = new GlideRecord('cmdb_rel_ci');
parentRel.addQuery('parent', current.sys_id);
parentRel.deleteMultiple();

// Delete child relationships
var childRel = new GlideRecord('cmdb_rel_ci');
childRel.addQuery('child', current.sys_id);
childRel.deleteMultiple();

gs.info('Removed relationships for retired/non-operational CI: ' + current.name);
})(current, previous);

Best Practice: Make it asynchronous (Background Script or Scheduled Job) to avoid performance impact during CI updates.

---

Option B – Use CMDB Health Dashboard & Indicators

Enable and monitor the CMDB Relationship Completeness and Orphan Relationships indicators in the CMDB Health Dashboard:

• This provides visibility into invalid or stale relationships.

• You can schedule automatic remediation tasks using Data Manager Policies tied to these indicators.

[_bhishek]

@MaxMixali Thanks for your response .I want to check one thing,Can we use CMDB data manager for cmdb_rel_ci table As i am getting only cmdb and child table of cmdb while policy creation.I am not able to create policy for cmdb_rel_ci table .Please confirm.

[_bhishek]

Hello @MaxMixali ,Is there any further suggestions for me .Any input would be helpful here.