Table: servicenow_incident - Query ServiceNow Incidents using SQL

ServiceNow Incident Management is a service within the ServiceNow platform that helps organizations respond to, resolve, and report on IT incidents. It aims to restore service operation to normal as quickly as possible and minimize the adverse impact on business operations. This ensures that the highest possible levels of service quality and availability are maintained.

Table Usage Guide

The servicenow_incident table provides insights into each incident recorded within the ServiceNow Incident Management system. As an IT operations manager, you can explore incident-specific details through this table, including incident state, priority, and associated metadata. Utilize it to uncover information about incidents, such as their current status, assigned personnel, and resolution progress.

Examples

What is the distribution of incidents by severity level?

Gain insights into the distribution of incidents according to their severity level. This query helps in understanding the proportion of each severity level in relation to the total incidents, aiding in effective incident management.

select
  severity,
  count(*) as incident_count,
  count(*) * 100.0 / sum(count(*)) over() as percentage
from
  servicenow_incident
group by
  severity
order by
  severity;

select
  severity,
  count(*) as incident_count,
  count(*) * 100.0 / (
    select
      count(*)
    from
      servicenow_incident
  ) as percentage
from
  servicenow_incident
group by
  severity
order by
  severity;

What is the average time taken to resolve incidents by category?

Explore the efficiency of incident resolution processes by determining the average time taken to resolve incidents, grouped by their respective categories. This can provide valuable insights into areas where response times may need improvement.

select
  category,
  avg(calendar_stc) as avg_time_to_resolve
from
  servicenow_incident
where
  incident_state = 7 -- only include resolved incidents
group by
  category;

select
  category,
  avg(calendar_stc) as avg_time_to_resolve
from
  servicenow_incident
where
  incident_state = 7 -- only include resolved incidents
group by
  category;

What was the most common reason for holding incidents?

Analyze the incidents to understand the most frequent reason for their hold status. This can help in identifying recurring issues and implementing preventive measures.

select
  hold_reason,
  count(*) as incident_count
from
  servicenow_incident
where
  hold_reason is not null
group by
  hold_reason
order by
  incident_count desc
limit
  1;

select
  hold_reason,
  count(*) as incident_count
from
  servicenow_incident
where
  hold_reason is not null
group by
  hold_reason
order by
  incident_count desc
limit
  1;

What is the average time taken to resolve incidents by severity level?

Analyze the average resolution time for incidents, grouped by severity level. This can be useful to identify areas of improvement in incident management and to prioritize resources based on severity.

select
  severity,
  avg(calendar_stc) as avg_time_to_resolve
from
  servicenow_incident
where
  incident_state = 7 -- only include resolved incidents
group by
  severity;

select
  severity,
  avg(calendar_stc) as avg_time_to_resolve
from
  servicenow_incident
where
  incident_state = 7 -- only include resolved incidents
group by
  severity;

What are the top 10 categories of incidents by count?

Uncover the details of the most frequently occurring incident categories to better prioritize and strategize your response efforts. This can help in efficiently allocating resources and improving response times to critical issues.

select
  category,
  count(*) as incident_count
from
  servicenow_incident
group by
  category
order by
  incident_count desc
limit
  10;

select
  category,
  count(*) as incident_count
from
  servicenow_incident
group by
  category
order by
  incident_count desc
limit
  10;

How many incidents were resolved in the last 30 days?

Analyze the volume of resolved incidents over the past month. This is useful for assessing the efficiency and effectiveness of your incident response team.

select
  count(*) as resolved_incidents
from
  servicenow_incident
where
  incident_state = '6'
  and resolved_at >= now() - interval '30 days';

select
  count(*) as resolved_incidents
from
  servicenow_incident
where
  incident_state = '6'
  and resolved_at >= datetime('now', '-30 days');

How many incidents were reopened more than once?

Assess the frequency of incidents that have been reopened multiple times, providing insights into potential issues with problem resolution or customer satisfaction.

select
  count(*) as reopened_incidents
from
  servicenow_incident
where
  reopen_count > 1;

select
  count(*) as reopened_incidents
from
  servicenow_incident
where
  reopen_count > 1;

