Microsoft Azure Data Services Integration into ServiceNow
Published Dec 12 2019 12:01 AM 41.4K Views
Microsoft

SNOW and MSFT.jpg

ServiceNow is a SaaS based application that provides service management software via multiple offerings such as:  IT services management (ITSM), IT operations management (ITOM) and IT business management (ITBM).  ServiceNow is a leader within the Gartner Magic Quadrant and holds a position in the top right of the quadrant.  ServiceNow ITSM, provides the ability to track/monitor tickets created and resolve quickly and effectively.  For users who want to analyze incidents and track progress, ServiceNow does provide out of the box reporting and analytics capabilities. In our use scenario, our customer wanted the ability to leverage an analytics platform like PowerBI in order to slice and dice and visualize the data in a variety of ways.  More specifically, there was a need to understand cross functional ITSM impacts on other areas of the organization to develop a comprehensive view and explore potential metrics and KPIs through a single integrated data view. Hence, extracting data from ServiceNow into Azure and/or PowerBI integration into ServiceNow was established and a defined requirement.

 

In addition, our customer wanted to create a POC to apply ML/AI on ITSM operations to understand root cause analysis and directly impact cost.  There is a finite and measurable cost for each ticket created.  The sooner major incidents can be identified and resolved, the more they would save on costs.  ML/AI leveraged models that could identify root cause, minimize duplication of tickets, reduce time to resolution and further reduce the operational complexity of managing a large number of open tickets all resulting in a simplified, better managed process with a measurable cost savings.

 
This post will demonstrate the integration options between Azure and ServiceNow as well as leveraging Azure to apply AI/ML on some of the scenarios described earlier.  We will share some of the learnings we had as we went through this journey.   Various integration points between Azure and ServiceNow:

 

  1. Azure Data Factory to ServiceNow Integration
  2. Connect directly to ServiceNow with PowerBI using the SIMBA driver
  3. Leverage PowerBI Premium ML in order to execute models for use cases described above leveraging ServiceNow data

 

At a high level, the solution will look as follows:

 

solution overview.png

 

Azure Data Factory ServiceNow Connector Integration

With Azure Data Factory, there are two integration options into ServiceNow:

  1. ServiceNow Connector out of the box or
  2. REST API Connector

ServiceNow Connector

In you Azure Data Factory, create a new connection and search for ServiceNow as shown below

servicenowconnectorADF.png

Configure the ServiceNow connectivity:

servicenowLink.png

Key takeaways from the ServiceNow connectivity option:

  • The connector is easy to configure and provides access to the out of the box tables and fields in ServiceNow.
  • If you are looking for the Problems or Incidents table in ServiceNow, this connector is a great way to get started. However, in our scenario we had many user defined tables and fields in ServiceNow and this connection option does not yet support user defined types.  Therefore, we went ahead and tested the next option, REST connection
     

REST API Connector

In Azure Data Factory, create a new connection and search for REST as shown below

REST api image.png

Configure the REST API to your ServiceNow instance. 

configureRESTapiADF.png

Key takeaways from the REST API connector option:

  • Uses the REST API access capabilities provided by ServiceNow
  • The Base URL will look something like: https://orgdomain.service-now/api/now/
  • We used basic authentication to provide REST API access
  • The end point and relative URL constructed by using ServiceNow REST explorer. Please see the screenshot below from ServiceNow portal:

Login to ServiceNow and Search for “REST”

  • You can use it to GET/CREATE/RETRIEVE/etc… records from sources.

SNOW restapi.png

  • In our case, we want Incidents and Problems data so we can build some analytics and apply AI/ML to this data.

As a side note, we found the Tables/Schemas documentation for ServiceNow that was relevant for the areas we were interested in like Problems leading to one or many Incidents.  The ServiceNow models and schemas provided were a great way to understand what was available to us and what table elements we would need.  This documentation can be found in the ServiceNow docs.

 

 

Here is a sample of the Incidents table and its associated relationships:

snow incidents and data models.png

 

Once you’ve established the linked service, you can even filter using the REST APIs as shown below.

RESTAPI Connector.png

