Finding where cmn_location records are used, and retiring old/unused/incorrect records

[RichardH6439164]

Hi there,

I'm in the process of sorting out a severely under-maintained CMDB. We're starting CSDM adoption at the foundation layer and Locations (cmn_location) is my first target.

I have a taxonomy and high-level list of locations sorted on paper, following common sense standards of Region>Country>City>Site>Building>Room. The challenge I am now facing is that we have 457 locations - almost all of which are just plain wrong. We've got supplier names in there, misspelt site names, some individual desks, you name it. As I progress through my CMDB fix, I expect this will be a regular theme and I expect we're going to be archiving nearly 95% of our CMDB then repopulating with ITOM Discovery.

I'm assuming these records have been added at one time or another, and other elements on the platform may rely on them. My next question: is there a way to identify if a specific record is being used on the platform somewhere? The risk I need to mitigate is me archiving a location, then finding that some key part of reporting or flow relies on that record. If this solution can be applied to other records, such as those in the CMDB, that would be of great help.

[Shivalika]

Hello @RichardH6439164 

This is exact same issue that we at our end are facing now and tbh there are several key flows and approvals where location is being referenced. So this is the approach I used, however I am still not completely confident to go ahead and archive it - risking the break of well running flow - the having a whole lot of additional task to fix it. 

var locationSysId = 'YOUR_LOCATION_SYS_ID_HERE';

var gr = new GlideRecord('sys_metadata');

gr.addQuery('name', 'cmn_location');

gr.query();

while (gr.next()) {

    var tableName = gr.name.toString();

    var refQuery = new GlideRecord(tableName);

    refQuery.addQuery('location', locationSysId);

    refQuery.query();

    if (refQuery.hasNext()) {

        gs.info("Location is referenced in table: " + tableName

);

    }

}

You may refer above code, hope that helps. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway,

Regards, 

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwN

eEISQCY

[Ashok Sasidhara]

Check whether the script attached in the following URL is helpful:

https://www.servicenow.com/community/common-service-data-model/migrating-into-csdm-identifying-table...