Get started with Atlassian Analytics
Learn how to add Atlassian Analytics to a site and understand what you need to query data and create charts.
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 several tables for Opsgenie 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 Opsgenie-specific attributes associated with each Atlassian account that uses Opsgenie.
The table uses the following columns:
Account ID
The unique identifier of the Atlassian account associated with the logged-in Opsgenie 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 Opsgenie 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 Opsgenie.
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 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. |
Custom account role
The opsgenie_custom_account_role table lists the Opsgenie custom account roles. 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 Opsgenie instance.
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 |
|
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 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 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 Opsgenie team.
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 |
Assigned at
The date and time (UTC time zone) when the alert was assigned.
SQL name | assigned_at |
|---|---|
Data type | Datetime |
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 |
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 |
|
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 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. |
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 Opsgenie 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 |
|
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. |
Notification
The opsgenie_notification table lists all Opsgenie notifications.
The table 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 several tables for incident data:
Incident
Incident alert mapping
Incident impacted service 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 |
|
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 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. |
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 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. |
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 |
|
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 |
|
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 |
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 |
|
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 |
|
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 |
|
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?