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.
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.
There are two tables for Opsgenie account data:
Account settings
Account settings extra property
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:
The unique identifier for the set of attributes.
SQL name | account_settings_id |
---|---|
Data type | String |
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. |
The date and time (UTC time zone) when the account was created in the Opsgenie workspace.
SQL name | created_at |
---|---|
Data type | Datetime |
The unique identifier of the custom role that was assigned to the account.
SQL name | custom_role_id |
---|---|
Data type | String |
The predefined role that was automatically provisioned to the account.
SQL name | default_role |
---|---|
Data type | String |
Indicates whether or not the account has logged in and verified their credentials.
SQL name | is_verified |
---|---|
Data type | Boolean |
User-defined strings attached to accounts for easier identification and categorization.
SQL name | labels |
---|---|
Data type | Array of strings |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The date and time (UTC time zone) when then the account was last updated.
SQL name | updated_at |
---|---|
Data type | Datetime |
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. |
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:
The unique identifier for the set of attributes associated with the key-value pair.
SQL name | account_settings_id |
---|---|
Data type | String |
The key of the property.
SQL name | key |
---|---|
Data type | String |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The value of the property.
SQL name | value |
---|---|
Data type | String |
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. |
There are three tables for alert data:
Alert
Alert property
Alert responder mapping
The opsgenie_alert table lists all of your Opsgenie alerts.
The table uses the following columns:
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. |
The time (in milliseconds) it took to acknowledge the alert.
SQL name | acknowledged_duration_milliseconds |
---|---|
Data type | Number |
User-defined actions for the alert.
SQL name | actions |
---|---|
Data type | Array of strings |
Example value | ["Restart", "Ping"] |
The unique alert identifier. Use it as the primary key for alert data.
SQL name | alert_id |
---|---|
Data type | String |
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. |
The alias used for the alert.
SQL name | alias |
---|---|
Data type | String |
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. |
The time (in milliseconds) it took to close the alert.
SQL name | closed_duration_milliseconds |
---|---|
Data type | Number |
The date and time (UTC time zone) when the alert was created.
SQL name | created_at |
---|---|
Data type | Datetime |
The number of times the alert occurred.
SQL name | deduplication_count |
---|---|
Data type | Number |
The description of the alert.
SQL name | description |
---|---|
Data type | String |
The user-defined entity to which the alert belongs.
SQL name | entity |
---|---|
Data type | String |
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. |
The labels associated with the alert.
SQL name | labels |
---|---|
Data type | Array of strings |
The date and time (UTC time zone) that the alert last occurred.
SQL name | last_occurred_at |
---|---|
Data type | Datetime |
The message of the alert.
SQL name | message |
---|---|
Data type | String |
The priority of the alert.
SQL name | priority |
---|---|
Data type | String |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The date and time (UTC time zone) when the alert is no longer snoozed.
SQL name | snoozed_until |
---|---|
Data type | Datetime |
The status of the alert.
SQL name | status |
---|---|
Data type | String |
The tiny ID associated with an alert. It will repeat after 99,999.
SQL name | tiny_id |
---|---|
Data type | String |
The last date and time (UTC time zone) the alert was updated.
SQL name | updated_at |
---|---|
Data type | Datetime |
true if alert was acknowledged; otherwise, false.
SQL name | was_acknowledged |
---|---|
Data type | Boolean |
true if the alert is seen; otherwise, false.
SQL name | was_seen |
---|---|
Data type | Boolean |
true if the alert is snoozed; otherwise, false.
SQL name | was_snoozed |
---|---|
Data type | Boolean |
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. |
The opsgenie_alert_property table lists all of your alert properties.
The table uses the following columns:
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 |
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 |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The detail value of the alert
SQL name | value |
---|---|
Data type | String |
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. |
The opsgenie_alert_responder_mapping table holds associations between Opsgenie alerts and alert responders.
The table uses the following columns:
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. |
The unique identifier of the alert.
SQL name | alert_id |
---|---|
Data type | String |
Foreign key | Links to a record in the opsgenie_alert table. |
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. |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
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. |
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. |
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. |
The opsgenie_notification table lists all Opsgenie notifications. It uses the following columns:
The type of Opsgenie account that is assigned to the user.
SQL name | account_type |
---|---|
Data type | String |
Example values | responder, stakeholder |
The date and time (UTC time zone) when the issue was created.
SQL name | created_at |
---|---|
Data type | Datetime |
The method used for the notification.
SQL name | delivery_method |
---|---|
Data type | String |
Example values | VOICE, SMS, MOBILE, and EMAIL |
true when it is a mass notification; otherwise, false.
SQL name | is_mass_notification |
---|---|
Data type | Boolean |
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. |
The unique identifier of the notification. Use it as the primary key for notification data.
SQL name | notification_id |
---|---|
Data type | String |
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 |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The date and time (UTC time zone) when the notification was sent.
SQL name | sent_at |
---|---|
Data type | Datetime |
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. |
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 |
The domain of notification for which it was triggered.
SQL name | source_type |
---|---|
Data type | String |
Example values | ALERT, SCHEDULE, AGGREGATED, SYSTEM, and reporting |
The status of the notification.
SQL name | status |
---|---|
Data type | String |
Example values | SENT, RECEIVED, and FAILED |
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. |
There are two tables for Opsgenie team data:
Team
Team member
The opsgenie_team table lists your Opsgenie teams.
The table uses the following columns:
The date and time (UTC time zone) when the team was created.
SQL name | created_at |
---|---|
Data type | Datetime |
The team description.
SQL name | description |
---|---|
Data type | String |
The name of the team.
SQL name | name |
---|---|
Data type | String |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The unique identifier of the team. Use this as the primary key for Opsgenie team data.
SQL name | team_id |
---|---|
Data type | String |
The date and time (UTC time zone) when the team was last updated.
SQL name | updated_at |
---|---|
Data type | Datetime |
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. |
The opsgenie_team_member table lists all Opsgenie team members.
The table uses the following columns:
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. |
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. |
The unique identifier of the default team role.
SQL name | default_team_role_id |
---|---|
Data type | String |
Example values | user, admin |
The role type of the team member.
SQL name | role_type |
---|---|
Data type | String |
Example values | custom team role, default team role |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
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. |
The unique identifier of the team role that the team member has.
SQL name | team_role_id |
---|---|
Data type | String |
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. |
There are three tables for incident data:
Incident
Incident alert mapping
Incident responder mapping
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:
The date and time (UTC time zone) the incident was closed.
SQL name | closed_at |
---|---|
Data type | Datetime |
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. |
The system that closed the incident.
SQL name | closed_by_account_id |
---|---|
Data type | String |
The date and time (UTC time zone) the incident was generated.
SQL name | closed_at |
---|---|
Data type | Datetime |
A long description related with the incident.
SQL name | description |
---|---|
Data type | String |
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 |
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 |
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 |
The unique identifier of the incident.
SQL name | incident_id |
---|---|
Data type | String |
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 |
Client-defined string attached to incidents for easier identification and categorization of incidents.
SQL name | labels |
---|---|
Data type | Array of strings |
A summary of the cause for the incident to be generated.
SQL name | message |
---|---|
Data type | String |
The date and time (UTC time zone) that the postmortem is due to be completed.
SQL name | postmortem_due_at |
---|---|
Data type | Datetime |
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 |
The date and time (UTC time zone) the incident was resolved.
SQL name | resolved_at |
---|---|
Data type | Datetime |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The current state of an incident, which will be either open, closed, or resolved.
SQL name | status |
---|---|
Data type | String |
Example values |
|
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 |
The date and time (UTC time zone) the incident was updated.
SQL name | updated_at |
---|---|
Data type | Datetime |
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. |
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:
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. |
The date and time (UTC time zone) the incident to alert mapping was created.
SQL name | created_at |
---|---|
Data type | Datetime |
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. |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The type of incident alert, which can be either associated or responder.
SQL name | type |
---|---|
Data type | String |
Example values | associated, responder |
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. |
The opsgenie_incident_responder_mapping table holds associations between incidents and incident responders.
The table uses the following columns:
The unique identifier of an incident.
SQL name | incident_id |
---|---|
Date type | String |
Foreign key | Links to a record in the opsgenie_incident table. |
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. |
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. |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
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. |
The opsgenie_integration table lists all your Opsgenie integrations.
The table uses the following columns:
The unique identifier of the integration.
SQL name | integration_id |
---|---|
Data type | String |
Indicates whether or not the integration is enabled.
SQL name | is_enabled |
---|---|
Data type | Boolean |
The name of the integration.
SQL name | name |
---|---|
Data type | String |
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. |
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
---|---|
Data type | Datetime |
The type of integration. Learn more about integration types.
SQL name | type |
---|---|
Data type | String |
The version of the integration API.
SQL name | version |
---|---|
Data type | String |
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?