IntegrationHub ETL Introduction

[Siyou Li]

What is IH-ETL 

• Create and manage ETL transform maps

• Integrate third-party data into the CMDB without compromising the integrity of the CMDB

• Simplified user interface that guides you through the integration process end-to-end

• Includes test integration run of sample data 

Associated Components 

• Robust Transform Engine (RTE): Used to transform raw source data that is stored in staging tables, into the data that is mapped and integrated into the CMDB. RTE uses ETL transform maps that were created for the integration during data transformation.

• Identification and Reconciliation engine (IRE): Used as a centralized framework for identification and reconciliation processes across different data sources. IRE processes help maintain data integrity in the CMDB. 

IntegrationHub ETL uses RTE and IRE which work together to process and integrate data. Data is first imported from a data source, and is then stored in temporary staging tables in Import Sets systems. Using the data in the staging tables and the ETL transform map created by IntegrationHub ETL, RTE creates IRE payloads which are then processed by IRE. IRE applies reconciliation processes to avoid potential problems such as duplicate CIs, ensuring that the CMDB remains healthy, and then integrates the resulting data into the CMDB. 

IH-ETL Workflow

At the high level, to create an ETL transform map you need to:  

• Setup a Data Source configuration in standard platform UI to allow for data ingestion into the platform

• Input your transform map’s basic detail such as name, discovery source etc. in IH-ETL UI

• Preview your data and apply data transform operations (e.x. copy, to lower/upper case) if necessary in IH-ETL UI

• Define the CMDB class, field and relationship mapping plan in IH-ETL UI

• Run sample integration and preview how the data looks like after inserting into CMDB in IH-ETL UI

• After running sample integration, choose to retain or rollback the sample run data in IH-ETL UI

How to install IH-ETL 

IH-ETL is automatically entitled on all sub-prod instances, all ServiceNow instances and all ServiceNow developer portal instances. If you need to entitle on your instance, please go to ServiceNow store and download the latest IH-ETL store app.

Roles Required 

Users with the cmdb_inst_admin role can use IntegrationHub ETL to create integrations, or customize a pre-existing integration provided by ServiceNow or a vendor at the ServiceNow Store. A vendor can create a new integration and provide it as an application for anyone to use.

Terminology Definitions 

Discovery source  

• A choice list value on a Configuration Item that is used to identify the source of ingested data.  

Data Source  

• The definition of how data is ingested into the integration.  

Staging Table  

• A table defined to accept the incoming data. Columns can be dynamically assigned by loading data through the data source. Any column created in this way has the prefix “u_”. 

Import Set  

• The record that encapsulates a single ingestion of data. It relates to multiple rows in the staging table and references the Data Source.  

Robust Transform Engine (RTE)  

• The mapping and transformation engine that takes in the definition for how data should be converted from an object to an IRE payload.   

Identification and Reconciliation Engine (IRE)  

• The engine that inserts records into the CMDB in the correct classes. It also provides functionality to deduplicate and order inserts to avoid duplicate data or incorrect updates. 

Additional Resources

Landing page

IH-ETL product documentation

Now Learning Course – K21 Import CMDB Data Safely Using IH-ETL

Integration Hub - ETL nested payload feature demo - NEW in Quebec

IH-ETL common error scenarios

Next Step

Prepare your data before creating transform map

[KKrabbenhoft]

Siyou Li,

Thank you for sharing this. I am a newbie tasked with being able to leverage this process for our CMDB. 

[GoBucks]

@Siyou Li- Can the IH-ETL workflow accept inbound REST as a datasource?  For example, could a custom 3rd party product send an inbound REST POST JSON payload to the instance that IH-ETL could then process?  Can it work like that?  OR, does it only work by the SN instance initiating the REST call to GET and then import the data?

