Hi,
I have a story requirement that currently in my instance there is a table CMDB Import Relations xls [u_cmdb_import_relations_xls] which uses bulk load to to create new relationships into the CMDB_REL_CI table. The requirement is to have the ability to change relationships too using this method.
Here is the transform map with the script inside it
and here are the field maps for this
Here is a picture of the excel that I am using to test this
The mapping for type is 1:1 and not scripted. I tried to set coalesce to false for the type field and when I load this excel using load data, the data goes into the staging table [u_cmdb_import_relations_xls] but it doesn't update anything in the target table.
I have checked the import set after and I get an error code of 800 and 'Unable to resolve target record, coalesce values not present: [Script],[Script]
How can I fix this? Or what is needed to fix this so that relationships can be changed using load data?
Thanks!
I will tell you the testing I have been doing and the errors I am getting and show you the scripts in the other fields, but for this field 'type' it is a 1:1 mapping.
When I leave the coalesce as true on parent, child and type and try to load data through an excel, I get this error:
When I set the coalesce on type to false then I get this error:
If I set coalesce to false on parent, child and type then I get this error
I did check and saw that there are a lot of business rules on the cmdb_rel_ci table as well, so maybe they are preventing this?
Below are field map scripts for the other fields parent and child.
Parent
answer = (function transformEntry(source) {
/*
This script manages matching of the CI's.
*/
var sourceparent = source.u_parent_name.toString();
var sourceparentclass = source.u_parent_class_name.toString();
var isVirtual = source.u_parent_is_virtual;
var appid = source.u_parent_application_id.toString();
var asset_tag = source.u_parent_asset_tag.toString();
var bpid = source.u_parent_id.toString();
var serialnumber = source.u_parent_serial_number.toString();
var ibpID = source.u_ibp_id.toString();
var isReady = false;
// If the parent is a process step, we need to find the correct one using its HLVC
var hlvc = source.u_process_step_hlvc.toString();
var parentci = new GlideRecord(sourceparentclass);
parentci.addQuery('sys_class_name', sourceparentclass);
switch (sourceparentclass) {
// For Data connections (TPCID)
case "u_cmdb_ci_data_connections":
if (bpid == "") {
break;
}
if (bpid != "") {
parentci.addQuery('u_connection_id', bpid);
}
isReady = true;
break;
/*
Application services and Business Services are stored in cmdb_ci_service table.
"Application ID" will match the Application Services
"Business Services" are matched by the "Name".
*/
case "cmdb_ci_service":
if (appid == "" && sourceparent == "") {
break;
}
if (appid != "") {
parentci.addQuery('u_application_id', appid);
} else {
parentci.addQuery('name', sourceparent);
}
isReady = true;
break;
/*
App Services (cmdb_ci_service_discovered) are matched by app ID
*/
case "cmdb_ci_service_discovered":
if(appid =='')
break;
parentci.addQuery('u_application_id', appid);
//childci.addQuery('number', appid);
isReady = true;
break;
/*
Physical and Virtual servers are stored in cmdb_ci_server table.
Virtaul servers matched with the use of "Serial Number" and "Name" combination. In nase "Serial Number" is empty, then the "Name" is used.
Physical servers are matched by the "Asset Tag".
*/
case "cmdb_ci_server":
if (isVirtual == "") {
break;
}
parentci.addQuery('virtual', isVirtual);
if (isVirtual.toString().toLowerCase() == 'true') {
if (sourceparent == "") break;
if (serialnumber != "") {
parentci.addQuery('name', sourceparent);
parentci.addQuery('serial_number', serialnumber);
} else {
parentci.addQuery('name', sourceparent);
}
} else {
if (asset_tag == "") {
break;
}
parentci.addQuery('asset_tag', asset_tag);
}
isReady = true;
break;
/*
Virtaul storage servers matched with the use of "Serial Number" and "Name" combination. In nase "Serial Number" is empty, then the "Name" is used.
Physical storage servers are matched by the "Asset Tag".
*/
case "cmdb_ci_storage_server":
parentci.addQuery('virtual', isVirtual);
if (isVirtual.toString().toLowerCase() == 'true') {
if (sourceparent == "") {
break;
}
if (serialnumber != "") {
parentci.addQuery('name', sourceparent);
parentci.addQuery('serial_number', serialnumber);
} else {
parentci.addQuery('name', sourceparent);
}
} else {
if (asset_tag == "") {
break;
}
parentci.addQuery('asset_tag', asset_tag);
}
isReady = true;
break;
/*
Business Processes are matched by "ID" of the process
*/
case "cmdb_ci_business_process":
if (bpid == "") {
break;
}
parentci.addQuery("u_id", bpid);
isReady = true;
break;
/*
Process steps are matched by name and their HLVC
*/
case "u_cmdb_ci_process_step":
parentci.addQuery('name', sourceparent);
isReady = true;
break;
/*
Buiness apps are matched by the app id
*/
case "cmdb_ci_business_app":
if (appid == "" && sourceparent == "") {
break;
}
parentci.addQuery('number', appid);
isReady = true;
break;
/*
service_offering for Important Business Process'
*/
case "service_offering":
parentci.addQuery('u_service_offering', ibpID);
isReady = true;
break;
/*
Business capabilities are mapped by hierarchy id if it is populated, else name
*/
case "cmdb_ci_business_capability":
if (sourceparent == "") {
break;
}
//Remove the number from the beginning of the name as it will be stored in Record ID.
var position = sourceparent.indexOf(' ');
var id = sourceparent.substring(0, position);
var dot = id.indexOf('.');
if (dot == id.length - 1) {
id = id.substring(0, id.length - 1);
}
parentci.addQuery('u_record_id', id);
isReady = true;
break;
/*
All other classes are matched by "Name"
*/
default:
if (sourceparent == "") {
break;
}
parentci.addQuery('name', sourceparent);
isReady = true;
break;
}
if (isReady) {
parentci.query();
if (parentci.next()) {
return parentci.sys_id;
} else {
status_message = "Parent not found";
ignore = true;
//gs.log("Query did not return any results for a Parent Ci");
}
}
})(source);
child
answer = (function transformEntry(source) {
var sourcechild = source.u_child_name.toString();
var sourcechildclass = source.u_child_class_name.toString();
var sourceparentclass = source.u_parent_class_name.toString();
var isVirtual = source.u_child_is_virtual;
var appid = source.u_child_application_id.toString();
var asset_tag = source.u_child_asset_tag.toString();
var bpid = source.u_child_id.toString();
var serialnumber = source.u_child_serial_number.toString();
var isReady = false;
var childci = new GlideRecord(sourcechildclass);
childci.addQuery('sys_class_name', sourcechildclass);
switch(sourcechildclass) {
/*
Application services and Business Services are stored in cmdb_ci_service table.
"Application ID" will match the Application Services
"Business Services" are matched by the "Name".
*/
case "cmdb_ci_service" :
if(appid == "" && sourcechild == "") break;
if(appid != "") {
childci.addQuery('u_application_id', appid);
} else {
childci.addQuery('name', sourcechild);
}
isReady = true;
break;
/*
Physical and Virtual servers are stored in cmdb_ci_Server table.
Virtaul servers matched with the use of "Serial Number" and "Name" combination. In nase "Serial Number" is empty, then the "Name" is used.
Physical servers are matched by the "Asset Tag".
*/
case "cmdb_ci_server" :
if(isVirtual == "") {
break;
}
childci.addQuery('virtual', isVirtual);
if(isVirtual.toString().toLowerCase() == 'true'){
if(sourcechild == "") {
break;
}
if(serialnumber!=""){
childci.addQuery('name', sourcechild);
childci.addQuery('serial_number', serialnumber);
}else{
childci.addQuery('name', sourcechild);
}
} else {
if(asset_tag == "") {
break;
}
childci.addQuery('asset_tag', asset_tag);
}
isReady = true;
break;
/*
Virtaul storage servers matched with the use of "Serial Number" and "Name" combination. In nase "Serial Number" is empty, then the "Name" is used.
Physical storage servers are matched by the "Asset Tag".
*/
case "cmdb_ci_storage_server" :
childci.addQuery('virtual', isVirtual);
if(isVirtual.toString().toLowerCase() == 'true'){
if(sourcechild == "") {
break;
}
if(serialnumber!=""){
childci.addQuery('name', sourcechild);
childci.addQuery('serial_number', serialnumber);
}else{
childci.addQuery('name',sourcechild );
}
} else {
if(asset_tag == "") {
break;
}
childci.addQuery('asset_tag', asset_tag);
}
isReady = true;
break;
/*
Business Processes are matched by "ID" of the process
*/
case "cmdb_ci_business_process":
if(bpid == "") {
break;
}
childci.addQuery("u_id", bpid);
isReady = true;
break;
/*
Business Apps are matched by ID if the parent is a process step or another business app, else matched by name
*/
case "cmdb_ci_business_app":
if (sourceparentclass == 'u_cmdb_ci_process_step' || sourceparentclass == 'cmdb_ci_business_app' ) {
if (appid == '')
break;
childci.addQuery('number', appid);
isReady = true;
break;
}
childci.query("name", sourcechild);
isReady = true;
break;
/*
App Services (cmdb_ci_service_discovered) are matched by app ID
*/
case "cmdb_ci_service_discovered":
if(appid =='')
break;
childci.addQuery('u_application_id', appid);
//childci.addQuery('number', appid);
isReady = true;
break;
// Connection Endpoint are matched by Host
case "u_cmdb_ci_connection_endpoint":
if (sourcechild == "") {
break;
}
childci.addQuery('host', sourcechild);
isReady = true;
break;
// Data Connection are matched by Host
case "u_cmdb_ci_data_connections":
if (bpid == "") {
break;
}
childci.addQuery('name', bpid);
isReady = true;
break;
/*
All other classes are matched by "Name"
*/
default:
if(sourcechild == "") {
break;
}
childci.addQuery('name', sourcechild);
isReady = true;
break;
}
if(isReady == true) {
childci.query();
if (childci.next()){
return childci.sys_id;
} else{
source.status_message = "Child not found";
ignore = true;
}
}
ignore = true;
})(source);thanks
