Cloudaware CMDB Data Import Into ServiceNow Using Google BigQuery

Cloudaware can import CMDB data into Google BigQuery and then automatically upload the data to ServiceNow on a regular basis. This guide explains how to set up and automate the import of Cloudaware CMDB data into ServiceNow CMDB using a Google BigQuery table.

To see how Cloudaware seamlessly integrates with ServiceNow in action, request a demo.

Use case: When using Cloudaware and ServiceNow for asset management, customers may want to download their appliation inventory or a list of AWS resources from Cloudaware CMDB into ServiceNow to enrich existing data. Cloudaware uses Google BigQuery as temporary storage for the imported CMDB data file.

The customer must configure data import in ServiceNow to ensure the data is uploaded and mapped correctly. The upload can be scheduled as one-time or as a recurring automated job.

Prerequisites

1. Define the scope of Cloudaware CMDB data to be downloaded into ServiceNow. Contact your dedicated account manager or tam@cloudaware.com to specify the scope.

2. Define whether to use a customer-managed^* or a Cloudaware-managed^** Google BigQuery instance.

^*If you already have a Google BigQuery table configured in your Google environment, contact your dedicated account manager or tam@cloudaware.com to obtain the resulting file.

^**If you prefer a Cloudaware-managed BigQuery instance, contact your account manager or tam@cloudaware.com. Cloudaware will configure the table and file and provide a path for setup.

3. A ServiceNow Admin role is required to run the Cloudaware script used for automation. If unavailable, contact your organization’s ServiceNow Admin for assistance.

Create a data source

Create a data source in ServiceNow to store imported Cloudaware data as the import set table (see ServiceNow Documentation). Fill in the form and click Submit:

If you don’t have a preconfigured BigQuery table, Cloudaware will provide the File path value.

Click Load All Records to load data into the import set table:

Create a target table

The target table stores ServiceNow CMDB data. If you already use ServiceNow CMDB and have a target table for external imports, skip to step 3 (Create a transform map).

Manual

See ServiceNow Documentation for instructions.

Use consistent field names in both the target table and the import set (stage) table to enable automated mapping of fields.

Automatic (custom script)

If you prefer automation, create the import set table (stage table) first. The script below checks field names and uses them to create a target table with automated mapping.

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();

Run this script before creating the transform map in Step 3 (Create a transform map).

Create a transform map

Set up a transform map to define and automate field mapping.

1. Click Create transform map:
   

   

2. Fill in the form:
   

   

   WHERE
   Source table – the combination of data source and import set table data (see step 1 - Create a data soure)
   Target table – the table that you create manually or automatically using the Cloudaware script (see step 2 - Create a target table)

   See ServiceNow Documentation for instructions.
   
   Click Submit.
   

3. In 'Related Links', select:
   Auto Map Matching Fields – to create mapping automatically
   Mapping Assist – to create mapping manually
   
   If field names were consistent or the target table was created by script, use automatic mapping. Example field mappings may look like this:
   

   

For one-time imports, set Coalesce to false.

For recurring imports, define a unique field and set Coalesce to true. Learn more here.

Click Transform.

4. Specify Import set and Transform map values. Сlick Transform again to proceed.
   

   

   
   The data will be loaded into the target table:
   

   

Schedule a data import

Once tables and mappings are configured, schedule recurring data imports. See ServiceNow Documentation for instructions.