Get started with Atlassian Analytics
Learn how to add Atlassian Analytics to a site and understand what you need to query data and create charts.
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.
The jira_issue_enhanced_table table contains information about all your Jira issues.
The table contains the following columns:
The unique identifier of the person assigned to the issue.
SQL name | assignee_account_id |
---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_account table in the data share schema for organization data. |
The date and time (UTC time zone) when the issue was created.
SQL name | created_at |
---|---|
Data type | Timestamp |
The unique identifier of the person who created the issue.
SQL name | creator_account_id |
---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_account table in the data share schema for organization data. |
The description of the issue.
SQL name | description |
---|---|
Data type | String |
The due date (UTC time zone) of the issue.
SQL name | due_date |
---|---|
Data type | Timestamp |
A short description of the environment in which the issue occurred.
SQL name | environment |
---|---|
Data type | String |
The unique identifier of the issue. Use this as the primary key for this table.
SQL name | issue_id |
---|---|
Data type | String |
The number of the issue within the project.
SQL name | issue_key |
---|---|
Data type | String |
Example value | TEST-12 |
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.
SQL name | issue_ref |
---|---|
Data type | String |
The type of issue.
SQL name | issue_type |
---|---|
Data type | String |
This column requires time-tracking to be enabled.
The original time estimate (in seconds) that was set.
SQL name | original_time_estimate_secs |
---|---|
Data type | Float |
The unique identifier of the immediate parent of the issue.
SQL name | parent_issue_id |
---|---|
Data type | String |
Foreign key | Links to another record in the jira_issue_enhanced_table table. |
The current issue priority.
SQL name | priority |
---|---|
Data type | String |
The sequence order of the priority, matching its order of appearance in the Jira product.
SQL name | priority_sort_order |
---|---|
Data type | Int |
The unique identifier of the project that the issue is in.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The unique identifier of the person who reported the issue.
SQL name | reporter_account_id |
---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_account table in the data share schema for organization data. |
The current issue resolution.
SQL name | resolution |
---|---|
Data type | String |
The date and time (UTC time zone) when the issue was resolved.
SQL name | resolution_at |
---|---|
Data type | Timestamp |
The sequence order of the resolution, matching its order of appearance in the Jira product.
SQL name | resolution_sort_order |
---|---|
Data type | Int |
The current issue status.
SQL name | status |
---|---|
Data type | String |
The status category of the current issue status.
SQL name | status_category |
---|---|
Data type | String |
The sequence order of the status, matching its order of appearance in the Jira product.
SQL name | status_sort_order |
---|---|
Data type | Int |
Only holds data for issues in team-managed projects. To get story points for issues in company-managed projects, use the “Issue field” table.
The number of story points assigned to the issue.
SQL name | story_points |
---|---|
Data type | Float |
The summary of the issue.
SQL name | summary |
---|---|
Data type | String |
The unique identifier of the team associated with the issue.
SQL name | team_id |
---|---|
Data type | String |
This column requires time-tracking enabled.
The estimated time (in seconds) remaining from the original estimate.
SQL name | time_estimate_secs |
---|---|
Data type | Float |
This column requires time-tracking enabled.
The amount of logged work (in seconds). The field is NULL if there is no time spent.
SQL name | time_spent_secs |
---|---|
Data type | Float |
The date and time (UTC time zone) when the issue was last updated.
SQL name | updated_at |
---|---|
Data type | Timestamp |
The URL of the issue.
SQL name | url |
---|---|
Data type | String |
The jira_issue_field table lists the contents of Jira fields, including custom fields.
The table contains the following columns:
The unique identifier of the Jira issue field. Use this as the primary key for this table.
SQL name | field_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
Indicates the type of field.
SQL name | field_type_key |
---|---|
Data type | String |
The unique identifier of an issue.
SQL name | issue_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_enhanced_table table. |
The unique identifier of a project.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The contents of the Jira field for this issue. The type of data represented by this string will depend on the field.
SQL name | value |
---|---|
Data type | String |
The jira_issue_field_metadata table holds additional information about each Jira issue field.
The table contains the following columns:
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.
SQL name | field_ref |
---|---|
Data type | String |
The unique identifier of the issue field that’s associated with the metadata.
SQL name | issue_field_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
The key of the issue field. The key can hold a different value to the ID when it’s defined by plugins.
SQL name | key |
---|---|
Data type | String |
The name of the issue field.
SQL name | name |
---|---|
Data type | String |
The type of data stored in the issue field.
SQL name | type |
---|---|
Data type | String |
The jira_issue_field_option table stores the selectable set of values for the following types of Jira issue fields: select lists, multi-select lists, cascading select lists, radio buttons, and multi-checkboxes.
The table contains the following columns:
The unique identifier of the issue field.
SQL name | field_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
The unique identifier of the issue field option. Use this as the primary key for this table.
SQL name | issue_field_option_id |
---|---|
Data type | String |
The identifier for the issue field option within a Jira site.
SQL name | issue_field_option_ref |
---|---|
Data type | String |
The value that the issue field option contains.
SQL name | value |
---|---|
Data type | String |
The jira_issue_fix_version_mapping table holds associations between issues and project versions that are fix versions for an issue.
The table contains the following columns:
The unique identifier of an issue.
SQL name | issue_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_enhanced_table table. |
The unique identifier of a project.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The unique identifier of a project version, which is the fix version of the issue.
SQL name | version_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_version table. |
The jira_issue_history_change_item table provides details of each Jira issue’s changes, capturing information such as when a field was updated, what the previous and new values are, and who made the change. If multiple updates are made at the same time, these updates will share the same changelog ID.
The table contains the following columns:
The unique identifier of the author.
SQL name | author_id |
---|---|
Data type | String |
The unique identifier of the changelog. Change items will share the same changelog ID if they were updated at the same time.
SQL name | changelog_id |
---|---|
Data type | String |
The date and time (UTC time zone) when the change was made.
SQL name | created_at |
---|---|
Data type | Timestamp |
The name of the field that was changed.
SQL name | field |
---|---|
Data type | String |
The unique identifier of the field that was changed.
SQL name | field_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
The type of the field that was updated.
It can be NULL if the field was removed since the history record was created.
SQL name | field_type |
---|---|
Data type | String |
The unique identifier of the issue history associated with the history change item.
SQL name | issue_history_id |
---|---|
Data type | String |
The unique identifier of an issue that was updated.
SQL name | issue_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_enhanced_table table. |
A string representing the value of the field after the change.
SQL name | new_string |
---|---|
Data type | String |
The value of the field after the change.
SQL name | new_value |
---|---|
Data type | String |
A string representing the value of the field before the change.
SQL name | old_string |
---|---|
Data type | String |
The value of the field before the change.
SQL name | old_value |
---|---|
Data type | String |
The jira_issue_priority table holds information about each issue priority.
The table contains the following columns:
The name of the issue priority.
SQL name | name |
---|---|
Data type | String |
The unique identifier of the issue priority. Use this as the primary key for this table.
SQL name | priority_id |
---|---|
Data type | String |
The identifier of the issue priority within a Jira site.
It can be used to associate this record with the priority identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | priority_ref |
---|---|
Data type | String |
The rank of the issue priority.
SQL name | sort_order |
---|---|
Data type | BigInt |
The jira_issue_resolution table holds information about each issue resolution.
The table contains the following columns:
The unique identifier of the issue resolution. Use this as the primary key for the table.
SQL name | issue_resolution_id |
---|---|
Data type | String |
The name of the issue resolution.
SQL name | name |
---|---|
Data type | String |
The identifier of the issue resolution within a Jira site.
It can be used to associate this record with the resolution identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | resolution_ref |
---|---|
Data type | String |
Ranks the order of the issue resolutions.
SQL name | sort_order |
---|---|
Data type | BigInt |
The jira_issue_status table holds information about each issue status.
The table contains the following columns:
The name of the category that the issue status belongs to.
SQL name | category_name |
---|---|
Data type | String |
The unique identifier of the issue status. Use this as the primary key for the table.
SQL name | issue_status_id |
---|---|
Data type | String |
The name of the issue status.
SQL name | name |
---|---|
Data type | String |
The rank of the status used to order the issue statuses.
SQL name | status_rank |
---|---|
Data type | BigInt |
The identifier of the issue status within a Jira site.
It can be used to associate this record with the status identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | status_ref |
---|---|
Data type | String |
The jira_issue_type table holds information about each issue type.
The table contains the following columns:
The unique identifier of the issue type. Use this as the primary key for the table.
SQL name | issue_type_id |
---|---|
Data type | String |
The identifier of the issue type within a Jira site.
It can be used to associate this record with the issue type identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | issue_type_ref |
---|---|
Data type | String |
The name of the issue type.
SQL name | name |
---|---|
Data type | String |
The unique identifier of the project that the issue type belongs to.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The jira_project table lists all your Jira projects.
The table contains the following columns:
A reference to the icon of the project. You can use it to create the avatar URL.
SQL name | avatar_ref |
---|---|
Data type | String |
The unique identifier of the project category.
SQL name | category_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_category table. |
The default assignee setting for the project.
SQL name | default_assignee_type |
---|---|
Data type | String |
The description of the project.
SQL name | description |
---|---|
Data type | String |
The unique key of the project.
SQL name | key |
---|---|
Data type | String |
The unique identifier of the person defined as the project lead.
SQL name | lead_account_id |
---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_account table in the data share schema for organization data. |
The name of the project.
SQL name | name |
---|---|
Data type | String |
The unique identifier of the project. Use this as the primary key for this table.
SQL name | project_id |
---|---|
Data type | 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.
SQL name | project_ref |
---|---|
Data type | BigInt |
The type of Jira project (in other words, Jira, Jira Service Management, and so on).
SQL name | type |
---|---|
Data type | String |
The jira_project_category table lists your project categories.
The table contains the following columns:
The unique identifier of the project category. Use this as the primary key for the table.
SQL name | category_id |
---|---|
Data type | String |
The identifier of the project category within a Jira site.
It can be used to associate this record with the category identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | category_ref |
---|---|
Data type | String |
The name of the project category.
SQL name | name |
---|---|
Data type | String |
The jira_project_component table lists your project components.
The table contains the following columns:
The type of assignee that is assigned to issues created with this component. The assignee type for the project component can be one of the following: project lead, unassigned, component lead, or project default.
SQL name | assignee_type |
---|---|
Data type | String |
Example values |
|
The unique identifier of the project component. Use this as the primary key for this table.
SQL name | component_id |
---|---|
Data type | String |
The unique identifier of the person designated as being responsible for issues that have the component.
SQL name | component_lead_id |
---|---|
Data type | String |
The identifier of the component within a Jira site.
SQL name | component_ref |
---|---|
Data type | BigInt |
The name of the project component.
SQL name | name |
---|---|
Data type | String |
The unique identifier of the project that contains the component.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The jira_project_version table lists your project versions.
The table contains the following columns:
The description of the project version.
SQL name | description |
---|---|
Data type | String |
The name of the project version.
SQL name | name |
---|---|
Data type | String |
The unique identifier of the project associated with the project version.
SQL name | project_id |
---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
The unique identifier of the project version. Use this as the primary key for the table.
SQL name | project_version_id |
---|---|
Data type | String |
The release date of the project version.
SQL name | released_at |
---|---|
Data type | date |
Ranks the order of the project versions.
SQL name | sort_order |
---|---|
Data type | BigInt |
The start date of the project version.
SQL name | started_at |
---|---|
Data type | Date |
The status of the project version.
SQL name | status |
---|---|
Data type | String |
The identifier of the project version within a Jira site.
SQL name | name |
---|---|
Data type | BigInt |
The jsm_incident_responder table lists the responders to an incident, as defined in Jira Service Management.
The table contains the following columns:
The unique identifier of the Jira issue representing the incident.
SQL name | issue_id |
---|---|
Data type | String |
The unique identifier of the project containing the incident.
SQL name | project_id |
---|---|
Data type | String |
The unique identifier of the responder. Use this as the primary key for the table.
SQL name | responder_id |
---|---|
Data type | String |
The type of incident responder.
SQL name | responder_type |
---|---|
Data type | String |
Was this helpful?