Schema for operations in Jira Service Management

Opsgenie data is now referred to as operations data under Jira Service Management.

If you include operations data from Jira Service Management to an Atlassian Data Lake connection, you can query that data from the following tables:

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.

Tables for account data

There are several tables for operations account data:

Account logged in
Account settings
Account settings extra property
Custom account role

Account logged in

The opsgenie_account_logged_in table lists the set of operations-specific attributes associated with each Atlassian account that uses operations in Jira Service Management.

The table uses the following columns:

Account ID

The unique identifier of the Atlassian account associated with the logged-in Jira Service Management account.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Account logged in ID

The unique identifier for the set of attributes.

SQL name	`account_logged_in_id`
Data type	String

Expired at

The date and time (UTC time zone) when the account login cookie expired for the account.

SQL name	`expired_at`
Data type	Datetime

Logged in at

The date and time (UTC time zone) when the account last logged in to Jira Service Management.

SQL name	`logged_in_at`
Data type	Datetime

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.

Account settings

The opsgenie_account_settings table lists the set of operations-specific attributes associated with each Atlassian account that uses operations in Jira Service Management.

The table uses the following columns:

Account settings ID

The unique identifier for the set of attributes.

SQL name	`account_settings_id`
Data type	String

Atlassian account ID

The unique identifier of the Atlassian account associated with the account settings.

