Orphaned CMDB relationships Product Success Playbook Orphaned CMDB relationships A guide for how to manage orphaned CI relationships Table of Contents Summary Audience Problem Overview Executive Summary How this playbook can help you achieve your business goals How this playbook is structured Problem Analysis Upstream Causes Downstream Consequences Impact on Your Business Engagement Questions Remediation Plays Summary Analysis Play Fix Play Data Governance Play Summary Goal of this Playbook The goal of this playbook is to help you identify and remove any orphaned configuration item (CI) relationship records in your system. Details about this playbook Author Emir EminovicDate 05/14/2020Addresses HSD # HSD0003783, HSD0008214Applicable ServiceNow Releases All ReleasesTime required Approximately 1 to 4 Hours Audience Configuration Manager or Configuration Management teamServiceNow Admin Problem Overview Orphaned CI relationships mean that one of the CIs in the relationship is no longer in the system, and that the relationship is invalid. These invalid relationships adversely affect how you use the system and how your system performs. Executive Summary How this playbook can help you achieve your business goals This playbook provides benefits to the following areas: Audit/Compliance: Improves system integrity by removing invalid recordsPerformance: Identifies relationship queries that are returning incomplete dataProcess Automation/User Experience: Improves the integrity of the relationships shown on the dependency maps How this playbook is structured This playbook guides you through three plays. The first play (an analysis play) shows you how many orphaned CI relationships you haveThe second play (a fix play) helps you remove the orphaned CI relationshipsThe third play (a governance play) documents how to use the Auto Flush form to clean up and delete the orphaned CI relationship records Cleaning up and deleting orphaned CI relationship records helps you maintain a healthy CMDB Problem Analysis Upstream Causes: ServiceNow Discovery is set to delete the CIs, but the underlying relationships aren't deleted. That is, the cascade delete option isn't enabledYou may have deleted a CI without reconciling its referencesYou may have merged CIs without reconciling any relationship referencesMigrating data across environments may have created sys_id conflicts on the CIs Downstream consequences Data Consequence: Empty or incorrect relationships can create confusion Operation Consequence: You may need to use special queries to filter the invalid CI relationshipsBroken relationships don't give you visibility into the underlying infrastructure App Consequence: To work properly, many product features rely on correct relationshipsDatabase efficiency is lost when tables are bloated with invalid data Impact on Your Business This playbook helps you to have more visibility into your application's infrastructure. Without the level of detail this playbook provides, you may make decisions that could adversely affect your business, which could lead to longer mean-time-to-response (MTTR) times. Engagement Questions Consider the answers to these questions: Do you see any broken CI relationships in your CMDB? For example, are any parent or child columns empty?Have you modified any Discovery patterns that may have caused the orphaned CI relationships? Patterns listed in the sa_ci_to_pattern table may also create or update CI Relationships (cmdb_rel_ci)Do you know of any custom scripts that access the cmdb_rel_ci table directly? Check for any Script Includes (sorted by date Updated). To find these scripts, you can use the Code Search feature in ServiceNow studio. Open the ServiceNow Studio application by navigating to System Applications --> StudioUse the keyboard shortcut to open the Code Search Windows: Control+Shift+F Mac: Command+Shift+F Provide the search term as cmdb_rel_ci and select the checkbox "Search in all applications"Click "Search" to initiate the search Note: Any CI relationships created through scripting should use DiscoveryFunctions. Remediation Plays Summary Play AnalysisWhat this play is aboutLets you see if you have any orphaned CI relationship recordsRequired tasksIf there are orphaned CI relationship records, use a script to determine how manyFixWhat this play is aboutExplains how to delete the orphaned CI relationships records, if anyRequired tasksRun a script that deletes the orphaned CI relationship records. Then verify that the orphaned CI relationship records are deletedData GovernanceWhat this play is aboutStresses the importance of cleaning up and deleting orphaned CI relationship records. You can clean up and delete these records by using the Auto Flush formRequired tasksUse the Auto Flush form Analysis Play What this Play is about This play helps you find any orphaned CI relationship records in the CMDB. This play includes a script you can use to find these records. Note: The script removes relationships in which a CI is present but can't be located in the CMDB. Once you use the script, there's no way to know which CI was deleted. Therefore, before you run the script, be sure to export the CMDB to an external data source. Required tasks Run this Query Script as a Fix Script Review the results of the script. The next steps depend on your results. If there aren't any orphaned CI relationship records, you don't need to do anything else. You've completed the Analysis Play.If there are orphaned CI relationship records, you need to delete them. To do that, complete the tasks in the Fix Play. If you don't have any orphaned CI relationship records, information similar to the following is displayed: Fix Play What this play is about In this play, you delete the orphaned CI relationship records you discovered in the Analysis Play. The Fix Play includes a script you can use to delete all of the orphaned CI relationship records. Required tasks Run this Fix Play script to delete the orphaned CI relationship records. (Run the script as a background task.) The first time you run this script, nothing is deleted. The script simply shows you the orphaned CI relationship records that will be deleted. The first time is considered a test or a "dry run." Review the results of the script. The results will either: Display the number of orphaned CI relationship records to be deleted.OrProvide instructions (shown on the last line of the results) for the next step you need to take (such as shown in the example below). Depending on the number of orphaned CI relationship records you want to delete, choose one of the following: If there are more than 10,000 records, delete them in batches. That is, run the script several times. For best performance, delete them in batches of 1,0000 (up to a maximum of 100,000) at one time.If there are fewer than 10,000 records, delete all of them. Note: Before you decide which option to use, note that deleting 25,000 records typically takes 10 minutes. For best performance, you may have to run the script several times. Go to line 30 of the script. Change the setting of the dryRun variable to false. Set the number of records you want to delete. Review line 32 (the deleteCount variable) and line 36 (the deleteAll variable) of the script. Do one of the following: If you want to delete more than 10,000 records, change the deleteCount variable to a maximum of 100000, and change the deleteAll variable to false.If you want to delete all the records, change the deleteCount variable to 0, and change the deleteAll variable to true. Run the Fix Play script again and take another look at the results. Run the script again, if needed. The following is an example of what appears after the script finishes deleting the records. Run the Validation Play script again to confirm that you don't any other orphaned CI relationship records that you need to fix. Data Governance What this play is about Cleaning up and deleting orphaned CI relationship records is essential for maintaining the health of the data in the CMDB. To clean up and delete orphaned CI relationships, use the Auto Flush form. Required tasks In the Filter Navigator pane, enter sys_auto_flush.list. The Auto Flush form appears. Click New. Fill in the form, as shown below Field Description Table nameEnter cmdb_rel_ciMatchfieldEnter sys_created_onAge in secondsEnter 604,800ActiveClick this check boxApplicationSelect Global (if not already selected.)Cascade DeleteDo not set this optionConditions Set either the Parent.Class or the Child.Class to None (the equivalent of empty). Click Submit Congratulations You have completed this Get Well Playbook.