How to import CMDB data to multiple tables (win server, Linux server etc..)using transform script

[Community Alums]

Hi,

I wanted to know how to import different types of CI's to servicenow CMDB tables using Transform maps and scripts.

Consider , I have an excel file which contains multiple Ci type data(windows server, linux server ..) .

i have created a transform map for 'cmdb_ci_win_server' table and it works if i import the windows server data (Ci type = Windows Server) .

If i want to add other CI types (Linux server in the same excel sheet) , how or where we can add a condition like when Type is Windows it should transform the data into ' 'cmdb_ci_win_server' ' and if it is Linux server then ' 'cmdb_ci_linux_server' . 

Thank you

[Fabian Kunzke]

Hey,

There is multiple ways of doing this and depending on the kind of data you have, you should approach that differently. The main reason for different approaches is the data that is contained within the sheet. Depending on how comfortable you feel with data manipulation, there is one tricky way of doing it.

Let's start with the simplest and most intuitive one: You can always create a transform map for each table you are transforming to. Let's say you have computers, windows servers and linux servers. Therefore you would have a transform map for each of them. For each of them create a "onBefore" transform script that skips the record, if it does not match the specific type.

if(source.u_type != "Computer)
  ignore = 'true';

Now, this is the best if you don't want to script a lot and don't want an intransparent transforming effort. It is very clear, which transform map does what and if any changes need to be done, then it's easy to find where.

However, there is a "dark side" way of doing this. It requires a bit of a history on how these transform maps and GlideRecords work:
A transform map takes a record from you temporary import table and transforms it based on the available table fields into a GlideRecord object. Note: This object is not yet inserted. This means, no matter what the table definition is, you can just add any values to the GlideRecord object via a script. However, if these values don't exist in the table, as soon as you insert the GlideRecord object, it will loose these values.
This allows us one little trick: We can create a transform map for the "cmdb_ci_hardware" table. This of course does not contain fields like the operating system or cpu info. That kind of information is something we then need to add in a transform script before the insert happens:

target.operating_system = source.u_operating_system;
target.cpu_count = source.u_cpu_count;
// Note: target relates to the record that will be inserted, while the source is the temporary record from your excel import

This allows us to insert the table specific values for all cmdb_ci_hardware classes even though our target table is "cmdb_ci_hardware" at the moment. Now, additionally we will add another mapping for the "sys_class_name" field. This is the field where we then set the target table (e.g. cmdb_ci_computer).

Finally, the record will be inserted.

This allows us to use only one transform map, but it requires more coding and far more documentation as it is not quite obvious, what happens. Further, it requires your cmdb to be very well structured and all the fields to be used in the same context.

In short: I'd always use the first of the two option, but there is always the second one for those who dare to join the dark side.

Regards

Fabian

[Community Alums]

Hi,

Thanks for your reply.

I tried the following as per your suggestion.

1.Created a Transform MAP for cmdb_ci_win_server (field map is done and name is coalesce field)

2.Created a Transform map for cmdb_ci_linux_server (field map is done and name is coalesce field)

3.Created a Transform map (onBefore) script like below for each transform map mentioned above

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
if(source.u_class != "Windows Server")
    {
        ignore = 'true';
    }
})(source, map, log, target);

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

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
if(source.u_class != "Linux Server")
    {
        ignore = 'true';
    }
})(source, map, log, target);

after the import

--windows server are created but additional undefined record also created

--Linux servers are created properly

please guide me how to troubleshoot and fix the issue.

[Fabian Kunzke]

Hey,

this is very odd. Could you try to remove the quotation marks on the ignore = 'true' part. This does not change anything, but this way it is in line with the docs.

Now, since it works for one of the two, i guess this has nothing to do with your import, but just to double check:

Before we run any checks on the actual transform maps, check the data in the data set and make sure that there isn't a record with no data in it. This can sometimes happen, if your excel sheet is not "correctly" formatted.

First, make sure that you run only one import and trigger both transform maps.

Second, run the import only with one transform map and see what is created. If the issue still persists, then the import script likely causes the issue.

Then, try to run the import with more than one test record (again with both transform maps together). If you get an undefined record for each test record (so 5 records imported and 5 undefined records), the transform maps are causing the issue.

Lastly, export the changes you made and put them on a personal developer instance and run the import there again. If the same issue occurs, we can be sure it is the import.

My guess is on the data.

Regards

Fabian

[Community Alums]

Hi,

I have followed the test case which you asked me to perform . please find the results below.

Run only one import and trigger both transform maps

windows Server file--> Both transform map --> only Windows server records created (No undefined records)
Linux Server file --> Both transform map --> only Linux server records created (undefined record created on windows server class)

Run the import only with one transform map

Windows Server file--> windows server  transform map --> only Windows server records created (No undefined records)
Linux Server file --> Linux server  transform map --> only Linux server records created  

if i run the Windows server transform map with Linux server file (excel) then i am getting undefined record.
(it should skip creating a record as per the script but undefined record is created when condition fails on windows server transform script but for linux server class it works as expected)

if(source.u_class != "Windows Server")
    {
        ignore = true;
    }

but for linux server transform map with Windows server file - works fine -no records created(ignore condition works fine)

Run the import with more than one test record
windows and linux server records are created accordingly but the existing underfined record is ther and no other undefined records created.