Cloudaware CMDB Data Import Into ServiceNow Using Google BigQuery

Use case: Using Cloudaware and ServiceNow for asset management, a customer would like to download their app inventory or the list of AWS-related resources from Cloudaware CMDB to ServiceNow to enrich the existing data. Cloudaware uses Google BigQuery as temporary storage for the imported CMDB data file. The customer should set up data import in their ServiceNow to get data uploaded and mapped correctly. The data upload can be scheduled as one-time or recurrent (automated) action.

Prerequisites

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

2. Define whether you would like to use your Google BigQuery* or a Cloudaware-managed** one.

3. You must be ServiceNow Admin to run a Cloudaware script required for data import automation. Otherwise, contact your organization’s ServiceNow Admin for assistance.

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:

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.

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

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:

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.