steampipe plugin install aws

Table: aws_ssm_association - Query AWS SSM Association using SQL

The AWS SSM Association is a component of AWS Systems Manager that allows you to configure and manage instances at scale. It enables you to perform administrative tasks such as installing patches, updating agents, or applying policies. With SSM Association, you can automate the process of keeping your managed instances in a desired state.

Table Usage Guide

The aws_ssm_association table in Steampipe provides you with information about the AWS Systems Manager (SSM) associations. This table enables you, as a DevOps engineer, to query association-specific details, including the association ID, the instance ID it is associated with, the association version, and the parameters of the association. You can utilize this table to gather insights on associations, such as the status of associations, the targets of associations, and the parameters of associations. The schema outlines the various attributes of the SSM association for you, including the association name, association ID, instance ID, association version, parameters, and more.

Examples

Basic info

Explore which AWS Systems Manager (SSM) associations are currently active, including their compliance severity and last execution date. This can help in managing and monitoring the AWS SSM associations effectively.

select
association_id,
association_name,
arn,
association_version,
last_execution_date,
document_name,
compliance_severity,
region
from
aws_ssm_association;
select
association_id,
association_name,
arn,
association_version,
last_execution_date,
document_name,
compliance_severity,
region
from
aws_ssm_association;

List associations that have a failed status

Identify instances where AWS System Manager associations have failed. This is useful for troubleshooting and rectifying the issues causing the failure.

select
association_id,
overview ->> 'AssociationStatusAggregatedCount' as association_status_aggregated_count,
overview ->> 'DetailedStatus' as detailed_status,
overview ->> 'Status' as status
from
aws_ssm_association
where
overview ->> 'Status' = 'Failed';
select
association_id,
json_extract(overview, '$.AssociationStatusAggregatedCount') as association_status_aggregated_count,
json_extract(overview, '$.DetailedStatus') as detailed_status,
json_extract(overview, '$.Status') as status
from
aws_ssm_association
where
json_extract(overview, '$.Status') = 'Failed';

List instances targeted by the association

Discover the instances that are targeted by a specific association within the AWS SSM service. This is beneficial for gaining insights into how your resources are being utilized and managed.

select
association.association_id as association_id,
target ->> 'Key' as target_key,
target ->> 'Values' as target_value,
instances
from
aws_ssm_association as association,
jsonb_array_elements(targets) as target,
jsonb_array_elements_text(target -> 'Values') as instances
where
target ->> 'Key' = 'InstanceIds';
select
association.association_id as association_id,
json_extract(target.value, '$.Key') as target_key,
json_extract(target.value, '$.Values') as target_value,
instances.value as instances
from
aws_ssm_association as association,
json_each(association.targets) as target,
json_each(json_extract(target.value, '$.Values')) as instances
where
json_extract(target.value, '$.Key') = 'InstanceIds';

List associations with a critical compliance severity level

Discover the associations that have a critical compliance severity level. This can be useful for identifying potential risk areas in your AWS Simple Systems Manager configuration.

select
association_id,
association_name,
targets,
document_name
from
aws_ssm_association
where
compliance_severity = 'CRITICAL';
select
association_id,
association_name,
targets,
document_name
from
aws_ssm_association
where
compliance_severity = 'CRITICAL';

Schema for aws_ssm_association

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
apply_only_at_cron_intervalbooleanBy default, when you create a new associations, the system runs it immediately after it is created and then according to the schedule you specified. Specify this option if you don't want an association to run immediately after you create it. This parameter is not supported for rate expressions.
arntextThe Amazon Resource Name (ARN) specifying the association.
association_idtext=The ID created by the system when you create an association.
association_nametext=The Name of association.
association_versiontextThe association version.
automation_target_parameter_nametextSpecify the target for the association. This target is required for associations that use an Automation document and target resources by using rate controls.
compliance_severitytextA cron expression that specifies a schedule when the association runs.
datetimestamp with time zoneThe date when the association was made.
document_nametextThe name of the Systems Manager document.
document_versiontextThe version of the document used in the association.
instance_idtext=The ID of the instance.
last_execution_datetimestamp with time zone=The date on which the association was last run.
last_successful_execution_datetimestamp with time zoneThe last date on which the association was successfully run.
last_update_association_datetimestamp with time zoneThe date when the association was last updated.
max_concurrencytextThe maximum number of targets allowed to run the association at the same time.
max_errorstextThe number of errors that are allowed before the system stops sending requests to run the association on additional targets.
output_locationjsonbAn S3 bucket where you want to store the output details of the request.
overviewjsonbInformation about the association.
parametersjsonbA description of the parameters for a document.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
schedule_expressiontextA cron expression that specifies a schedule when the association runs.
statustext=The status of the association. Status can be: Pending, Success, or Failed.
sync_compliancetextThe mode for generating association compliance. You can specify AUTO or MANUAL.
target_locationsjsonbThe combination of AWS Regions and AWS accounts where you want to run the association.
targetsjsonbA cron expression that specifies a schedule when the association runs.
titletextTitle of the resource.

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_association