Dedup/Merge of CI extended table

[snowE]

Hi All,

I have a requirement where we are removing SDLC component table (extended from cmdb_ci). Before we remove this, we want to merge the related list items and relationships from these records to a corresponding Application Service record. My first thought was to use Dedup/Merge, but because there are 120 records, it is a lot of manual work. Is there a better way or script to achieve this? Any leads or help appreciated

[Bert_c1]

Hi @snowE,

I has some time and came up with the following, based on common fields for both tables. First is a script to get those and build statements for a separate script the creates records in the 'cmdb_ci_service_auto' from existing records in the 'cmdb_ci_sdlc_component' table.  This works in my instance but I have very little demo data to test.  And since I didn't find any dictionary records that reference the 'cmdb_ci_sdlc_component' table, I believe this can work.  The First script:

// Get fields for both tables
var sysDict = new GlideRecord('sys_dictionary');
sysDict.addEncodedQuery('GOTOname=cmdb_ci_service_auto^elementNOT LIKEsys_');
sysDict.addOrderBy('element');
sysDict.query();
var saFields = [];
while (sysDict.next()) {
	saFields.push(sysDict.element.toString());
}
var sdlcFields = [];
var sysDict = new GlideRecord('sys_dictionary');
sysDict.addEncodedQuery('GOTOname=cmdb_ci_sdlc_component^elementNOT LIKEsys_');
sysDict.addOrderBy('element');
sysDict.query();
while (sysDict.next()) {
	sdlcFields.push(sysDict.element.toString());
}
gs.info("saFields = " + saFields);
gs.info("sdlcFields = " + sdlcFields);
// Get common fields
var au = new ArrayUtil();
var commonFields = au.intersect(saFields, sdlcFields);
gs.info('commonFields = ' + commonFields);

//Now go through common fields and create script statements
for (i = 0; i < commonFields.length; i++) {
	gs.info("serviceAuto."+commonFields[i]+"=sdlcComponent."+commonFields[i]+";");
}
// copy and paste output to new script that creates records

I took the output, pasted in a text editor and removed the "*** Script " to get the script statements.  Then used those in my second script (lines 7 to 77) to create records in 'cmdb_ci_service_auto' from those in the 'cmdb_ci_sdlc_component' table.

var serviceAuto = new GlideRecord('cmdb_ci_service_auto');
var sdlcComponent = new GlideRecord('cmdb_ci_sdlc_component');
//Now go through source table records and create records in the target table
sdlcComponent.query();
while (sdlcComponent.next()) {
	serviceAuto.initialize();
	serviceAuto.asset=sdlcComponent.asset;
	serviceAuto.asset_tag=sdlcComponent.asset_tag;
	serviceAuto.assigned=sdlcComponent.assigned;
	serviceAuto.assigned_to=sdlcComponent.assigned_to;
	serviceAuto.assignment_group=sdlcComponent.assignment_group;
	serviceAuto.attestation_score=sdlcComponent.attestation_score;
	serviceAuto.attestation_status=sdlcComponent.attestation_status;
	serviceAuto.attested=sdlcComponent.attested;
	serviceAuto.attested_by=sdlcComponent.attested_by;
	serviceAuto.attested_date=sdlcComponent.attested_date;
	serviceAuto.attributes=sdlcComponent.attributes;
	serviceAuto.business_unit=sdlcComponent.business_unit;
	serviceAuto.can_print=sdlcComponent.can_print;
	serviceAuto.category=sdlcComponent.category;
	serviceAuto.change_control=sdlcComponent.change_control;
	serviceAuto.checked_in=sdlcComponent.checked_in;
	serviceAuto.checked_out=sdlcComponent.checked_out;
	serviceAuto.cmdb_ot_entity=sdlcComponent.cmdb_ot_entity;
	serviceAuto.comments=sdlcComponent.comments;
	serviceAuto.company=sdlcComponent.company;
	serviceAuto.correlation_id=sdlcComponent.correlation_id;
	serviceAuto.cost=sdlcComponent.cost;
	serviceAuto.cost_cc=sdlcComponent.cost_cc;
	serviceAuto.cost_center=sdlcComponent.cost_center;
	serviceAuto.delivery_date=sdlcComponent.delivery_date;
	serviceAuto.department=sdlcComponent.department;
	serviceAuto.discovery_source=sdlcComponent.discovery_source;
	serviceAuto.dns_domain=sdlcComponent.dns_domain;
	serviceAuto.due=sdlcComponent.due;
	serviceAuto.due_in=sdlcComponent.due_in;
	serviceAuto.duplicate_of=sdlcComponent.duplicate_of;
	serviceAuto.environment=sdlcComponent.environment;
	serviceAuto.fault_count=sdlcComponent.fault_count;
	serviceAuto.first_discovered=sdlcComponent.first_discovered;
	serviceAuto.fqdn=sdlcComponent.fqdn;
	serviceAuto.gl_account=sdlcComponent.gl_account;
	serviceAuto.install_date=sdlcComponent.install_date;
	serviceAuto.install_status=sdlcComponent.install_status;
	serviceAuto.invoice_number=sdlcComponent.invoice_number;
	serviceAuto.ip_address=sdlcComponent.ip_address;
	serviceAuto.justification=sdlcComponent.justification;
	serviceAuto.last_discovered=sdlcComponent.last_discovered;
	serviceAuto.lease_id=sdlcComponent.lease_id;
	serviceAuto.life_cycle_stage=sdlcComponent.life_cycle_stage;
	serviceAuto.life_cycle_stage_status=sdlcComponent.life_cycle_stage_status;
	serviceAuto.location=sdlcComponent.location;
	serviceAuto.mac_address=sdlcComponent.mac_address;
	serviceAuto.maintenance_schedule=sdlcComponent.maintenance_schedule;
	serviceAuto.managed_by=sdlcComponent.managed_by;
	serviceAuto.managed_by_group=sdlcComponent.managed_by_group;
	serviceAuto.manufacturer=sdlcComponent.manufacturer;
	serviceAuto.model_id=sdlcComponent.model_id;
	serviceAuto.model_number=sdlcComponent.model_number;
	serviceAuto.monitor=sdlcComponent.monitor;
	serviceAuto.name=sdlcComponent.name;
	serviceAuto.operational_status=sdlcComponent.operational_status;
	serviceAuto.order_date=sdlcComponent.order_date;
	serviceAuto.owned_by=sdlcComponent.owned_by;
	serviceAuto.po_number=sdlcComponent.po_number;
	serviceAuto.purchase_date=sdlcComponent.purchase_date;
	serviceAuto.schedule=sdlcComponent.schedule;
	serviceAuto.serial_number=sdlcComponent.serial_number;
	serviceAuto.short_description=sdlcComponent.short_description;
	serviceAuto.skip_sync=sdlcComponent.skip_sync;
	serviceAuto.start_date=sdlcComponent.start_date;
	serviceAuto.subcategory=sdlcComponent.subcategory;
	serviceAuto.supported_by=sdlcComponent.supported_by;
	serviceAuto.support_group=sdlcComponent.support_group;
	serviceAuto.unverified=sdlcComponent.unverified;
	serviceAuto.vendor=sdlcComponent.vendor;
	serviceAuto.warranty_expiration=sdlcComponent.warranty_expiration;
	serviceAuto.insert();
}

I hope this helps. I'm not sure if there is a better way.