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.

Was this helpful?

It wasn't accurate

It wasn't clear

It wasn't relevant