Key takeaways from configuring the REST API connector:

  • REST API call allows data filtering; we can use the ServiceNow REST explorer to construct the relative URL with extra parameters including data filters.
  • The relative URL can be dynamically constructed by using Azure Data Factory expressions, functions and system variables. In our case, we are only interested in the last 365 days of Incidents (adddays(utcnow(‘yyyy-MM-dd’),-364)…

snowconnector with user defined.png

  • If you click on “Mappings”, you can see with the REST API user defined fields like “u_rca_status” and “u_major_incident”
  • In our scenario, we want to extract data from ServiceNow and put it into Azure SQL PaaS instance so we can execute queries without impacting ServiceNow. Along the way, we discovered ADF natively translates the JSON schema which is mapped to the target table

PowerBI Desktop to ServiceNow via SIMBA driver

We discovered that PowerBI provided a ServiceNow app that provided an out of the box dashboard into ServiceNow however after we discovered it, it was subsequently removed as an option and hence we had to look for alternative connectivity options like the SIMBA driver

 

The SIMBA is a 3rd party vendor that driver provides JDBC/ODBC connectivity to ServiceNow.  We installed the SIMBA driver and was able to connect relatively easily to ServiceNow using an JDBC/ODBC driver. The SIMBA driver was also able to read the user defined tables and columns as the Azure Data Factory REST API described earlier

Leveraging the SIMBA driver, we were able to directly query ServiceNow for our targeted tables like Incidents and Problems as shown below:

 

pbisimbadriver.png

 

PowerBI Premium ML

 

We used a subset of Microsoft’s support ticket data; our subset contained only a description of the ticket and the category assigned. We wanted to train our machine learning model to effectively predict the category given the support ticket description in plain text. PowerBI Premium provided AutoML (Automated Machine Learning) capability that let users build ML models without writing any code. Behind the scenes AutoML iteratively trains several models optimizing model accuracy to return the best model for a given dataset.

 

After creating a PBI workspace for the project, we created a dataflow that contains all the entities (data connection, datasets and ML models).

powerbipremium.png

To link our dataset, stored as an Azure SQL table, we added an entity to our dataflow setting data source as an Azure SQL DB

powerbipremium azuresqldb.png

Specify the connection settings and set query to make sure that data is in the format you want for your model

powerpremium query.png

To use AutoML to train our ML model, click ML icon in actions for our entity.

mlmodeledit.png

 

Pick Category field as an outcome (what we want to predict) and classification as ML problem, select Description field as data to study, provide a name for your model and hit Save and train. We may also pick how long we want training to continue.

mlmodeltraing.png

 

Once the model is trained, training report shows various metrics on model efficiency and the top predictors as well as training details including how many models were trained to find the best model

ml1.png

 

 

ml2.png

 

 

We can also apply model on new data periodically (e.g., every hour) to get the predicted category given support ticket description which can help IT teams to effectively direct expert resources

 

Key takeaways from PBI Machine Learning:

  • Loading data from blob storage or other storage services in Azure (e.g., Azure SQL DB) is the best practice for effective data I/O
  • AutoML can run for several minutes (and potentially hours) as it trains several models improving the desired accuracy to provide the best model.
  • During exploration and development, limiting the model training time helps in faster experimentation

Wrap Up

In summary, in this blog we were able to demonstrate the following:

  • ServiceNow integration into Azure with Azure Data Factory Connectivity Options
  • Leveraging the PowerBI Platform to do comprehensive analytics on ServiceNow data as well as merge additional data sources together in a single repository
  • Apply Machine Learning via PowerBI Premium in order to apply root cause analysis and major incident categorizations

 

About the authors:

 

 

 

 

4 Comments
Copper Contributor

Hi @alpkaya, thanks for this great blog, it is really useful. 

I have a question, I am getting an error when I try to connect Servicenow with OAuth2 authentication type. for Basic Authentication, there is no issue. 

All required information is ok. (endpoint, user, password, ClientId, Client Secret)

 

The Error message is:

ERROR [HY000] [Microsoft][ServiceNow] (1017) Malformed response received from Authentication server that lacks following information: "Auth_AccessToken" located at "access_token" from the server response; "Auth_RefreshToken" located at "refresh_token" from the server response; "Auth_Expires" located at "expires_in" from the server response. 
 
I could not find any related page about ADF - Servicenow connnection with OAuth2. Do you have any idea about the problem? 
Thanks in advance. 
Mustafa
Copper Contributor

Hello,

I need help as I'm facing this blocker -  My goal is to put data in the SQL database using copy data tool from ServiceNow rest API, but I'm not able to find azure SQL database as an option in my destination data store. 

 

What could be the reason and what can I do for it? Please suggest. 

Copper Contributor

Thanks for a great article.

 

It seems that the ServiceNow connector still does not support user defined fields (June 2021), does anyone know if this is being looked into? We are also forced to go with building a REST API based pipeline in ADF.

 

If the connector does not support user defined fields most enterprise customers will not be able to use it in my opinion makes it almost useless.

Copper Contributor

Hi All

Same problem as Mustafa, only basic authentication is allowed connecting from Data Factory to Service Now.

Connecting OAuth2 I get this error.  I had to create a local user account on Service Now with Admin rights to resolve. 

The Error message is:

ERROR [HY000] [Microsoft][ServiceNow] (1017) Malformed response received from Authentication server that lacks following information: "Auth_AccessToken" located at "access_token" from the server response; "Auth_RefreshToken" located at "refresh_token" from the server response; "Auth_Expires" located at "expires_in" from the server response. 
 
Is this on Micro Soft Roadmap to allow connection with OAuth2?
Alan
 
Version history
Last update:
‎Dec 18 2019 05:31 AM
Updated by: