ServiceNow Incident Management

• Introduction
  • Incident Management process
  • Tags
  • Due dates
• ServiceNow configuration
  • Source system
  • Environmental settings
  • System specific settings
• Configuring CData Sync for ServiceNow
  • Setting up the source connection
  • Creating the extraction job
    • Setting up environment variables in the extraction job
    • Table Replication
• Input fields
  • Input types
  • cmdb_ci
  • incident
  • task_sla
  • contract_sla
  • metric_definition
  • metric_instance
• Design specifications
  • Entities
  • Activities
• Customizing the transformations
  • Dbt variables
• Limitations, known issues, common problems
  • Known issues
  • Common problems

Introduction

With the ServiceNow Incident Management app template you can load raw input data from ServiceNow for the ServiceNow Incident Management process, extracted to either Snowflake or SQL Server using CData Sync. The raw input data is transformed through a series of transformations that take place via dbt (data build tool) and produce the required input data used in Process Mining process apps.

Incident Management process

The Incident Management process is the end-to-end process which starts with the opening of an incident up to the resolution and closing of the incident. Other steps can be assigning the incident to correct teams and users, working on, and resolving incidents. Cases can be in a pending status, which is usually related to further information being needed to resolve the incident. Reassignment to different teams and users may occur during the process. For example, if the initial assignment was incorrect, or if the user was assigned or the incident needs attention from multiple users and/or teams during its resolution.

The Incident Management for ServiceNow app template enables:

• monitoring multiple levels and categories of SLA compliance.
• identifying processes that do not follow the correct path.
• tracking the average resolution time of incidents.
• identifying top service agents.

Tags

Due dates

ServiceNow has a complete framework for tracking and categorizing SLA. Therefore, the Due dates for this connector were created in a way that utilizes the different SLA names created in ServiceNow's own system. Each company will have their own categories and priority levels for SLA shown out of the box. ServiceNow stores the maximum duration in contract_sla table for each SLA category and the planned end time in task_sla table for each incident number (time for reaching 'resolved' state). Using both information, it is possible to obtain the last SLA category assigned to an incident number and to correctly set the due dates.

ServiceNow configuration

Source system

ServiceNow systems (versions up to the version "Rome")

Environmental settings

In order to connect via CData, the user must have appropriate read permissions on each of the objects which are being extracted. All fields included in the input tables must be visible for the user extracting the data.

System specific settings

The ServiceNow Incident Management app template requires the following ServiceNow objects:

• incident
• metric_definition
• metric_instance

Additional events are included by tracking priority changes, assignment changes and pending status. However it is possible to add activities by tracking the Mi_definition from the supporting table "Incident_metric_input".

In order to obtain the due dates, it also necessary to have access to the following objects.

• task_sla
• contract_sla

In order to obtain the Configuration Item name from the Configuration Management Database it is also necessary to have access to the following object.

• cmdb_ci

Configuring CData Sync for ServiceNow

Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining ServiceNow Incident Management process app.

In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using ServiceNow, pay attention to the steps described below.

Setting up the source connection

• Select ServiceNow as the source system to which you want to create a connection from the list.
• Select Basic as the Auth Scheme.
• Enter the correct URL, User, and Password to set up the connection to ServiceNow.
• On the Advanced tab, enter the value displayvalue=ALL; for the Other (Optional) parameter in the Other section.

Creating the extraction job

Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.

• Select the Alter Schema option.
• Select the Drop Table option.

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

In order to setup the environment variables:

<!-- Modify variables here. Variable start_extraction_date must be populated. In case a specific end date is needed, replace now() with the required date in yyyy-MM-dd format  -->
<api:set attr="out.env:start_extraction_date"  value="1900-01-01" />
<api:set attr="out.env:end_extraction_date"  value="[null | now() | todate('yyyy-MM-dd')]" />
<api:push item="out" />

Important: Do not modify the api:info details that are shown by default.

In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tables tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

