• Products
  • Documentation
  • Resources

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 theopsgenie_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 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 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 t