steampipe plugin install awssteampipe plugin install aws

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_count
from
  aws_ssm_managed_instance_patch_state;

select
  instance_id,
  baseline_id,
  operation,
  patch_group,
  failed_count,
  installed_count,
  installed_other_count
from
  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_count
from
  aws_ssm_managed_instance_patch_state;

select
  instance_id,
  baseline_id,
  installed_count
from
  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_count
from
  aws_ssm_managed_instance_patch_state;

select
  instance_id,
  baseline_id,
  installed_other_count
from
  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_count
from
  aws_ssm_managed_instance_patch_state;

select
  instance_id,
  baseline_id,
  security_non_compliant_count
from
  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_time
from
  aws_ssm_managed_instance_patch_state
where
  operation_end_time >= now() - interval '10' day;

select
  instance_id,
  baseline_id,
  operation,
  operation_end_time,
  operation_start_time
from
  aws_ssm_managed_instance_patch_state
where
  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,
  operation
from
  aws_ssm_managed_instance_patch_state
where
  operation = 'Scan';

select
  instance_id,
  baseline_id,
  operation
from
  aws_ssm_managed_instance_patch_state
where
  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.
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