CMDB import from SFTP into ServiceNow

Find on this page the instructions to import data from an SSH File Transfer Protocol (SFTP) server into ServiceNow.

Nexthink recommends using the out-of-the-box procedure in ServiceNow to import data into the configuration management database (CMDB).

Below, find the official ServiceNow documentation for further reference:

• ServiceNow | Import Set landing page

Additionally, Nexthink provides as a reference, a group of configuration changes in the form of an update set. Find further details about this update set here:

• Nexthink SFTP Import connector installation guide

Prerequisites

Nexthink SFTP Import Connector requires a Nexthink cloud platform instance. The system imports the data in ServiceNow as a CSV file from an SFTP server that is generated using the Nexthink platform outbound integrations and stored in an SFTP server for ServiceNow Data source consumption.

To ensure the process runs smoothly, you must have the following components:

• A Nexthink cloud instance.

• An SFTP server.

• A ServiceNow instance.

• A Data Exporter created using the Outbound connector section, that exports CSV files to an SFTP server.

• A CMDB Strategy

Users involved in the import process must have the necessary roles to be compliant with the ACL rules of the target tables.

Main ServiceNow components

Data source

The first step in the process is creating an artifact to fetch data from the SFTP Server and populate the import set table with the given data.

See the official ServiceNow documentation for further details: Data sources | ServiceNow

Data Source Example

How to create a Data Source

To create a data source, go to the System Import Sets > Administration > Data Sources module and click on the new button.

The following table explains how you can configure the data in every field of the artifact.

Field	Details
Name	You can set any name.
Import set table label	You can set any table label.
Import set table name	Populated automatically based on the previous field value.
Type	To use SFTP as the retrieval method, it is necessary to set the value to "File".
Format	CSV
File retrieval method	SFTP
Server	SFTP DNS information
Port	SFTP port. “22“ by default. Set a different port if you have customized it in the SFTP Server configuration.
File Path	Absolute path where the system stores the file in the server. It must include the file name, for example: /folder/folder/file_name.csv.
Username	SFTP username.
Password	SFTP user Password.
CSV delimiter	Delimiter set in the file that the system splits among the different columns.

After configuring the Data Source, test the connectivity by clicking on the related link Test load 20 records. With this utility, confirm that all details provided are correct and that the system has imported the data from the SFTP server in the ServiceNow import set tables.

Test load expected result

Note that the data inserted in this test cannot be transferred to the target table as it generates an import set created for testing purposes. To import data in the CMDB tables click on the Load All Records Related link.

For further reference, find the data source included in the update as an example here:

ServiceNow import via SFTP installation guide | Scheduled Import Configuration

Transform map

A transform map a set of field maps that determines the relationships between fields in an import set and fields in an existing ServiceNow table. During this part of the process, it is also possible to modify, slice or convert any data imported into the import set and store the resulting data in the final table per the requirements.

How to create a transform map

The system creates all transform maps in the table sys_transform_map. The system can create them directly in the table form. However, we recommend creating the transform map starting from the data source that will be linked to it.

There is a related list called transforms at the bottom of the data source form. If you click on this tab and click on the new button, the system redirects you to the form to create a new transform map:

Transform Map Example

In the new record form, you must populate the following fields:

Field	Details
Name	Set any name.
Source Table	Set the import set table created for the Data source.
Active	Check the box for this field.
Target Table	Final CMDB table where the system must insert the data.

Once you have set the details explained above, save the record, wait for the page to reload and then create the field mapping with the related link Mapping assist:

Field map assistant

This tool eases the task of creating field maps. Instead of creating every mapping from scratch using the related list Field Map which you can find in the transform map, we strongly recommend using the mapping assist for this purpose. If necessary, make small adjustments in the Field Map related list.

To use this tool, move the fields in the left column (Source: Data Source) and the right column (target table) to the Field Map column located in the center of the display. Then, match the order of the source and the target field in the same row of the column to ensure that you have correctly mapped them.

For example, to map the source field device.name to the field Name of the computer table, ensure both fields are located in the same row of the field map.

Finally, it is important to set coalesce fields to ensure that the system updates the records in case there is an attempt to import them in the future. For example, if the system is using the field name in the table to identify the CIs, it is important not to create duplicates. To avoid this, you must set the field as coalesce in the transform map configuration to ensure that the system updates the target table record and does not insert it again.

