Importing relationships- reference field value

[joanct]

In an effort to get our CI data in SNOW configuration management , we   successfully identified critical CIs from old system and imported CIs into SNOW CMDB tables Now our team wants to import relationships to cmdb-rel-ci table from external source- CSV template extract.We have verified relationship type exists in cmdb-rel-type . But while trying to come up with a web service import set and transform map , we ran into challenges identifying whether its the tablename or the CI name that should be mapped in parent and child fields - so is it the CI name or CI's table name .if its the CI name - say VMserverC23 , is a script required for the parent and child   field on the web service transform map to check that specific CI data being imported exists in their original tables? what is reference field value on transform maps?

[Bhavesh Jain1]

You can write this in your web services transform map.

In case you would like to do the delta, then in the onBefore script, you will have to query the entire cmdb_rel_ci table to see if the same relationship already exists.

So your script would be something like :

if (source.u_child_class != '' && source.u_child_name != '' && source.u_parent_name != '' && source.u_parent_class != '')   
{ 
var childCI;
var parentCI; 
var gr= new GlideRecord('source.u_child_class');   
gr.addQuery('name','source.u_child_name');   
gr.query();   
if (gr.next()) {   
           childCI = gr.sys_id;   
   }   
   
var gre = new GlideRecord('source.u_parent_class');   
gre.addQuery('name','source.u_parent_name');   
gre.query();   
if (gre.next()) {   
   parentCI = gre.sys_id;   
   }

var rel= new GlideRecord('cmdb_rel_ci');   
rel.addQuery('child',childCI);
rel.addQuery('parent',parentCI);   
rel.addQuery('type',source.u_type);
rel.query();   
if (rel.hasNext()) 
{ ignore = true;
}
else
{
 target.child = childCI ;
 target.parent = parentCI   ;
 } 
}

[Bhavesh Jain1]

Remember - You cannot use coalesce field because your excel contains only CI names.

And there can be same CI name for different CI classes. So if you just go by the name, you will map a server instead of a database because the CI names were same. So the entire coalesce logic has to be built in the onbefore script.

Let me know if you have queries.

Regards,

Bhavesh

http://www.igate.com

[joanct]

but should there also be a query or script for parent field and child field individually on transform map so that when one   imports into rel-ci table the parent lets say its "unix12Z45XW" server exists in orignal table i.e. cmdb_ci_unix_server. Similar when child values are imported , say "databaseOrc3452" exists in cmdb_ci_database?Is sysid rquired here for comparison?or is this not required .

thanks in advance. J

[Bhavesh Jain1]

The script will take care of that.

Just add one line in the code in the end :

}  

else   if(childCI != '' &&   parentCI != '')

{  

target.child = childCI ;  

target.parent = parentCI   ;  

}

[mathankumar2]

Hi Bhavesh,

We have a requirement to validate the CI relationship type during the transform map import.

Ex:If mistakenly user enters a relationship type on the data load sheet which   is not defined between 2 CI's on the system then the import should not happen.But what I see is even though if relation ship is not defined between 2 CI's on the system   , say Network and Linux, if you still have a relation ship mentioned on the sheet called - Network123 Contains ::Contained by Linux123 ,, then the new relationship type between two CI's gets inserted in to CMDB, which should not happen as per the requirement.

I tried validating using the on before script by getting the CI class for parent,child and relationship type on the 'cmdb_rel_type_suggest' but no luck. Please suggest.

==================================================================

Also, please suggest if there is a way to ignore the complete sheet from being updated in to CMDB if a single record on the data load sheet fails the validation , as i know that we can ignore the particular row which failed during the validation.

=================================================================

Thanks,

Mathan R