Jira

Jira is a proprietary product developed by Atlassian that allows bug tracking, issue tracking and agile project management. Jira is used by a large number of clients & users globally for project, time, requirements, task, bug, change, code, test, release, sprint management.

To learn how Jira entities map to SQL tables, consult the table mappings guide.

How to Setup

To connect to a Jira data source you need to register the Jira credentials. This requires:

• the name of the credential (which you can choose)
• Base URL
• Username
• One of the following token types:
  • Api Token
  • Personal Access Token

Using RAW App, this is the respective form for adding Jira credentials:

Base URL

This is the site url of the user's attlassian jira subscription., e.g. https://your-domain.atlassian.net/

Username

The user name to access the jira cloud instance, e.g. user@domain.com

Token

There are two token types:

• API tokens for user's Atlassian account. API tokens are created and managed here.
• Personal Access Tokens for self hosted Jira instances. This access token type can only be used to query jira_backlog_issue, jira_board, jira_issue and jira_sprint tables. More info regarding PAT

Users must define one of the two token types but not both.

Examples

Choose one of the examples below:

Jira administrators listing

Retrieve Jira administrators.

Overview

Sample usage:

/jira/users/administrators

Code

select
  display_name,
  active as status,
  account_id,
  group_names
from
  my_jira.jira_user
where 'administrators' in (select * from jsonb_array_elements_text(group_names));
  "<credentials_name>".jira_user

Jira dashboard listing

List Jira dashboards.

Overview

Sample usage:

/jira/dashboards/basic

Code

select
  key,
  project_key,
  created,
  creator_display_name,
  status,
  summary
from
  "<credentials_name>".jira_issue

Jira epic listing

List Jira epics.

Overview

Sample usage:

/jira/epics/basic

Code

select
  id,
  name,
  key,
  done as status,
  summary
from
  "<credentials_name>".jira_epic;

Jira epic listing with number of issues

List Jira epics ordered by the number of related issues in descending order.

Overview

Sample usage:

/jira/epics/cardinality[?days=<number_of_days>]

For instance, to retrieve epics with issues created in the last 10 days:

/jira/epics/cardinality?days=10

If not specified, the default number of days is set to 30

Code

with base as (
  select jira_issue.epic_key, count(jira_issue.id) as num_issues
  from "<credentials_name>".jira_issue
  where jira_issue.epic_key is not null and jira_issue.created > now() - (coalesce(30,:days) || ' DAY')::interval
  group by epic_key)
select e.id, e.name, e.key, e.done, e.summary, num_issues
from "<credentials_name>".jira_epic as e
    inner join base on base.epic_key=e.key
order by num_issues desc;

Querying average resolution time for Jira issues grouped by priority

Retrieve average resolution time for Jira issues grouped by priority.

Overview

Sample usage:

/jira/issues/avg_resolution_time_by_priority[?days=<number_of_days>]

For instance, to retrieve average resolution time by priority for issues created in the last 10 days:

/jira/issues/avg_resolution_time_by_priority?days=10

If not specified, the default number of days is set to 30

Code

select priority, extract ( day from avg(updated - created)) AS average_time_spent
from "<credentials_name>".jira_issue
where status = 'Done' and
  created > now() - (coalesce(30,:days) || ' DAY')::interval
group by priority
order by (case priority when 'Highest' then 1
              when 'High' then 2
              when 'Medium' then 3
              when 'Low' then 4
              when 'Lowest' then 5
              else 100 end);

Jira backlog issue listing

List backlog issues.

Overview

Sample usage:

/jira/issues/backlog/basic[?days=<number_of_days>]

For instance, to retrieve backlog issues created in the last 10 days:

/jira/issues/backlog/basic?days=10

If not specified, the default number of days is set to 30

Code

select
  key,
  project_key,
  created,
  creator_display_name,
  status,
  summary
from
  "<credentials_name>".jira_backlog_issue
where created > now() - (coalesce(30,:days) || ' DAY')::interval;

Jira 30-days-due backlog issue listing