Schema for servicenow_incident

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
active	boolean	Indicates if the incident is currently active.
activity_due	timestamp with time zone	Due date and time for the current activity associated with the incident.
additional_assignee_list	text	List of additional users assigned to the incident.
approval	text	Current approval state of the incident.
approval_history	text	History of approvals associated with the incident.
approval_set	timestamp with time zone	Approval set associated with the incident.
assigned_to	jsonb	User or group assigned to handle the incident.
assignment_group	jsonb	Group assigned to handle the incident.
business_duration	timestamp with time zone	Duration of the incident in business hours.
business_impact	text	Impact of the incident on the business.
business_service	jsonb	Business service associated with the incident.
business_stc	bigint	Service target compliance for the incident in business hours.
calendar_duration	timestamp with time zone	Duration of the incident in calendar time.
calendar_stc	bigint	Service target compliance for the incident in calendar time.
caller_id	jsonb	User who reported the incident.
category	text	Category or classification of the incident.
cause	text	Root cause or reason for the incident.
caused_by	jsonb	Configuration item or event that caused the incident.
child_incidents	bigint	Child incidents associated with the incident.
close_code	text	Code indicating the reason for closing the incident.
close_notes	text	Notes or comments added when closing the incident.
closed_at	timestamp with time zone	Date and time when the incident was closed.
closed_by	jsonb	User who closed the incident.
cmdb_ci	jsonb	Configuration item associated with the incident.
comments	text	Additional comments or notes on the incident.
comments_and_work_notes	text	Combined list of comments and work notes for the incident.
company	jsonb	Company or organization associated with the incident.
contact_type	text	Type of contact associated with the incident.
contract	jsonb	Contract associated with the incident.
correlation_display	text	Display value for the correlation ID.
correlation_id	text	Identifier for correlating related records.
delivery_plan	jsonb	Plan for delivering a resolution to the incident.
delivery_task	jsonb	Task associated with delivering a resolution to the incident.
description	text	Description or details of the incident.
due_date	timestamp with time zone	Due date and time for resolving the incident.
escalation	bigint	Escalation details for the incident.
expected_start	timestamp with time zone	Expected start date and time for the incident.
follow_up	timestamp with time zone	Date and time for a follow-up action on the incident.
group_list	text	List of groups associated with the incident.
hold_reason	bigint	Reason for placing the incident on hold.
impact	bigint	Impact level of the incident.
incident_state	bigint	Current state of the incident.
knowledge	boolean	Knowledge article associated with the incident.
location	jsonb	Location or place associated with the incident.
made_sla	boolean	Indicates if the incident met the service level agreement.
notify	bigint	Flag indicating if notifications are sent for the incident.
number	text	Unique number or identifier of the incident.
opened_at	timestamp with time zone	Date and time when the incident was opened.
opened_by	jsonb	User who opened the incident.
order	bigint	Order associated with the incident.
origin_id	jsonb	Identifier for the source of the incident.
origin_table	text	Table or source from which the incident originated.
parent	jsonb	Parent record associated with the incident.
parent_incident	jsonb	Parent incident associated with the incident.
priority	bigint	Priority level assigned to the incident.
problem_id	jsonb	Problem record associated with the incident.
reassignment_count	bigint	Number of times the incident has been reassigned.
rejection_goto	jsonb	Next state in the workflow when the incident is rejected.
reopen_count	bigint	Number of times the incident has been reopened.
reopened_by	jsonb	User who reopened the incident.
reopened_time	timestamp with time zone	Date and time when the incident was reopened.
resolved_at	timestamp with time zone	Date and time when the incident was resolved.
resolved_by	jsonb	User who resolved the incident.
rfc	jsonb	Related Request for Change (RFC) associated with the incident.
route_reason	bigint	Reason for routing or assigning the incident.
service_offering	jsonb	Service offering associated with the incident.
severity	bigint	Severity level assigned to the incident.
short_description	text	Brief description or summary of the incident.
skills	text	Skills required to handle the incident.
sla_due	timestamp with time zone	Due date and time for meeting the service level agreement (SLA).
sn_esign_document	jsonb	Document associated with electronic signature.
sn_esign_esignature_configuration	jsonb	Electronic signature configuration associated with the incident.
state	bigint	Current state or status of the incident.
subcategory	text	Subcategory or more specific classification of the incident.
sys_class_name	text	Name of the system class or table for the incident.
sys_created_by	text	User who created the incident.
sys_created_on	timestamp with time zone	Date and time when the incident was created.
sys_domain	jsonb	Domain associated with the incident.
sys_domain_path	text	Path of the domain associated with the incident.
sys_id	text	Unique system identifier for the incident.
sys_mod_count	bigint	Number of times the incident has been modified.
sys_updated_by	text	User who last updated the incident.
sys_updated_on	timestamp with time zone	Date and time of the last update to the incident.
task_effective_number	text	Effective number of tasks associated with the incident.
time_worked	timestamp with time zone	Total time worked on the incident.
universal_request	jsonb	Universal Request associated with the incident.
upon_approval	text	Actions to be performed upon approval of the incident.
upon_reject	text	Actions to be performed upon rejection of the incident.
urgency	bigint	Urgency level assigned to the incident.
user_input	text	User input or response associated with the incident.
variables	text	Variables or additional data associated with the incident.
watch_list	text	List of users watching or monitoring the incident.
wf_activity	jsonb	Workflow activity associated with the incident.
work_end	timestamp with time zone	Date and time when work on the incident ends.
work_notes	text	Additional work notes or comments on the incident.
work_notes_list	text	List of work notes added to the incident.
work_start	timestamp with time zone	Date and time when work on the incident starts.

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)" -- servicenow

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

steampipe_export_servicenow --config '<your_config>' servicenow_incident