SQL name	`atlassian_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Created at

The date and time (UTC time zone) when the account was created in the workspace.

SQL name	`created_at`
Data type	Datetime

Custom role ID

The unique identifier of the custom role that was assigned to the account.

SQL name	`custom_role_id`
Data type	String

Default role*

The predefined role that was automatically provisioned to the account.

SQL name	`default_role`
Data type	String

Is verified*

Indicates whether or not the account has logged in and verified their credentials.

SQL name	`is_verified`
Data type	Boolean

Labels*

User-defined strings attached to accounts for easier identification and categorization.

SQL name	`labels`
Data type	Array of strings

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Time zone

The time zone of the account.

SQL name	`time_zone`
Data type	String

Updated at

The date and time (UTC time zone) when then the account 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.

Account settings extra property

The opsgenie_account_settings_extra_property table lists additional key-value pairs related with an Atlassian account that uses operations in Jira Service Management.

The table uses the following columns:

Account settings ID

The unique identifier for the set of attributes associated with the key-value pair.

SQL name	`account_settings_id`
Data type	String

Key*

The key of the property.

SQL name	`key`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Value*

The value of the property.

SQL name	`value`
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.

Custom account role

The opsgenie_custom_account_role table lists the custom account roles for operations. Custom account roles start with a base role, which is either user or stakeholder. Base roles are already-defined roles where you can extend or remove the base role permissions to give accounts more flexibility in your operations.

The table uses the following columns:

Admin right*

The designated admin right if extended_role is user.

SQL name	`admin_right`
Data type	String
Example values	no-admin-rights read-only only-edit-configuration edit-and-delete-configuration

Created at

The date and time (UTC time zone) when the custom account role was created.

SQL name	`created_at`
Data type	Datetime

Custom account role ID

The unique identifier of the custom account role.

SQL name	`custom_account_role_id`
Data type	String

Extended role*

The base role whose permissions were extended.

SQL name	`extended_role`
Data type	String
Example values	user, stakeholder

Name*

The name of the custom account role.

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

Updated at

The date and time (UTC time zone) when the custom account role 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.

Tables for alert data

There are several tables for alert data:

Alert
Alert escalated
Alert max team escalated
Alert ownership assign time
Alert property
Alert recipient
Alert responder mapping
Alert routed
Alert routed to team

Alert

The opsgenie_alert table lists all of your alerts in Jira Service Management.

The table uses the following columns:

Acknowledged by account ID

The unique identifier of the person who acknowledged the alert.

SQL name	`acknowledged_by_account_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_user` table; however, this could also contain only a string value that does not relate to the `opsgenie_user` table.

Acknowledged duration (milliseconds)

The time (in milliseconds) it took to acknowledge the alert.

SQL name	`acknowledged_duration_milliseconds`
Data type	Number

Actions*

User-defined actions for the alert.

SQL name	`actions`
Data type	Array of strings
Example value	`["Restart", "Ping"]`

Alert ID

The unique alert identifier. Use it as the primary key for alert data.

SQL name	`alert_id`
Data type	String

Alert owner account ID

The unique identifier of the person who owns the alert.

SQL name	`alert_owner_account_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_user` table; however, this could also contain only a string value that does not relate to the`opsgenie_user` table.

Alias*

The alias used for the alert.

SQL name	`alias`
Data type	String

Closed by account ID

The unique identifier of the person who closed the alert.

SQL name	`closed_by_account_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_user` table; however, this could also contain only a string value that does not relate to the `opsgenie_user` table.

Closed duration (milliseconds)

The time (in milliseconds) it took to close the alert.

SQL name	`closed_duration_milliseconds`
Data type	Number

Created at

The date and time (UTC time zone) when the alert was created.

SQL name	`created_at`
Data type	Datetime

Deduplication count*

The number of times the alert occurred.

SQL name	`deduplication_count`
Data type	Number

Description*

The description of the alert.

SQL name	`description`
Data type	String

Entity*

The user-defined entity to which the alert belongs.

SQL name	`entity`
Data type	String

First acknowledged duration (milliseconds)

The amount of time (in milliseconds) it takes for the responder to acknowledge the alert for the first time. This will remain consistent even after the alert is un-acknowledged and re-acknowledged.

SQL name	`first_acknowledged_duration_milliseconds`
Data type	Number

Integration ID

The integration used to raise this alert.

SQL name	`integration_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_integration` table.

Labels*

The labels associated with the alert.

SQL name	`labels`
Data type	Array of strings

Last occurred at*

The date and time (UTC time zone) that the alert last occurred.

SQL name	`last_occurred_at`
Data type	Datetime

Message*

The message of the alert.

SQL name	`message`
Data type	String

Priority*

The priority of the alert.

SQL name	`priority`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Snoozed until*

The date and time (UTC time zone) when the alert is no longer snoozed.

SQL name	`snoozed_until`
Data type	Datetime

Source

The source that triggered the alert.

SQL name	`source`
Data type	String

Status*

The status of the alert.

SQL name	`status`
Data type	String

Tiny ID

The tiny ID associated with an alert. It will repeat after 99,999.

SQL name	`tiny_id`
Data type	String

Updated at

The last date and time (UTC time zone) the alert was updated.

SQL name	`updated_at`
Data type	Datetime

Was acknowledged*

true if alert was acknowledged; otherwise, false.

SQL name	`was_acknowledged`
Data type	Boolean

Was seen

true if the alert is seen; otherwise, false.

SQL name	`was_seen`
Data type	Boolean

Was snoozed*

true if the alert is snoozed; otherwise, false.

SQL name	`was_snoozed`
Data type	Boolean

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.

Alert escalated

The opsgenie_alert_escalated table records escalated alert events and whether they’re routed to accounts, groups, teams, or schedules. An escalated alert event is created when the alert state matches an escalation rule.

The table uses the following columns:

Action

The action that was performed during the event.

SQL name	`action`
Data type	String
Example value	The value is always escalated.

Alert escalated ID

The unique identifier of the escalated alert event. Use this as the primary key for the table.

SQL name	`alert_escalated_id`
Data type	String

Alert ID

The unique identifier of the alert that matched the state of the escalation rule.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Delay minutes*

The amount of minutes after the alert has been created and not been acknowledged or closed before routing to the recipient of the escalation rule.

SQL name	`delay_minutes`
Data type	Number

Escalated to account ID

The unique identifier of the account that the alert was escalated to.

SQL name	`escalated_to_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Escalated to group reference

The identifier of the group that the alert was escalated to.

SQL name	`escalated_to_group_ref`
Data type	String

Escalated to schedule ID

The unique identifier of the schedule that the alert was escalated to.

SQL name	`escalated_to_schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Escalated to team ID

The unique identifier of the team that the alert was escalated to.

SQL name	`escalated_to_team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

Escalation ID

The unique identifier of the escalation that has the rule that matched with the state of the alert.

SQL name	`escalation_id`
Data type	String

Escalation type*

The type of escalation, which refers to where the escalation action came from.

SQL name	`escalation_type`
Data type	String

Occurred at

The date and time (UTC time zone) when the alert was escalated.

