Connect your data to Atlassian Analytics
Learn how to connect and manage your connected Atlassian data and external data.
Only people who are part of this invite-only early access program have access to Atlassian Analytics.
The Jira family of products share many of the same tables. In other words, data for the available Jira products in the Atlassian Data Lake are aggregated into the same set of tables.
If you need to filter for specific Jira product data, use the Project type column from the Jira project table in the “Filters” section of your visual mode queries or the WHERE clause of your SQL mode queries.
Columns with an asterisk (*) are only available when you include all data for your products in a Data Lake connection. Learn more about how to set the scope of data for Atlassian Data Lake connections.
The diagram above shows the relationships between the schema for the Jira family of products, the schema for Jira Software, and the schema for Jira Service Management. Columns in bold are the primary keys of the table that they’re in.
There are several tables for Jira project data:
Jira project
Jira project component
Jira project fix version
The jira_project table lists your Jira projects. It uses the following columns:
assignee_type* (string)
The default issue assignee setting for the project
Example values include PROJECT_LEAD and UNASSIGNED.
description* (string)
The description of the project
lead_account_id (string)
The unique identifier of the person defined as the project lead
name* (string)
The name of the project
project_category* (string)
The project category that this project is assigned to
project_id (string)
The unique identifier of the project. Use this as the primary key for project data.
project_key* (string)
The unique key for the project
project_ref* (string)
The identifier of the project within a Jira site
It can be used to associate this record with the project identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
project_type* (string)
The type of the project. Learn more about project types.
Possible values: business, software, service_desk
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_project_component table lists the components defined for each project. It uses the following columns:
assignee_type* (string)
The default issue assignee setting for the project
Example values include PROJECT_DEFAULT and UNASSIGNED.
component_id (string)
The unique identifier of the project component. Use this as the primary key for component data.
component_ref* (string)
The identifier of the component within a Jira site.
It can be used to associate this record with the component identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
description* (string)
The description of the project component
lead_account_id (string)
The unique identifier of the person defined as the component lead
name* (string)
The name of the project component
project_id (string)
The unique identifier of the project
It links to a record in the jira_project table.
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_project_fix_version table lists the versions defined for each project. It uses the following columns:
name* (string)
The name of the project version
project_id (string)
The unique identifier of the project.
It links to a record in the jira_project table.
release_date* (date)
The release date of the project version
sort_order* (number)
The sequence order of versions, matching their order of appearance in the Jira product
start_date* (date)
The start date of the project version
status* (string)
The current status of the project version
version_id (string)
The unique identifier of the project version. Use this as the primary key for version data.
version_ref* (string)
The identifier of the project version with a Jira site.
It can be used to associate this record with the version identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
There are several tables for Jira issue data:
Jira issue
Jira issue component mapping
Jira issue cycle time
Jira issue fix version mapping
Jira issue field
Jira issue history
Jira issue status history
The jira_issue lists your Jira issues. It uses the following columns:
assignee_account_id* (string)
The unique identifier of the person assigned to the issue
created_at (datetime)
The time (UTC time zone) when the issue was created
creator_account_id* (string)
The unique identifier of the person who created the issue
description* (string)
The description of the issue
due_date (datetime)
The due date (UTC time zone) of the issue
environment* (string)
A short description of the environment in which the issue occurred—for example, linux
issue_id (string)
The unique identifier of the issue. Use this as the primary key for issue data.
issue_key* (string)
The number of the issue within the project—for example, TEST-12
issue_ref* (string)
The identifier of the issue within a Jira site
It can be used to associate this record with the issue identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
issue_type* (string)
The type of issue
Example values include Task and Story.
labels* (array of strings)
An array of label names added to the issue
original_time_estimate_secs* (number)
This column requires time-tracking enabled.
The original time estimate (in seconds) that was set
priority* (string)
The current issue priority
priority_sort_order (integer)
The sequence order of the priority, matching its order of appearance in the Jira product
project_id (string)
The unique identifier of the project
It links to a record in the jira_project table.
reporter_account_id* (string)
The unique identifier of the person who reported the issue
resolution* (string)
The current issue resolution
Example values: Done, Won’t do, Duplicate
resolution_at (datetime)
The time (UTC time zone) when the issue was resolved
resolution_sort_order (integer)
The sequence order of the resolution, matching its order of appearance in the Jira product
status* (string)
The current issue status—for example, To do
status_category* (string)
The status category of the current issue status
Possible values: To do, In progress, Done
status_sort_order (integer)
The sequence order of the status, matching its order of appearance in the Jira product
summary* (string)
The summary of the issue
time_estimate_secs* (number)
This column requires time-tracking enabled.
The estimated time (in seconds) remaining from the original estimate
time_spent_secs* (number)
This column requires time-tracking enabled.
The amount of logged work (in seconds)
The field is NULL if there is no time spent.
updated_at (datetime)
The time (UTC time zone) when the issue was last updated
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_issue_component_mapping table holds associations between issues and project components. It uses the following columns:
component_id (string)
The unique identifier of a project component
It links to a record in the jira_project_component table.
issue_id (string)
The unique identifier of an issue
It links to a record in the jira_issue table.
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_issue_cycle_time table contains data derived from the issue_status_history.
You can use it to calculate flow metrics based on issue status transitions. The start of work is defined as the first transition to a status assigned the In progress status category, and the end of work is defined as the latest transition to a status assigned the Done status category.
If the issue is currently in a status of the Done status category, the elapsed time will be available in the cycle_time_secs column. That value will be NULL if the end of work precedes the start of work, to avoid capturing negative cycle times).
cycle_time_secs (number)
The overall time elapsed (in seconds) from the start of work to the end of work, indicating the overall issue cycle time
If the recorded work_ended_at is before work_started_at, the value will be NULL.
issue_id (string)
The unique identifier of the issue
It links to a record in the jira_issue table.
work_ended_at (datetime)
The latest time (UTC time zone) in which the issue transitioned into a status in the Done status category, denoting the time work on the issue completed
work_started_at (datetime)
The earliest time (UTC time zone) in which the issue transitioned into a status in the In progress status category, denoting the time work on the issue started
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
Not all custom field types are currently supported, but we’ll add additional fields over time.
The jira_issue_field lists the contents of Jira fields. It uses the following columns:
field_id (string)
The unique identifier of the Jira field
Use it together with the issue_id column in this table as the primary key for issue field data.
field_key* (string)
The key of a Jira field
The key can hold a different value to the ID when it is defined in apps.
field_ref (string)
The identifier of the field within a Jira site
It can be used to associate this record with the field identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
field_type* (string)
The data type stored in the field
issue_id (string)
The unique identifier of the issue
Use it together with the field_id column in this table as the primary key for issue field data.
name* (string)
The name of the field
value* (string)
The contents of the Jira field for this issue
The type of data represented by this string will depend on the field.
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_issue_fix_version_mapping table holds associations between issues and project versions that are fix versions for an issue. It uses the following columns:
issue_id (string)
The unique identifier of an issue
It links to a record in the jira_issue table.
version_id (string)
The unique identifier of a project version, which is the fix version of the issue
It links to a record in the jira_project_version table.
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_issue_history table tracks changes in issue data over time. Unlike the History tab in issue details, this table includes the initial value The jira_issue_history table tracks changes in issue data over time. Unlike the History tab in issue details, this table includes the initial value allocated to a field on issue creation.
Sometimes, several updates are applied to the issue at the same time. These updates will share the same changelog_id value. For each of these groups of changes, there will be a row for each field that was updated.
The table uses the following columns:
author_account_id* (string)
The unique identifier of the person who applied the changes to the issue
changelog_id (string)
The unique identifier for a group of updates to an issue
ended_at (datetime)
The time (UTC time zone) when the new field value as replaced by another
This is the time where the change captured in this record was displaced by a subsequent change. It is NULL if there was no subsequent change.
field* (string)
The name of the field that was updated
field_id (string)
The unique identifier of the field that was updated
field_key* (string)
The key of the field
field_name* (string)
The current name of the field that was updated
It can be NULL if the field was removed since the history record was created.
field_type* (string)
The type of of the field that was updated
It can be NULL if the field was removed since the history record was created.
issue_id (string)
The unique identifier of the issue that was updated
It links to a record in the jira_issue table.
prev_value* (string)
The value of the field before the change
prev_value_string* (string)
A string representing the value of the field before the change
started_at* (datetime)
The time (UTC time zone) when the new field value became effective
This is the time of the change captured by this record.
time_in_value_secs (integer)
The time interval (in seconds) between the start and end time of this field value
It is NULL if there was no subsequent change in the field (in other words, the new field value is the current one).
value* (string)
The value of the field after the change
value_string* (string)
A string representing the value of the field after the change
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
The jira_issue_status_history table tracks changes in issue status over time. Unlike the History tab in issue details, this tables also includes the initial status on issue creation. There is a row for each issue transition.
author_account_id* (string)
The unique identifier of the person who set or changed the issue status
ended_at (datetime)
The time (UTC time zone) when the new issue status was replaced by another
It is NULL if there was no subsequent issue transition.
issue_id (string)
The unique identifier of the Jira issue that was updated
It links to a record in the jira_issue table.
prev_status* (string)
The previous issue status
prev_status_category* (string)
The status category of the previous issue status
prev_status_id (string)
The unique identifier of the previous issue status
started_at* (datetime)
The time (UTC time zone) when the new issue status was set
status* (string)
The new issue status
status_category* (string)
The status category of the new issue status
status_id (string)
The unique identifier of the new issue status
time_in_status_secs (number)
The time interval (in seconds) between the start and end time of the status
It will be NULL if there was no subsequent issue transition (in other words, the new issue status is the current one).
workspace_id (string)
An Atlassian identifier that maps data to a site in the workspace_info table
Was this helpful?