Name Field set as coalesce

Find further information about coalesce field behavior here:

Updating records using coalesce | ServiceNow

How to create a custom transformation

Sometimes, the incoming data from the SFTP server does not have the expected format, or the system only needs to store part of the data inserted instead of the whole value. In these cases, the mapping assist won’t be sufficient, and you’ll need to create a custom transformation.

The next section provides details on how to create a custom transformation. If you need further information, the official ServiceNow documentation is here:

• Creating a field map | ServiceNow

• Field map script variables | ServiceNow

To create a custom transformation, you must check the Use Source Script box to use a script instead of the Source field.

Once you have activated this, the source script editor will be prompted in the display:

Source Script editor

Here, you have the possibility to add any JavaScript code to compute the value that will be inserted in the target field. Note that whatever is set to be returned will be inserted in the target field set in the field map.

Apart from this, the most useful part is that any columns of the import set table columns are accessible from this script through the source object. Given this, it is possible, for example, to use this object as the base of any script:

For further reference, find the transform map included in the update set here:

ServiceNow import via SFTP installation guide | Transform map configuration

Custom transformation sample

One of the fields that is often imported from Nexthink is disk space information. As documented in the NQL Data model reference the field system_drive_free_space stores the data in Bytes format. However, the target field in the computer table to store this information expects the data to be in GBs. To resolve this situation, use a custom script:

As explained in the previous section, go to the field map configuration form and enable the Use Source Script box. When the editor appears, set the following script:

answer = (function transformEntry(source) {
    var value = source.u_disk_capacity;
 return value / Math.pow(1024, 3);
})(source);

The script shown above will store the data received from Nexthink (in Bytes) in the variable value and return the same value divided 3 times by 1024. In other words, it converts Bytes into GBs.

Scheduled import

Scheduled imports specify that the system executes a given import operation at a regular interval, which you can define as daily, weekly, periodically or a different timeframe. By default, the scheduled imports provided by the integration are inactive, as a different script will execute them programmatically. Find further details in the section below.

How to create a scheduled import

To create a scheduled import, go to the System Import Sets > Administration > Scheduled Imports module and click on the new button. This brings you to the scheduled data import new record form:

New Scheduled Import form

In the new record form, populate the following fields:

Field	Details
Name	Set any name.
Data Source	Schedule the Data Source to import the data.
Active	Check the box for this field.
Run	This is the frequency at which the system will execute the import. The fields underneath will differ depending on what you select in this field.

The most important setting above is the active setting. After you activate this field, new fields appear below the run field to configure the date or the interval details of the scheduled import. Find further details about these options in the ServiceNow documentation.

For further reference, find the scheduled import created for the application update set here:

ServiceNow import via SFTP installation guide | Scheduled Import Configuration

Executing Import procedure

There are two main methods to import the data:

Manual execution

Manually running the data import procedure requires you to create an import set and populate the import set table with Nexthink data.

For this purpose, go to the data source and click on the Load all records related link:

Load all Records related link

The system redirects you to the import status form:

Expected result

If you receive any result different from Success, refer to the error returned and double-check the configuration in order to adjust the data source settings.

On this form, click on the Run transform link, to choose where the import set and the transform map import the data to:

Once you click the transform button, the transformation will be complete, and the system will display the import status form again:

In addition to the status, you can navigate to the Import set, transform history and the import log to access the logs and confirm that the system has imported all CIs as expected.

Scheduled execution

To schedule the import set execution, go to System Import Sets → Administration → Scheduled Imports in the filter navigator. Click on New and complete the form as shown in the example below:

It is important to accurately fill in the following 3 fields:

Field	Details
Data Source	Use the Data Source that you created at the beginning.
Run As	The system will execute the scheduled import on behalf of the user listed here. Note that the roles assigned to this user may impact the whole process.
Run	Choose the frequency at which you want to run the import.

Once you have checked the active box, the way the system displays the columns will vary depending on the option you have chosen in the Run field:

• Monthly

• Weekly

• Daily

• Periodically

Click on this link for more information about scheduling.

F.A.Q.

How do I import a CI Type?

Firstly, generate a CSV file with the required Nexthink data and store it in the SFTP server.

