Simplifying Data Imports from Third Parties

Recently along with my Crossfuze colleagues Josh McMullen and Scott Cornthwaite, I performed work for a client that needed to import asset data from several third party vendors. The company, a global manufacturer of household appliances, has dozens of vendors around the world, which supply data (CSV and Excel files) using proprietary formats and column names. The client’s desired future state is to enforce a single format for use by all vendors. But to control their hardware and software assets today, they needed a solution to work with multiple vendors and data formats.

We faced a few challenges. Our solution needed to:

• Allow data imports without elevated roles or system privileges
• Handle the same kind of data from multiple vendors (e.g. hardware asset imports)
• Handle data in a variety of file formats including text (.csv) and MS Excel
• Provide feedback to the client’s IT asset management (ITAM) team
• Check data quality and handle exception conditions defined by the client
• Run with minimal input or intervention

We started to create custom inbound email actions to process emails with attached data files as they were sent from each vendor. But we discovered some big downsides with that approach. The most serious was that there’s no way to validate the accuracy or completeness of data being sent by the vendor. Inbound email processing also leaves the client at the mercy of the vendor for the timing of the imports. Finally, it isn’t very scalable, since a new inbound email action must be created for every additional vendor and/or data source.

What the client needed was a simple way to perform a data import via a Data Source with a file attachment. They were already aware of the Load Data wizard that ServiceNow provides on the System Import Sets application menu, but that solution isn’t very user-friendly, and it requires a lot of manual input each time new data are imported.

Asset Import Wizard

To make it easy for the client’s ITAM team to import their data in to ServiceNow, we leveraged the power of the Service Catalog. Specifically, we created a Record Producer to provide a simple front-end for importing vendors’ data files. Record producers can be used by anyone. Their visibility can be limited to only interested parties. Files attached to the record producer are automatically attached to the record that is created. And the record producer’s Script field enables powerful data processing.

We configured the record producer as follows:

• Name: Asset Import Wizard
• Table: Data Source (sys_data_source)

We added a single variable to the record producer, a lookup select box to allow the ITAM team member to specify the type of import they were performing.

Import Wizard Configurations

The variable gets its list of options from a custom table called Import Wizard Configurations. This table allowed us to build a flexible framework for defining different types of file imports from any vendor.
This table has many fields that are similar to those on a Data Source record. That’s because the record producer’s script queries the Wizard Configuration table for values to use when it inserts a new record in the Data Source table. Here’s how this form breaks down.

• Vendor references records in the Company (core_company) table where the Vendor field is set to true.
• Import Type is a choice list of options that describe the kind of asset data being imported (e.g. Hardware, Software, HW end of life disposal, or Lease Contract)
• Expected Data Format is a choice list that allows either CSV or Excel (.xls) formats to be specified.
• Data Source Name Prefix is a text field for naming the data source. The record producer’s script automatically appends the current time/date stamp to the prefix when each Data Source record is created
• Import Set Table and Transform Map are fields that reference records in those tables. The import set and transform map to be used for these data imports must be created in advance.
• Header Row and Sheet Number are used to specify values when an MS Excel file is the data source. For CSV files, the client just sets these to 1.
• Active allows the client to deactivate the Wizard Configuration record if it is no longer needed. The Lookup Select Box variable on the record producer displays only active records in this table.

Record Producer Script

The record producer’s script is where the wizard’s “magic” happens. The script does several things:

1. It validates that the wizard is submitted with an attachment of the correct format.
2. It queries the Wizard Configuration table for the record that is selected in the Import Type variable, and inserts a new record into the Data Source table using values from the Wizard Configuration record.
3. It imports the data into the import set table and executes the specified transform map, using ServiceNow helper functions GlideImportSetLoader and GlideImportSetTransformerWorker
4. It provides feedback to the user upon successful execution of the script, or displays appropriate error messages if the script encountered errors. Error conditions cause the record producer to abort without creating the Data Source record.

Here is the code we used in the record producer script:

The Miracle of Transform Map Scripts

If the wizard works magic on the front end, then Transform Maps do the same on the back end. Even when the client’s ITAM team have an opportunity to review the vendors’ data before importing it, there can still be errors. We identified all of the potential failure points in each vendor’s data.

We used onBefore transform scripts to check each source row for exceptions before the source fields are mapped into the target table. We grouped these exceptions into two categories; Errors and Warnings. The transform scripts use log.error() and log.warn() to write exceptions to the import set log for each import run. Error exceptions cause the source row to be skipped by setting the ignore variable to true. Warning exceptions are logged, but the source row is transformed.

We also determined whether other tables required records to be updated or inserted as a result of the data import. For example, lease contract imports contain information about the hardware assets that are under lease. As each hardware record is updated, a related record has to be created in the Assets Covered table, in order to associate that asset with its lease contract.

We used onAfter transform scripts to handle these secondary table updates. OnAfter scripts run after the source record has been transformed into the target table. These scripts also logged exceptions if any were encountered during the update.

After all of the source rows have been evaluated and/or transformed, an onComplete script compiles the exceptions from the import set log in a block of text, then queues a system event. The user who initiated the import receives a notification containing that block of text. The notification provides feedback in near-real time, and lists exceptions from the import set log that would normally only be available to administrators.

The Universal Translator

The final cog in this machine is the Vendor Model Translation table. We created this custom table because the client found that their vendors used model identifiers that did not match the names in the client’s Model (cmdb_model) table. The lack of a common name, model number or some other identifier in the vendor’s data makes it impossible to match asset models.

The vendor translation table is a simple cross-reference table that associates a vendor’s name for a given model with the model record in ServiceNow. It contains just four fields:

• Vendor is a reference to a vendor record in the Company (core_company) table
• Active is a true/false value that can be used to filter records during queries
• Vendor Model is a text field that stores the identifier used by the vendor to reference the model
• ServiceNow Model is a reference to a record in the Model (cmdb_model) table

With this simple table it is possible to establish aliases for any number of models and/or vendors. It can also be used as a kind of normalization table, listing several vendor models that all refer to the same model in the client’s Model table. In the example below, three mobile phone models provided by the vendor are all associated with a single model in ServiceNow:

We put this table to use in a couple of ways. The transform map for hardware asset imports used an onBefore script to set values in the target Hardware (alm_hardware) table based on a match in the vendor translation table. This script also illustrates some of the exception logging we performed for the client:

For mobile device imports we wrote an onStart transform script that uses the Vendor Model Translation table to update source rows before any source rows are transformed. That reduces the ITAM team’s administrative task load by leveraging information already in ServiceNow’s Model table.

Putting It All Together

The Import Wizard gave the client a simple way to initiate data imports from any number of vendors at a convenient time, after they verified the quality of the data. The Wizard Configuration table provided a means to extend the wizard’s functionality for multiple vendors and data imports. And the Vendor Model Translation table allowed the ITAM team to associate a vendor’s model information with Model records in the client’s ServiceNow instance.

The client’s ITAM team reports that the wizard has already paid off.  In a few short days it has simplified their work, reduced errors, and made processing asset data much more efficient. And project managers are making plans to bring more vendors on board as they work toward enforcing common data formats across all of their vendors.

Standing on the Shoulders of Giants

This article wouldn’t be complete without acknowledging the assistance of others. We based our design for the Import Wizard record producer on this ServiceNow Community post by Michael Ritchie and endorsed by everyone’s favorite bow-tie wearing technical genius, Chuck Tomasi. The post is well worth reading, since Michael leads you through the steps you’ll need to follow before using the record producer to perform a data import.