jplanckeel/opsgenie

Overview

2Tables

Versions

GitHub

steampipe plugin install jplanckeel/opsgeniesteampipe plugin install jplanckeel/opsgenie

opsgenie_alert opsgenie_team

Table: opsgenie_alert

List all alerts in the Opsgenie account.

Examples

Basic group info

select
  alert_id,
  message,
  owner_team_id,
  created_at,
  status
from
  opsgenie_alert;

Alert for a team

select
  alert_id,
  message,
  owner_team_id,
  created_at,
  status
from
  opsgenie_alert
where
  owner_team_id = '<<TEAM-ID>>';

Total no of alerts

select
  count(*) as number_of_alerts
from
  opsgenie_alert;

Alert by message priority

select
  message,
  priority,
  count(*) as number_of_alerts
from
  opsgenie_alert
group by
  message,
  priority
order by
  number_of_alerts desc;

Top 10 alerts by message priority

select
  message,
  priority,
  count(*) as number_of_alerts
from
  opsgenie_alert
group by
  message,
  priority
order by
  number_of_alerts desc
limit
  10;

Top 10 alerts by message priority in last 30 days

select
  message,
  priority,
  count(*) as number_of_alerts
from
  opsgenie_alert
where
  created_at >= now() - '30 days' :: interval
group by
  message,
  priority
order by
  number_of_alerts desc
limit
  10;

List alerts that are less than seven days old

select
  message,
  created_at
from
  opsgenie_alert
where
  created_at >= now() - '7 days' :: interval;

List top 15 alerts with delta between 2 weeks

with alert_by_month as (
  select
    message,
    count(*) as "Nb Alerts",
    date_part('month', created_at) as month,
    lag(count(*), 1) over (
      partition by message
      order by
        date_part('month', created_at)
    ) as "Nb Alerts Sprint - 1"
  from
    opsgenie.opsgenie_alert
  where
    created_at >= now() - '5 months' :: interval
  group by
    message,
    month
  order by
    "Nb Alerts" desc
)
select
  month as "Month",
  message as "Alert",
  "Nb Alerts",
  "Nb Alerts Sprint - 1",
  "Nb Alerts" - "Nb Alerts Sprint - 1" AS "Delta",
  ROUND(
    100.0 * (
      ("Nb Alerts" - "Nb Alerts Sprint - 1") / "Nb Alerts Sprint - 1" :: decimal
    ),
    2
  ) AS "Delta % "
from
  alert_by_month
where
  month = (
    select
      MAX(month) - 1
    from
      alert_by_month
  )
order by
  month desc,
  "Nb Alerts" desc
limit
  15;

Schema for opsgenie_alert

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
acknowledged	boolean		A boolean indicating whether the alert has been acknowledged.
alert_id	text		The Id of the alert.
alias	text		Client-defined identifier of the alert, that is also the key element of Alert De-Duplication.
count	bigint		This field indicates the number of times this alert has been triggered or reported.
created_at	timestamp with time zone		The timestamp of when the alert was created.
last_occurred_at	timestamp with time zone		This timestamp shows when the alert was last triggered.
message	text		Message of the alert.
owner	text		The user or entity that is currently responsible for the alert.
owner_team_id	text		The Id of the team that owns the alert.
priority	text		Priority level of the alert. Possible values are P1, P2, P3, P4 and P5. Default value is P3.
source	text		Display name of the request source.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	text		The current status of the alert (e.g., open, closed).
updated_at	timestamp with time zone		The timestamp when the alert was last updated..