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

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	Team User

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

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 calculated and updated every 30 minutes. 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?

It wasn't accurate

It wasn't clear

It wasn't relevant

Still need help?

The Atlassian Community is here for you.

Ask the Community