Common transform map for multiple data sources

 

Overview

Service-Now has a CMDB module which has almost all types of assets init. cmdb_ci is the base table for all assets. Same applies to Service-Now tickers, task is the base table. Mostly these base tables has all the common fields. Data created / added in CMDB has some what similar fields. If we want to import data into system using excel file or any other file or data source (SQL, Oracle etc.) one needs to create Data Source and transform map and mapping source fields with target fields. Creating and maintaining transform map for all assets becomes painful process when removing common columns from all asset transform map.

To over come this issue a common transform map is created. Create a data source called "LinuxServer Data Source". In Import Set table label add name "Common Import Set Table". For now we consider, each server data is fetched from different table. So to add queries appropriately we create multiple data sources like "Win Data Source", "IBM Data Source" etc. but the Import used for these data sources are common that is "Common Import Set Table". If the data is pulled from any database make sure column "devide_type" with default value is added. We need this column later to re-set transform map target table dynamically.

SELECT name, vendor, CPU, 'cmdb_ci_linux_server' AS device_type
FROM [ table_name ]

Pull data into "Common Import Set Table" by clicking on "test load 20 records". It will create source table in Service-Now instance with all columns and 20 records. Create Transform Map "Common Transform Map" as shown below.

Select source table "Common Import Set Table". Click "Run Script" checkbox to write script which will be executed before processing a row. So this script will run on before transformation of each row.

var sysClass = source.u_device_type.toString();
if (JSUtil.notNil(sysClass))
{
 target.sys_class_name = sysClass;
}

Above code will change the target table of transform map at run time. Once field mapping is done the records will be updated in the Linux Server of Service-Now CMBD table.

So far records are populated in Linux Server with common fields. Now we need to populate Linux Server specific columns as well. e.g. "u_service_level" is a Linux Server specific column which is created on "cmdb_ci_linux_server" table. This field is not available in field mapping as the target table is re-set dynamically. Hence we need to write a script onAfter event of transform map

var deviceType = source.u_device_type.toString();
if (deviceType == 'cmdb_ci_linux_server')
{
 // Fech class name from target
 var v_ClassName = target.sys_class_name.toString();  
 
 // Create a glide record  
 var grServer = new GlideRecord(v_ClassName);           
 grServer.addQuery("sys_id", target.sys_id.toString());
 grServer.query();
 if (grServer.next())
 {
  grServer.u_service_level = source.u_service_level.toString();
  grServer.update();
   }
} 

To Get more information go through Servicenow Developer Training