SQL name	`occurred_at`
Data type	Datetime

Repeat escalation rule count*

Repeat functionality must be enabled for the escalation.

The number of times (20 max) we’ll repeat the entire escalation notification sequence.

SQL name	`repeat_escalation_rule_count`
Data type	Number

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Rule index

For each escalation, there are one or more escalation rules specified. The rules are ordered by ascending time. If the escalation occurred using the first rule, then the index is 0. If it occurred using the second rule, then the index is 1, and so on.

SQL name	`rule_index`
Data type	Number

Source*

The source from which the event was triggered, it is the string value: Alert Escalated by Rule.

SQL name	`source`
Data type	String
Example value	The value is always Alert Escalated by Rule.

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.

Alert max team escalated

The opsgenie_alert_max_team_escalated lists the max details for an alert escalated by an escalation policy owned by a team.

The table uses the following columns:

Alert ID

The unique identifier of the escalated alert.

SQL name	`alert_id`
Data type	String

Max escalated at

The maximum date and time (UTC time zone) when the alert was escalated using an escalation policy of the owner team.

SQL name	`max_escalated_at`
Data type	Datetime

Max escalation level

The maximum rule index of an escalation used to escalate the alert. It starts at 0.

SQL name	`max_escalation_level`
Data type	Number

Owner team ID

The unique identifier of the team who owns the escalation policy.

SQL name	`owner_team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

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.

Alert ownership assign time

The opsgenie_alert_ownership_assign_time table lists the records for alerts and corresponding assignment time.

The table uses the following columns:

Alert ID

The unique identifier of the alert being assigned ownership.

SQL name	`alert_id`
Data type	String

Alert ownership assign time ID

The unique identifier of the alert ownership assign time event. Use this as the primary key for the table.

SQL name	`alert_ownership_assign_time_id`
Data type	String

Assigned at

The date and time (UTC time zone) when the alert was assigned.

SQL name	`assigned_at`
Data type	Datetime

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.

Alert property

The opsgenie_alert_property table lists all of your alert properties.

The table uses the following columns:

Alert ID

The unique identifier of the alert. Use it with the alert’s key as the primary key for alert details data.

SQL name	`alert_id`
Data type	String

Key*

The detail key of the alert. Use it with the alert_id as the primary key for alert details data.

SQL name	`key`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Value*

The detail value of the alert

SQL name	`value`
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.

Alert recipient

The opsgenie_alert_recipient table records the alerts with the recipient account categorized by delivery method and status.

The table uses the following columns:

Account ID

The unique identifier of the account that received the alert.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Alert ID

The unique identifier of the alert that was sent to the recipient.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Alert recipient ID

The unique identifier of the alert recipient. Use this as the primary key for the table.

SQL name	`alert_recipient_id`
Data type	String

Created at

The date and time (UTC time zone) when the alert recipient was created.

SQL name	`created_at`
Data type	Datetime

Delivery method*

The delivery method that was used to notify the alert recipient.

SQL name	`delivery_method`
Data type	String

Delivery status*

The current status of sending the alert to the recipient.

SQL name	`delivery_status`
Data type	String
Example values	ACTION ESCALATION FORWARDED MUTE NOT_ACTIVE NO_RULE PENDING QUIET_HOURS RECEIVED SEEN SEND_FAILED SENT

Is aware

Indicates whether or not the recipient is aware of the alert. A recipient is aware of the alert if one of the following is true:

The recipient has seen the alert
The recipient has taken an action
The delivery status is either RECEIVED or SENT

SQL name	`is_aware`
Data type	Boolean

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Was seen

Indicates whether or not the recipient has seen the alert. A recipient has seen the alert if the recipient has done one of the following:

Closed the alert
Taken ownership of the alert
Acknowledged the alert
Viewed the alert description from either mobile or web

SQL name	`was_seen`
Data type	Boolean

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.

Alert responder mapping

The opsgenie_alert_responder_mapping table holds associations between alerts and alert responders.

The table uses the following columns:

Account ID

The unique identifier of the account responder, if the alert was routed to an account.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Alert ID

The unique identifier of the alert.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Escalation ID

The unique identifier of the escalation responder, if the alert was routed to an escalation.

SQL name	`escalation_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_escalation` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule ID

The unique identifier of the schedule responder, if the alert was routed to a schedule.

SQL name	`schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Team ID

