Jira Incident Management app template


Introduction

With the Jira-Incident-Management app template you can load raw input data from Jira for the Jira 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 related to issue tracking; from the moment the issue is reported until its solution. The process takes several steps from opening new issues, assigning them to correct users, working on them, to resolving and closing issues. In some cases, some time can be spent on pending status (usually related to a requisition for further information needed to resolve an issue) or waiting for approval status. Also, reassignments for the different users may occur during the process depending if the first assignment was correct, if the user assigned is actually the best option for resolving any specific issue or if the issue needs attention from multiple users during its resolution.

Tags

Tag Description Value added to analysis
Resolved in less than a day Issue has "Set resolution to Done" activity in less than a day This indicates that an issue was resolved in less than a day, which can also allow visibility into the efficiency with which the problem is handled.
Estimated time set multiple times The Issue has multiple estimated times set The time estimated is used to identify the expected time it will take the consultant to complete their tasks. Changes to the estimated time might impact the correct expected date and can show inefficiencies in the process.
Linked to other issue Identify when an Issue_id is linked to another Issue_id Linked issues may show the ramification of a single issue into multiple issues. The resolution of the parent issue might bring the resolution of multiple other issues. Therefore all these issues will have similar throughput times and also be part of the same process.
Multiple assignments The Issue has multiple user assignments This either represents a lack of skills of the first assigned user to resolve the Issue or inefficiencies in identifying the best qualified user to work on it.
Unassigned issue Identify if the Issue does not have a user assigned Opening Issues without an assigned user shows inconsistency to the assignment process. This lack of information might offer a challenge for tracking the expert that resolved/worked on the Issue. It could also reflect issues that were left without anybody working on them for a long while.
Reopen A ticket is reopened after it was resolved (An activity 'Remove resolution status' happens after the setting the resolution). The reopening of a ""Resolved"" Incident might indicate that the solution provided for an Incident was not satisfactory. This could mean that the support user rushed to close it to comply with the SLA providing a less optimal service.

Due dates

Due date Description Value added to analysis
Time to resolution Time to resolution SLA Complying with the SLA is the most important performance tool used when assessing the overall quality of an Incident management service. It will always be frowned upon when Incidents don't meet the expected due dates for their resolutions.

The "Stop_time" and "Breach_time" (actual date and expected date for the due dates) fields are extracted from the json-formatted field "Time to resolution" in Issues. It is not possible to know in advance what activity Jira is using for calculating this SLA, as the latter can contain multiple customizations. Therefore, the last Event_ID of each Case is selected for the Due_dates.sql, even if there is no way to be sure that the selected event is the actual trigger for the due date. The SLA is a custom setup in Jira. The due date created will track the exact same Time to resolution SLA" from Jira's interface.

Note: The "Stop_time" may not always be the same as the "Resolution_date".


Jira configuration

Source system

Jira Service Management (cloud versions)

Environmental settings

Ïn 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 Jira Incident-Management app template requires the following Jira objects:


Configuring CData Sync for Jira

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

Generating an API token in Jira Service Management

Follow these steps to generate an API token that you need to set up a source connection in CData Sync.

Step Action
1 Go to Jira Service Management environment url, click on Your profile and settings and select Manage Account.
2 In the new page, select Security menu and search for Create and manage API tokens.
3 Click in Create API token, name it as you prefer and press Create.
4 A new API token will be created, copy it and save for the next step.

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 Jira, pay attention to the steps described below.

Setting up the source connection

Creating the extraction job

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

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.

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.
project_key_value Project Identifier Mandatory
project_key_value_like Project Identifier Mandatory
issue_type_name_value Issue type Identifier

Jira Service Management may have multiple projects. The project_key_value and project_key_value_like should be used to identify the correct project being mapped.

Because the Jira Service Management app template uses the Issue Number as the main case ID, consideration should be made when choosing an extraction date, as it will be used across all objects. In order to setup the environment variables:

