Table: aws_ssm_managed_instance_patch_state - Query AWS Systems Manager Managed Instance Patch State using SQL
The AWS Systems Manager Managed Instance Patch State is a feature of AWS Systems Manager that provides information about the patch state of your managed instances. It allows you to determine the patch compliance of instances in your managed environment, helping you to maintain the security and compliance of your instances. This feature can be queried using SQL, providing detailed information about the patch status of each instance.
Table Usage Guide
The aws_ssm_managed_instance_patch_state
table in Steampipe provides you with information about the patch state of managed instances within AWS Systems Manager (SSM). This table allows you, as a DevOps engineer, to query specific details related to the patch state, including the instance ID, patch group, owner information, installed patches, and more. You can utilize this table to gather insights on patch compliance and to monitor the patching status of your managed instances. The schema outlines the various attributes of the managed instance patch state for you, including the instance ID, patch group, owner information, installed patches, and associated tags.
Examples
Basic info
Analyze the status of patch installation in AWS managed instances to understand the effectiveness of patching operations. This helps in identifying instances where patch installation has failed, thereby enabling timely troubleshooting and ensuring system security.
select instance_id, baseline_id, operation, patch_group, failed_count, installed_count, installed_other_countfrom aws_ssm_managed_instance_patch_state;
select instance_id, baseline_id, operation, patch_group, failed_count, installed_count, installed_other_countfrom aws_ssm_managed_instance_patch_state;
Count the number of patches installed from patch base line
Determine the total number of installed patches from a baseline to assess the level of system updates in your AWS managed instances. This can help in identifying systems that may be lagging in updates, aiding in maintaining security and performance standards.
select instance_id, baseline_id, installed_countfrom aws_ssm_managed_instance_patch_state;
select instance_id, baseline_id, installed_countfrom aws_ssm_managed_instance_patch_state;
Count the number of patches installed not from patch base line
Determine the areas in which patches have been installed outside of the baseline, allowing for a better understanding of potential security vulnerabilities or inconsistencies in system management.
select instance_id, baseline_id, installed_other_countfrom aws_ssm_managed_instance_patch_state;
select instance_id, baseline_id, installed_other_countfrom aws_ssm_managed_instance_patch_state;
Count of non-compliant security patches for each instance
Determine the areas in which non-compliant security patches exist for each instance. This helps in identifying potential security vulnerabilities and aids in maintaining system integrity.
select instance_id, baseline_id, security_non_compliant_countfrom aws_ssm_managed_instance_patch_state;
select instance_id, baseline_id, security_non_compliant_countfrom aws_ssm_managed_instance_patch_state;
List patch operations in the last 10 days
Explore the recent activities of patch operations within the last 10 days. This can be beneficial for monitoring and maintaining the health and security of your managed instances.
select instance_id, baseline_id, operation, operation_end_time, operation_start_timefrom aws_ssm_managed_instance_patch_statewhere operation_end_time >= now() - interval '10' day;
select instance_id, baseline_id, operation, operation_end_time, operation_start_timefrom aws_ssm_managed_instance_patch_statewhere operation_end_time >= datetime('now', '-10 day');
List scan patches
Discover the segments that are currently in the 'Scan' operation state within your managed instances. This can be particularly useful in understanding and managing your system's security patching process.
select instance_id, baseline_id, operationfrom aws_ssm_managed_instance_patch_statewhere operation = 'Scan';
select instance_id, baseline_id, operationfrom aws_ssm_managed_instance_patch_statewhere operation = 'Scan';
Schema for aws_ssm_managed_instance_patch_state
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
account_id | text | =, !=, ~~, ~~*, !~~, !~~* | The AWS Account ID in which the resource is located. |
baseline_id | text | The ID of the patch baseline used to patch the managed node. | |
critical_non_compliant_count | bigint | The number of patches per node that are specified as Critical for compliance reporting in the patch baseline aren't installed. These patches might be missing, have failed installation, were rejected, or were installed but awaiting a required managed node reboot. The status of these managed nodes is NON_COMPLIANT. | |
failed_count | bigint | The number of patches from the patch baseline that were attempted to be installed during the last patching operation, but failed to install. | |
install_override_list | bigint | An https URL or an Amazon Simple Storage Service (Amazon S3) path-style URL to a list of patches to be installed. | |
installed_count | bigint | The number of patches from the patch baseline that are installed on the managed node. | |
installed_other_count | bigint | The number of patches not specified in the patch baseline that are installed on the managed node. | |
installed_pending_reboot_count | bigint | The number of patches installed by Patch Manager since the last time the managed node was rebooted. | |
installed_rejected_count | bigint | The number of patches installed on a managed node that are specified in a RejectedPatches list. Patches with a status of InstalledRejected were typically installed before they were added to a RejectedPatches list. | |
instance_id | text | = | The ID of the managed node the high-level patch compliance information was collected for. |
last_no_reboot_install_operation_time | timestamp with time zone | The time of the last attempt to patch the managed node with NoReboot specified as the reboot option. | |
missing_count | bigint | The number of patches from the patch baseline that are applicable for the managed node but aren't currently installed. | |
not_applicable_count | bigint | The number of patches from the patch baseline that aren't applicable for the managed node and therefore aren't installed on the node. This number may be truncated if the list of patch names is very large. The number of patches beyond this limit are reported in UnreportedNotApplicableCount. | |
operation | text | The type of patching operation that was performed. | |
operation_end_time | timestamp with time zone | The time the most recent patching operation completed on the managed node. | |
operation_start_time | timestamp with time zone | The time the most recent patching operation was started on the managed node. | |
other_non_compliant_count | bigint | The number of patches per node that are specified as other than Critical or Security but aren't compliant with the patch baseline. The status of these managed nodes is NON_COMPLIANT. | |
owner_information | text | Placeholder information. This field will always be empty in the current release of the service. | |
partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). | |
patch_group | text | The name of the patch group the managed node belongs to. | |
reboot_option | text | Indicates the reboot option specified in the patch baseline. Reboot options apply to Install operations only. Reboots aren't attempted for Patch Manager Scan operations. | |
region | text | The AWS Region in which the resource is located. | |
security_non_compliant_count | bigint | The number of patches per node that are specified as Security in a patch advisory aren't installed. These patches might be missing, have failed installation, were rejected, or were installed but awaiting a required managed node reboot. The status of these managed nodes is NON_COMPLIANT. | |
snapshot_id | text | The ID of the patch baseline snapshot used during the patching operation when this compliance data was collected. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
title | text | Title of the resource. | |
unreported_not_applicable_count | bigint | The number of patches beyond the supported limit of NotApplicableCount that aren't reported by name to Inventory. Inventory is a capability of Amazon Web Services Systems Manager. |
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)" -- aws
You can pass the configuration to the command with the --config
argument:
steampipe_export_aws --config '<your_config>' aws_ssm_managed_instance_patch_state