turbot/servicenow
steampipe plugin install servicenow

Table: servicenow_sn_chg_rest_change_affected_cmdb_ci - Query ServiceNow Change Management Configuration Items using SQL

ServiceNow Change Management is a service within ServiceNow that helps organizations manage and control IT infrastructure changes. It provides a systematic approach to control the lifecycle of all changes, facilitating beneficial changes to be made with minimal disruption to IT services. Configuration Items (CI) in ServiceNow represent specific, tangible objects in an environment such as hardware, software, and services.

Table Usage Guide

The servicenow_sn_chg_rest_change_affected_cmdb_ci table provides insights into Configuration Items (CI) affected by changes within ServiceNow Change Management. As an IT service manager, explore CI-specific details through this table, including CI's impacted by specific changes, their relationships, and associated metadata. Utilize it to uncover information about the impact of changes, such as those affecting critical services, the relationships between CIs, and verification of change plans.

Examples

How many CMDB CI items are affected by each change?

Determine the impact of each change on your Configuration Management Database (CMDB) by identifying the number of Configuration Items (CI) affected by each task. This can help prioritize tasks based on the scale of their impact.

select
task_name,
count(ci_item_sys_id) as num_ci_affected
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task_name
order by
num_ci_affected desc;
select
task_name,
count(ci_item_sys_id) as num_ci_affected
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task_name
order by
num_ci_affected desc;

What is the total number of manual proposed changes in the table?

Explore the total count of proposed changes that have been manually inputted, providing a quick overview of interventions that may require further review or approval. This could be useful in assessing the volume of manual interventions and their potential impact on system stability.

select
count(*)
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
manual_proposed_change = true;
select
count(*)
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
manual_proposed_change = 1;

Which CMDB CI items were affected by a specific change?

Determine the configuration items (CI) affected by a specific change in your ServiceNow Change Management Database (CMDB). This can be useful for understanding the impact of changes, allowing for more informed decision making.

select
ci_item_name,
task_name,
applied_date
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
task_name = 'CHG0000060'
order by
applied_date desc;
select
ci_item_name,
task_name,
applied_date
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
task_name = 'CHG0000060'
order by
applied_date desc;

What is the distribution of applied and not applied changes in the table?

Explore the distribution of applied and unapplied changes to understand the overall change management process. This can help identify potential bottlenecks and areas for improvement in the change application process.

select
applied,
count(*) as num_changes
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
applied;
select
applied,
count(*) as num_changes
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
applied;

Which change affected a specific CMDB CI item?

Analyze the settings to understand which modifications impacted a particular configuration item (CI) in your Configuration Management Database (CMDB). This is particularly useful for tracking changes and troubleshooting issues related to specific CIs.

select
task_name,
applied_date
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
ci_item_sys_id = '2216daf0d7820200c1ed0fbc5e6103ca'
order by
applied_date desc;
select
task_name,
applied_date
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
ci_item_sys_id = '2216daf0d7820200c1ed0fbc5e6103ca'
order by
applied_date desc;

What is the total number of changes that affected each CMDB CI item?

Determine the frequency of changes impacting each configuration item to assess the stability and potential risk areas in your IT environment. This can help prioritize areas for improvement and risk mitigation.

select
ci_item_name,
count(task_name) as num_changes
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
ci_item_name
order by
num_changes desc;
select
ci_item_name,
count(task_name) as num_changes
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
ci_item_name
order by
num_changes desc;

Who created the most changes in the table?

Discover the users who have made the most changes in a system, providing a way to identify key contributors or potential sources of system instability. This information can be useful in managing system maintenance and troubleshooting.

select
sys_created_by,
count(*) as num_changes_created
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
sys_created_by
order by
num_changes_created desc;
select
sys_created_by,
count(*) as num_changes_created
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
sys_created_by
order by
num_changes_created desc;

Which changes were applied manually?

Determine the areas in which changes were manually applied to understand potential risks and ensure proper change management protocols were followed. This is beneficial in maintaining system integrity and avoiding unexpected issues due to manual interventions.

select
task_name,
ci_item_name
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
manual_proposed_change = true;
select
task_name,
ci_item_name
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
where
manual_proposed_change = 1;

What is the most common task in the table?

Explore which task appears most frequently within a certain service, providing insight into the most common operation or action that occurs within that context. This can help in identifying areas for process optimization or resource allocation.

select
task,
task_name,
count(*) as num_tasks
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task,
task_name
order by
num_tasks desc
limit
1;
select
task,
task_name,
count(*) as num_tasks
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task,
task_name
order by
num_tasks desc
limit
1;

What is the average number of CMDB CI items affected by each change?

Determine the average number of Configuration Items (CI) impacted by each change in your ServiceNow change management process. This can help in assessing the potential impact and risk of changes, aiding in better change planning and management.

select
avg(num_ci_affected) as avg_num_ci_affected
from
(
select
task_name,
count(ci_item_sys_id) as num_ci_affected
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task_name
) as subquery;
select
avg(num_ci_affected) as avg_num_ci_affected
from
(
select
task_name,
count(ci_item_sys_id) as num_ci_affected
from
servicenow_sn_chg_rest_change_affected_cmdb_ci
group by
task_name
);

Schema for servicenow_sn_chg_rest_change_affected_cmdb_ci

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
appliedbooleanIndicates whether the change was applied to the CI item.
applied_datetimestamp with time zoneDate when the change was applied to the CMDB CI item.
ci_item_nametextName of the CI item affected by the change.
ci_item_sys_idtextSystem ID of the CMDB CI item affected by the change.
manual_proposed_changebooleanIndicates whether the proposed change was manual.
sys_created_bytextUser who created the record.
sys_created_ontimestamp with time zoneDate and time when the record was created.
sys_idtextUnique system identifier for the record.
sys_mod_countbigintNumber of times the record was modified.
sys_tagstextTags associated with the record.
sys_updated_bytextUser who last updated the record.
sys_updated_ontimestamp with time zoneDate and time when the record was last updated.
tasktextTask associated with the affected CMDB CI item.
task_nametextName of the task associated with the affected CMDB CI item.

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_affected_cmdb_ci