turbot/servicenow
steampipe plugin install servicenow

Table: servicenow_sn_chg_rest_change - Query ServiceNow Change Management Records using SQL

ServiceNow Change Management is a service within the ServiceNow platform that enables organizations to manage all changes in IT infrastructure. It provides a structured approach to control and manage IT changes, minimizing the impact of change-related incidents and improving day-to-day operations. ServiceNow Change Management helps you maintain control over the entire change process, from creation, review, and approval, to scheduling, implementation, and post-implementation review.

Table Usage Guide

The servicenow_sn_chg_rest_change table provides insights into change requests within ServiceNow Change Management. As an IT manager or administrator, explore change request details through this table, including the current state, priority, category, and associated metadata. Utilize it to monitor the lifecycle of IT changes, such as those pending approval, in progress, or completed, and ensure the controlled management of IT changes.

Examples

What are the most common category for change requests?

Discover the segments that have the highest frequency of change requests, helping prioritize areas for process improvement or resource allocation.

select
category,
count(*) as cnt
from
servicenow_sn_chg_rest_change
group by
category
order by
cnt desc
limit
5;
select
category,
count(*) as cnt
from
servicenow_sn_chg_rest_change
group by
category
order by
cnt desc
limit
5;

How many change requests are currently open for a specific category?

Discover the number of ongoing change requests in a specific category, such as 'network'. This is useful for tracking and managing workload related to different types of change requests.

select
count(*)
from
servicenow_sn_chg_rest_change
where
state != 3
and category = 'network';
select
count(*)
from
servicenow_sn_chg_rest_change
where
state != 3
and category = 'network';

How many changes are in the "work in progress" state?

Analyze the number of changes that are currently in the 'work in progress' state. This is useful for tracking progress and managing workload in a service management context.

select
count(*) as num_changes
from
servicenow_sn_chg_rest_change
where
state = 2;
select
count(*) as num_changes
from
servicenow_sn_chg_rest_change
where
state = 2;

What is the average duration of changes made by each user?

Analyze the average time taken by each user to make changes, which could be useful for assessing individual productivity or identifying inefficiencies in the change process.

select
user,
avg(duration) as avg_duration
from
servicenow_sn_chg_rest_change
group by
user;
select
user,
avg(duration) as avg_duration
from
servicenow_sn_chg_rest_change
group by
user;

How many changes are in the "scheduled" state and have a "high" priority?

Determine the volume of high-priority tasks that are currently scheduled. This can assist in gauging the workload and prioritizing resource allocation.

select
count(*) as num_changes
from
servicenow_sn_chg_rest_change
where
state = 1
and priority = 2;
select
count(*) as num_changes
from
servicenow_sn_chg_rest_change
where
state = 1
and priority = 2;

