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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The AWS Account ID in which the resource is located.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
alarm_configuration	jsonb		The details for the CloudWatch alarm you want to apply to an automation or command.
apply_only_at_cron_interval	boolean		By 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.
arn	text		The Amazon Resource Name (ARN) specifying the association.
association_id	text	=	The ID created by the system when you create an association.
association_name	text	=	The Name of association.
association_version	text		The association version.
automation_target_parameter_name	text		Specify the target for the association. This target is required for associations that use an Automation document and target resources by using rate controls.
calendar_names	jsonb		The names or Amazon Resource Names (ARNs) of the Change Calendar type documents your associations are gated under.
compliance_severity	text		A cron expression that specifies a schedule when the association runs.
date	timestamp with time zone		The date when the association was made.
document_name	text		The name of the Systems Manager document.
document_version	text		The version of the document used in the association.
instance_id	text	=	The ID of the instance.
last_execution_date	timestamp with time zone	=	The date on which the association was last run.
last_successful_execution_date	timestamp with time zone		The last date on which the association was successfully run.
last_update_association_date	timestamp with time zone		The date when the association was last updated.
max_concurrency	text		The maximum number of targets allowed to run the association at the same time.
max_errors	text		The number of errors that are allowed before the system stops sending requests to run the association on additional targets.
output_location	jsonb		An S3 bucket where you want to store the output details of the request.
overview	jsonb		Information about the association.
parameters	jsonb		A description of the parameters for a document.
partition	text		The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
region	text		The AWS Region in which the resource is located.
schedule_expression	text		A cron expression that specifies a schedule when the association runs.
schedule_offset	bigint		Number of days to wait after the scheduled day to run an association.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	text	=	The status of the association. Status can be: Pending, Success, or Failed.
sync_compliance	text		The mode for generating association compliance. You can specify AUTO or MANUAL.
target_locations	jsonb		The combination of AWS Regions and AWS accounts where you want to run the association.
targets	jsonb		A cron expression that specifies a schedule when the association runs.
title	text		Title of the resource.
triggered_alarms	jsonb		The CloudWatch alarm that was invoked during the association.

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