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.
Data share schema for Jira family of products
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.
Tables for issue data
Jira issue component mapping
The jira_issue_component_mapping table holds associations between issues and project components.
The table uses the following columns:
Component ID
The unique identifier of a project component.
SQL name | component_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_component table. |
Global component ID
The unique identifier of a global Jira component.
SQL name | global_component_id |
|---|---|
Data type | String |
Issue ID
The unique identifier of an issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Project component ID
The unique identifier of a project component.
SQL name | project_component_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_component table. |
Project ID
The unique identifier of a project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Jira issue enhanced table
The jira_issue_enhanced_table table contains information about all your Jira issues.
The table contains the following columns:
Assignee account ID
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. |
Created at
The date and time (UTC time zone) when the issue was created.
SQL name | created_at |
|---|---|
Data type | Timestamp |
Creator account ID
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. |
Description
The description of the issue.
SQL name | description |
|---|---|
Data type | String |
Due date
The due date (UTC time zone) of the issue.
SQL name | due_date |
|---|---|
Data type | Timestamp |
Environment
A short description of the environment in which the issue occurred.
SQL name | environment |
|---|---|
Data type | String |
Issue ID
The unique identifier of the issue. Use this as the primary key for this table.
SQL name | issue_id |
|---|---|
Data type | String |
Issue key
The number of the issue within the project.
SQL name | issue_key |
|---|---|
Data type | String |
Example value | TEST-12 |
Issue ref
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 |
Issue type
The type of issue.
SQL name | issue_type |
|---|---|
Data type | String |
Original time estimate secs
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 |
Parent issue ID
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. |
Priority
The current issue priority.
SQL name | priority |
|---|---|
Data type | String |
Priority sort order
The sequence order of the priority, matching its order of appearance in the Jira product.
SQL name | priority_sort_order |
|---|---|
Data type | Int |
Project ID
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. |
Reporter account ID
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. |
Resolution
The current issue resolution.
SQL name | resolution |
|---|---|
Data type | String |
Resolution at
The date and time (UTC time zone) when the issue was resolved.
SQL name | resolution_at |
|---|---|
Data type | Timestamp |
Resolution sort order
The sequence order of the resolution, matching its order of appearance in the Jira product.
SQL name | resolution_sort_order |
|---|---|
Data type | Int |
Status
The current issue status.
SQL name | status |
|---|---|
Data type | String |
Status category
The status category of the current issue status.
SQL name | status_category |
|---|---|
Data type | String |
Status sort order
The sequence order of the status, matching its order of appearance in the Jira product.
SQL name | status_sort_order |
|---|---|
Data type | Int |
Story points
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 |
Summary
The summary of the issue.
SQL name | summary |
|---|---|
Data type | String |
Team ID
The unique identifier of the team associated with the issue.
SQL name | team_id |
|---|---|
Data type | String |
Time estimate secs
This column requires time-tracking enabled.
The estimated time (in seconds) remaining from the original estimate.
SQL name | time_estimate_secs |
|---|---|
Data type | Float |
Time spent secs
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 |
Updated at
The date and time (UTC time zone) when the issue was last updated.
SQL name | updated_at |
|---|---|
Data type | Timestamp |
URL
The URL of the issue.
SQL name | url |
|---|---|
Data type | String |
Jira issue field
The jira_issue_field table lists the contents of Jira fields, including custom fields.
The table contains the following columns:
Field ID
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. |
Field type key
Indicates the type of field.
SQL name | field_type_key |
|---|---|
Data type | String |
Issue ID
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. |
Project ID
The unique identifier of a project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Value
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 |
Jira issue field metadata
The jira_issue_field_metadata table holds additional information about each Jira issue field.
The table contains the following columns:
Field ref
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 |
Issue field ID
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. |
Key
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 |
Name
The name of the issue field.
SQL name | name |
|---|---|
Data type | String |
Type
The type of data stored in the issue field.
SQL name | type |
|---|---|
Data type | String |
Jira issue field option
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:
Field ID
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. |
Issue field option ID
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 |
Issue field option ref
The identifier for the issue field option within a Jira site.
SQL name | issue_field_option_ref |
|---|---|
Data type | String |
Value
The value that the issue field option contains.
SQL name | value |
|---|---|
Data type | String |
Jira issue fix version mapping
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:
Issue ID
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. |
Project ID
The unique identifier of a project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Version ID
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. |
Jira issue history change item
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:
Author ID
The unique identifier of the author.
SQL name | author_id |
|---|---|
Data type | String |
Changelog ID
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 |
Created at
The date and time (UTC time zone) when the change was made.
SQL name | created_at |
|---|---|
Data type | Timestamp |
Field
The name of the field that was changed.
SQL name | field |
|---|---|
Data type | String |
Field ID
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. |
Field type
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 |
Issue history ID
The unique identifier of the issue history associated with the history change item.
SQL name | issue_history_id |
|---|---|
Data type | String |
Issue ID
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. |
New string
A string representing the value of the field after the change.
SQL name | new_string |
|---|---|
Data type | String |
New value
The value of the field after the change.
SQL name | new_value |
|---|---|
Data type | String |
Old string
A string representing the value of the field before the change.
SQL name | old_string |
|---|---|
Data type | String |
Old value
The value of the field before the change.
SQL name | old_value |
|---|---|
Data type | String |
Jira issue priority
The jira_issue_priority table holds information about each issue priority.
The table contains the following columns:
Name
The name of the issue priority.
SQL name | name |
|---|---|
Data type | String |
Priority ID
The unique identifier of the issue priority. Use this as the primary key for this table.
SQL name | priority_id |
|---|---|
Data type | String |
Priority ref
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 |
Sort order
The rank of the issue priority.
SQL name | sort_order |
|---|---|
Data type | BigInt |
Jira issue resolution
The jira_issue_resolution table holds information about each issue resolution.
The table contains the following columns:
Issue resolution ID
The unique identifier of the issue resolution. Use this as the primary key for the table.
SQL name | issue_resolution_id |
|---|---|
Data type | String |
Name
The name of the issue resolution.
SQL name | name |
|---|---|
Data type | String |
Resolution ref
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 |
Sort order
Ranks the order of the issue resolutions.
SQL name | sort_order |
|---|---|
Data type | BigInt |
Jira issue status
The jira_issue_status table holds information about each issue status.
The table contains the following columns:
Category name
The name of the category that the issue status belongs to.
SQL name | category_name |
|---|---|
Data type | String |
Issue status ID
The unique identifier of the issue status. Use this as the primary key for the table.
SQL name | issue_status_id |
|---|---|
Data type | String |
Name
The name of the issue status.
SQL name | name |
|---|---|
Data type | String |
Status rank
The rank of the status used to order the issue statuses.
SQL name | status_rank |
|---|---|
Data type | BigInt |
Status ref
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 |
Jira issue type
The jira_issue_type table holds information about each issue type.
The table contains the following columns:
Issue type ID
The unique identifier of the issue type. Use this as the primary key for the table.
SQL name | issue_type_id |
|---|---|
Data type | String |
Issue type ref
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 |
Name
The name of the issue type.
SQL name | name |
|---|---|
Data type | String |
Project ID
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. |
Tables for project data
Jira project
The jira_project table lists all your Jira projects.
The table contains the following columns:
Avatar ref
A reference to the icon of the project. You can use it to create the avatar URL.
SQL name | avatar_ref |
|---|---|
Data type | String |
Category ID
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. |
Default assignee type
The default assignee setting for the project.
SQL name | default_assignee_type |
|---|---|
Data type | String |
Description
The description of the project.
SQL name | description |
|---|---|
Data type | String |
Key
The unique key of the project.
SQL name | key |
|---|---|
Data type | String |
Lead account ID
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. |
Name
The name of the project.
SQL name | name |
|---|---|
Data type | String |
Project ID
The unique identifier of the project. Use this as the primary key for this table.
SQL name | project_id |
|---|---|
Data type | String |
Project ref
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 |
Type
The type of Jira project (in other words, Jira, Jira Service Management, and so on).
SQL name | type |
|---|---|
Data type | String |
Jira project category
The jira_project_category table lists your project categories.
The table contains the following columns:
Category ID
The unique identifier of the project category. Use this as the primary key for the table.
SQL name | category_id |
|---|---|
Data type | String |
Category ref
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 |
Name
The name of the project category.
SQL name | name |
|---|---|
Data type | String |
Jira project component
The jira_project_component table lists your project components.
The table contains the following columns:
Assignee type
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 |
|
Component ID
The unique identifier of the project component. Use this as the primary key for this table.
SQL name | component_id |
|---|---|
Data type | String |
Component lead ID
The unique identifier of the person designated as being responsible for issues that have the component.
SQL name | component_lead_id |
|---|---|
Data type | String |
Component ref
The identifier of the component within a Jira site.
SQL name | component_ref |
|---|---|
Data type | BigInt |
Name
The name of the project component.
SQL name | name |
|---|---|
Data type | String |
Project ID
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. |
Jira project version
The jira_project_version table lists your project versions.
The table contains the following columns:
Description
The description of the project version.
SQL name | description |
|---|---|
Data type | String |
Name
The name of the project version.
SQL name | name |
|---|---|
Data type | String |
Project ID
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. |
Project version ID
The unique identifier of the project version. Use this as the primary key for the table.
SQL name | project_version_id |
|---|---|
Data type | String |
Released at
The release date of the project version.
SQL name | released_at |
|---|---|
Data type | date |
Sort order
Ranks the order of the project versions.
SQL name | sort_order |
|---|---|
Data type | BigInt |
Started at
The start date of the project version.
SQL name | started_at |
|---|---|
Data type | Date |
Status
The status of the project version.
SQL name | status |
|---|---|
Data type | String |
Version ref
The identifier of the project version within a Jira site.
SQL name | version_ref |
|---|---|
Data type | BigInt |
Jira sprint
The jira_sprint table lists your sprints.
The table contains the following columns:
Completed at
The date and time the sprint’s status changed to closed. This signifies that the sprint was completed.
SQL name | completed_at |
|---|---|
Data type | Timestamp |
Created at
The date and time the sprint was created.
SQL name | created_at |
|---|---|
Data type | Timestamp |
Goal
A brief explanation of what the team plans to achieve during the course of the sprint.
SQL name | goal |
|---|---|
Data type | String |
Name
The name of the sprint.
SQL name | name |
|---|---|
Data type | String |
Original board ID
The unique identifier of the board where the sprint was originally planned/created. This doesn’t mean the sprint was started on this board as you can create a sprint on one board then start the sprint on a separate board, and the original board will be where the sprint was created.
SQL name | original_board_id |
|---|---|
Data type | String |
Projected to start at
The date and time the sprint was projected to start.
SQL name | projected_to_start_at |
|---|---|
Data type | Timestamp |
Projected to end at
The date and time the sprint was projected to end.
SQL name | projected_to_end_at |
|---|---|
Data type | Timestamp |
Sort order
The order in which sprints are displayed in the backlog. Moving a sprint up or down will result in a change to the sort order.
SQL name | sort_order |
|---|---|
Data type | BigInt |
Sprint ID
The unique identifier of the sprint.
SQL name | sprint_id |
|---|---|
Data type | String |
Sprint ref
The identifier of the sprint within a Jira site.
SQL name | sprint_ref |
|---|---|
Data type | String |
Status
The current status of the sprint.
SQL name | status |
|---|---|
Data type | String |
Example values |
|
Tables specific to Jira Service Management
Atlassian service refined
Created at
The date and time the service was created.
SQL name | created_at |
|---|---|
Data type | Timestamp |
Description
A description of the service and the functionality it provides.
SQL name | description |
|---|---|
Data type | String |
Name
The name of the service.
SQL name | name |
|---|---|
Data type | String |
Opsgenie owner team ID
The unique identifier of the Opsgenie team that owns the service.
SQL name | opsgenie_owner_team_id |
|---|---|
Data type | String |
Service ID
The unique identifier of the service.
SQL name | service_id |
|---|---|
Data type | String |
Tier
Indicates how critical a service is to the operation of your business.
SQL name | tier |
|---|---|
Data type | String |
Example values | tier1 (most critical), tier2, tier3, tier4 (least critical) |
Type
The type of service.
SQL name | type |
|---|---|
Data type | String |
Updated at
The date and time the service was last updated.
SQL name | updated_at |
|---|---|
Data type | Timestamp |
JSM affected service
Affected services ID
The unique identifier of the affected service.
SQL name | affected_services_id |
|---|---|
Data type | String |
Field ID
The unique identifier of a field.
SQL name | field_id |
|---|---|
Data type | String |
Issue ID
The unique identifier of an issue.
SQL name | issue_id |
|---|---|
Data type | String |
Project ID
The unique identifier of a project.
SQL name | project_id |
|---|---|
Data type | String |
JSM incident responder
The jsm_incident_responder table lists the responders to an incident, as defined in Jira Service Management.
The table contains the following columns:
Issue ID
The unique identifier of the Jira issue representing the incident.
SQL name | issue_id |
|---|---|
Data type | String |
Project ID
The unique identifier of the project containing the incident.
SQL name | project_id |
|---|---|
Data type | String |
Responder ID
The unique identifier of the responder. Use this as the primary key for the table.
SQL name | responder_id |
|---|---|
Data type | String |
Responder type
The type of incident responder.
SQL name | responder_type |
|---|---|
Data type | String |
Jira request type
The jira_request_type table lists your Jira Service Management request types.
The table contains the following columns:
Description
The description of the request type.
SQL name | description |
|---|---|
Data type | String |
Help text
Help text for the request type.
SQL name | help_text |
|---|---|
Data type | String |
Icon ref
The identifier of the request type icon.
SQL name | icon_ref |
|---|---|
Data type | String |
Issue type ID
The unique identifier of the issue type.
SQL name | issue_type_id |
|---|---|
Data type | String |
Name
The name of the request type.
SQL name | name |
|---|---|
Data type | String |
Portal ref
The identifier of the customer portal associated with the service desk project.
SQL name | portal_ref |
|---|---|
Data type | String |
Project ID
The unique identifier of the project.
SQL name | project_id |
|---|---|
Data type | String |
Request type ID
The unique identifier of the request type.
SQL name | request_type_id |
|---|---|
Data type | String |
Request type ref
The identifier of the request type within a Jira site.
SQL name | request_type_ref |
|---|---|
Data type | String |
JSM SLA
The jsm_sla table lists the issue service-level agreements (SLAs) defined in Jira Service Management.
Breached
Indicated whether or not the SLA was breached.
SQL name | breached |
|---|---|
Data type | String |
Cycle type
Indicates whether or not the current SLA cycle is ongoing or completed.
SQL name | cycle_type |
|---|---|
Data type | String |
Elapsed time
Represents the time (in milliseconds) that has passed since the SLA cycle started.
SQL name | elapsed_time |
|---|---|
Data type | BigInt |
Field ID
SQL name | field_id |
|---|---|
Data type | String |
Goal duration
Represents time (in milliseconds) taken to complete the current cycle.
SQL name | goal_duration |
|---|---|
Data type | BigInt |
Issue ID
The unique identifier of a Jira issue associated with the SLA.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Paused
Indicates whether or not the SLA cycle is paused.
SQL name | paused |
|---|---|
Data type | String |
Project ID
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Remaining time
Represents the time (in milliseconds) remaining before the expected SLA limit is breached.
Remaining times are calculated and updated every 30 minutes. Therefore, the provided value may not represent the actual current remaining time.
SQL name | remaining_time |
|---|---|
Data type | BigInt |
SLA ID
The unique identifier of the SLA.
SQL name | sla_id |
|---|---|
Data type | String |
SLA name
The name of the SLA.
SQL name | sla_name |
|---|---|
Data type | String |
Started at
The date and time (UTC time zone) when the SLA cycle started.
SQL name | started_at |
|---|---|
Data type | Timestamp |
Stopped at
The date and time (UTC time zone) when the SLA cycle transitioned from Ongoing to Completed.
SQL name | stopped_at |
|---|---|
Data type | Timestamp |
Was this helpful?
Still need help?
The Atlassian Community is here for you.