steampipe plugin install jira

Table: jira_issue - Query Jira Issues using SQL

Jira is a project management tool developed by Atlassian, widely used for issue tracking, bug tracking, and agile project management. It allows teams to manage, plan, track, and release software, ensuring transparency and team collaboration. Jira's issues, the core units of Jira, are used to track individual pieces of work that need to be completed.

Table Usage Guide

The jira_issue table provides insights into Jira issues within a project. As a project manager or software developer, explore issue-specific details through this table, including status, assignee, reporter, and associated metadata. Utilize it to uncover information about issues, such as those unassigned, those in progress, and to verify project timelines.

Examples

Basic info

Discover the segments that detail the creation and status of a project. This is useful to gain insights into project timelines, creators, and their current progress.

select
key,
project_key,
created,
creator_display_name,
status,
summary
from
jira_issue;
select
key,
project_key,
created,
creator_display_name,
status,
summary
from
jira_issue;

List issues for a specific project

Explore the status and details of issues related to a specific project. This can aid in understanding the project's progress, identifying any roadblocks, and planning further actions effectively.

select
id,
key,
project_key,
created,
creator_display_name,
assignee_display_name,
status,
summary
from
jira_issue
where
project_key = 'TEST';
select
id,
key,
project_key,
created,
creator_display_name,
assignee_display_name,
status,
summary
from
jira_issue
where
project_key = 'TEST';

List all issues assigned to a specific user

Explore which tasks are allocated to a particular individual, allowing you to gain insights into their workload and responsibilities. This is particularly useful for project management and task distribution.

select
id,
key,
summary,
project_key,
status,
assignee_display_name,
assignee_account_id
from
jira_issue
where
assignee_display_name = 'Lalit Bhardwaj';
select
id,
key,
summary,
project_key,
status,
assignee_display_name,
assignee_account_id
from
jira_issue
where
assignee_display_name = 'Lalit Bhardwaj';

List issues due in the next week

Explore upcoming tasks by identifying issues scheduled for completion within the next week. This can help in prioritizing work and managing team assignments effectively.

select
id,
key,
summary,
project_key,
status,
assignee_display_name,
assignee_account_id,
duedate
from
jira_issue
where
duedate > current_date
and duedate <= (current_date + interval '7' day);
select
id,
key,
summary,
project_key,
status,
assignee_display_name,
assignee_account_id,
duedate
from
jira_issue
where
duedate > date('now')
and duedate <= date('now', '+7 day');

Get linked issue details for the issues

The query enables deep analysis of linked issues within Jira. Each issue can have multiple links to other issues, that might represent dependencies, blockers, duplicates, or other relationships critical for project management and bug tracking.

select
ji.id,
ji.title,
ji.project_key,
ji.status,
il.issue_link_id,
il.issue_link_self,
il.issue_link_type_name,
il.inward_issue_id,
il.inward_issue_key,
il.inward_issue_status_name,
il.inward_issue_summary,
il.inward_issue_priority_name
from
jira_issue ji,
lateral jsonb_array_elements(ji.fields -> 'issuelinks') as il_data,
lateral (
select
il_data ->> 'id' as issue_link_id,
il_data ->> 'self' as issue_link_self,
il_data -> 'type' ->> 'name' as issue_link_type_name,
il_data -> 'inwardIssue' ->> 'id' as inward_issue_id,
il_data -> 'inwardIssue' ->> 'key' as inward_issue_key,
il_data -> 'inwardIssue' -> 'fields' -> 'status' ->> 'name' as inward_issue_status_name,
il_data -> 'inwardIssue' -> 'fields' ->> 'summary' as inward_issue_summary,
il_data -> 'inwardIssue' -> 'fields' -> 'priority' ->> 'name' as inward_issue_priority_name
) as il;
select
ji.id,
ji.title,
ji.project_key,
ji.status,
json_extract(il_data.value, '$.id') as issue_link_id,
json_extract(il_data.value, '$.self') as issue_link_self,
json_extract(il_data.value, '$.type.name') as issue_link_type_name,
json_extract(il_data.value, '$.inwardIssue.id') as inward_issue_id,
json_extract(il_data.value, '$.inwardIssue.key') as inward_issue_key,
json_extract(il_data.value, '$.inwardIssue.fields.status.name') as inward_issue_status_name,
json_extract(il_data.value, '$.inwardIssue.fields.summary') as inward_issue_summary,
json_extract(
il_data.value,
'$.inwardIssue.fields.priority.name'
) as inward_issue_priority_name
from
jira_issue ji,
json_each(json_extract(ji.fields, '$.issuelinks')) as il_data;