List Jira backlog issues which are due within the next 30 days.

Overview

Sample usage:

/jira/issues/backlog/month_due

Code

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

Jira backlog issue listing for specific project

List backlog issues for specific project.

Overview

Sample usage:

/jira/issues/backlog/project/fixed[?days=<number_of_days>]

For instance, to retrieve backlog issues for a fixed project and created in the last 10 days:

/jira/issues/backlog/project/fixed?days=10

If not specified, the default number of days is set to 30

Code

select
  id,
  key,
  project_key,
  created,
  creator_display_name,
  assignee_display_name,
  status,
  summary
from
  "<credentials_name>".jira_backlog_issue
where created > now() - (coalesce(30,:days) || ' DAY')::interval and
  project_key = 'MY_PRJ';

Jira backlog issue listing for specific user

List backlog issues for specific user.

Overview

Sample usage:

/jira/issues/backlog/user/fixed[?days=<number_of_days>]

For instance, to retrieve backlog issues for a fixed user and created in the last 10 days:

/jira/issues/backlog/user/fixed?days=10

If not specified, the default number of days is set to 30

Code

select
  id,
  key,
  summary,
  project_key,
  status,
  assignee_display_name,
  assignee_account_id
from
  "<credentials_name>".jira_backlog_issue
where
  assignee_display_name = 'user_name';

Jira issue listing

Retrieve Jira issues.

Overview

Sample usage:

/jira/issues/basic

Code

select
  key,
  project_key,
  created,
  creator_display_name,
  status,
  summary
from
  "<credentials_name>".jira_issue

Querying number of issues created vs. resolved over time

Retrieve number of issues created vs. resolved per day.

Overview

Sample usage:

/jira/issues/created_resolved[?days=<number_of_days>]

For instance, to retrieve issues created vs. resolved per day in the last 10 days:

/jira/issues/created_resolved?days=10

If not specified, the default number of days is set to 30

Code

select date_trunc('day', created) AS day,
     count(*) filter (where status != 'Done') as issues_created,
     count(*) filter(where status = 'Done') as issues_resolved
from "<credentials_name>".jira_issue
where
  created > now() - (coalesce(30,:days) || ' DAY')::interval
group by day
order by day;

Jira issue in epic listing

Retrieve Jira issues in epic.

Overview

Sample usage:

/jira/issues/epic[?days=<number_of_days>]

For instance, to retrieve all issues in epic created the last 10 days:

/jira/issues/epic?days=10

If not specified, the default number of days is set to 30

Code

select
  jira_issue.id as issue_id,
  jira_issue.status as issue_status,
  jira_issue.created as issue_created,
  jira_issue.creator_display_name,
  jira_issue.assignee_display_name,
    coalesce((select jira_epic.name from "<credentials_name>".jira_epic where jira_issue.epic_key = jira_epic.key), '-') epic_name,
  jira_issue.summary
from
  "<credentials_name>".jira_issue
where created > now() - (COALESCE(30,:days) || ' DAY')::INTERVAL;

Jira high priority issues listing

Retrieve high priority Jira issues.

Overview

Sample usage:

/jira/issues/high_priority[?days=<number_of_days>]

For instance, to retrieve all high priority issues created the last 10 days:

/jira/issues/unresolved/daily?days=10

If not specified, the default number of days is set to 30

Code

select
  id as issue_no,
  description as issue_description,
  assignee_display_name as assigned_to
from
  "<credentials_name>".jira_issue
where
  created > now() - (coalesce(30,:days) || ' DAY')::INTERVAL and
  priority = 'High';

Jira latest issue listing

Retrieve Jira issues created over the last days.

Overview

Sample usage:

/jira/issues/latest[?days=<number_of_days>]

For instance, to retrieve all issues created the last 10 days:

/jira/issues/latest?days=10

If not specified, the default number of days is set to 30

Code

select
  key,
  project_key,
  created,
  creator_display_name,
  status,
  summary
from
  "<credentials_name>".jira_issue
