ServiceNow Incident Management

• Process description
  • Available tags & due dates
• Extraction
  • System configuration
  • Setting up the source connection in CData Sync
  • Setting up the destination connection in CData Sync
  • Creating the extraction job in CData Sync
• Input fields
  • Input types
  • Input tables & fields
• Configuring transformations
  • Seed files
  • Dbt variables
• Design specifications
  • Objects
  • Activities
  • Design details

Process description

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. Incidents 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.

Available tags & due dates

The following tags are available by default.

Due dates

ServiceNow has a complete framework for tracking and categorizing SLA. Therefore, the Due dates for this app template 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.

Extraction

This app template uses ServiceNow (versions up to the version "Rome") as source system.

Extraction for the system is done using CData Sync. To set-up the extraction, make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

To be able to extract data from ServiceNow the following configuration in ServiceNow is required.

Read permissions

Assign 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.

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

Setting up the source connection in CData Sync

• In CData - Connections, add a new source connection for ServiceNow. If this option is not in the list, click + Add More, search for ServiceNow and click Download & install
• Select Basic as the Auth Scheme.
• Enter the correct URL, User and Password for the account.
• On the Advanced tab, enter the value displayvalue=ALL; for the Other (Optional) parameter in the Other section.
• Create and test the connection.

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

• In CData - Jobs, create a new job.
• Select the source and destination connections created for the ServiceNow Incident Management app template and create the job.

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

• Select the Alter Schema option.
• Select the Drop Table option.
• If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.

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

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tasks 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

Input types

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

Input tables & fields

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 incidents.

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.

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Dbt variables

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

Design specifications

Objects

Activities

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

• The value of the Definition should be added for the Name filter list in Incident_metric.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_base.sql by using a UNION ALL function.

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

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

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.`Creation_date` as `Event_end`,
		Incident_metric.`Incident_ID`,
		Incident_metric.`Definition`,
		'New activity' as `Activity`
	from Incident_metric
	-- New activity mapped, based on the 'Definition'
	where Incident_metric.`Definition` = 'New activity definition'
),

New_activity_definition_events as (
	select
		New_activity_definition.`Event_end`, 
		New_activity_definition.`Incident_ID`,
		New_activity_definition.`Definition`,
		New_activity_definition.`Activity`,
		Assigned_to_duration_times.`Value` as `User`,
		Assignment_group_times.`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.`Incident_ID` = New_activity_definition.`Incident_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.`Incident_ID` = New_activity_definition.`Incident_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

Design details

Known issues

• The timestamps stored in the database might be different from the display value in the ServiceNow interface.
• Some incidents 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. 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.