The unique identifier of the team that the responder belongs to.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` 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.

Alert routed

The opsgenie_alert_routed table records when an alert is routed to a team, account, or schedule and also keeps track of primary recipients for those alerts.

The table uses the following columns:

Action

The action that was performed during the event.

SQL name	`action`
Data type	String
Example value	The value is always routed.

Alert ID

The unique identifier of the routed alert.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Alert routed ID

The unique identifier of the routed alert event. Use this as the primary key for the table.

SQL name	`alert_routed_id`
Data type	String

Escalation ID

The unique identifier of the escalation that triggered the event of the routed alert.

SQL name	`escalation_id`
Data type	String

Is primary recipient*

Indicates whether or not the alert recipient was the primary recipient (on-call).

SQL name	`is_primary_recipient`
Data type	Boolean

Occurred at

The date and time (UTC time zone) when the event for the routed alert occurred.

SQL name	`occurred_at`
Data type	Datetime

Routed to account ID

The unique identifier of the Atlassian account to whom the alert was routed.

SQL name	`routed_to_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Routed to schedule ID

The unique identifier of the on-call schedule to which the alert was routed.

SQL name	`routed_to_schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Routed to team ID

The unique identifier of the team to whom the alert was routed.

SQL name	`routed_to_team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Source*

The source that triggered the event.

SQL name	`source`
Data type	String
Example value	The value is always Alert Escalation Routing.

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.

Alert routed to team

The opsgenie_alert_routed_to_team table captures each event when an alert is routed to a team or account from an escalation rule.

The table uses the following columns:

Action

The action that was performed during this event.

SQL name	`action`
Data type	String
Example value	The value is always routed.

Alert ID

The unique identifier of the alert that was routed.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Alert routed ID

The unique identifier of the alert routing event. Use this as the primary key for the table.

SQL name	`alert_routed_id`
Data type	String

Is primary recipient*

Indicates if the recipient of the routed alert was the primary recipient.

SQL name	`is_primary_recipient`
Data type	Boolean

Occurred at

The date and time (UTC time zone) the alert was routed.

SQL name	`occurred_at`
Data type	Datetime

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Source*

The source from which the event was triggered.

SQL name	`source`
Data type	String
Example value	The value is always Alert Escalation Routing.

Team ID

The unique identifier of the team that the alert was routed to.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` 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.

Table for notification and incoming call data

Incoming call history

The opsgenie_incoming_call_history table hold details about the incoming calls to establish a relation between the caller number, integration number, and call duration for an account. An incoming call is an event associated with a call routing integration. When a call is placed to a number that is configured to receive incoming calls, that call is routed to the appropriate responders to take action.

The table uses the following columns:

Agent number

The phone number of the agent that got called by the integration number.

SQL name	`agent_number`
Data type	String

Call duration (seconds)

The duration of the call in seconds. This includes from when the customer starts the calls to when it ends.

SQL name	`call_duration_seconds`
Data type	Number

Called account ID

The unique identifier of the Atlassian account of the agent who received the call.

SQL name	`called_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Caller number

The phone number of the person that placed the call.

SQL name	`caller_number`
Data type	String

Created at

The date and time (UTC time zone) when the incoming call event occurred.

SQL name	`created_at`
Data type	Datetime

Incoming call ID

The unique identifier for the set of attributes. Use this as the primary key for the table.

SQL name	`incoming_call_id`
Data type	String

Incoming call reference

The in-product identifier of the incoming call. It maps the history of all actions to an incoming call.

SQL name	`incoming_call_ref`
Data type	String

Integration number*

The number that the caller calls. The integration number ultimately routes the call to an agent. Each agent has their own agent number.

SQL name	`integration_number`
Data type	String

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Status*

The status of the incoming call.

SQL name	`status`
Data type	String
Example values	`agent_call_failure` `agent_responded` `agent_not_responded` `call_started` `call_ended_answered` `call_ended_no_one_answered` `call_ended_voicemail_sent` `customer_started_call` `no_one_responded` `voicemail_sent`

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.

Opsgenie notification

The opsgenie_notification table lists all Opsgenie notifications.

The table uses the following columns:

Account type*

The type of account that is assigned to the user.

SQL name	`account_type`
Data type	String
Example values	responder, stakeholder

Created at

The date and time (UTC time zone) when the issue was created.

SQL name	`created_at`
Data type	Datetime

Delivery method*

The method used for the notification.

SQL name	`delivery_method`
Data type	String
Example values	VOICE, SMS, MOBILE, and EMAIL

Is mass notification*

true when it is a mass notification; otherwise, false.

SQL name	`is_mass_notification`
Data type	Boolean

Notification ID

The unique identifier of the notification. Use it as the primary key for notification data.

SQL name	`notification_id`
Data type	String

Notified account ID

The unique identifier of the notified user.

SQL name	`notified_account_id`
Data type	String
Foreign key	Links to a record in the `opsgenier_user` table.

Reason*

The reason for the notification.

SQL name	`reason`
Data type
Example values	`Create`, `Weekly Report`, `notificationAggregation`, `scheduleEnd`, `scheduleStart`, `Close`, `AddNote`, `Acknowledge`, `AddResponder`, `rightToBeForgotten`, `simpleGenericSms`, `userRevokeFailed`, `customerDataDeletion`, and `AssignOwnership`

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Sent at*

The date and time (UTC time zone) when the notification was sent.

SQL name	`sent_at`
Data type	Datetime

Source alert ID

The unique identifier of the alert that is associated with the notification, if the source_type is alert.

SQL name	`source_alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Source schedule ID

The unique identifier of the schedule that is associated with the notification, if the source_type is schedule.

SQL name	`source_schedule_id`
Data type	String

Source type*

The domain of notification for which it was triggered.

SQL name	`source_type`
Data type	String
Example values	ALERT, SCHEDULE, AGGREGATED, SYSTEM, and reporting

Status*

The status of the notification.

SQL name	`status`
Data type	String
Example values	SENT, RECEIVED, and FAILED

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 team data

There are two tables for Opsgenie team data:

Opsgenie team
Opsgenie team member

Team

The opsgenie_team table lists your Opsgenie teams.

The table uses the following columns:

Created at

The date and time (UTC time zone) when the team was created.

SQL name	`created_at`
Data type	Datetime

Description*

The team description.

SQL name	`description`
Data type	String

Name*

The name of the team.

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

Team ID

The unique identifier of the team. Use this as the primary key for Opsgenie team data.

SQL name	`team_id`
Data type	String

Updated at

The date and time (UTC time zone) when the team 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.

Opsgenie team member

The opsgenie_team_member table lists all Opsgenie team members.

The table uses the following columns:

Account ID

The unique identifier of the Atlassian account associated with the team member.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Custom team role ID

The unique identifier of the custom team role.

SQL name	`custom_team_role_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_custom_team_role` table.

Default team role ID*

The unique identifier of the default team role.

SQL name	`default_team_role_id`
Data type	String
Example values	user, admin

Role type*

The role type of the team member.

SQL name	`role_type`
Data type	String
Example values	custom team role, default team role

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Team ID

The unique identifier of the team that the team member belongs to.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

Team member ID

The unique identifier of the team member.

SQL name	`team_member_id`
Data type	String

Team role ID

The unique identifier of the team role that the team member has.

SQL name	`team_role_id`
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 several tables for incident data:

Incident alert mapping
Incident impacted service mapping
Opsgenie incident
Opsgenie incident alert mapping
Opsgenie incident responder mapping

Incident alert mapping

The opsgenie_incident_alert_mapping table represents the relationship between an incident and the alerts that are related to the incident. Alerts can be associated together to create an incident. Alerts are also sent to service owners and responders to alert them of the incident.

The table uses the following columns:

Alert ID

The unique identifier of the alert that caused an incident to be declared.

SQL name	`alert_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_alert` table.

Created at

The date and time (UTC time zone) the incident to alert mapping was created.

SQL name	`created_at`
Data type	Datetime

Incident ID

The unique identifier of the incident that was declared as a result of alerts that were received.

SQL name	`incident_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_incident` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Type*

The type of incident alert, which can be either associated or responder.

SQL name	`type`
Data type	String
Example values	associated, responder

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 impacted service mapping

The opsgenie_incident_impacted_service_mapping table holds associations between incidents and impacted Atlassian services.

The table uses the following columns:

Atlassian service ID

The unique identifier of the impacted Atlassian service.

SQL name	`atlassian_service_id`
Data type	String
Foreign key	Links to a record in the `service` table in the schema for Jira Service Management.

Incident ID

The unique identifier of an incident.

SQL name	`incident_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_incident` table.

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.

Opsgenie incident

The opsgenie_incident table lists all your Opsgenie incidents. Incidents are unplanned interruptions to or quality reductions of a service. Learn more about Opsgenie incidents.

The table uses the following columns:

Closed at

The date and time (UTC time zone) the incident was closed.

SQL name	`closed_at`
Data type	Datetime

Closed by account ID

The unique identifier of the person who closed the incident.

SQL name	`closed_by_account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Closed by system*