where
  created > now() - (COALESCE(30,:days) || ' DAY')::INTERVAL

Jira issue listing for specific project

List issues for specific project.

Overview

Sample usage:

/jira/issues/project/fixed[?days=<number_of_days>]

For instance, to retrieve issues for a fixed project and created in the last 10 days:

/jira/issues/project/fixed?days=10

If not specified, the default number of days is set to 30

Code

select
  id,
  key,
  project_key,
  created,
  creator_display_name,
  assignee_display_name,
  status,
  summary
from
  my_jira.jira_issue
where created > now() - (coalesce(30,:days) || ' DAY')::interval and cast(project_key as varchar)='MY_PRJ';

Jira unresolved issues per day listing

Retrieve unresolved Jira issues per day.

Overview

Sample usage:

/jira/issues/unresolved/daily[?days=<number_of_days>]

For instance, to retrieve all unresolved issues created the last 10 days:

/jira/issues/unresolved/daily?days=10

If not specified, the default number of days is set to 30

Code

select date_trunc('day', created) as day,
    count(*) as unresolved_issues
from "<credentials_name>".jira_issue
where
  created > now() - (coalesce(30,:days) || ' DAY')::INTERVAL and status != 'Done'
group by day
order by day;

Querying Jira workload distribution

Compute Jira workload distribution across team members.

Overview

Sample usage:

/jira/issues/workload_distribution[?days=<number_of_days>]

For instance, to retrieve user workload based on issues created the last 10 days:

/jira/issues/workload_distribution?days=10

If not specified, the default number of days is set to 30

Code

select assignee_display_name, count(*) AS total_issues_assigned,
     count(*) filter (where status = 'Done') as issues_resolved
from "<credentials_name>".jira_issue
where
  created > now() - (coalesce(30,:days) || ' DAY')::interval
group by assignee_display_name
order by total_issues_assigned desc;

Jira project listing

List Jira projects.

Overview

Sample usage:

/jira/projects/basic

Code

select
  name,
  id,
  key,
  lead_display_name,
  project_category,
  description
from
  "<credentials_name>".jira_project;

Jira active sprint listing

List active Jira sprints.

Overview

Sample usage:

/jira/sprints/active

Code

select
  id,
  name,
  board_id,
  state,
  start_date,
  end_date,
  complete_date
from
  "<credentials_name>".jira_sprint
where
  state = 'active';

Jira sprint listing

List Jira sprints.

Overview

Sample usage:

/jira/sprints/basic

Code

select
  id,
  name,
  board_id,
  state,
  start_date,
  end_date,
  complete_date
from
  "<credentials_name>".jira_sprint;

Jira weekly-due sprint listing

List Jira sprints which are due within the next week.

Overview

Sample usage:

/jira/sprints/week_due

Code

select
  id,
  name,
  board_id,
  state,
  start_date,
  end_date
from
  "<credentials_name>".jira_sprint
where
  end_date > current_date
  and end_date <= (current_date + interval '7' day);

Jira user listing

Retrieve specific Jira user.

Overview

Sample usage:

/jira/user

Code

select
  display_name,
  account_type as type,
  active as status,
  account_id
from
  "<credentials_name>".jira_user
where display_name = 'Confluence Analytics (System)'

Jira inactive users listing

Retrieve inactive Jira users.

Overview

Sample usage:

/jira/users/inactive

Code

select
  display_name,
  account_type as type,
  active as status,
  account_id
from
  "<credentials_name>".jira_user
where not active

Jira users listing

Retrieve Jira users.

Overview

Sample usage:

/jira/users/basic

Code

select
  display_name,
  account_type as type,
  active as status,
  account_id
from
  "<credentials_name>".jira_user

Table Mappings

Once the Jira credential is registered, a new schema will be available for query within RAW. The name of this schema matches the name of the "RAW credential" you chose above.

Within this schema, you will find a set of tables, which contain the information stored in Jira. These tables are:

