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_affectedfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by task_nameorder by num_ci_affected desc;
select task_name, count(ci_item_sys_id) as num_ci_affectedfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by task_nameorder 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_ciwhere manual_proposed_change = true;
select count(*)from servicenow_sn_chg_rest_change_affected_cmdb_ciwhere 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_datefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere task_name = 'CHG0000060'order by applied_date desc;
select ci_item_name, task_name, applied_datefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere 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_changesfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by applied;
select applied, count(*) as num_changesfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup 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_datefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere ci_item_sys_id = '2216daf0d7820200c1ed0fbc5e6103ca'order by applied_date desc;
select task_name, applied_datefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere 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_changesfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by ci_item_nameorder by num_changes desc;
select ci_item_name, count(task_name) as num_changesfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by ci_item_nameorder 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_createdfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by sys_created_byorder by num_changes_created desc;
select sys_created_by, count(*) as num_changes_createdfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by sys_created_byorder 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_namefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere manual_proposed_change = true;
select task_name, ci_item_namefrom servicenow_sn_chg_rest_change_affected_cmdb_ciwhere 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_tasksfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by task, task_nameorder by num_tasks desclimit 1;
select task, task_name, count(*) as num_tasksfrom servicenow_sn_chg_rest_change_affected_cmdb_cigroup by task, task_nameorder by num_tasks desclimit 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_affectedfrom ( 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_affectedfrom ( 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
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
applied | boolean | Indicates whether the change was applied to the CI item. | |
applied_date | timestamp with time zone | Date when the change was applied to the CMDB CI item. | |
ci_item_name | text | Name of the CI item affected by the change. | |
ci_item_sys_id | text | System ID of the CMDB CI item affected by the change. | |
manual_proposed_change | boolean | Indicates whether the proposed change was manual. | |
sys_created_by | text | User who created the record. | |
sys_created_on | timestamp with time zone | Date and time when the record was created. | |
sys_id | text | Unique system identifier for the record. | |
sys_mod_count | bigint | Number of times the record was modified. | |
sys_tags | text | Tags associated with the record. | |
sys_updated_by | text | User who last updated the record. | |
sys_updated_on | timestamp with time zone | Date and time when the record was last updated. | |
task | text | Task associated with the affected CMDB CI item. | |
task_name | text | Name 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