Table: jira_backlog_issue - Query Jira Backlog Issues using SQL

Jira is a project management tool used for issue tracking, bug tracking, and agile project management. A Jira Backlog Issue refers to a task or a bug that has been identified but is not currently being worked on. These issues are stored in the backlog, a list of tasks or bugs that need to be addressed but have not yet been prioritized for action.

Table Usage Guide

The jira_backlog_issue table provides insights into the backlog issues within a Jira project. As a project manager or a software developer, you can use this table to explore details of each issue, including its status, priority, and assignee. This can help you prioritize tasks, manage project workflows, and ensure timely resolution of bugs and tasks.

Examples

Basic info

Explore which projects have been created, who initiated them, their current status, and a brief summary. This information can be useful to gain an overview of ongoing projects and their progress.

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

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

List backlog issues for a specific project

Explore the status and details of pending tasks within a specific project to manage workload and track progress effectively. This can help in prioritizing tasks and assigning them to the right team members.

select
  id,
  key,
  project_key,
  created,
  creator_display_name,
  assignee_display_name,
  status,
  summary
from
  jira_backlog_issue
where
  project_key = 'TEST1';

select
  id,
  key,
  project_key,
  created,
  creator_display_name,
  assignee_display_name,
  status,
  summary
from
  jira_backlog_issue
where
  project_key = 'TEST1';

List backlog issues assigned to a specific user

Explore which backlog issues are assigned to a specific user to manage and prioritize their workload efficiently. This is useful in tracking project progress and ensuring tasks are evenly distributed among team members.

select
  id,
  key,
  summary,
  project_key,
  status,
  assignee_display_name,
  assignee_account_id
from
  jira_backlog_issue
where
  assignee_display_name = 'sayan';

select
  id,
  key,
  summary,
  project_key,
  status,
  assignee_display_name,
  assignee_account_id
from
  jira_backlog_issue
where
  assignee_display_name = 'sayan';

List backlog issues due in 30 days

Explore which backlog issues are due within the next 30 days to better manage your project timeline and delegate tasks effectively. This can assist in prioritizing work and ensuring that deadlines are met.

select
  id,
  key,
  summary,
  project_key,
  status,
  assignee_display_name,
  assignee_account_id,
  due_date
from
  jira_backlog_issue
where
  due_date > current_date
  and due_date <= (current_date + interval '30' day);

select
  id,
  key,
  summary,
  project_key,
  status,
  assignee_display_name,
  assignee_account_id,
  due_date
from
  jira_backlog_issue
where
  due_date > date('now')
  and due_date <= date('now', '+30 day');

Schema for jira_backlog_issue

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
assignee_account_id	text		Account Id the user/application that the issue is assigned to work.
assignee_display_name	text		Display name the user/application that the issue is assigned to work.
board_id	bigint		The ID of the board the issue belongs to.
board_name	text		The name of the board the issue belongs to.
components	jsonb		List of components associated with the issue.
created	timestamp with time zone		Time when the issue was created.
creator_account_id	text		Account Id of the user/application that created the issue.
creator_display_name	text		Display name of the user/application that created the issue.
description	text		Description of the issue.
due_date	timestamp with time zone		Time by which the issue is expected to be completed.
epic_key	text		The key of the epic to which issue belongs.
fields	jsonb		Json object containing important subfields of the issue.
id	text		The ID of the issue.
key	text		The key of the issue.
labels	jsonb		A list of labels applied to the issue.
login_id	text	=, !=, ~~, ~~, !~~, !~~	The unique identifier of the user login.
priority	text		Priority assigned to the issue.
project_id	text		A friendly key that identifies the project.
project_key	text		A friendly key that identifies the project.
project_name	text		Name of the project to that issue belongs.
reporter_account_id	text		Account Id of the user/application issue is reported.
reporter_display_name	text		Display name of the user/application issue is reported.
self	text		The URL of the issue details.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	text		The status of the issue. Eg: To Do, In Progress, Done.
summary	text		Details of the user/application that created the issue.
tags	jsonb		A map of label names associated with this issue, in Steampipe standard format.
title	text		Title of the resource.
type	text		The name of the issue type.
updated	timestamp 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_backlog_issue