Table Name	Description
jira_advanced_setting	Advanced Setting
jira_backlog_issue	Backlog Issue
jira_board	Board
jira_component	Component
jira_dashboard	Dashboard
jira_epic	Epic
jira_global_setting	Global Setting
jira_group	Group
jira_issue	Issue
jira_issue_comment	Issue Comment
jira_issue_type	Issue Type
jira_issue_worklog	Issue Worklog
jira_priority	Priority
jira_project	Project
jira_project_role	Project Role
jira_sprint	Sprint
jira_user	User
jira_workflow	Workflow

Advanced Setting

Atlassian's Jira Advanced Settings is a functionality that enables customization and adjustment of the system according to your organization's requirements. It offers the capability to establish and control key-value pairs for a range of settings, such as index path, attachment size, among others. This feature ensures you are up-to-date with the existing configurations and facilitates the implementation of necessary changes when required.

The jira_advanced_setting table offers a detailed look at the sophisticated configurations available in Jira. System administrators can delve into specifics of these settings via this table, which lists the key and value pairs for each setting. It serves as a tool for discovering details about system configurations, including aspects like attachment size, index path, and opportunities for adjustments.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
allowed_values	jsonb	The allowed values, if applicable.
description	text	The description of the application property.
id	text	The ID of the application property.
key	text	The key of the application property.
name	text	The name of the application property.
title	text	Title of the resource.
type	text	The data type of the application property.
value	text	The new value.

Backlog Issue

Jira serves as a tool for project management, specializing in tracking issues, bugs, and managing agile projects. A Jira Backlog Issue is a term for a task or bug that has been recognized but is not actively being addressed. These issues are kept in the backlog, which is a compilation of tasks or bugs awaiting attention but have not yet been designated a priority for resolution.

The jira_backlog_issue table offers an overview of backlog issues in a Jira project. This table is a valuable tool for project managers or software developers to examine the specifics of each issue, such as its status, priority, and who it is assigned to. Utilizing this information can aid in task prioritization, project workflow management, and the prompt resolution of bugs and tasks.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
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.
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.
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.

Board

Jira Boards, a functionality of Atlassian's Jira Software, enables teams to visually represent their tasks. These boards are adaptable to match the specific workflow of any team, simplifying the management of tasks and projects. They offer a visual and interactive platform for monitoring the advancement of work.

The jira_board table gives detailed information on Jira Boards within Atlassian's Jira Software. This resource is invaluable for project managers or team leaders looking to delve into specifics about boards, including their configurations, types, and linked projects. By accessing this table, you can gain comprehensive insights into various aspects of boards, like the projects they are associated with, the diversity of board types, and how they are set up.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
filter_id	bigint	Filter id of the board.
id	bigint	The ID of the board.
name	text	The name of the board.
self	text	The URL of the board details.
sub_query	text	JQL subquery used by the given board - (Kanban only).
title	text	Title of the resource.
type	text	The board type of the board. Valid values are simple, scrum and kanban.

Component

A Jira Component acts as a subcategory within a project, serving to organize issues into more manageable segments. It allows for the assignment of a default assignee specific to the component, superseding the project's overall default assignee.

The jira_component table offers a detailed look at the components of a Jira project. For Project Managers or Developers, this table is a crucial tool for examining specific information about components, such as their names, descriptions, lead details, and associated project keys. It serves as a valuable resource for organizing and managing issues within a project, thereby enhancing the efficiency and organization of project management.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
assignee_account_id	text	The account id of the user associated with assigneeType, if any.
assignee_display_name	text	The display name of the user associated with assigneeType, if any.
assignee_type	text	The nominal user type used to determine the assignee for issues created with this component.
description	text	The description for the component.
id	text	The unique identifier for the component.
is_assignee_type_valid	boolean	Whether a user is associated with assigneeType.
issue_count	bigint	The count of issues for the component.
lead_account_id	text	The account id for the component's lead user.
lead_display_name	text	The display name for the component's lead user.
name	text	The name for the component.
project	text	The key of the project to which the component is assigned.
project_id	bigint	The ID of the project the component belongs to.
real_assignee_account_id	text	The account id of the user assigned to issues created with this component, when assigneeType does not identify a valid assignee.
real_assignee_display_name	text	The display name of the user assigned to issues created with this component, when assigneeType does not identify a valid assignee.
real_assignee_type	text	The type of the assignee that is assigned to issues created with this component, when an assignee cannot be set from the assigneeType.
self	text	The URL for this count of the issues contained in the component.
title	text	Title of the resource.