Also: We are currently using a scripted REST API that accepts an inbound REST POST that contains a JSON payload.  The scripted REST API resource then parses the JSON and inserts the data to various import set staging tables accordingly.  It's a simple payload representing various hardware CIs.  But we'd like to include relationship data which means the JSON payload would get a bit more complex with nested data.  I see IH-ETL as being really helpful with this, but what we send to our instance in the inbound REST POST is essentially from a custom script on a server somewhere.  Its not a specific 3rd party product like some of the examples in the docs (SCCM).  Can IH-ETL work with such custom inbound data sources like this as well?

Thanks

[Siyou Li]

Hi @GoBucks,

IH-ETL is agnostic of data source type, i.e. it does not know or control the types of data source. Instead, IH-ETL uses the import sets that are pulled from standard platform data source. 

So here the question should be does ServiceNow platform sys_data_source accept inbound REST. I would recommend to refer to ServiceNow data source document below:

https://docs.servicenow.com/bundle/vancouver-integrate-applications/page/administer/import-sets/conc...

From your post, I see that both REST and Scripted could be your options in sys_data_source config.

Thanks,

Siyou

[GoBucks]

@Siyou Li, thanks for the reply.  What I am still wondering is:  it seems like a Data Source is something that the instance retrieves on a schedule, or is provided with (in the case of a file upload).  But what about a 3rd party system that initiates the sending of the data making an inbound REST call (a POST) that delivers a payload-- can SN still capture that incoming payload of data as a Data Source and proceed to use it as if it retrieved it?

In other words, SN would NOT have credentials to connect to the 3rd party system to retrieve data for a Data Source (a pull).  Instead, the 3rd party system is what connects to SN via basic auth over https, for example, and provides the data to SN on its own schedule (a push).  With a push of data in to SN in this way, can IH-ETL still be used?

[Christian Alm]

Hey, lifting this old thread as we are in the need of the same scenario GoBucks mentions, ie. the data is pushed to us and we need to handle it. Is there a tutorial to set that up?

[MichaelTomar]

Thank you for sharing the ServiceNow Community article on IntegrationHub ETL. It offers valuable insights into integrating third-party data into the CMDB while maintaining data integrity. For an alternative solution, you might consider using Skyvia. Skyvia provides a versatile cloud data integration service that can simplify the process of integrating data from various sources into ServiceNow, potentially offering a more streamlined approach than manual ETL processes.

[Community Alums]

I'm in the need of the same scenario GoBucks mentions, ie. the data is pushed to us and we need to handle it. Is there a tutorial to set that up?

[Siyou Li]

Hi @Community Alums  @GoBucks @Christian Alm ,

The raw data import problem seems more like a ServiceNow platform question. There are a lot of ways users can import raw data into ServiceNow. I would suggest to reach out to ServiceNow support for more information about all the methods you can use to import raw data.

IH-ETL does not handle how the raw data is being retrieved from outside. As long as the raw data is from a standard ServiceNow sys_data_source config, and this data source config can pull raw data into a standard ServiceNow sys_import_set, then IH-ETL can support processing/transforming these data from those sys_import_set.

Thanks,

Siyou

[Community Alums]

Support for non-CMDB tables

Starting with the Washington DC release, IntegrationHub ETL supports the integration of third-party data into some non-CMDB tables. IntegrationHub ETL supports those non-CMDB tables that are supported by Identification and Reconciliation (IRE).

Supported non-CMDB tables are available in IntegrationHub ETL when specifying classes, conditional classes, class associations, and reference sources in mapping definitions. However, there are some differences between using CMDB classes and non-CMDB tables in IntegrationHub ETL:

• Specifying class associations isn't mandatory for non-CMDB tables.
• Adding relationships doesn't apply to non-CMDB tables.
• Class associations for a non-CMDB table is based on a reference field instead of a CMDB relationship.

Note: Although the IntegrationHub ETL user interface and accompanying documentation references CMDB and CMDB elements, most of those references also apply to supported non-CMDB tables.

[jsb81]

wondering if using IntegrationHub ETL and CMDB can be used to bring data from other platforms for reporting purpose?