Disclaimer: Importing configuration item data using excel spreadsheets is the worst method to import data. Often the data is old as soon as you start the import. Using ServiceNow Discovery, SCCM Plugin, Procurement, or anything else will produce better results.
However there are certain undiscoverable CI Classes and some companies just don’t have Discovery yet. Using Excel files and Import Sets is sometimes the only choice.
ServiceNow Documentation
Here is the official ServiceNow documentation on import sets.
Import Process
In this post, we are importing using import sets. Not the easy import feature.
Important Note: It is best practice to run the import in a development instance first to see if you have any errors or mistakes. It can be difficult to remove a mistake in data after it is within production.
Import set process is as follows
Data Source. Usually Excel.
Import Set. Data is imported to a Import Set, a temporary location for the data
Transform Map. Data is transformed to the Target Table
Target Table. The end result of the import. CIs, Users, Groups, etc.
Example Import
Step 1: Create Excel File
Here is an example excel file. You can import any number of columns and types.
Class | Asset tag | Serial number | Manufacturer | Model ID | Assigned to
cmdb_ci_computer | P1000241 | 56WHL71 | Gateway | Gateway DX Series | Carol Coughlin
cmdb_ci_computer | P1000640 | FVG-200-L80989-GT | Apple | Apple MacBook Air 13" | Florine Willardson
cmdb_ci_computer | P1000512 | KIE-450-K88260-FO | Apple | Apple MacBook Pro 15" | Mellissa Sule
cmdb_ci_computer | P1000479 | BQP-854-D33246-GH | Apple | Apple MacBook Pro 15" | Miranda Hammitt
cmdb_ci_computer | P1000249 | RSB-980-E66113-CM | Dell Inc. | Dell Inc. PowerEdge T610 | Genevieve Kekiwi
cmdb_ci_computer | P1000251 | KWF-742-G95931-TF | Dell Inc. | Dell Inc. PowerEdge T610 | Emilia Oxley
cmdb_ci_computer | P1000250 | FBJ-435-J27123-DL | Dell Inc. | Dell Inc. PowerEdge T610 | Freida Michelfelder
cmdb_ci_computer | P1000252 | QVQ-746-C74363-IT | Dell Inc. | Dell Inc. PowerEdge T610 | Darrell Amrich
cmdb_ci_computer | P1000471 | ERU-672-W29354-HW | IBM | IBM Thinkpad T20 | Roger Seid
cmdb_ci_computer | P1000454 | GOU-623-U55281-RH | IBM | IBM Thinkpad T20 | Suzette Devaughan
cmdb_ci_computer | P1000467 | OGT-690-V53734-CD | IBM | IBM Thinkpad T20 | Tyree Courrege
cmdb_ci_computer | P1000465 | LSD-159-C38065-XA | IBM | IBM Thinkpad T20 | Tammie Schwartzwalde
Step 2: Load Data
1. Login as an administrator
2. Left Navigator Bar > System Import Sets > Load Data
Create Table
Import Set Table: CMDB Data Upload [u_cmdb_data_load]
Source of the import: File
File: Select your spreadsheet
Sheet number: 1
Header row: 1
3. Click Submit
4. After Import Set Table is created, Click Create Transform Map link.
Step 3: Transform Map
Transform Map
1. Fill in fields
Name: CMDB Data Load
Source Table: Configuration Item [cmdb_ci]
Target Table: Incident
Run Business Rules: true
Run Script: true
Enforce Mandatory fields: No
Copy empty fields: true (You can also put false if you don’t want to blank out fields)
2. Click Save
Field Maps
The field maps map the fields from the import set to the target table. In this example,
Source | Target | Coalesce | Choice action
u_model_id | model_id | false | ignore
u_serial_number | serial_number | true | ignore
u_manufacturer | manufacturer | false | ignore
u_asset_tag | asset_tag | false | ignore
u_class | sys_class_name | false | ignore
u_assigned_to | assigned_to | false | ignore
CoaleAse Methods
If you are just inserting records and not concerned with matching to existing record, no coalease (match) is needed.
However if you can set the coalease to match in the field map on a certain field if you want as well. A common field for matching for configuration items is serial number.
If you need something more complex, here is an article that can help:
Transform Scripts
Often data isn’t perfect. You may need to write a transform script to manipulate certain data. A common thing to do is write a transform script for a reference field to find a sys_id.
Here is an article on how to write transform scripts if needed:
Step 4: Transform!
After your Import Set, Transform Map, and Transform Script are ready. Transform the Import Set you created.
It is import to note, if there are certain emails you don’t want sent, you can disable them during the transform process as well.
Important Note: It is best practice to run the import in a development instance first to see if you have any errors or mistakes. It can be difficult to remove a mistake in data after it is within production.
After you import the data, there is a Related link to Transform.
Step 5: Review Results
You can view the import set to see what data was processed or if there are errors.
Running an import in development allows you to test multiple times until it is correct. Taking a small sample size first also helps.
Best of luck in your imports!