Dashboard

Jira Software, crafted by Atlassian, is a project management application designed for the planning, monitoring, and deployment of software. It is favored by agile teams for its functionality. A prominent aspect of Jira Software is its dashboards, which offer a tailored and adaptable perspective on the progress and condition of a project.

The jira_dashboard table delivers insights into the array of dashboards present in a Jira Software instance. This table is a key resource for project managers or team leads to investigate specifics about each dashboard, including details about the owner, who can view it, and the projects it is linked to. It enables the examination of various dashboard characteristics, such as which dashboards are publicly shared, those owned by individual users, and the specific projects tied to each dashboard, aiding in the comprehensive management and oversight of project information.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
edit_permissions	jsonb	The details of any edit share permissions for the dashboard.
id	text	The ID of the dashboard.
is_favourite	boolean	Indicates if the dashboard is selected as a favorite by the user.
name	text	The name of the dashboard.
owner_account_id	text	The user info of owner of the dashboard.
owner_display_name	text	The user info of owner of the dashboard.
popularity	bigint	The number of users who have this dashboard as a favorite.
rank	bigint	The rank of this dashboard.
self	text	The URL of the dashboard details.
share_permissions	jsonb	The details of any view share permissions for the dashboard.
title	text	Title of the resource.
view	text	The URL of the dashboard.

Epic

Jira, created by Atlassian, is a project management application designed to assist in planning, monitoring, and overseeing agile software development projects. Within Jira, an Epic refers to a significant user story that can be subdivided into multiple smaller stories.

The jira_epic table offers a detailed perspective on Epics within Jira, making it an essential tool for project managers or team leads. Through this table, you can delve into specifics about each Epic, such as its progress, current status, and tasks it encompasses. This allows for a deeper understanding of Epics, including identifying those that are overdue, understanding how tasks correlate with Epics, and gauging the overall advancement of a project.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
color	jsonb	Label colour details for the epic.
done	boolean	Indicates the status of the epic.
id	bigint	The id of the epic.
key	text	The key of the epic.
name	text	The name of the epic.
self	text	The URL of the epic details.
summary	text	Description of the epic.
title	text	Title of the resource.

Global Setting

Jira Global Settings encompass a series of overarching configurations within the Jira tool, enabling administrators to manage elements like user permissions, security settings, and additional system configurations comprehensively. This feature offers a unified approach to overseeing and adjusting the Jira system's general operations and capabilities. By facilitating control over settings on a global scale, Jira Global Settings play a crucial role in upholding the system's integrity, security, and efficiency.

The jira_global_setting table sheds light on the global configurations within Jira, serving as a crucial resource for Jira administrators to access comprehensive system-wide information. This includes details on security measures, user permissions, and various other system settings. By consulting this table, administrators can gain a thorough understanding of the Jira system's overarching operations and features, as well as ensure the maintenance of the system's integrity, security, and optimal performance.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
attachments_enabled	boolean	Whether the ability to add attachments to issues is enabled.
issue_linking_enabled	boolean	Whether the ability to link issues is enabled.
sub_tasks_enabled	boolean	Whether the ability to create subtasks for issues is enabled.
time_tracking_configuration	jsonb	The configuration of time tracking.
time_tracking_enabled	boolean	Whether the ability to track time is enabled.
unassigned_issues_allowed	boolean	Whether the ability to create unassigned issues is enabled.
voting_enabled	boolean	Whether the ability for users to vote on issues is enabled.
watching_enabled	boolean	Whether the ability for users to watch issues is enabled.

