Duplicate location clean up

[_bhishek]

Hi All,

In location table(cmn_location),There are duplicate locations based on same names and this is referenced in other tables like incident ,cmdb, change etc.

What should be the best approach to deduplication or clean up the locations .Would it be normalization or de-dup with script .Please advise if there is any other approach for this .

[GlideFather]

Hi @_bhishek,

navigate to the table and check the values in Created by and Updated by to understand why the duplicates exist in the first place. Because not knowing the culprit might result in future duplicates again :))

If you will decide what duplicates to remove, then make a backup > export it all to XML and/or mark the sys IDs of removed locations , both just in case... 

After you will remove the undesired Location records, then check incidents and other records, if you will see it doesn't show any value but it's NOT empty, then you can check the sys ID (either in the XML of that record, using SNUtils or perhaps there're some other methods that I don't remember at the moment).

Now when I think of that, it would be better to review the sys IDs to be removed and check in fix script whether there are any records with this location and eventually replace it with the proper one and then remove it...

Let me know if it makes sense to you or if you want to discuss it in more details

EDIT: *changed to make more sense

[Bert_c1]

Here's a script that can be run in Scripts - Background to do what you want, after determining which cmn_location records are duplicate (to be deleted) and which records have the desired 'location'.

var dupLocations = [
	'5f669b59c0a8010e00209343c0c6f9c1',
	'sys_id_2',
	'sys_id_3'
];
var newLocations = [
	'sys_id_4',
	'sys_id_5',
	'sys_id_6'
];
var tsk = new GlideRecord('task');
for (i=0; i <dupLocations.length; i++) {
	tsk.addQuery('location', dupLocations[i]);
	tsk.query();
	gs.info("Found " + tsk.getRowCount() + " records where location = " + dupLocations[i]);
	while (tsk.next()) {
		tsk.location = newLocations[i];
		tsk.setWorkflow(false);
//		tsk.update();
	}
}

But then what about the other tables with Reference fields to the 'cmn_location' table? They can be identified here:

https://[instance].service-now.com/sys_dictionary_list.do?sysparm_query=internal_type%3Dreference%5Ereference%3Dcmn_location&sysparm_view=advanced 

You could enhance the script by performing that query and using the logic above within a loop that runs through the query results.