The system that closed the incident.

SQL name	`closed_by_account_id`
Data type	String

Created at

The date and time (UTC time zone) the incident was generated.

SQL name	`closed_at`
Data type	Datetime

Description*

A long description related with the incident.

SQL name	`description`
Data type	String

Impact detected at*

The date and time (UTC time zone) that the impact of the incident was first detected.

This value will be null until a date and time are manually added in Opsgenie.

SQL name	`impact_detected_at`
Data type	Datetime

Impact ended at*

The date and time (UTC time zone) that the impact of the incident ended.

This value will be null until a date and time are manually added in Opsgenie.

SQL name	`impact_ended_at`
Data type	Datetime

Impact started at*

The date and time (UTC time zone) that the impact of the incident started.

This value will be null until a date and time are manually added in Opsgenie.

SQL name	`impact_started_at`
Data type	Datetime

Incident ID

The unique identifier of the incident.

SQL name	`incident_id`
Data type	String

Is postmortem required*

Indicates if the incident requires a postmortem to be created.

The default value is true, indicating a postmortem is required.

SQL name	`is_postmortem_required`
Data type	Boolean

Labels*

Client-defined string attached to incidents for easier identification and categorization of incidents.

SQL name	`labels`
Data type	Array of strings

Message*

A summary of the cause for the incident to be generated.

SQL name	`message`
Data type	String

Postmortem due at*

The date and time (UTC time zone) that the postmortem is due to be completed.

SQL name	`postmortem_due_at`
Data type	Datetime

Priority*

The importance of the incident, indicated by the following values: p1, p2, p3, p4, or p5. p1 has the highest level of importance, and p5 has the lowest level.

SQL name	`priority`
Data type	String

Resolved at

The date and time (UTC time zone) the incident was resolved.

SQL name	`resolved_at`
Data type	Datetime

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Status*

The current state of an incident, which will be either open, closed, or resolved.

SQL name	`status`
Data type	String
Example values	open: The incident has not been resolved closed: The incident no longer requires attention resolved: The incident is resolved and no further action needs to be taken, unless further impact is detected

Tiny ID

An identifier assigned to all incidents in a workspace.

It has a max value of 9,999 and will loop back to 1 when the max value has been exceeded.

SQL name	`tiny_id`
Data type	String

Updated at

The date and time (UTC time zone) the incident was 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.

Opsgenie incident responder mapping

The opsgenie_incident_responder_mapping table holds associations between incidents and incident responders.

The table uses the following columns:

Incident ID

The unique identifier of an incident.

SQL name	`incident_id`
Date type	String
Foreign key	Links to a record in the `opsgenie_incident` table.

Responder account ID

The unique identifier of the incident responder if the responder is an account.

SQL name	`responder_account_id`
Date type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Responder team ID

The unique identifier of the incident responder if the responder is an Opsgenie team.

SQL name	`responder_team_id`
Date type	String
Foreign key	Links to a record in the `opsgenie_team` table.

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.

Tables for integration data

There are two tables for integration data:

API called
Integration

API called

The opsgenie_api_called table captures how many times API calls are triggered by an integration.

The table uses the following columns:

API called ID

The unique identifier of the event for the called API. Use this as the primary key for the table.

SQL name	`api_called_id`
Date type	String

API count

The cumulative count of API calls made by a specific integration and domain.

SQL name	`api_count`
Date type	Number

Domain

The domain that triggered the API.

SQL name	`domain`
Date type	String

