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
andjira_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
- Overview
- Code
Sample usage:
/jira/users/administrators
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
- Overview
- Code
Sample usage:
/jira/dashboards/basic
select
key,
project_key,
created,
creator_display_name,
status,
summary
from
"<credentials_name>".jira_issue
Jira epic listing
- Overview
- Code
Sample usage:
/jira/epics/basic
select
id,
name,
key,
done as status,
summary
from
"<credentials_name>".jira_epic;
Jira epic listing with number of issues
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
Sample usage:
/jira/issues/backlog/month_due
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
Sample usage:
/jira/issues/basic
select
key,
project_key,
created,
creator_display_name,
status,
summary
from
"<credentials_name>".jira_issue
Querying number of issues created vs. resolved over time
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
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
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
- Overview
- Code
Sample usage:
/jira/projects/basic
select
name,
id,
key,
lead_display_name,
project_category,
description
from
"<credentials_name>".jira_project;
Jira active sprint listing
- Overview
- Code
Sample usage:
/jira/sprints/active
select
id,
name,
board_id,
state,
start_date,
end_date,
complete_date
from
"<credentials_name>".jira_sprint
where
state = 'active';
Jira sprint listing
- Overview
- Code
Sample usage:
/jira/sprints/basic
select
id,
name,
board_id,
state,
start_date,
end_date,
complete_date
from
"<credentials_name>".jira_sprint;
Jira weekly-due sprint listing
- Overview
- Code
Sample usage:
/jira/sprints/week_due
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
- Overview
- Code
Sample usage:
/jira/user
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
- Overview
- Code
Sample usage:
/jira/users/inactive
select
display_name,
account_type as type,
active as status,
account_id
from
"<credentials_name>".jira_user
where not active
Jira users listing
- Overview
- Code
Sample usage:
/jira/users/basic
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. |