Access Atlassian Statuspage API from SQL

Wiki (en)
,
1

Atlassian statuspage provides both public and private APIs for data exchange. The public APIs can even be embedded in web pages through for instance JavaScript. This topic describes however to access the private APIs from Atlassian Statuspage through SQL without using a separate driver.

The private API is a so-called REST API and is documented on https://developer.statuspage.io/. This also means that data is exchanged as so-called “JSON data”. Invantive SQL has extensive support for creating, exchange and dissecting JSON data.

Acquire API Key

The first step is to acquire an Atlassian statuspage API Key.

Execute the following steps to get an Atlassian statuspage API key:

  • Go to https://manage.statuspage.io/.
  • Log in.
  • Click on your user profile on the top right of the page.
  • From the menu choose “API info”.
  • Reuse an existing API key or use the “Create key” button.
  • Please note that the API keys are shown on the site in plain text. Make sure to lock your device when left unattended.

The API token has the format of a GUID.

Acquire page ID

Many private Atlassian statuspage APIs require the page ID as a parameter. The page ID can be found at the bottom of the API Info-page as described in the previous step. The page ID can also be retrieved using a private API with the API key:

select jte.*
from   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://api.statuspage.io/v1/pages'
       , headers => 'Authorization=OAuth APIKEYTOBEPLACEDHERE'
       , minimumDurationMs => 1000 /* Default Atlassian Statuspage rate limit. */
       ) htp
join   jsontable
       ( '[*]'
         passing htp.contents_char
         columns id                         varchar2 not null path 'id'
         ,       created_at                 datetime not null path 'created_at'
         ,       updated_at                 datetime not null path 'updated_at'
         ,       name                       varchar2 not null path 'name'
         ,       hidden_from_search         boolean  not null path 'hidden_from_search'
         ,       allow_page_subscribers     boolean  not null path 'allow_page_subscribers'
         ,       allow_incident_subscribers boolean  not null path 'allow_incident_subscribers'
         ,       allow_email_subscribers    boolean  not null path 'allow_email_subscribers'
         ,       allow_sms_subscribers      boolean  not null path 'allow_sms_subscribers'
         ,       allow_rss_atom_feeds       boolean  not null path 'allow_rss_atom_feeds'
         ,       allow_webhook_subscribers  boolean  not null path 'allow_webhook_subscribers'
         ,       viewers_must_be_team_members  boolean  not null path 'viewers_must_be_team_members'
         ,       activity_score             int          null path 'activity_score'
         ,       headline                   varchar2     null path 'headline'
         ,       branding                   varchar2     null path 'branding'
         ,       subdomain                  varchar2     null path 'subdomain'
         ,       domain                     varchar2     null path 'domain'
         ,       url                        varchar2     null path 'url'
         ,       support_url                varchar2     null path 'support_url'
         ,       ip_restrictions            varchar2     null path 'ip_restrictions'
         ,       city                       varchar2     null path 'city'
         ,       state                      varchar2     null path 'state'
         ,       country                    varchar2     null path 'country'
         ,       time_zone                  varchar2     null path 'time_zone'
         ,       notifications_from_email   varchar2     null path 'notifications_from_email'
         ,       notifications_email_footer varchar2     null path 'notifications_email_footer'
         ,       twitter_username           varchar2     null path 'twitter_username'
         ,       page_description           varchar2     null path 'page_description'
         -- CSS colors and icons skipped.
      ) jte

Please note that by default the Atlassian Statuspage has a rate limit of 60 calls per 60 seconds.

Also note that the API has metadata available in Swagger / OpenAPI format.

The jsontable can be extended by more metadata, such as by adding label to generate a nice user interface directly from the SQL queries.

Sample: Get all subscribers

The query shown in the previous section can be used in general to acquire or upload data. For instance, the following SQL statement retrieves a list of all subscribers to a page:

select jte.*
from   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://api.statuspage.io/v1/pages/5c015ylxmccl/subscribers?type=email&page=0&limit=1000'
       , headers => 'Authorization=OAuth APIKEYTOBEPLACEDHERE'
       , minimumDurationMs => 1000 /* Default Atlassian Statuspage rate limit. */
       ) htp
join   jsontable
       ( '[*]'
         passing htp.contents_char
         columns id                             varchar2 not null path 'id'
         ,       mode                           varchar2 not null path 'mode'
         ,       email                          varchar2     null path 'email'
         ,       phone_number                   varchar2     null path 'phone_number'
         ,       phone_country                  varchar2     null path 'phone_country'
         ,       display_phone_number           varchar2     null path 'display_phone_number'
         ,       endpoint                       varchar2     null path 'endpoint'
         ,       obfuscated_channel_name        varchar2     null path 'obfuscated_channel_name'
         ,       workspace_name                 varchar2     null path 'workspace_name'
         ,       page_access_user_id            varchar2     null path 'page_access_user_id'
         ,       created_at                     datetime not null path 'created_at'
         ,       quarantined_at                 datetime     null path 'quarantined_at'
         ,       purge_at                       datetime     null path 'purge_at'
         ,       skip_confirmation_notification boolean  not null path 'skip_confirmation_notification'
       ) jte

Sample: upload data to Atlassian Statuspage

In a similar way and despite no Statuspage-specific SQL driver, data can be uploaded such as a subscriber using the following SQL:

select jte.*
from   ( select '{ "subscriber": { ' 
                || jsonelement('email', 'john.doe@acme.com') 
                || ', ' 
                || jsonelement('skip_confirmation_notification', true )  
                || ' } }' 
                payload 
       ) dta
join   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://api.statuspage.io/v1/pages/5c015ylxmccl/subscribers'
       , method => 'POST'
       , headers => 'Authorization=OAuth APIKEYTOBEPLACEDHERE'
       , minimumDurationMs => 1000 /* Default Atlassian Statuspage rate limit. */
       , textPayload => dta.payload
       ) htp
join   jsontable
       ( passing htp.contents_char
         columns id                             varchar2 not null path 'id'
         ,       mode                           varchar2 not null path 'mode'
         ,       email                          varchar2     null path 'email'
         ,       phone_number                   varchar2     null path 'phone_number'
         ,       phone_country                  varchar2     null path 'phone_country'
         ,       display_phone_number           varchar2     null path 'display_phone_number'
         ,       endpoint                       varchar2     null path 'endpoint'
         ,       obfuscated_channel_name        varchar2     null path 'obfuscated_channel_name'
         ,       workspace_name                 varchar2     null path 'workspace_name'
         ,       page_access_user_id            varchar2     null path 'page_access_user_id'
         ,       created_at                     datetime not null path 'created_at'
         ,       quarantined_at                 datetime     null path 'quarantined_at'
         ,       purge_at                       datetime     null path 'purge_at'
         ,       skip_confirmation_notification boolean  not null path 'skip_confirmation_notification'
       ) jte

Sample: package for SQL queries

In general, it is more convenient to package such long queries as a database view. In this scenario, it would be recommended to create a small table, for instance on Microsoft SQL Server, which contains (encrypted or unencrypted) the API key. Then join this table into the query and create a view for other users, such as:

create or replace view NAME
as
select ...

The view can then be queried as if it was a SQL table:

select *
from   NAME@DataDictionary
where  mode = 'email'

This view also becomes available for use by Power BI, Power Query, Power App/Automate and Azure when placed in the Startup SQL of an Invantive Cloud database.