Welcome Login
Blog Photos Links

RSS Feed

Merging duplicate records in ServiceNow

August 9, 2019, 4:42 pm - James Farrer

To merge duplicate records, you have to make sure you know which record to keep and combine data in that record. The first usually takes a manual comparison and update of the fields. The other major piece is resolving references to the record that will be deleted. That's where this script comes in.

If you’ve got a unique identifier of some kind you can use a script like this to find the duplicates. Otherwise, it’s likely going to be a manual process of identifying them.

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('count', 'name');
count.orderByAggregate('count', 'name');
count.addHaving("count", ">", 1);
count.query();  
while (count.next()) {
   var name = count.name;
   var record_count = count.getAggregate('count', 'name');
   gs.info("Duplicates: " + name + ", Count: " + record_count);
}

Once you know which record to keep and which to delete, the following code will be helpful. It goes through the relevant tables where we can find any references to it and updates the records to point to the record that will be kept. It’s got a built-in flag so you can do a test run. The script takes the table the records are on, the Sys ID of the old record, the Sys ID of the record to move references over to, and a flag to indicate whether to save the changes (true = save changes, false or omitted = test run).

function mergeRecords(table, move_from_id, move_to_id, save_changes){
  if(move_from_id == move_to_id){
    gs.info("The ID's are the same: " + move_from_id);
    return;
  }
	
  var refTable = new TableUtils(table).getTables();
  gs.include('j2js');
  refTable = j2js(refTable).join();

  //Query dictionary table for reference, document_id, and condition fields
  var dict = new GlideRecord('sys_dictionary');
  dict.addEncodedQuery('referenceIN' + refTable).addOrCondition('internal_type', 'document_id').addOrCondition('internal_type', 'conditions');
  //Do not query audit and log fields
  dict.addQuery('name', 'DOES NOT CONTAIN', 'var__m_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ecc_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ha_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'syslog');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_history');
  dict.addQuery('name', 'DOES NOT CONTAIN', '_log');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'text_search');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ts_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_watermark');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_audit');
  dict.orderBy('name');
  dict.orderBy('element');
  dict.query();
  while(dict.next()){
    var table_name = dict.name;
    var field_name = dict.element;
    // Skip tables used for Table Rotation
    var table_rotation_gr = new GlideRecord("sys_table_rotation_schedule");
    table_rotation_gr.addQuery("name.name", '!=', table_name);
    table_rotation_gr.addQuery("table_name", table_name);
    table_rotation_gr.query();
    if(!table_rotation_gr.hasNext()){
      
      var reference_type = dict.internal_type;

      // Look up the Table Scope
      var table_gr = new GlideRecord("sys_db_object");
      table_gr.addQuery("name", table_name);
      table_gr.addQuery("scriptable_table", false);
      table_gr.query();
      if(table_gr.next()){

        // Change to the scope of the table
        gs.setCurrentApplicationId(table_gr.sys_scope);
        
        
        //Query each table for matching records
        var related_record_gr = new GlideRecord(table_name);
        if(reference_type == 'glide_list' || reference_type == 'conditions'){
          related_record_gr.addQuery(field_name, 'CONTAINS', move_from_id);
        } else{
          related_record_gr.addQuery(field_name, move_from_id);
        }
        related_record_gr.query();
        while(related_record_gr.next()){
          // Do a replace so we support all field types including lists and conditions
          var old_value = related_record_gr.getValue(field_name);
          var new_value = old_value.replace(move_from_id, move_to_id);
          related_record_gr.setValue(field_name, new_value);
          //Silent update
          related_record_gr.setWorkfow(false);
          related_record_gr.autoSysFields(false);
          if(save_changes){
            related_record_gr.update();
          }
        }
        var record_count = related_record_gr.getRowCount();
        if(record_count > 0){
          var record_message = ' records found';
          if(record_count == 1){
            record_message = ' record found';
          }
          gs.info(record_count + record_message + " in " + table_name + "." + field_name);
        }
      }
    }
  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Catalog Variables
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Change back to global scope
  gs.setCurrentApplicationId("global");
  // Process Catalog Variable References
  var catalog_var_gr = new GlideRecord('sc_item_option');
  catalog_var_gr.addQuery('value', 'CONTAINS', move_from_id);
  catalog_var_gr.query();
  while(catalog_var_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = catalog_var_gr.getValue(field_name);
    var new_value = old_value.replace(move_from_id, move_to_id);
    catalog_var_gr.setValue(field_name, new_value);
    //Silent update
    catalog_var_gr.setWorkfow(false);
    catalog_var_gr.autoSysFields(false);
    if(save_changes){
      catalog_var_gr.update();
    }
  }
  var variables_count = catalog_var_gr.getRowCount();
  var variables_message = ' catalog variables found';
  if(variables_count == 1){
    variables_message = ' catalog variable found';
  }
  gs.info(variables_count + variables_message);
  
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Workflow Variables
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  //Query for workflow variable values
  var_value_table_name = 'sys_variable_value';
  var var_value_gr = new GlideRecord(var_value_table_name);
  var_value_gr.addQuery('value', 'CONTAINS', move_from_id);
  var_value_gr.query();
  while(var_value_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = var_value_gr.getValue("value");
    var new_value = old_value.replace(move_from_id, move_to_id);
    var_value_gr.setValue("value", new_value);
    //Silent update
    var_value_gr.setWorkfow(false);
    var_value_gr.autoSysFields(false);
    if(save_changes){
      var_value_gr.update();
    }
  }
  var wf_variables_count = var_value_gr.getRowCount();
  var wf_variables_message = ' workflow variables found';
  if(wf_variables_count == 1){
    wf_variables_message = ' workflow variable found';
  }
  gs.info(wf_variables_count + wf_variables_message); 


  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Attachments
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  //Query for attachments
  attachment_table_name = 'sys_attachment';
  var attachment_gr = new GlideRecord(attachment_table_name);
  attachment_gr.addQuery('table_sys_id', move_from_id);
  attachment_gr.query();
  while(attachment_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = attachment_gr.getValue('table_sys_id');
    var new_value = old_value.replace(move_from_id, move_to_id);
    attachment_gr.setValue('table_sys_id', new_value);
    //Silent update
    attachment_gr.setWorkfow(false);
    attachment_gr.autoSysFields(false);
    if(save_changes){
      attachment_gr.update();
    }
  }
  var attachment_count = attachment_gr.getRowCount();
  var attachment_message = ' attachments found';
  if(attachment_count == 1){
    attachment_message = ' attachment found';
  }
  gs.info(attachment_count + attachment_message); 


  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Delete old record
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  var old_gr = new GlideRecord(table);
  if(old_gr.get(move_from_id)){
    gs.info("Deleting old record: " + table + " - " + old_gr.getDisplayValue() + " (" + old_gr.sys_id + ")");
    if(save_changes){
      old_gr.deleteRecord();
    }
  }
  
}

What's New

There are currently no new items, please check back later.

Archives
2021 (2)
  September (1)
  May (1)
2019 (1)
  August (1)
2018 (3)
  August (1)
  April (1)
  January (1)
2017 (1)
  January (1)
2016 (4)
  December (1)
  November (1)
  May (1)
  January (1)
2015 (1)
  December (1)
2014 (2)
  August (1)
  February (1)
2013 (4)
  October (1)
  July (1)
  June (1)
  April (1)
2012 (11)
  December (2)
  October (3)
  September (1)
  May (1)
  April (1)
  February (2)
  January (1)
2011 (14)
  December (1)
  November (1)
  September (2)
  July (2)
  June (1)
  May (1)
  April (2)
  March (3)
  January (1)
2009 (2)
  October (1)
  June (1)
2008 (1)
  September (1)