I am working on a fix script in our development environment to automatically find, merge, and delete duplicate records on our cmdb_ci_computer table.
When I run the code, it deletes the duplicate record, and merges into a target record as design, but it also creates a new record on the table that is almost entirely blank.
Before run:
After Run:
The code:
// Get all CIs in the cmdb_ci_computer table where:
// created by is [REDACTED]
// class is cmdb_ci_computer (This limits the query to none of the extended tables)
var fromComputer = new GlideRecord("cmdb_ci_computer");
fromComputer.addQuery("sys_created_by", "[REDACTED]");
fromComputer.addQuery("sys_class_name", "cmdb_ci_computer");
fromComputer.addQuery("serial_number", "[REDACTED]"); // This is just to test. Remove it for the production run.
fromComputer.query();
gs.info("[Fix Duplicate Computers from Discovery] " + fromComputer.getRowCount() + " computers from REDACTED to count.");
var toDelete = [];
// For each
while(fromComputer.next()) {
// Get all CIs in the cmdb_ci_computer table where:
// created by is [REDACTED]
// class is cmdb_ci_computer
// sys_id is not the sys_id of the computer from REDACTED
// serial number contains the serial number from the REDACTED computer.
var toComputer = new GlideRecord("cmdb_ci_computer");
toComputer.addQuery("sys_created_by", "REDACTED");
toComputer.addQuery("sys_class_name", "cmdb_ci_computer");
toComputer.addQuery("sys_id", "!=", fromComputer.getValue("sys_id"));
toComputer.addQuery("serial_number", "CONTAINS", fromComputer.getValue("serial_number"));
toComputer.query();
// If no duplicates, Skip.
if (toComputer.getRowCount() == 0) {continue;}
gs.info("[Fix Duplicate Computers from Discovery] REDACTED computer " + fromComputer.getValue("serial_number") + " has " + toComputer.getRowCount() + " duplicates created by discovery.");
if (toComputer.getRowCount() > 1) {
gs.info("[Fix Duplicate Computers from Discovery] More than one duplicate for " + fromComputer.getValue("serial_number") + "!!!");
}
// Update the record discovered by REDACTED with the following fields from the one discovered by REDACTED.
// Serial number
// Last used user
// Multi array of last used users
// Assigned to
toComputer.setValue("serial_number", fromComputer.getValue("serial_number"));
toComputer.setValue("u_last_used_user", fromComputer.getValue("u_last_used_user"));
toComputer.setValue("u_multi_value_array_of_last_used_users", fromComputer.getValue("u_multi_value_array_of_last_used_users"));
toComputer.setValue("assigned_to", fromComputer.getValue("assigned_to"));
toComputer.update();
// Get all the tasks this CI is related to and re-point that relationship to the ci discovered by ServiceNow.
var task = new GlideRecord("task");
task.addQuery("cmdb_ci", fromComputer.getValue("sys_id"));
task.query();
gs.info("[Fix Duplicate Computers from Discovery] There are " + task.getRowCount() + " Tasks related to the REDACTED computer.");
while(task.next()) {
task.setValue("cmdb_ci", toComputer.getValue("sys_id"));
task.update();
}
// Do the same for task_ci records.
var taskCI = new GlideRecord("task_ci");
taskCI.addQuery("ci_item", fromComputer.getValue("sys_id"));
taskCI.query();
gs.info("[Fix Duplicate Computers from Discovery] There are " + taskCI.getRowCount() + " TaskCIs related to the REDACTED computer.");
while(taskCI.next()) {
taskCI.setValue("ci_item", toComputer.getValue("sys_id"));
taskCI.update();
}
// Add to delete array.
toDelete.push(fromComputer.getValue("sys_id"));
}
var deleteCIs = new GlideRecord("cmdb_ci");
deleteCIs.addQuery("sys_id", "IN", toDelete.join(","));
deleteCIs.query();
gs.info("[Fix Duplicate Computers from Discovery] Deleting" + deleteCIs.getRowCount() + " duplicate CIs.");
deleteCIs.deleteMultiple();
gs.info("[Fix Duplicate Computers from Discovery] Job complete.");