Domain group

The domain group that triggered the API.

SQL name	`domain_group`
Date type	String

Integration ID

The unique identifier of the integration that made the API call.

SQL name	`integration_id`
Date type	String
Foreign key	Links to a record in the `opsgenie_incident` table.

Occurred at

The date and time (UTC time zone) for the cumulative recording for a specific API call.

SQL name	`occurred_at`
Date type	Datetime

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.

Integration

The opsgenie_integration table lists all your integrations in Jira Service Management.

The table uses the following columns:

Integration ID

The unique identifier of the integration.

SQL name	`integration_id`
Data type	String

Is enabled*

Indicates whether or not the integration is enabled.

SQL name	`is_enabled`
Data type	Boolean

Name*

The name of the integration.

SQL name	`name`
Data type	String

Owner team ID

The unique identifier of the team that owns the integration.

SQL name	`owner_team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Type

The type of integration. Learn more about integration types.

SQL name	`type`
Data type	String

Version*

The version of the integration API.

SQL name	`version`
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 schedule and on-call data

There are several tables for schedule and on-call data:

On call responder
On call responder participant
On call responder period
Schedule
Schedule rotation
Schedule rotation participant
Schedule rotation time restriction

On call responder

The opsgenie_on_call_responder records the event times when the on-call account starts and ends for a particular schedule and rotation.

The table uses the following columns:

Action

Indicates whether the event is the start or the end time for the on-call account.

SQL name	`action`
Data type	String
Example values	start end

Occurred at

The date and time (UTC time zone) when the event occurred.

SQL name	`occurred_at`
Data type	Datetime

On call responder ID

The unique identifier for the set of attributes. Use this as the primary key for the table.

SQL name	`on_call_responder_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

Schedule ID

The unique identifier of the associated schedule.

SQL name	`schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Schedule rotation ID

The unique identifier of the associated schedule rotation.

SQL name	`schedule_rotation_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule_rotation` 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.

On call responder participant

The opsgenie_on_call_responder_participant table has participant details for the on-call responder.

The table uses the following columns:

Account ID

The unique identifier of the associated Atlassian account of the participant.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Escalation ID

The unique identifier of the associated escalation.

SQL name	`escalation_id`
Data type	String

Group reference

The identifier of the associated group of the participant.

SQL name	`group_ref`
Data type	String

On call responder ID

The unique identifier of the on-call responder.

SQL name	`on_call_responder_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_on_call_responder` table.

Responder type*

The type of responder participant.

SQL name	`responder_type`
Data type	String
Example values	account team group escalation no-one

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Team ID

The unique identifier of the associated team of the participant.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` 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.

On call responder period

The opsgenie_on_call_responder_period table lists the on-call information for a user. It gives the information on when the onCall started for a user and when it ended.

The table uses the following columns:

Account ID

The unique identifier of the Atlassian account associated with the person who was on call.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Ended at

The date and time (UTC time zone) when the person ended their on-call. If the person has yet to complete their on-call, then the value will show the system date.

SQL name	`ended_at`
Data type	Datetime

On call responder ID

The unique identifier of the on-call responder.

SQL name	`on_call_responder_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_on_call_responder` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule ID

The unique identifier of the associated schedule.

SQL name	`schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Schedule rotation ID

The unique identifier of the associated schedule rotation.

SQL name	`schedule_rotation_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule_rotation` table.

Started at

The date and time (UTC time zone) when the person started their on-call.

SQL name	`started_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.

Schedule

The opsgenie_schedule table lists the information for all schedules.

The table uses the following columns:

Description*

The description of the schedule that’s used to provide detailed information about the schedule.

SQL name	`description`
Data type	String

Is enabled*

Indicates whether or not the schedule is enabled.

SQL name	`is_enabled`
Data type	Boolean

Name*

The name of the schedule.

SQL name	`name`
Data type	String

Owner team ID

The unique identifier of the team that owns the schedule.

SQL name	`owner_team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` table.

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule ID

The unique identifier of the schedule. Use this as the primary key for schedule data.

SQL name	`schedule_id`
Data type	String

Time zone

The time zone of the schedule.

SQL name	`time_zone`
Data type	String

Updated at

The date and time (UTC time zone) when the schedule 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.

Schedule rotation