Get issues belonging to a sprint

  1. "Explore which tasks are part of a particular sprint, allowing you to manage and prioritize your team's workflow effectively."
  2. "Identify all tasks that have been completed, providing a clear overview of your team's accomplishments and productivity."
  3. "Determine the tasks that are currently awaiting support, helping you to allocate resources and address bottlenecks in your workflow."
  4. "Review the different status categories within a specific project, offering insights into the project's progress and potential areas for improvement.
select
id,
key,
summary,
project_key,
status,
assignee_display_name,
assignee_account_id,
duedate
from
jira_issue
where
sprint_ids @> '2';
Error: SQLite does not support array contains operator '@>'.

List all issues in status category 'Done'

select
id,
key,
summary,
status,
status_category,
assignee_display_name
from
jira_issue
where
status_category = 'Done';
select
id,
key,
summary,
status,
status_category,
assignee_display_name
from
jira_issue
where
status_category = 'Done';

List all issues in status Waiting for Support

select
id,
key,
summary,
status,
status_category,
assignee_display_name
from
jira_issue
where
status = 'Waiting for support';
select
id,
key,
summary,
status,
status_category,
assignee_display_name
from
jira_issue
where
status = 'Waiting for support';

List all possible status for each status_category for a speficic project

select
distinct project_key,
status_category,
status
from
jira_issue
where
project_key = 'PROJECT-KEY'
order by
status_category;
select
distinct project_key,
status_category,
status
from
jira_issue
where
project_key = 'PROJECT-KEY'
order by
status_category;

Schema for jira_issue

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
assignee_account_idtext=, !=Account Id the user/application that the issue is assigned to work.
assignee_display_nametext=, !=Display name the user/application that the issue is assigned to work.
componentsjsonbList of components associated with the issue.
createdtimestamp with time zone=, >, >=, <=, <Time when the issue was created.
creator_account_idtext=, !=Account Id of the user/application that created the issue.
creator_display_nametext=, !=Display name of the user/application that created the issue.
descriptiontextDescription of the issue.
duedatetimestamp with time zone=, >, >=, <=, <Time by which the issue is expected to be completed.
epic_keytext=, !=The key of the epic to which issue belongs.
fieldsjsonbJson object containing important subfields of the issue.
idtext=The ID of the issue.
keytext=The key of the issue.
labelsjsonbA list of labels applied to the issue.
login_idtext=, !=, ~~, ~~*, !~~, !~~*The unique identifier of the user login.
prioritytext=, !=Priority assigned to the issue.
project_idtext=, !=A friendly key that identifies the project.
project_keytext=, !=A friendly key that identifies the project.
project_nametext=, !=Name of the project to that issue belongs.
reporter_account_idtext=, !=Account Id of the user/application issue is reported.
reporter_display_nametext=, !=Display name of the user/application issue is reported.
resolution_datetimestamp with time zone=, >, >=, <=, <Date the issue was resolved.
selftextThe URL of the issue details.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
sprint_idsjsonbThe list of ids of the sprint to which issue belongs.
sprint_namesjsonbThe list of names of the sprint to which issue belongs.
statustext=, !=Json object containing important subfields info the issue.
status_categorytext=, !=The status category (Open, In Progress, Done) of the ticket.
summarytextDetails of the user/application that created the issue.
tagsjsonbA map of label names associated with this issue, in Steampipe standard format.
titletextTitle of the resource.
typetext=, !=The name of the issue type.
updatedtimestamp with time zone=, >, >=, <=, <Time when the issue was last updated.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- jira

You can pass the configuration to the command with the --config argument:

steampipe_export_jira --config '<your_config>' jira_issue