Percent of unique locations <!-- .SOKMKBArticle div.margin { padding: 10px 40px 40px 30px; color: #283d40; font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; font-size: 10pt; } .SOKMKBArticle div.fed{ background-color: #f5f8fa; border: 1px solid; border-color: #bfbfbf; padding: 10px; } .SOKMKBArticle .FedRestricted{ background-color: #c00000; color: #ffffff; padding: 10px; margin-top: 10px; text-align: center; font-size: 14pt; font-weight: bold; } .SOKMKBArticle .CustRestricted{ background-color: #ff0000; color: #ffffff; padding: 10px; margin-top: 10px; text-align: center; font-size: 14pt; font-weight: bold; } .SOKMKBArticle .SNRestricted{ background-color: #ea700d; color: #ffffff; padding: 10px; margin-top: 10px; text-align: center; font-size: 14pt; font-weight: bold; } .SOKMKBArticle .SNConfidential{ background-color: #ffc000; color: #ffffff; padding: 10px; margin-top: 10px; text-align: center; font-size: 14pt; font-weight: bold; } .SOKMKBArticle .Public{ background-color: #00b050; color: #ffffff; padding: 10px; margin-top: 10px; text-align: center; font-size: 14pt; font-weight: bold; } .SOKMKBArticle table.tocTable { border: 1px solid; border-color: #f2f2f2; background-color: #f2f2f2; padding-top: .6em; padding-bottom: .6em; padding-left: .9em; padding-right: .6em; } .SOKMKBArticle table.noteTable { align: left; border: none; border-color: #81b5a1; background-color: #f2f2f2; width: 100%; border-spacing: 2; font-size: 11px; } .SOKMKBArticle table.internalTable { border-top: 1px solid; border-left: 1px solid; border-color: #81b5a1; width: 100%; border-spacing: 1px; } .SOKMKBArticle .sp td { border-bottom: 1px solid; border-right: 1px solid; border-color: #81b5a1; background-color: #ffffff; height: 20px; padding-top: .5em; padding-bottom: .5em; padding-left: .5em; padding-right: .5em; } .SOKMKBArticle .sphr td { border-right: 1px solid; border-bottom: 1px solid; border-color: #81b5a1; background-color: rgb(245, 245, 245); padding-top: .5em; padding-bottom: .5em; padding-left: .5em; padding-right: .5em; height: 20px; } .SOKMKBArticle .sh td { border-bottom: 1px solid; border-right: 1px solid; border-color: #81b5a1; background-color: #81b5a1; color: #ffffff; height: 20px; padding-top: .5em; padding-bottom: .5em; padding-left: .5em; padding-right: .5em; } .SOKMKBArticle th { padding-top: .5em; padding-bottom: .5em; padding-left: .5em; padding-right: .5em; border-bottom: 1px solid; border-right: 1px solid; border-color: #646464; background: #646464; font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; font-size: 10pt; color: white !important; height: 20px; } .SOKMKBArticle td { border-color: #646464; margin: 5px 5px 5px 5px; padding: 5px 5px 5px 5px; font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; font-size: 10pt; color: #283d40; } .SOKMKBArticle p { color: #283d40; font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; } .SOKMKBArticle li { color: #283d40; font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; font-size: 10pt; line-height: 1.5; } .SOKMKBArticle pre { font-family: Courier New; } .SOKMKBArticle div { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; } .SOKMKBArticle hr { border-top-width: 1px; border-top-style: solid; border-top-color: #81b5a1; } .SOKMKBArticle a { color: #81b5a1; } .SOKMKBArticle a.two:link { padding: 15px 45px 15px 45px; margin-top: 20px; color: #ffffff; text-align: center; background-color: #1F8476; border: 1px solid; border-color: #1F8476; } .SOKMKBArticle a.two:visited { padding: 15px 45px 15px 45px; margin-top: 20px; color: #ffffff; text-align: center; background-color: #1F8476; border: 1px solid; border-color: #1F8476; } .SOKMKBArticle a.two:hover { color: #ffffff; background-color: #259b8a; } .SOKMKBArticle .button { padding: 15px 45px 15px 45px; margin-top: 20px; color: #ffffff; text-align: center; background-color: #1F8476; border: 1px solid; border-color: #1F8476; } .SOKMKBArticle .title { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #81b5a1; font-size: 30pt; } .SOKMKBArticle .hd1 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-size: 20pt; border-bottom: 1px solid; border-bottom-color: #81b5a1; text-decoration: none; } .SOKMKBArticle h1 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-size: 20pt; font-weight: normal; border-bottom: 1px solid; border-bottom-color: #81b5a1; text-decoration: none; } .SOKMKBArticle .hd2 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #68a1af; font-weight: bold; font-size: 16pt; text-decoration: none; } .SOKMKBArticle h2 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #68a1af; font-weight: bold; font-size: 16pt; font-weight: normal; text-decoration: none; } .SOKMKBArticle .hd3 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 14pt; text-decoration: none; } .SOKMKBArticle h3 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 14pt; text-decoration: none; } .SOKMKBArticle .hd4 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 12pt; text-decoration: none; } .SOKMKBArticle h4 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 12pt; text-decoration: none; } .SOKMKBArticle .hd5 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: bold; font-size: 10pt; text-decoration: bold; } .SOKMKBArticle h5 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: bold; font-size: 10pt; text-decoration: bold; } .SOKMKBArticle .hd6 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 10pt; text-decoration: underline; } .SOKMKBArticle h6 { font-family: Century Gothic, Verdana, Helvetica, Arial, sans-serif; color: #283d40; font-weight: normal; font-size: 10pt; text-decoration: underline; } .SOKMKBArticle details { font-size: 10pt; } .SOKMKBArticle details[open] summary ~ * { animation: sweep .5s; margin-top: 0; padding-top: 10px; } @keyframes sweep { 0% {opacity: 0; margin-top: -10px} 100% {opacity: 1; margin-top: 0px} } .SOKMKBArticle summary { cursor: pointer; outline: none; margin-bottom: 3px; } .SOKMKBArticle .summary { background-color: #81b5a1; font-size: 10px; color: white; cursor: pointer; padding: 5px; width: 100%; border: none; text-align: left; outline: none; vertical-align: top; } --> Get Well Playbook Manage Duplicate Locations A guide for how to remediate duplicate Locations in your CMDB Table of Contents Summary Goal of this Playbook Audience Problem Overview Executive Summary How this playbook can help you achieve business goals How this playbook is structured Problem Analysis Upstream Causes Downstream Consequences Impact on Your Business Engagement Questions Remediation Plays Summary Play 1: Analysis Play Play 2: Usage Play Play 3: Fix Play - Update Existing References Play 4: Fix Play - Remove Duplicates Data Governance General Usage Guidelines Summary Goal of this Playbook The goal of this playbook is to help you identify and remediate duplicate Locations in the Configuration Management Database (CMDB). In case you are looking for fixing your server CI records not having a location, check out the playbook "Percent of Active CIs in Server class without a location" Details about this playbook Author Emir EminovicDate 08/19/2020Addresses HSD # HSD0003656Applicable ServiceNow Releases All ReleasesTime required Approximately 1 to 8 hours Audience Configuration Manager or Configuration Management teamServiceNow Admin or Discovery Admin Note: As you follow the instructions in this playbook, you may need help from people in the following roles: Owner of the Application which feeds the Location table Problem Overview Locations are used by various applications to locate users, facilities, or configuration items (CI). Having locations with the same name (Duplicates) will result in a poor User Experience (UX). Executive Summary How this playbook can help you achieve business goals Incident Reduction Data Accuracy Audit/Compliance System Integrity Lower MTTR Data Validation Better User Experience Platform Usage How this playbook is structured This playbook guides you through five plays. The first play (an analysis play) lets you see the number of Locations that have the same name. Additionally, you can review any customizations made to the table. The second play (an usage play) allows you to identify usage of duplicate locations in your systemThe third play (a fix play) allows you to reassign any CIs assigned to a Duplicate locationThe fourth play (a fix play) lets you remove the duplicate location from the systemThe fifth play (a governance play) explains the processes you can use to prevent Duplicate Locations Problem Analysis Upstream Causes The following can create Duplicate Locations: A naming standard for Locations has not been defined and/or publishedSource of Data is not maintained and has Duplicates itselfBroken import process results in Duplicates being createdCustomization has been applied to the table, but has not been applied to forms that utilize the data Downstream Consequences Data Consequence CIs not always assigned to the correct location of the Duplicate pair, resulting in more complex queries to report the data Operation Consequence CIs not always assigned to the correct location of the Duplicate pair, resulting in more complex queries to report the data App Consequence Baseline Filters and Form show multiple of the same LocationAutomation cannot differentiate between Duplicates and might assign a CI to the incorrect one Impact on Your Business Inaccuracies in locating Users, Facilities and CIs. Engagement Questions: Consider the answers to these questions: What is the Source of your Location Data?Is the Source maintained? If so, how often?Do Duplicate Locations exist in the Source?Have you modified baseline Locations table? Have you modified forms and filters to reflect that? Remediation Plays Summary The table below lists and summarizes each of the remediation plays in the playbook. Details are included later. Play Analysis Play What this play is about Lets you see if you have Duplicate Locations in the CMDB Required tasks Run a Script and review the output Usage Play What this play is about Lets you see where Duplicate Locations are used Required tasks Run a script and review output Fix Play: Update Existing References What this play is about Update records to point to the location that is correct Required tasks Modify and run a script Fix Play: Remove Duplicates What this play is about Delete duplicate locations Required tasks Delete a record Data Governance What this play is about Prevent Duplicate Locations from being created Required tasks Guidance on how to prevent reoccurrence Play 1 - Analysis Play What this Play is about This play helps you find any duplicate Location in your system. Duplicate Locations are defined as more than one location with the same name. Required tasks Option 1: Install CMDB and CSDM Data Foundations Dashboard from the ServiceNow App StoreNavigate to the CMDB Data Foundations Dashboard module in the left navigation menuSelect the Data Management Practices tabSelect the Duplicate Locations report to view a list of duplicate locations. If you see a count of zero then this playbook is not applicable to you. Option 2: Run the Analysis Play script as a Fix Script, to check for locations that have the same name. Review the output in Section 1. If you see a count of zero, then you do not have any duplicates based on the name, and this playbook is not applicable.Note: The script checks for names to be exactly the same. If you have locations that might have inconsistencies in the name, which have the same address or some other attribute, consider modifying the script to detect duplicates based on that field. For example, a record named Phoenix Convention Center and a record named PCC would not be detected by this script unless queried by the address attribute. If you see a count greater than zero, then proceed to the next step In Section 2, a detection is performed to see if you have applied a custom active flag to the cmn_location table. In the base installation, the table does not contain this attribute. Review the message in this section. If you do have a custom u_active attribute, you require some additional steps before you can proceed with this playbook.Note: Various forms and filters in the product will display the "inactive" locations by default, unless you have modified those to reflect the custom attribute you have created. For example, a user can be assigned to an inactive location named Phoenix Convention Center by a feed from your Directory Service based on a location name match. If you do not have a custom attribute, proceed to the next step. In Section 3, a list of duplicate locations is displayed. The list is sorted by the most common occurring duplicates. Result of step 2a above, might be the contributor to the high number of records. Review the data and see if you can identify a pattern, which could be used to remediate the finding. If the data is coming from an external source, you may need to revisit the integration.Note: there might be duplicate names that are false positives. This can occur when a location name is a common suite, branch or some other value, but is in different geographic location. This is not a good naming standard to have, and you should consider updating your locations to be better uniquely identified. Save the output of section 3 as you will need it in the subsequent plays. You will need to complete Usage and Fix plays for every duplicate location in the findings, one at a time. Play 2 - Usage Play What this Play is about This play guides you through identifying usage of the duplicate location in the system. Before you can decide which record to keep it is imperative to understand the usage of those that you do not wish to keep. If there are related records that are referencing the duplicates, then the references have to be updated before you can remove them from your system. Required tasks Review the Analysis Play script output, Section 3 once again Start with the location that has the most duplicate instance; it should be the first record on the list. Copy the location name from the results. Ensure no trailing spaces or tabs are copied.Download and install the Usage Script as a Fix ScriptModify the locationName variable with the location name recorded in step two aboveRun the ScriptReview the output The location name is displayed once again for confirmation The SYS ID of each cmn_location record that matches the same name is listedExampleFollowing are the tables in which there are references to the locations identified aboveExample (in the alm asset tables there are seven references to locations with the name you provided. At this time, you should understand the impact and the complexity of the cleanup effort. Proceed to the Fix play if you intend to automatically fix the duplicates Note: if the duplicate locations are created by an external source, then they will be recreated during the next import/refresh. Do not perform the fix play until the problem is fixed in the source or you have applied measure to prevent reoccurrence (see Data Governance section of this playbook). Additionally, review the locations from the UI screens and ensure they are indeed duplicates. Play 3: Fix Play - Update Existing References What is this Play about? This play guides you through automatically updating all references that point to duplicate location. Before you can decide which record to keep it is imperative to understand the usage of those that you do not wish to keep, as it was reported in the previous step. Note: We recommended you perform this step in a recent clone of your production environment. There might be many cascading updates that get triggered by this script. Required tasks Download and install the Duplicate Location Fix Script as a Fix ScriptBased on the research you have completed in the previous step, provide the sys_id of location to be retained in the locationName variable.Run the Fix ScriptThe script has not updated any data yet, by default it is just confirming what will be updated.If you are update the data, then modify the dryRun variable to false and save the script. This variable was a safety mechanism to prevent unattended modifications. Run the script. var dryRun = false; Review the outputNote: If any record is used by a ServiceMap then the map has to be recomputedTo validate the success, you can rerun the Usage Play again. At this time the duplicates still exist in the system, but should not be utilized anywhere. You may proceed to the next Fix Script to delete the duplicate locations. Play 4: Fix Play - Remove Duplicates What is this Play about? This play guides you through deleting Locations no longer needed.Note: If your locations are fed from another system, then this step will be redundant and the same locations will most likely be recreated during the next import. Consider fixing the source or prevent insertion of duplicate records. Required tasks Navigate to the cmn_location tableIdentify the records that need to be deletedOpen the record and verify the sys id matches step 3 from the first Fix PlayDelete the recordTo validate, rerun the Fix Play: Update existing references, however do it without any updatesProceed to the next record to be reviewedNote: There is no script provided for this because you really need to be sure you have completed all the previous steps before deleting the record. Data Governance What is this Play about? To maintain the health of the CMDB, you need to regularly monitor for duplicate locations Required tasks Periodically review if you still have duplicate locations General Usage Guidelines Locations should be UniqueEasily IdentifiableMake sense to the end Users consuming themHierarchicalCome from a single sourceStandardizedNot Empty Congratulations You have completed this Get Well Playbook.