Step Action
1 Go to the JOBS tab and open the extraction job created in 4: Creating the extraction job.
2 Go to the Events tab in the Job Settings panel.
3 Edit the Pre-Job Event section to add the code displayed below after `<!-- Code goes here -- >.
4 Click on Save Changes.
<!-- 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="2022-01-01" /> <api:set attr="out.env:end_extraction_date" value="[null | now() | todate('yyyy-MM-dd')]" /> <!-- Identify and replace the correct project key for the following variables. The project key value identifies the projects from Jira that will be extracted. In case the client decide to have all the projects in a Jira environment mapped remove the next two lines and the filters for them in the replicate script. --> <api:set attr="out.env:project_key_value" value="PROJ" /> <api:set attr="out.env:project_key_value_like" value="PROJ%" /> <!-- Identify and replace the correct issue type(s) for the following variable if needed. --> <api:set attr="out.env:issue_type_name_value" value="%Incident" /> <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 [Issues] SELECT [Id], FORMAT([Updated], 'yyyy-MM-dd hh:mm:ss') as [Updated], [AssigneeDisplayName], FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [CreatorDisplayName], [Description], [IssueTypeName], [Key], [OriginalEstimateSeconds], [PriorityName], [ProjectName], [ReporterDisplayName], FORMAT([ResolutionDate], 'yyyy-MM-dd hh:mm:ss') as [ResolutionDate], [ResolutionName], [SecurityLevel], [Severity], [StatusName], [Time_to_resolution] FROM [Issues] WHERE ([Created] >= '{env:start_extraction_date}') AND ([Created] <= '{env:end_extraction_date}') AND ([ProjectKey] = '{env:project_key_value}'); REPLICATE [IssueChangelogs] SELECT FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [IssueId], [ItemField], [AuthorDisplayName], [ItemToString] FROM [IssueChangelogs] WHERE (ItemField IN ('resolution', 'status', 'timeestimate','Link','assignee','Product categorization', 'Operational categorization','Impact', 'Urgency', 'labels','Request participants', 'Component', 'Severity', 'priority')) AND ([IssueKey] LIKE '{env:project_key_value_like}'); REPLICATE [Statuses] SELECT [Name], [CategoryKey] FROM [Statuses];

Other scenarios

In case all the projects should be mapped the following replicate query should be used.

REPLICATE [Issues] SELECT [Id], FORMAT([Updated], 'yyyy-MM-dd hh:mm:ss') as [Updated], [AssigneeDisplayName], FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [CreatorDisplayName], [Description], [IssueTypeName], [Key], [OriginalEstimateSeconds], [PriorityName], [ProjectKey], [ProjectName], [ReporterDisplayName], FORMAT([ResolutionDate], 'yyyy-MM-dd hh:mm:ss') as [ResolutionDate], [ResolutionName], [SecurityLevel], [Severity], [StatusName], [Time_to_resolution] FROM [Issues] WHERE ([Created] >= '{env:start_extraction_date}') AND ([Created] <= '{env:end_extraction_date}'); REPLICATE [IssueChangelogs] SELECT FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [IssueId], [ItemField], [AuthorDisplayName], [ItemToString] FROM [IssueChangelogs] WHERE (ItemField IN ('resolution', 'status', 'timeestimate','Link','assignee','Product categorization', 'Operational categorization','Impact', 'Urgency', 'labels','Request participants', 'Component', 'Severity', 'priority')); REPLICATE [Statuses] SELECT [Name], [CategoryKey] FROM [Statuses];

In case only certain types of issues should be mapped then use the following replicate query.

REPLICATE [Issues] SELECT [Id], FORMAT([Updated], 'yyyy-MM-dd hh:mm:ss') as [Updated], [AssigneeDisplayName], FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [CreatorDisplayName], [Description], [IssueTypeName], [Key], [OriginalEstimateSeconds], [PriorityName], [ProjectName], [ReporterDisplayName], FORMAT([ResolutionDate], 'yyyy-MM-dd hh:mm:ss') as [ResolutionDate], [ResolutionName], [SecurityLevel], [Severity], [StatusName], [Time_to_resolution] FROM [Issues] WHERE ([Created] >= '{env:start_extraction_date}') AND ([Created] <= '{env:end_extraction_date}') AND ([ProjectKey] = '{env:project_key_value}') AND [IssueTypeName] IN ('Issue_type_name_1', 'Issue_type_name_2', 'Issue_type_name_X'); REPLICATE [IssueChangelogs] SELECT FORMAT([Created], 'yyyy-MM-dd hh:mm:ss') as [Created], [IssueId], [ItemField], [AuthorDisplayName], [ItemToString] FROM [IssueChangelogs] WHERE (ItemField IN ('resolution', 'status', 'timeestimate','Link','assignee','Product categorization', 'Operational categorization','Impact', 'Urgency', 'labels','Request participants', 'Component', 'Severity', 'priority')) AND ([IssueKey] LIKE '{env:project_key_value_like}') AND [IssueKey] IN (SELECT [Key] FROM [Issues] WHERE [IssueTypeName] = '{env:Issue_type_name_value'}); REPLICATE [Statuses] SELECT [Name], [CategoryKey] FROM [Statuses];

Input fields

The following tables include the list of fields per input table. Note: Table names and field names are case-sensitive. Always make sure that the field names (column headers) in your dataset match the field names in the table below and that the file names match the table names.

Input types

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

Field type Description
boolean true, false, 1, 0
date yyyy-mm-dd
datetime yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional.
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

IssueChangelogs

Table containing changes performed on each issue. This is the historical table from which most activities are created.

Field Stores Type Filtering
AuthorDisplayName User that made the change. text
Created Date in which the history record was created. datetime Time based filter
ItemField Field that is being changed. text in ('resolution', 'status', 'timeestimate', 'Link', 'assignee', 'Product categorization', 'Operational categorization', 'Impact', 'Urgency', 'labels', 'Request participants', 'Component', 'Severity', 'priority')
IssueId System’s number ID for each issue. text
ItemToString New value set to the field described in ItemField. text

Issues

This is a transactional table for issues.

Field Stores Type Filtering
AssigneeDisplayName Last user assigned to the issue. text
Created Date the case started. datetime Time based filter
CreatorDisplayName User that created the issue on Jira’s system. text
Description Contains details about the reported issue. text
Id System’s number ID for each issue. text
IssueTypeName Name of the corresponding issue type. text
Key Issue identifier. text
OriginalEstimateSeconds Planned resolution time (in seconds). integer
PriorityName Defines the priority level for the reported issue. text
ProjectName Name of the project. text
ReporterDisplayName User that reports the issue to the creator of the issue. text
ResolutionName Type of resolution. text
Severity Defines the level of severity for the reported issue. text
StatusName Last status name reported for the issue. text
ResolutionDate Date for the issue resolution. datetime
Updated Date of the last update. datetime
Time to resolution Stores information related to the sla. text (json)

Statuses

This table brings a categorization for the different statuses applied to the tickets.

Field Stores Type Filtering
CategoryKey Name of the corresponding status. text
Name Status type. text

Design specifications

Entities

Jira has only one entity and is created based on the Issues table. If any activity is missing a new "Item_field" value needs to be identified and added in the CDATA replicate query, IssueChangeLogs_input and Issues_events.

Entity Input data
Issue Issues

Cases_base

Field Data Type Description
Assignee Text Last assigned user.
Breach_time Datetime Expected date for a case before breaching the SLA.
Case Varchar System’s internal ID number.
Case_ID Text Issue number.
Case_owner Text User that created the issue on Jira’s system.
Creation_date Datetime Date and time the case started.
Case_status Text Last status.
Case_type Text Categorization for the case.
Case_value Double Jira does not contain data related to Case_value.
Completion_reason Text Holds the completion reason.
Estimated_time Integer Time estimated to finish the issue.
Issue_description Text Field that describes details about the reported issue.
Priority Text Defines the priority level for the reported issue.
Project Text Name of the project.
Reporter Text User that reports the issue to the creator of the issue.
Resolution_date Datetime Timestamp for the issue resolution.
Severity Text Severity level.
Stop_time Datetime Date the activity configured as SLA actually took place.

Activities

Activity Description
Assign user Assign a user to the issue.
Change status to Contains a set of activities related to statuses of issues.
Create issue Create a new issue in the process.
Link to other issue Link issue to another Issue.
Set component Identify component.
Set impact Establish impact level.
Set label Establish label for the issue.
Set operational category Identify the operational category.
Set priority Identify priority level.
Set product category Identify the product category.
Set time estimate Set estimated time.
Set resolution to Contains a set of activities related to the resolution type.
Set severity Establish severity level.
Set urgency Establish urgency level.
Remove resolution status Remove a previously set resolution.
Request participant Request participant.

Customizing the transformations

Configuration

Seeds

Seed files can be used for variables that require a list of values. Seed files are available in the seeds folder of the transformations. The Automated_users file contains a list of users which are considered automated.