Group

Jira Groups represent a compilation of users within a Jira instance, a widely used project management platform. These groups provide a streamlined method for handling user collections. They are instrumental in assigning permissions, limiting access, and orchestrating notifications throughout the Jira environment.

The jira_group table offers an in-depth view of the user groups in a Jira instance, making it a valuable asset for project managers or system administrators. This table allows for the exploration of specific details related to each group, such as group names, the members within each group, and the permissions granted to them. It serves as a tool to facilitate access control, define permissions, and enhance the efficiency of user management within the Jira environment.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
id	text	The ID of the group, if available, which uniquely identifies the group across all Atlassian products. For example, 952d12c3-5b5b-4d04-bb32-44d383afc4b2.
member_ids	jsonb	List of account ids of users associated with the group.
member_names	jsonb	List of names of users associated with the group.
name	text	The name of the group.
title	text	Title of the resource.

Issue

Jira, a project management application created by Atlassian, is extensively utilized for tracking issues, bugs, and managing agile projects. It enables teams to oversee, organize, follow, and deploy software projects, promoting openness and collaboration among team members. At the heart of Jira are its issues, which serve as the fundamental elements for tracking distinct tasks that require completion.

The jira_issue table delivers insights into the issues of a Jira project, serving as a key resource for project managers or software developers. This table enables the examination of detailed information about each issue, including its status, who it's assigned to, the reporter, and relevant metadata. It is an effective tool for identifying unassigned issues, tracking those in progress, and confirming adherence to project timelines.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
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.
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.
duedate	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.
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.
resolution_date	timestamp with time zone	Date the issue was resolved.
self	text	The URL of the issue details.
sprint_ids	jsonb	The list of ids of the sprint to which issue belongs.
sprint_names	jsonb	The list of names of the sprint to which issue belongs.
status	text	Json object containing important subfields info the issue.
status_category	text	The status category (Open, In Progress, Done) of the ticket.
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.

Issue Comment

Jira is a project management software that supports issue tracking and agile project management, commonly employed by software development teams to plan, monitor progress, and release software. Within Jira, issue comments function as a platform for communication, enabling users to engage in discussions, offer updates, and monitor modifications related to specific issues.

The jira_issue_comment table offers a detailed look at comments on issues within the Jira platform, acting as an invaluable tool for project managers or team members. This table allows for the exploration of specifics about each comment, such as the comment's author, the date it was created, and which issue the comment pertains to. It serves as a means to follow communication trails, grasp the nuances of discussions, and oversee the development of issues.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
author	jsonb	The user information who added the issue comment.
body	text	The content of the issue comment.
created	timestamp with time zone	Time when the issue comment was created.
id	text	The ID of the issue comment.
issue_id text The ID of the issue.
jsd_public	boolean	JsdPublic set to false does not hide comments in Service Desk projects.
self	text	The URL of the issue comment.
title	text	Title of the resource.
update_author	jsonb	The user information who updated the issue comment.
updated	timestamp with time zone	Time when the issue comment was last updated.

Issue Type

Jira Issue Types provide a method for classifying various work items within a Jira project. This classification aids in differentiating between tasks, bugs, stories, epics, and other work forms, allowing teams to effectively organize, monitor, and handle their tasks. These issue types offer customization options to meet the unique requirements of each project or team.

The jira_issue_type table gives a comprehensive overview of the various Issue Types within a Jira project, making it a vital resource for project managers or team leads. By accessing this table, you can delve into the specifics of each issue type, including their names, descriptions, and avatar URLs. This enables a thorough understanding of the diverse issue types present in your project, facilitating improved project management and task organization.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
avatar_id	bigint	The ID of the issue type's avatar.
description	text	The description of the issue type.
entity_id	bigint	Unique ID for next-gen projects.
hierarchy_level	bigint	Hierarchy level of the issue type.
icon_url	text	The URL of the issue type's avatar.
id	text	The ID of the issue type.
name	text	The name of the issue type.
scope	jsonb	Details of the next-gen projects the issue type is available in.
self	text	The URL of the issue type details.
subtask	boolean	Whether this issue type is used to create subtasks.
title	text	Title of the resource.

