Jira Incident Management app template

• 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
• Design specifications
  • Objects
  • Activities
• Customizing the transformations
  • Seed files

Process description

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.

Available tags & due dates

The following tags are available by default.

The following due dates are available by default.

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

Extraction

This app template uses Jira Service Management (cloud versions) 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 connect to Jira Serive Management an API token has to be generated for a user which has 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 Jira Incident-Management app template requires the following Jira objects:

• IssueChangelogs
• Issues
• Statuses

Follow these steps to generate an API token that is necessary to connect to the system and retrieve the data.

Setting up the source connection in CData Sync

• In CData - Connections, add a new source connection for Jira. If this option is not in the list, deselect Only Installed and install the connector
• Select Basic as the Auth Scheme.
• Enter the correct URL, User, and Password to set up the connection to Jira.
• On the Advanced tab, enter the API token generated in Jira Service Management and set Include custom fields to true.

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 Jira Service Management app template and create the job.

Advanced job settings

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

• Enable the Alter Schema option.
• Enable the Drop Table option.
• Copy the following text: LongTextSize = 255 and paste it in the Additional Options field.

If you are using Automation Suite, set the Destination Schema in the Settings panel on the overview tab. Use the schema name 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.

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

Because the Jira Service Management app template uses the Issue Number as the main object, consideration should be made when choosing an extraction date, as it will be used across all objects. 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="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 variable. 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 line and the filters for them in the replicate script. -->
<api:set attr="out.env:project_key_value"  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" />

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.

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, go to Task tab, click + Add Tasks, enable the Custom Query option 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], [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}%') AND ([Created] >= '{env:start_extraction_date}') AND ([Created] <= '{env:end_extraction_date}');
REPLICATE [Statuses] SELECT [Name], [CategoryKey] FROM [Statuses];

Note: If you do not use SLAs, the field Time_to_resolution may not be available, you can remove it from the Issues query as this field is optional.

Other scenarios

Optional fields are available

In case optional fields are available, you can add them in the already available replication queries, using the syntax [field]. For the following table you can find the fields that can be added and that are used in the transformations:

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], [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], [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}%')  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.

Input tables & fields

IssueChangelogs

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

Issues

This is a transactional table for issues.

Fields marked with * are optional.

Statuses

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

Design specifications

Objects

Jira has only one object 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.

Activities

Customizing the 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.

Automated_users

This file is used to set the users that are automated.