Reducing Alert Fatigue with the PagerDuty Plugin

• Pick a destination to download your PagerDuty data into (this guide provides example queries for PostgreSQL).
  You can find our list of supported destinations here.
• Create a PagerDuty authorization token.
• Follow our quickstart guide, and create a pagerduty.yml configuration file:

  kind: source
  spec:
    name: 'pagerduty'
    path: cloudquery/pagerduty
    registry: cloudquery
    version: 'v6.11.3'
    destinations: ['postgresql']
    tables: ['*']
  

• Export your PagerDuty authorization token, and run cloudquery.

  PAGERDUTY_AUTH_TOKEN=
  cloudquery sync .
  

• This query calculates the average time it took to respond to incidents in the last 3 months, grouped by priority:

  WITH incident_ack_logs AS (
    SELECT pagerduty_incidents.id AS incident_id,
           pagerduty_incidents.priority->>'name' AS priority,
           pagerduty_incident_log_entries.created_at - pagerduty_incidents.created_at AS time_to_log
    FROM pagerduty_incidents
    INNER JOIN pagerduty_incident_log_entries
    ON pagerduty_incidents.id = pagerduty_incident_log_entries.incident->>'id'
    WHERE pagerduty_incident_log_entries.type = 'acknowledge_log_entry'
    AND pagerduty_incidents.created_at > NOW() - INTERVAL '3 months'
  ),
  incident_ack_time AS ( -- Make sure only the first acknowledgement is used (incidents may be acknowledged twice)
    SELECT incident_id,
           priority,
           MIN(time_to_log) AS time_to_ack
    FROM incident_ack_logs
    GROUP BY incident_id, priority
  )
  SELECT priority, AVG(time_to_ack) AS average_time_to_ack
  FROM incident_ack_time
  GROUP BY priority
  

  priority | average_time_to_ack
  ----------+---------------------
  P1       | 00:05:10
  P2       | 00:30:00
  P3       | 06:20:00
  

• This query calculates the average time it takes to resolve incidents, grouped by priority:

  WITH incident_resolution_logs AS (
    SELECT pagerduty_incidents.id,
           pagerduty_incidents.priority->>'name' AS priority,
           pagerduty_incident_log_entries.created_at - pagerduty_incidents.created_at AS time_to_log
    FROM pagerduty_incidents
    INNER JOIN pagerduty_incident_log_entries
    ON pagerduty_incidents.id = pagerduty_incident_log_entries.incident->>'id'
    WHERE pagerduty_incident_log_entries.type = 'resolve_log_entry'
    AND pagerduty_incidents.created_at > NOW() - INTERVAL '3 months'
  ),
  incident_resolution_time AS ( -- Make sure only the last resolution is used (in case of multiple resolutions)
    SELECT id,
           priority,
           MAX(time_to_log) AS time_to_resolve
    FROM incident_resolution_logs
    GROUP BY id, priority
  )
  SELECT priority, AVG(time_to_resolve) AS average_time_to_resolve
  FROM incident_resolution_time
  GROUP BY priority
  

  priority | average_time_to_resolve
  ---------+-------------------------
  P1       | 08:12:37
  P2       | 1 day 04:22:24:04
  P3       | 3 days 01:02:00
  

  WITH incident_ack_logs AS (
    SELECT pagerduty_incidents.id AS incident_id,
           pagerduty_incident_log_entries.agent->>'id' AS agent_id
    FROM pagerduty_incidents
    INNER JOIN pagerduty_incident_log_entries
    ON pagerduty_incidents.id = pagerduty_incident_log_entries.incident->>'id'
    WHERE pagerduty_incident_log_entries.type = 'acknowledge_log_entry'
    AND pagerduty_incidents.created_at > NOW() - INTERVAL '3 months'
  )
  SELECT pagerduty_users.id AS user_id,
         pagerduty_users.name AS user_name,
         COUNT(DISTINCT incident_id) AS acknowledge_count FROM
  pagerduty_users INNER JOIN incident_ack_logs
  ON pagerduty_users.id = incident_ack_logs.agent_id
  GROUP BY user_id, user_name
  ORDER BY acknowledge_count DESC

 user_id |  user_name  | acknowledge_count
---------+-------------+-------------------
 PDYR2Y8 | John        | 15
 PDYR2Y9 | Dave        | 12
 PDYR2Z5 | Jane        | 8

  SELECT pagerduty_services.id AS service_id,
         pagerduty_services.name AS service_name,
         COUNT(pagerduty_incidents.id) AS incident_count
  FROM pagerduty_services
  INNER JOIN pagerduty_incidents
  ON pagerduty_services.id = pagerduty_incidents.service->>'id'
  WHERE pagerduty_incidents.created_at > NOW() - INTERVAL '3 months'
  GROUP BY service_id, service_name
  ORDER BY incident_count DESC
  LIMIT 10

 service_id |  service_name   | incident_count
------------+-----------------+----------------
 PYS6MP5    | UnstableService | 25
 PAZ9U1C    | StableService   | 3