Table: pagerduty_incident_log - Query PagerDuty Incident Logs using SQL

PagerDuty Incident Logs are a record of all the activities related to an incident within PagerDuty. These logs provide a detailed timeline of incident activity, including status changes, escalations, and notes added by users. They are crucial for postmortem analysis and understanding the incident's lifecycle.

Table Usage Guide

The pagerduty_incident_log table provides insights into incident logs within PagerDuty. As an incident responder or a DevOps engineer, explore the detailed timeline of incident activity through this table, including status changes, escalations, and user-added notes. Utilize it to facilitate postmortem analysis and gain a comprehensive understanding of the incident's lifecycle.

Important Notes

You must specify the incident_id in the where or join clause (where incident_id=, join pagerduty_incident_log l on l.incident_id=) to query this table.
It is recommended that queries specify created_at (usually in the where clause) to filter the log entries within a specific time range.

Examples

List log entries for all incident in last 24 hrs

Explore the recent incidents in the last 24 hours for a comprehensive understanding of the situation, including the incident's summary and who created it. This query can assist in identifying patterns and trends in incidents, which can be useful for troubleshooting and improving system stability.

select
  i.summary as incident_summary,
  l.id as log_entry_id,
  l.created_at,
  l.agent ->> 'summary' as created_by
from
  pagerduty_incident_log as l,
  pagerduty_incident as i
where
  l.incident_id = i.id
  and l.created_at > now() - interval '24 hrs';

select
  i.summary as incident_summary,
  l.id as log_entry_id,
  l.created_at,
  json_extract(l.agent, '$.summary') as created_by
from
  pagerduty_incident_log as l,
  pagerduty_incident as i
where
  l.incident_id = i.id
  and l.created_at > datetime('now', '-24 hours');

List incident logs for an incident from the last 3 days

Explore recent incident logs to gain insights into the activities and changes made within the last three days. This is beneficial in understanding the sequence of events or actions taken for a specific incident, aiding in incident management and resolution.

select
  id,
  incident_id,
  created_at,
  agent ->> 'summary' as created_by
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and created_at > now() - interval '3 days';

select
  id,
  incident_id,
  created_at,
  json_extract(agent, '$.summary') as created_by
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and created_at > datetime('now', '-3 days');

List incident log entries for activities performed by users

Discover the segments that highlight user activities within specific incidents. This can be beneficial in tracking user actions, identifying patterns, and managing incident responses more effectively.

select
  id,
  incident_id,
  created_at,
  jsonb_pretty(channel) as action,
  agent ->> 'summary' as user_name
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and agent ->> 'type' = 'user_reference';

select
  id,
  incident_id,
  created_at,
  channel as action,
  json_extract(agent, '$.summary') as user_name
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and json_extract(agent, '$.type') = 'user_reference';

List incident log entries for activities performed by services

Explore which activities have been performed by different services in specific incidents, allowing you to gain insights into the actions taken and who performed them for better incident management.

select
  id,
  incident_id,
  created_at,
  jsonb_pretty(channel) as action,
  agent ->> 'summary' as user_name
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and agent ->> 'type' = 'service_reference';

select
  id,
  incident_id,
  created_at,
  channel as action,
  json_extract(agent, '$.summary') as user_name
from
  pagerduty_incident_log
where
  incident_id = 'Q0FH5K82AJ101C'
  and json_extract(agent, '$.type') = 'service_reference';

Schema for pagerduty_incident_log

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
acknowledgement_timeout	bigint		Specifies the acknowledgement timeout (in seconds) for the incident.
agent	jsonb		The agent (user, service or integration) that created or modified the incident log entry.
channel	jsonb		Polymorphic object representation of the means by which the action was channeled. Has different formats depending on type, indicated by channel[type]. Will be one of auto, email, api, nagios, or timeout if agent[type] is service. Will be one of email, sms, website, web_trigger, or note if agent[type] is user.
contexts	jsonb		A list of contexts to be included with the trigger such as links to graphs, or images.
created_at	timestamp with time zone	>, >=, =, <, <=	Time at which the log entry was created.
event_details	jsonb		A list of information about the change events.
html_url	text		The API show URL at which the object is accessible.
id	text		An unique identifier of the log entry.
incident_id	text	=	An unique identifier of the queried incident.
self	text		The API show URL at which the object is accessible.
summary	text		A short-form, server-generated string that provides succinct, important information about an object suitable for primary labeling of an entity in a client. In many cases, this will be identical to name, though it is not intended to be an identifier.
teams	jsonb		A list of team references unless included.
title	text		Title of the resource.
type	text		The type of object being created.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- pagerduty

You can pass the configuration to the command with the --config argument:

steampipe_export_pagerduty --config '<your_config>' pagerduty_incident_log