Issue Worklog

Jira is a project management application designed to assist teams in organizing, monitoring, and overseeing their projects and tasks. Within Jira, Issue Worklogs record the work completed and the time dedicated to each issue. This data is vital for assessing the advancement of projects, recognizing individual efforts, and controlling the timelines of the overall project.

The jira_issue_worklog table offers insights into the work completed and the time allocated to each issue within Jira, serving as an essential tool for project managers or team leaders. This table allows for an examination of detailed aspects of issue worklogs, including the amount of time spent, the nature of the work done, and related metadata. It is useful for monitoring project advancement, recognizing individual efforts, and efficiently managing project schedules.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
author	jsonb	Information about the user who created the worklog entry, often including their username, display name, and user account details.
comment	text	Any comments or descriptions added to the worklog entry.
created	timestamp with time zone	The date and time when the worklog entry was created.
id	text	A unique identifier for the worklog entry.
issue_id text The ID of the issue.
properties	jsonb	The properties of each worklog.
self	text	The URL of the worklogs.
started	timestamp with time zone	The date and time when the worklog activity started.
time_spent	text	The duration of time logged for the task, often in hours or minutes.
time_spent_seconds	bigint	The duration of time logged in seconds.
title	text	Title of the resource.
update_author	jsonb	Details of the user who last updated the worklog entry, similar to the author information.
updated	timestamp with time zone	The date and time when the worklog entry was last updated.

Priority

Jira is a project management software tailored for tracking issues, identifying bugs, and managing agile projects. In Jira, an issue's priority indicates its level of significance compared to other issues. This characteristic, which can be adjusted by the user upon issue creation or modification, highlights the urgency and importance of the issue within the project.

The jira_priority table sheds light on the various priority levels designated to issues within a Jira project, proving to be a crucial resource for project managers or agile team members. By examining this table, you can access detailed information about each priority, including its description, icons, and related metadata. This enables a deeper understanding of the priorities, including their relative significance, which assists in the efficient management and resolution of issues.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
description	text	The description of the issue priority.
icon_url	text	The URL of the icon for the issue priority.
id	text	The ID of the issue priority.
name	text	The name of the issue priority.
self	text	The URL of the issue priority.
status_color	text	The color used to indicate the issue priority.
title	text	Title of the resource.

Project

Jira is a widely used project management application favored by software development teams for planning, monitoring, and deploying software. It encompasses various features such as tracking bugs, managing issues, and overseeing projects. Within Jira, Projects act as the main organizational units where issues are generated and followed up on.

The jira_project table offers a comprehensive view of the Projects within Jira, serving as a valuable tool for Project Managers or Scrum Masters. This table allows for the exploration of specifics related to each project, such as the project key, type, lead, and category. It is instrumental in revealing details about projects, including the status of the project, the lead overseeing it, and the category it falls under, facilitating effective project oversight and management.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
component_ids	jsonb	List of the components contained in the project.
description	text	A brief description of the project.
email	text	An email address associated with the project.
id	text	The ID of the project.
issue_types	jsonb	List of the issue types available in the project.
key	text	The key of the project.
lead_account_id	text	The user account id of the project lead.
lead_display_name	text	The user display name of the project lead.
name	text	The name of the project.
project_category	jsonb	The category the project belongs to.
project_type_key	text	The project type of the project. Valid values are software, service_desk and business.
properties	jsonb	This resource represents project properties, which provide for storing custom data against a project.
self	text	The URL of the project details.
title	text	Title of the resource.
url	text	A link to information about this project, such as project documentation.

Project Role

Jira Project Roles offer a versatile method for linking users and groups to specific projects, enabling project administrators to oversee the membership of these roles. These roles are integral in managing access and permissions through permission schemes, setting issue security levels, configuring notification schemes, and controlling the visibility of comments within the project.