Schema for servicenow_sn_chg_rest_change

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
action_statusbigintCurrent action status of the associated change request.
activebooleanFlag that indicates whether the change request is active.
activity_duetextDate and time for which the associated case is expected to be completed.
additional_assignee_listjsonbList of sys_ids of additional persons assigned to work on the change request.
approvaltextType of approval process required.
approval_historytextMost recent approval history journal entry.
approval_settextDate and time that the associated action was approved.
assigned_totextSys_id of the user assigned to the change request.
assignment_grouptextSys_id of the group assigned to the change request.
backout_plantextDescription of the plan to execute if the change must be reversed.
business_durationtextLength in scheduled work hours, work days, and work weeks that it took to complete the change.
business_servicetextSys_id of the business service associated with the change request. Located in the Service [cmdb_ci_service] table.
cab_datetextDate on which the Change Advisory Board (CAB) meets.
cab_delegatetextSys_id of the user that can substitute for the CAB manager during a CAB meeting. Located in the User [sys_user] table.
cab_recommendationtextDescription of the CAB recommendations for the change request.
cab_requiredbooleanFlag that indicates whether the CAB is required.
calendar_durationtextNot currently used by Change Management.
categorytextCategory of the change, for example hardware, network, or software.
change_plantextActivities and roles for managing and controlling the change request.
chg_modeltextSys_id of the change model that the associated change request was based on. Located in the Change Model [chg_model] table. The Change Model defines the state flow, transitions, and process activities that must be completed for the change request.
close_codetextCode assigned to the change request when it was closed. For example, Successful, Successful with issues, and Unsuccessful.
close_notestextNotes that the person entered when closing the change request.
closed_attextDate and time that the associated change request was closed.
closed_bytextSys_id of the person that closed the change request. Located in the User [sys_user] table.
cmdb_citextSys_id of the configuration item associated with the change request. Located in the Configuration Item [cmdb_ci] table.
commentsjsonbList of customer-facing work notes entered in the associated change request.
comments_and_work_notesjsonbList of both internal and customer-facing work notes entered for the associated change request.
companytextSys_id of the company associated with the change request. Located in the Company [core_company] table.
conflict_last_runtextDate and time that the conflict detection script was last run on the change request.
conflict_statustextCurrent conflict status as detected by the conflict detection script, such as Conflict and Not Run.
contact_typetextMethod in which the change request was initially requested.
contracttextSys_id of the contract associated with the change request. Located in the Contract [ast_contract] table.
correlation_displaytextUser-friendly name for the correlation_id.
correlation_idtextGlobally unique ID (GUID) of a matching change request record in a third-party system.
delivery_plantextNo longer in use. Sys_id of the delivery plan associated with the change request. Located in the Execution Plan [sc_cat_item_delivery_plan] table.
delivery_tasktextNo longer in use. Sys_id of the delivery task associated with the change request. Located in the Execution Plan Task [sc_cat_item_delivery_task] table.
descriptiontextDetailed description of the change request.
due_datetextTask due date. Not used by change request process.
end_datetextDate and time when the change request is to be completed.
escalationbigintCurrent escalation level.
expected_starttextDate and time when the task is to start. Not used by the change request process.
follow_uptextDate and time when a user followed-up with the person requesting the change request.
group_listjsonbList of sys_ids and names of the groups associated with the change request.
impactbigintImpact on the change request will have on the customer.
implementation_plantextSequential steps to execute to implement this change. It also contains any dependencies between steps and assignee details for each step.
justificationtextBenefits of implementing this change and the impact if this change is not implemented.
knowledgebooleanFlag that indicates whether there are any knowledge base ()KB) articles associated with the change request.
locationtextSys_id and name of the location of the equipment referenced in the change request. Located in the Location Located in the Location [cmn_location] table.
made_slabooleanNo longer used. Flag that indicates whether the change request was implemented in alignment with the associated service level agreement.
needs_attentionbooleanFlag that indicates whether the change request needs attention.
numbertextChange number assigned to the change request by the system, such as CHG0040007.
on_holdbooleanFlag that indicates whether the change request is currently on hold.
on_hold_reasontextIf the on_hold parameter is 'true', description of the reason why the change request is being held up.
on_hold_tasktextIf the on_hold parameter is 'true', list of the sys_ids of the tasks that must be completed before the hold is released.
opened_attextDate and time that the change release was created.
opened_bytextSys_id and name of the user that created the change release. Located in the User [sys_user] table.
orderbigintNot used by Change Management. Optional numeric field by which to order records, such as when retrieving them from a database.
outside_maintenance_schedulebooleanFlag that indicates whether maintenance by an outside company has been schedule for the change request.
parenttextSys_id and name of the parent task to this change request, if any. Located in the Task [task] table.
phasetextCurrent phase of the change request. This defines what the change is doing in greater detail.
phase_statetextChange_phase records that should be created for a change. They are dependent on the category, such that each type of change may have different change_phase records. The change_phase records provide an opportunity to control the approval process as each change_phase can have a schedule and a set of approvers.
prioritybigintPriority of the change request.
production_systembooleanFlag that indicates whether the change request is for a ServiceNow instance that is in a production environment.
reasontextDescription of why the change request was initiated.
reassignment_countbigintNumber of times that the change request has been reassigned to a new owner.
rejection_gototextSys_id of the task to perform if the change request is rejected. Located in the Task [table].
requested_bytextSys_id of the user that requested the change. Located in the User [sys_user] table.
requested_by_datetextDate and time when the change is requested to be implemented by.
review_commentstextComments entered when the change request was reviewed.
review_datetextDate that the change request was reviewed.
review_statustextCurrent status of the requested change request review.
riskbigintLevel of risk associated with the change request.
risk_impact_analysistextDescription of the risk and analysis of implementing the change request.
route_reasonbigintNot currently used by Change Management. Reason that the change request was transferred.
scopebigintSize of the change request.
service_offeringtextSys_id of the service offering associated with the change request. Service offerings uniquely define the level of service in terms of availability, scope, pricing, and packaging options. Located in the Offering [service_offering] table.
short_descriptiontextDescription of the change request.
skillsjsonbList of the sys_ids of all of the skills required to implement the change request. Located in the Skill [cmn_skill] table.
sla_duetextNo longer in use. Date and time that the change request must be completed based on the associated service level agreement.
sn_esign_documenttextSys_id of any E-signed document attached to the change request. Located in the Attachment [sys_attachment] table.
sn_esign_esignature_configurationtextSys_id of the E-signed signature template used for the associated document. Located in the E-signature Template [sn_esign_configuration] table.
start_datetextDate and time that the change request is planned to start implementation.
statebigintCurrent state of the change request. Possible values are defined in the change model.
std_change_producer_versiontextSys_id of the record producer and change proposal associated with the change request. It also includes the number and percentage of successful and unsuccessful change requests created from the proposal. Located in the Standard Change Template Version [std_change_producer_version] table.
sys_class_nametextName of the table in which the change request is located.
sys_created_bytextName of the user that initially created the change request.
sys_created_ontextDate and time that the associated change request record was originally created.
sys_domaintextIf using domains in the instance, the name of the domain to which the change module record is associated.
sys_domain_pathtextIf using domains in the instance, the domain path in which the associated change module record resides.
sys_idtextUnique identifier of the associated change request record.
sys_mod_countbigintNumber of updates to the case since it was initially created.
sys_updated_bytextPerson that last updated the case.
sys_updated_ontextDate and time when the case was last updated.
task_effective_numbertextUniversal request number.
task_fortextNot used by Change Management. Sys_id of the user that the task was created for. Located in the User [sys_user] table.
test_plantextDescription of the associated test plan for the change.
time_workedtextTotal amount of time worked on the change request.
typetextChange request type.
unauthorizedbooleanFlag that indicates whether the change request is unauthorized
universal_requesttextSys_id of the Parent Universal request to which this change request is a part of. Located in the Task [task] table.
upon_approvaltextAction to take if the change request is approved.
upon_rejecttextAction to take if the change request is rejected.
urgencybigintUrgency of the change request.
user_inputtextAdditional user input.
variablestextName-value pairs of variables associated with the change request.
watch_listjsonbList of sys_ids of the users who receive notifications about this change request when additional comments are added or if the state of a change request is changed to Resolved or Closed. Located in the User [sys_user] table.
wf_activitytextSys_id of the workflow activity record associated with the change request. Located in the Workflow Activity [wf_activity] table.
work_endtextDate and time work ended on the change request.
work_notestextInformation about how to resolve the change request, or steps taken to resolve it.
work_notes_listjsonbList of sys_ids of the internal users who receive notifications about this change request when work notes are added. Located in the User [sys_user] table.
work_starttextDate and time that work started on the change request.

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_sn_chg_rest_change