Jira Issue Management SpotApp

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The Jira Issue Management SpotApp mimics the Jira data model. When you deploy it, ThoughtSpot creates several Worksheets, Answers, and Liveboards, based on your Jira data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the Jira Issue Management SpotApp:

Jira Issue SpotApp Liveboard

Use the Jira Issue Management SpotApp to track the number of Jira tickets submitted, and the efficiency of how they are resolved. This can help you determine what part of the workflow causes the most delays, and how each team member is performing at resolving tickets.

Prerequisites

Before you can deploy the Jira Issue Management SpotApp, you must complete the following prerequisites:

Run SQL commands

The following SQL commands help standardize data types and column names. Replace the database name and schema name with your specific information.

The following SQL example is for the Snowflake cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse.

SQL commands:

Open the dropdown menu to view the SQL commands.
create or replace view "<DATABASE NAME>"."<SCHEMA NAME>"."JIRA_ISSUES" as
select distinct
"ISSUE"."ID" "ID",
"PROJECT"."NAME" "PROJECT_NAME",
"PROJECT"."KEY" "PROJECT_KEY",
"ISSUE"."KEY" "KEY",
"ISSUE_TYPE"."NAME" "ISSUE_TYPE_NAME",
"STATUS"."NAME" "STATUS_NAME",
"PRIORITY"."NAME" "PRIORITY_NAME",
"RESOLUTION"."NAME" "RESOLUTION_NAME",
"RESOLUTION"."DESCRIPTION" "RESOLUTION_DESCRIPTION",
"ISSUE"."RESOLVED" "RESOLUTION_DATE",
"ISSUE"."DESCRIPTION" "DESCRIPTION",
"USER_CREATOR"."NAME" "CREATOR",
"USER_CREATOR"."EMAIL" "CREATOR_EMAIL",
"USER_ASIGNEE"."NAME" "ASSIGNEE",
"USER_ASIGNEE"."EMAIL" "ASSIGNEE_EMAIL",
"USER_REPORTER"."NAME" "REPORTER",
"USER_REPORTER"."EMAIL" "REPORTER_EMAIL",
"ISSUE"."DUE_DATE" "DUE_DATE",
"ISSUE"."CREATED" "CREATED",
"ISSUE"."UPDATED" "UPDATED",
cast("ESTIMATE"."'timeoriginalestimate'" as NUMBER(19,0)) "TIME_ORIGINAL_ESTIMATE",
cast("ESTIMATE"."'timeestimate'" as NUMBER(19,0)) "TIME_ESTIMATE",
"ISSUE_TIME"."TIME_SPENT_SECONDS" "TIME_SPENT_SECONDS",
"ISSUE"."TIME_SPENT" "TIME_SPENT",
"ISSUE"."REMAINING_ESTIMATE" "REMAINING_ESTIMATE",
"ISSUE"."ORIGINAL_ESTIMATE" "ORIGINAL_ESTIMATE",
"ISSUE"."ENVIRONMENT" "ENVIRONMENT",
"ISSUE"."SUMMARY" "SUMMARY"
from
"<DATABASE NAME>"."<SCHEMA NAME>"."ISSUE" "ISSUE"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."PROJECT"
on "ISSUE"."PROJECT" = "PROJECT"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."PRIORITY" "PRIORITY"
on "ISSUE"."PRIORITY" = "PRIORITY"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."RESOLUTION" "RESOLUTION"
on "ISSUE"."RESOLUTION" = "RESOLUTION"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."STATUS" "STATUS"
on "ISSUE"."STATUS" = "STATUS"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."ISSUE_TYPE" "ISSUE_TYPE"
on "ISSUE"."ISSUE_TYPE" = "ISSUE_TYPE"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."USER" "USER_ASIGNEE"
on "ISSUE"."ASSIGNEE" = "USER_ASIGNEE"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."USER" "USER_CREATOR"
on "ISSUE"."CREATOR" = "USER_CREATOR"."ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."USER" "USER_REPORTER"
on "ISSUE"."REPORTER" = "USER_REPORTER"."ID"
left outer join (select * from "<DATABASE NAME>"."<SCHEMA NAME>"."ISSUE_FIELD_HISTORY" pivot(sum("VALUE") for "FIELD_ID" in ('timeestimate','timeoriginalestimate')) as p where "'timeestimate'" is not null) "ESTIMATE"
on "ISSUE"."ID" = "ESTIMATE"."ISSUE_ID"
left outer join "<DATABASE NAME>"."<SCHEMA NAME>"."WORKLOG" "ISSUE_TIME"
on "ISSUE"."ID" = "ISSUE_TIME"."ISSUE_ID";

Deploy the Jira Issue Management SpotApp

After you complete the prerequisites, you are ready to deploy the Jira Issue Management SpotApp and begin leveraging its pre-built content.

To deploy the Jira Issue Management SpotApp, refer to Deploying SpotApps.

Jira Issue Management SpotApp schema

The following table describes the schema for the Jira Issue Management SpotApp.

Table Column Column type Required column

JIRA_ISSUES

CREATED

TIMESTAMP

Yes

JIRA_ISSUES

ISSUE_TYPE_NAME

VARCHAR

Yes

JIRA_ISSUES

ID

NUMBER

No

JIRA_ISSUES

DUE_DATE

DATE

No

JIRA_ISSUES

KEY

VARCHAR

Yes

JIRA_ISSUES

RESOLUTION_DATE

TIMESTAMP

Yes

JIRA_ISSUES

SUMMARY

VARCHAR

No

JIRA_ISSUES

UPDATED

TIMESTAMP

Yes

JIRA_ISSUES

PRIORITY_NAME

VARCHAR

Yes

JIRA_ISSUES

RESOLUTION_NAME

VARCHAR

Yes

JIRA_ISSUES

PROJECT_KEY

VARCHAR

No

JIRA_ISSUES

ASSIGNEE

VARCHAR

Yes

JIRA_ISSUES

CREATOR

VARCHAR

Yes

JIRA_ISSUES

REPORTER

VARCHAR

Yes

JIRA_ISSUES

PROJECT_NAME

VARCHAR

Yes

JIRA_ISSUES

STATUS_NAME

VARCHAR

Yes

JIRA_ISSUES

TIME_ESTIMATE

NUMBER

Yes

JIRA_ISSUES

TIME_SPENT_SECONDS

NUMBER

Yes

JIRA_ISSUES

TIME_ORIGINAL_ESTIMATE

NUMBER

Yes