Nexthink recommends creating a Data Exporter in the Nexthink Infinity > Integrations > Outbound connector section. Here, it is possible to export a CSV file to the SFTP server following this guide and using, for example, the following query to export columns related to the workstations.

devices
| where hardware.machine_serial_number != "" and (hardware.type == laptop or hardware.type == desktop)
| include cpus
| compute num_of_cores = number_of_cores.count(), number_of_cpus = count(), freq = frequency.sum()
| include disks
| compute disk_capacity = capacity.sum()
| list group_name, entity, hardware.manufacturer, hardware.model, operating_system.architecture, hardware.machine_serial_number, name, hardware.memory, num_of_cores, number_of_cpus, freq ,disk_capacity, operating_system.name, operating_system.build 

Once you have stored the CSV file in the SFTP server, configure the data source as explained in the how to create a Data Source section and schedule or manually execute the scheduled import as explained in the Execute import procedure section.

What happens with the reference and sys_choice fields when importing the data?

This kind of field, at first, doesn't work in the same way as any other kind of field (such as string, integer, or other types) where the data is simply inserted according to the transform map configuration.

These fields are actually referencing a different CI in another table, so the system expects that the record referenced exists before inserting the record.

The behavior of the application will differ depending on one parameter:

• If the setting Choice action is set to Create in the field map, the system creates a new choice or record in the reference table.

• If the setting Choice action is set to Ignore in the field map, the system ignores the new value from the source table, and the CI is inserted or updated anyway and the reference field remains empty.

• If the setting Choice action is set to Reject in the field map, the system skips the entire record containing the new value and the import process continues to the next row.

How do I import a CI Relationship?

Firstly, generate a CSV file with the required Nexthink data and store it in the SFTP server.

We recommend creating a Data Exporter in the Nexthink Infinity > Integrations > Outbound connector section. Here, it is possible to export a CSV file to the SFTP server following this guide and using, for example, the following query to export columns related to the workstation-software relationship.

package.installed_packages
| summarize c1=count() by package.name, package.version , device.name
| list package.name, package.version , device.name

Once you have stored the CSV file in the SFTP server, configure the data source as explained in the How to create a Data Source section and schedule or manually execute the scheduled import as explained in the Execute import procedure section.

How do the relationships work in ServiceNow?

The fields in the tables configured for the relationships are actually reference fields that point to an external table. For example, in the cmdb_rel_person table there are two fields that reference other tables:

• CI → References cmdb_ci.

• User → References sys_user.

Thus, it is necessary to decide on a strategy to follow depending on the requirements.

1. If you want to create the missing CIs of the relationship Set the Choice action configuration to Create in the field map configured within the transformations.

2. If you want to create the relationship but not the referenced CIs Set the Choice action configuration to Ignore in the field map configured within the transformations. This creates relationships where one of the keys is empty. This option is not recommended.

3. If you don’t want to create the relationship when any of the CIs are missing If the setting Choice action is set as Reject in the field map, the entire record containing the new value will be skipped and the import process will continue to the next row.

What happens if I have other import mechanisms running at the same time?

If there are different connectors importing data into the CMDB tables, ensure that you have accurately configured the coalesce fields. As explained in the How to create a transform map section, the behavior of the connector depends on the coalesce fields. Then, if any record is found in the target table, where the value of the coalesce field already exists, the system updates the CI with the information of the last import set.

Note that the system does not use the IRE engine at any point during this procedure. This means, the system does not use any identification rules at all to merge data inserted from different sources.

Can I execute this process automatically?

Unfortunately, it is not possible at the moment.

The limitation comes from the fact that the system must update the file path of the data sources before it can import the data. A future release might provide an automatic mechanism to solve that.

It is possible to add an extra level of automation using a flow that calls an action with an SFTP step where the Get File List lists the CSV files stored on the SFTP server.

What happens if the system imports a file twice?

If you have scheduled the import process twice without updating the filepath, the system will attempt to insert the same information in the CMDB. Thus, two different import sets will be available in the sys_import_set table, where the first one will display all records as inserted/updated and the second one will ignore/skip all records whose information is already identical in the target table.

In future releases, we will provide an automatic mechanism to avoid the step of manually updating the filepath in the data source.