This article instructs you on how to set up and automate Cloudaware data import into ServiceNow CMDB using a Google BigQuery table.
Prerequisites
1. Ensure you have sufficient permissions to operate in ServiceNow or contact your ServiceNow administrator for assistance.
2. Contact your dedicated account manager or tam@cloudaware.com to specify what data you would like to import* from Cloudaware.
*If you have a Google BigQuery table preconfigured, Cloudaware will provide you with the resulting file. Otherwise, Cloudaware provides a path to the file stored in Cloudaware's BigQuery table.
1 - Create Data Source
Create a data source in ServiceNow to store the imported Cloudaware data as the import set table (check ServiceNow Documentation). Fill in the form and click Submit:
If you don't have a Google BigQuery table preconfigured for data, the File path value should be provided by Cloudaware.
Click Load All Records to load data into the import set table:
2 - Create Target Table
The target table stores ServiceNow CMDB data. If you use ServiceNow as CMDB and have a target table already to import external data, skip to step 3.
Manually
Check ServiceNow Documentation for instructions.
Use consistent field names in both the target table and the import set table (stage table) to ensure automated mapping of matching fields.
Automatically using a custom script
To use this option, create the import set table (stage table) first as the script checks field names and uses them to create a target table to automatically map fields while transforming.
var attrs = new Packages.java.util.HashMap();
var table_label = "target_table_label", // specify your target table label name
table_name = "target_table_name", // specify your target table name
extends_table = 'cmdb_ci'; // specify where the target table to be created
var gr = new GlideRecord('u_gcp_import_set_table_label'); // specify your import set table (stage table)
gr.setLimit(1);
gr.query();
while (gr.next()) {
var fields = new GlideRecordUtil().getFields(gr);
for (var i = 0; i < fields.length; i++){
fieldName = fields[i];
var labelName = gr[fieldName].getLabel();
var ca = new GlideColumnAttributes(labelName);
ca.setType("string");
ca.setUsePrefix(true);
attrs.put(labelName, ca);
}
}
var tc = new GlideTableCreator(table_name , table_label);
tc.setColumnAttributes(attrs);
if(typeof extends_table != 'undefined') tc.setExtends(extends_table);
tc.update();
This script must be run before transform map creation in step 3.
3 - Create Transform Map
Set up a transform map to define and automate field mapping. Click Create transform map:
Check ServiceNow Documentation for instructions. Fill in the form:
Source table - the combination of data source and import set table data (see step 1)
Target table - the table that you create manually or automatically using the Cloudaware script (see step 2)
Click Submit.
Select one of the options in the section 'Related Links':
Auto Map Matching Fields to create mapping automatically
Mapping Assist to create mapping manually
If you created the target table using the script or maintained consistency of field names when creating the target table manually, select automated mapping. The Field Maps may look the following:
For one-time data import, values in the column Coalesce must be set to false.
If you would like to import data and update records on a regular basis, define the unique field and set Coalesce to true for it. Learn more: https://www.servicenowelite.com/blog/2014/3/30/coalesce-methods
Click Transform.
Specify Import set and Transform map values. Сlick Transform to proceed.
The data will be loaded to the target table:
4 - Schedule Data Import
Once data tables are managed and mapping is set up, schedule data import runs - check ServiceNow Documentation for instructions.
