turbot/servicenow
steampipe plugin install servicenow

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
activebooleanIndicates if the incident is currently active.
activity_duetimestamp with time zoneDue date and time for the current activity associated with the incident.
additional_assignee_listtextList of additional users assigned to the incident.
approvaltextCurrent approval state of the incident.
approval_historytextHistory of approvals associated with the incident.
approval_settimestamp with time zoneApproval set associated with the incident.
assigned_tojsonbUser or group assigned to handle the incident.
assignment_groupjsonbGroup assigned to handle the incident.
business_durationtimestamp with time zoneDuration of the incident in business hours.
business_impacttextImpact of the incident on the business.
business_servicejsonbBusiness service associated with the incident.
business_stcbigintService target compliance for the incident in business hours.
calendar_durationtimestamp with time zoneDuration of the incident in calendar time.
calendar_stcbigintService target compliance for the incident in calendar time.
caller_idjsonbUser who reported the incident.
categorytextCategory or classification of the incident.
causetextRoot cause or reason for the incident.
caused_byjsonbConfiguration item or event that caused the incident.
child_incidentsbigintChild incidents associated with the incident.
close_codetextCode indicating the reason for closing the incident.
close_notestextNotes or comments added when closing the incident.
closed_attimestamp with time zoneDate and time when the incident was closed.
closed_byjsonbUser who closed the incident.
cmdb_cijsonbConfiguration item associated with the incident.
commentstextAdditional comments or notes on the incident.
comments_and_work_notestextCombined list of comments and work notes for the incident.
companyjsonbCompany or organization associated with the incident.
contact_typetextType of contact associated with the incident.
contractjsonbContract associated with the incident.
correlation_displaytextDisplay value for the correlation ID.
correlation_idtextIdentifier for correlating related records.
delivery_planjsonbPlan for delivering a resolution to the incident.
delivery_taskjsonbTask associated with delivering a resolution to the incident.
descriptiontextDescription or details of the incident.
due_datetimestamp with time zoneDue date and time for resolving the incident.
escalationbigintEscalation details for the incident.
expected_starttimestamp with time zoneExpected start date and time for the incident.
follow_uptimestamp with time zoneDate and time for a follow-up action on the incident.
group_listtextList of groups associated with the incident.
hold_reasonbigintReason for placing the incident on hold.
impactbigintImpact level of the incident.
incident_statebigintCurrent state of the incident.
knowledgebooleanKnowledge article associated with the incident.
locationjsonbLocation or place associated with the incident.
made_slabooleanIndicates if the incident met the service level agreement.
notifybigintFlag indicating if notifications are sent for the incident.
numbertextUnique number or identifier of the incident.
opened_attimestamp with time zoneDate and time when the incident was opened.
opened_byjsonbUser who opened the incident.
orderbigintOrder associated with the incident.
origin_idjsonbIdentifier for the source of the incident.
origin_tabletextTable or source from which the incident originated.
parentjsonbParent record associated with the incident.
parent_incidentjsonbParent incident associated with the incident.
prioritybigintPriority level assigned to the incident.
problem_idjsonbProblem record associated with the incident.
reassignment_countbigintNumber of times the incident has been reassigned.
rejection_gotojsonbNext state in the workflow when the incident is rejected.
reopen_countbigintNumber of times the incident has been reopened.
reopened_byjsonbUser who reopened the incident.
reopened_timetimestamp with time zoneDate and time when the incident was reopened.
resolved_attimestamp with time zoneDate and time when the incident was resolved.
resolved_byjsonbUser who resolved the incident.
rfcjsonbRelated Request for Change (RFC) associated with the incident.
route_reasonbigintReason for routing or assigning the incident.
service_offeringjsonbService offering associated with the incident.
severitybigintSeverity level assigned to the incident.
short_descriptiontextBrief description or summary of the incident.
skillstextSkills required to handle the incident.
sla_duetimestamp with time zoneDue date and time for meeting the service level agreement (SLA).
sn_esign_documentjsonbDocument associated with electronic signature.
sn_esign_esignature_configurationjsonbElectronic signature configuration associated with the incident.
statebigintCurrent state or status of the incident.
subcategorytextSubcategory or more specific classification of the incident.
sys_class_nametextName of the system class or table for the incident.
sys_created_bytextUser who created the incident.
sys_created_ontimestamp with time zoneDate and time when the incident was created.
sys_domainjsonbDomain associated with the incident.
sys_domain_pathtextPath of the domain associated with the incident.
sys_idtextUnique system identifier for the incident.
sys_mod_countbigintNumber of times the incident has been modified.
sys_updated_bytextUser who last updated the incident.
sys_updated_ontimestamp with time zoneDate and time of the last update to the incident.
task_effective_numbertextEffective number of tasks associated with the incident.
time_workedtimestamp with time zoneTotal time worked on the incident.
universal_requestjsonbUniversal Request associated with the incident.
upon_approvaltextActions to be performed upon approval of the incident.
upon_rejecttextActions to be performed upon rejection of the incident.
urgencybigintUrgency level assigned to the incident.
user_inputtextUser input or response associated with the incident.
variablestextVariables or additional data associated with the incident.
watch_listtextList of users watching or monitoring the incident.
wf_activityjsonbWorkflow activity associated with the incident.
work_endtimestamp with time zoneDate and time when work on the incident ends.
work_notestextAdditional work notes or comments on the incident.
work_notes_listtextList of work notes added to the incident.
work_starttimestamp with time zoneDate 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