The opsgenie_schedule_rotation table lists the information for all schedule rotations.

The table uses the following columns:

Ended at

The date and time (UTC time zone) when the schedule rotation ended, if it does end.

SQL name	`ended_at`
Data type	Datetime

Length*

The length value that the rotation type should switch to the next on-call participant. Default is 1. Based on the rotation type, it’ll rotate every type duration. For example, if length is 1 and rotation_type is hourly, it’ll rotate every hour.

SQL name	`length`
Data type	Number

Name*

The name of the schedule rotation.

SQL name	`name`
Data type	String

Rotation type*

The type of rotation, which will be either hourly, daily, or weekly. Note that schedule rotations could rotate every three days instead of every day, for example, but the rotation type is still considered daily.

SQL name	`rotation_type`
Data type	String
Example values	hourly daily weekly

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule ID

The unique identifier of the schedule associated with the schedule rotation.

SQL name	`schedule_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule` table.

Schedule rotation ID

The unique identifier of the schedule rotation. Use this as the primary key for the table.

SQL name	`rotation_id`
Data type	String

Sequence

The creation order of the rotation per schedule. If there are multiple rotations in a schedule, this shows their order of creation, in ascending order.

SQL name	`sequence`
Data type	String

Started at

The date and time (UTC time zone) when the schedule rotation started.

SQL name	`started_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.

Schedule rotation participant

The opsgenie_schedule_rotation_participant lists the rotation participants that will be on-call.

Account ID

The unique identifier of the associated Atlassian account, if “Participant type” is SCHEDULE_ROTATION_ACCOUNT.

SQL name	`account_id`
Data type	String
Foreign key	Links to a record in the `account` table in the schema for organization data.

Escalation ID

The unique identifier of the associated participant escalation, if “Participant type” is SCHEDULE_ROTATION_ESCALATION.

SQL name	`escalation_id`
Data type	String

Group reference

The identifier of the associated participant group, if “Participant type” is SCHEDULE_ROTATION_GROUP.

SQL name	`group_ref`
Data type	String

Participant type*

The type of participant.

SQL name	`participant_type`
Data type	String
Example values	SCHEDULE_ROTATION_ACCOUNT SCHEDULE_ROTATION_ESCALATION SCHEDULE_ROTATION_GROUP SCHEDULE_ROTATION_NO_ONE SCHEDULE_ROTATION_TEAM

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule rotation ID

The unique identifier of the schedule rotation.

SQL name	`rotation_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule_rotation` table.

Team ID

The unique identifier of the associated team, if “Participant type” is SCHEDULE_ROTATION_TEAM.

SQL name	`team_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_team` 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.

Schedule rotation time restriction

The opsgenie_schedule_rotation_time_restriction contains the periods of time in which the on-call is restricted. This includes the start hour, start minute, end hour, and end minute, and depending on the time restriction type, the start and end day of week.

End day

The specified day of the week for the time restriction to end, when “Time restriction type” is weekday-and-time-of-day.

SQL name	`end_day`
Data type	String

End hour

The hour when the restriction will end.

SQL name	`end_hour`
Data type	Number

End minute

The ending minute of the end hour, which can either be at the top of the hour or 30 minutes after.

SQL name	`end_minute`
Data type	Number

Row refreshed at

The last refresh time (UTC time zone) for this record.

SQL name	`row_refreshed_at`
Data type	Datetime

Schedule rotation ID

The unique identifier of the schedule rotation.

SQL name	`rotation_id`
Data type	String
Foreign key	Links to a record in the `opsgenie_schedule_rotation` table.

Start day

The specified day of the week for the time restriction to start, when “Time restriction type” is weekday-and-time-of-day.

SQL name	`start_day`
Data type	String

Start hour

The hour when the restriction will start.

SQL name	`start_hour`
Data type	Number

Start minute

The starting minute of the start hour, which happens every 30 minutes. It can either start at the top of the hour or 30 minutes after.

SQL name	`start_minute`
Data type	Number

Time restriction type*

The type of time restriction.

SQL name	`end_day`
Data type	String
Example values	time-of-day weekday-and-time-of-day

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 accurateIt wasn't clearIt wasn't relevant

Still need help?

The Atlassian Community is here for you.

Ask the Community