• Products
  • Documentation
  • Resources

Schema for Opsgenie

If you include Opsgenie data 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 two tables for Opsgenie account data:

  • Account settings

  • Account settings extra property

Account settings

The opsgenie_account_settings table lists the set of Opsgenie-specific attributes associated with each Atlassian account that uses Opsgenie.

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 Opsgenie 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 Opsgenie 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

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 Opsgenie.

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.


Tables for alert data

There are three tables for alert data:

  • Alert

  • Alert property

  • Alert responder mapping

Alert

The opsgenie_alert table lists all of your Opsgenie alerts.

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

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

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 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 responder mapping

The opsgenie_alert_responder_mapping table holds associations between Opsgenie 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.


Table for notification data

Notification

The opsgenie_notification table lists all Opsgenie notifications. It uses the following columns:

Account type*

The type of Opsgenie 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 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.

Notification ID

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

SQL name

notification_id

Data type

String

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:

  • Team

  • 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.

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 three tables for incident data:

  • Incident

  • Incident alert mapping

  • Incident responder mapping

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.

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 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.


Table for integration data

Integration

The opsgenie_integration table lists all your Opsgenie integrations.

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.

Additional Help