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:
- Azure Data Factory to ServiceNow Integration
- Connect directly to ServiceNow with PowerBI using the SIMBA driver
- 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:
Azure Data Factory ServiceNow Connector Integration
With Azure Data Factory, there are two integration options into ServiceNow:
- ServiceNow Connector out of the box or
- REST API Connector
ServiceNow Connector
In you Azure Data Factory, create a new connection and search for ServiceNow as shown below
Configure the ServiceNow connectivity:
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
Configure the REST API to your ServiceNow instance.
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.
- 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:
Once you’ve established the linked service, you can even filter using the REST APIs as shown below.
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)…
- 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:
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).
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
Specify the connection settings and set query to make sure that data is in the format you want for your model
To use AutoML to train our ML model, click ML icon in actions for our entity.
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.
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
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:
Microsoft