REPLICATE [metric_instance] SELECT [sys_id], [id], FORMAT([sys_created_on], 'yyyy-MM-dd hh:mm:ss') as [sys_created_on], [value], [definition] FROM [metric_instance] WHERE ([sys_created_on] >= '{env:start_extraction_date}') AND ([sys_created_on] <= '{env:end_extraction_date}');
REPLICATE [cmdb_ci] SELECT [name_display_value], [sys_class_name_display_value], [sys_updated_on] FROM [cmdb_ci] WHERE ([sys_created_on] >= '{env:start_extraction_date}') AND ([sys_created_on] <= '{env:end_extraction_date}');
REPLICATE [contract_sla] SELECT [name_display_value], [duration] FROM [contract_sla] WHERE ([sys_created_on] >= '{env:start_extraction_date}') AND ([sys_created_on] <= '{env:end_extraction_date}');
REPLICATE [task_sla] SELECT [sla_display_value], [task_display_value], FORMAT([planned_end_time], 'yyyy-MM-dd hh:mm:ss') as [planned_end_time], [stage] FROM [task_sla] WHERE [stage] = 'completed' AND ([sys_created_on] >= '{env:start_extraction_date}') AND ([sys_created_on] <= '{env:end_extraction_date}');
REPLICATE [metric_definition] SELECT [sys_id], [name], [table] FROM [metric_definition] WHERE [table] = 'incident';
REPLICATE [incident] SELECT [sys_id], [sys_updated_on], [assigned_to_display_value], [assignment_group_display_value], [caller_id_display_value], [category_display_value], [close_code_display_value], [cmdb_ci_display_value], [contact_type_display_value], [priority_display_value], [number], [state_display_value], [upon_approval_display_value], [upon_reject_display_value], [urgency_display_value] FROM [incident] WHERE ([sys_created_on] >= '{env:start_extraction_date}') AND ([sys_created_on] <= '{env:end_extraction_date}');

Input fields

The following tables include the list of fields per input table.

Input types

Below is an overview of the different field types and their default format settings.

cmdb_ci

Table that contains base configuration item information.

incident

Table that contains data related to each incident number. A filter is applied on incident_input to get only the last record (Last_line = 1) for each incident number. This filter is necessary because some incident numbers may have multiple entries on this table.

task_sla

Data related SLA and due dates.

contract_sla

Contains data related to the duration for each SLA type. This table is needed in conjunction with task_sla table to obtain the last SLA record (due date type) assigned to case ids.

metric_definition

Defined metrics can track how long an audited field holds a certain value.

metric_instance

A metric instance is a record in the metric_instance table.

Design specifications

Entities

Cases_base

Note: The Cases_base table filter Cases IDs based whether the table Incident_metric_input have a record for that same Case ID. This is used to guarantee that all Case IDs will have at least one activity.

Activities

For adding new activities, one will have to identify the Mi_definition from the incident_metric input table that relates to the activity being added.

• The value of the Mi_definition should be added for the Name filter list in Incident_metric_input.sql.
• A new SQL file should be added to 3_events folder like the example SQL below.
• The new table should be added to the Events_all.sql by using a UNION ALL function.

The following example shows the SQL on how to add a new activity.

with Incident_metric_input as (
	select * from {{ ref('Incident_metric_input') }}
),

Assignment_group_times as (
	select * from {{ ref('Assignment_group_times') }}
),

Assigned_to_duration_times as (
	select * from {{ ref('Assigned_to_duration_times') }}
),

New_activity_definition as (
	select 
		Incident_metric_input."Event_end", 
		Incident_metric_input."Case_ID", 
		Incident_metric_input."Mi_definition", 
		'New activity' as "Activity"
	from Incident_metric_input
	-- New activity mapped, based on the "Mi_definition"
	where Incident_metric_input."Mi_definition" = 'New activity definition'
),

New_activity_definition_events as (
	select
		New_activity_definition."Event_end", 
		New_activity_definition."Case_ID",
		New_activity_definition."Mi_definition",
		New_activity_definition."Activity",
		Assigned_to_duration_times."Mi_value" as "User",
		Assignment_group_times."Mi_value" as "Team",
		-- Where 'number' must be filled accordingly to the logic for ordering the activities. 
		'number' as "Activity_order"
	from New_activity_definition
	left join Assigned_to_duration_times ON Assigned_to_duration_times."Case_ID" = New_activity_definition."Case_ID" 
		and New_activity_definition."Event_end" between Assigned_to_duration_times."Next_start" and Assigned_to_duration_times."Next_end"
	left join Assignment_group_times ON Assignment_group_times."Case_ID" = New_activity_definition."Case_ID" 
		and New_activity_definition."Event_end" between Assignment_group_times."Next_start" and Assignment_group_times."Next_end"
)

select * from New_activity_definition_events

Customizing the transformations

Dbt variables

Below is an overview of the variables that must be configured. These variables are defined in the dbt_project.yml file.

Limitations, known issues, common problems

Known issues

• The timestamps stored in the database that might be different from the display value in the ServiceNow interface.
• Some Case_IDs may have some duplicated activity 'Close Incident', these duplicated records come from the ServiceNow system.

Common problems

Loading a large amount of data may cause a timeout failure in the job. If this happens follow the instructions below:

• Open the job in CData Sync.
• On the Advanced tab reduce the Batch size and/or increase the Command Timeout.

Note: Recommend values for loading a large amount of data is Batch size = 10000 and Command Timeout = 5000. This can be increased or decreased depending of the amount of records.