Schema for Jira Service Management
In addition to the tables for the Jira family of products, Jira Service Management has extra tables added to the Atlassian Data Lake schema.
Tables for service data
There are two tables for service data:
Affected service mapping
Service
Affected service mapping
The jsm_affected_service_mapping table holds associations between Jira Service Management issues and services.
The table uses the following columns:
Issue ID
The unique identifier of a Jira issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table in the schema for Jira family of products. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Service ID*
The unique identifier of a service.
SQL name | service_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the service table. |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Service
The service table lists your Atlassian services defined in Jira Service Management. It uses the following columns:
Created at
The date and time (UTC time zone) when the service was created.
SQL name | created_at |
|---|---|
Data type | Datetime |
Description*
The description of the service.
SQL name | description |
|---|---|
Data type | String |
Opsgenie owner team ID
The unique identifier of the Opsgenie team that owns the service.
SQL name | opsgenie_owner_team |
|---|---|
Data type | String |
Foreign key | Links to a record in the opsgenie_team table in the schema for operations in Jira Service Management. |
Name*
The name of the service.
SQL name | name |
|---|---|
Data type | String |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Service ID
The unique identifier of the service. Use this as the primary key for service data.
SQL name | service_id |
|---|---|
Data type | String |
Tier*
The tier indicating service criticality.
SQL name | tier |
|---|---|
Data type | String |
Type*
The type of service.
SQL name | type |
|---|---|
Data type | String |
Updated at
The date and time (UTC time zone) when the object schema was last updated.
SQL name | updated_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Table for change data
Change
The jsm_change table captures change-related fields for Jira issues in Jira Service Management that have the change-management work category.
The table uses the following columns:
Change completion at
The actual date and time (UTC time zone) that the change ended.
SQL name | change_completion_at |
|---|---|
Data type | Datetime |
Change reason*
A short description or code that indicates why the reporter needs the change.
SQL name | change_reason |
|---|---|
Data type | String |
Change risk*
The risk of implementing the change as determined by the change advisory board. It’s usually based on complexity, scope, testing, recovery, timing, etc.
SQL name | change_risk |
|---|---|
Data type | String |
Example values | Low, Medium, Critical |
Change start at
The actual date and time (UTC time zone) that the change started.
SQL name | change_start_at |
|---|---|
Data type | Datetime |
Change type*
The category of the change.
SQL name | change_type |
|---|---|
Data type | String |
Example values | Emergency, Standard, Normal |
Impact*
The effect of the change, usually in regards to service-level agreements.
SQL name | impact |
|---|---|
Data type | String |
Example value | Minor / Localized |
Issue ID
The unique identifier of the issue. Use this as the primary key for Jira Service Management change data.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jsm_issue table. |
Planned end at
The date and time (UTC time zone) when the change request is planned to end.
SQL name | planned_end_at |
|---|---|
Data type | Datetime |
Planned start at
The date and time (UTC time zone) when the change request is planned to start.
SQL name | planned_start_at |
|---|---|
Data type | Datetime |
Urgency*
The time available before the business feels the request’s impact.
SQL name | urgency |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Tables for incident data
There are two tables for incident data:
Incident
Incident responder mapping
Incident
The jsm_incident table captures change-related fields for Jira issues in Jira Service Management that have the incident-management work category.
The table uses the following columns:
Impact*
The effect of the incident, usually in regards to service level agreements.
SQL name | impact |
|---|---|
Data type | String |
Example value | Significant / Large |
Is major incident
Indicates whether or not an incident is major.
SQL name | is_major_incident |
|---|---|
Data type | Boolean |
Issue ID
The unique identifier of the issue. Use this as the primary key for Jira Service Management incident data.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jsm_issue table. |
Severity*
A measurement of the impact an incident has on the business.
SQL name | severity |
|---|---|
Data type | String |
Example value | Sev-1 |
Source*
The asset or system where the incident originated.
SQL name | source |
|---|---|
Data type | String |
Example value | Monitoring systems |
Urgency*
The time available before the business feels the incident’s impact.
SQL name | urgency |
|---|---|
Data type | String |
Example value | Critical |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Incident responder mapping
The jsm_incident_responder_mapping table holds associations between incidents and responders to an incident.
The table uses the following columns:
Issue ID
The unique identifier of the Jira issue representing the incident.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jsm_issue table. |
Responder account ID
The unique identifier of the incident responder.
SQL name | responder_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Responder type*
The type of responder.
SQL name | responder_type |
|---|---|
Data type | String |
Example values |
|
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Table for issue data
Issue
The jsm_issue table captures fields related to Jira Service Management for Jira issues (requests) in Jira Service Management.
The table uses the following columns:
Issue ID
The unique identifier of the issue. Use this as the primary key for request type data.
SQL name | issue_id |
|---|---|
Data type | String |
Is major incident
Indicates whether or not this issue is associated with a major incident.
SQL name | is_major_incident |
|---|---|
Data type | Boolean |
Priority*
The current issue priority.
SQL name | priority |
|---|---|
Data type | String |
Request type*
The request type of the issue.
SQL name | request_type |
|---|---|
Data type | String |
Satisfaction rating
The rating given as part of a Jira Service Management issue satisfaction survey.
SQL name | satisfaction_rating |
|---|---|
Data type | Number |
Satisfaction submitted at
The date and time (UTC time zone) when the customer satisfaction (CSAT) survey was submitted.
SQL name | satisfaction_submitted_at |
|---|---|
Data type | Datetime |
Work category*
The work category of the request.
SQL name | work_category |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Tables for organization data
There are two tables for organization data:
Issue organization mapping
Organization
Issue organization mapping
The jsm_issue_organization_mapping table holds associations between Jira Service Management issues and organizations.
Issue ID
The unique identifier of the issue the organization was added to.
SQL name | issue_id |
|---|---|
Data type | String |
Organization ID
The unique identifier of the organization that was added to the issue.
SQL name | organization_id |
|---|---|
Data type | String |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Organization
The jsm_organization table lists the organizations added to your Jira Service Management projects.
The table uses the following columns:
Name*
The name of the organization.
SQL name | name |
|---|---|
Data type | String |
Organization ID
The unique identifier of the organization.
SQL name | organization_id |
|---|---|
Data type | String |
Project ID
The unique identifier of the project that the organization is part of.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table in the schema for Jira family of products. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Updated at
The date and time when the organization was last updated.
SQL name | updated_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Table for SLA data
SLA
The jsm_sla table lists the issue service-level agreements (SLAs) defined in Jira Service Management.
The table uses the following columns:
Breached*
Indicates whether or not the SLA was breached.
For ongoing cycles, this value may change over time, along with remaining_time_msecs.
SQL name | breached |
|---|---|
Data type | String |
Example values | true, false, null |
Cycle type*
Indicates whether or not the current SLA cycle is ongoing or completed.
SQL name | cycle_type |
|---|---|
Data type | String |
Example values | Ongoing, Completed |
Elapsed time (milliseconds)*
Represents the time (in milliseconds) that has passed since the SLA cycle started.
SQL name | elapsed_time_msecs |
|---|---|
Data type | Number |
Goal duration (milliseconds)*
Only available for cycles where cycle_type is Completed
Represents time (in milliseconds) taken to complete the current cycle.
SQL name | goal_duration_msecs |
|---|---|
Data type | Number |
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 in the schema for Jira family of products. |
Paused*
Only available for cycles where cycle_type is Completed
Indicates whether or not the SLA cycle is paused.
SQL name | paused |
|---|---|
Data type | String |
Example values | true, false, null |
Remaining time (milliseconds)*
Represents the time (in milliseconds) remaining before the expected SLA limit is breached.
Remaining times are recalculated when the SLA's configuration changes (for example, calendars, goals, or conditions) or when the issue associated with the SLA is updated (for example, assignee or status changed, comments added, and so on). Therefore, the provided value may not represent the actual current remaining time.
SQL name | remaining_time_msecs |
|---|---|
Data type | Number |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
SLA ID*
The unique identifier of an SLA setting.
SQL name | sla_id |
|---|---|
Data type | String |
SLA name*
The name of the SLA that the SLA cycle belongs to.
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 | Datetime |
Stopped at*
Only available for cycles where cycle_type is Completed
The date and time (UTC time zone) when the SLA cycle transitioned from Ongoing to Completed.
SQL name | stopped_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Was this helpful?