The jira_project_role table delivers insights into the project roles available within Jira, making it an essential resource for project administrators. Through this table, administrators can delve into details specific to each role, including the permissions granted and the users and groups associated with those roles. This facilitates the management of project role memberships and the establishment of permission schemes, issue security levels, and notification schemes, streamlining project administration and security.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
actor_account_ids	jsonb	The list of user ids who act in this role.
actor_names	jsonb	The list of user ids who act in this role.
description	text	The description of the project role.
id	bigint	The ID of the project role.
name	text	The name of the project role.
self	text	The URL the project role details.
title	text	Title of the resource.

Sprint

Jira Sprints are a crucial element of the agile project management features provided by Jira. They define a specific duration in which designated tasks must be finished and prepared for evaluation. Utilized within the Scrum framework, Sprints assist teams in effectively organizing and managing their workload, by dividing larger projects into smaller, time-limited segments.

The jira_sprint table offers a detailed look into the sprints of a Jira project, serving as a crucial tool for project managers or team leads. This table enables the exploration of each sprint's specifics, such as its progress, current status, and the issues linked to it. It is instrumental for monitoring the advancement of active sprints, strategizing upcoming sprints, and evaluating the outcomes of previous sprints, thereby enhancing sprint planning and performance assessment.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
board_id	bigint	The ID of the board the sprint belongs to.z
complete_date	timestamp with time zone	Date the sprint was marked as complete.
end_date	timestamp with time zone	The projected time of completion of the sprint.
id	bigint	The ID of the sprint.
name	text	The name of the sprint.
self	text	The URL of the sprint details.
start_date	timestamp with time zone	The start timestamp of the sprint.
state	text	Status of the sprint.
title	text	Title of the resource.

User

Jira, developed by Atlassian, is a renowned project management platform mainly utilized for tracking issues, identifying bugs, and overseeing projects. It facilitates teams in planning, monitoring, and managing agile software development endeavors.

The jira_user table offers a glimpse into the user profiles within Jira. For project managers or system administrators, this table is a resource to delve into specifics about users, encompassing account statuses, email addresses, and related metadata. It enables the exploration of user information, including their account conditions, last access times, and group affiliations.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	The account ID of the user, which uniquely identifies the user across all Atlassian products. For example, 5b10ac8d82e05b22cc7d4ef5.
account_type	text	The user account type. Can take the following values: atlassian, app, customer and unknown.
active	boolean	Indicates if user is active.
avatar_urls	jsonb	The avatars of the user.
display_name	text	The display name of the user. Depending on the user's privacy setting, this may return an alternative value.
email_address	text	The email address of the user. Depending on the user's privacy setting, this may be returned as null.
group_names	jsonb	The groups that the user belongs to.
self	text	The URL of the user.
title	text	Title of the resource.

Workflow

Jira Workflows, a capability of Atlassian's Jira software, empower teams to oversee and trace the progression of tasks and issues from inception to completion. This feature visualizes the journey an issue takes, offering teams the flexibility to tailor and direct the flow of their work. Jira Workflows facilitate the definition of necessary steps for issue resolution, establish who has the authority to transition issues through various stages, and automate these changes.

The jira_workflow table presents an in-depth examination of Jira Workflows within a Jira software environment. Project managers or team leaders can use this table to understand the sequence of steps, transitions, and status categories within each workflow. It is a valuable resource for optimizing work processes, comprehending the lifecycle of tasks, and pinpointing any bottlenecks in your project's workflow.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
description	text	The description of the workflow.
entity_id	text	The entity ID of the workflow.
is_default	boolean	Whether this is the default workflow.
name	text	The name of the workflow.
statuses	jsonb	The statuses of the workflow.
title	text	Title of the resource.
transitions	jsonb	The transitions of the workflow.

Acknowledgement

We would like to acknowledge the amazing work from the Steampipe community, from which this documentation and examples were derived.

To learn more about our commitment and stance to open source, please check our Community and Support page.