Table: aws_ssm_parameter - Query AWS Systems Manager Parameter Store using SQL

The AWS Systems Manager Parameter Store provides secure, hierarchical storage for configuration data management and secrets management. It allows you to centrally manage your configuration data, whether plain-text data such as database strings or secrets like passwords, thus improving the security of your data by using AWS Key Management Service (KMS). Parameter Store is designed to use with other AWS services to pull configuration data and keep your applications secure and scalable.

Table Usage Guide

The aws_ssm_parameter table in Steampipe provides you with information about parameters within the AWS Systems Manager Parameter Store. This table allows you, as a DevOps engineer, to query parameter-specific details, such as parameter names, types, values, and associated metadata. You can utilize this table to gather insights on parameters, such as parameter descriptions, last modification dates, and the user who last modified the parameter. The schema outlines the various attributes of the parameter for you, including the parameter ARN, type, value, and associated tags.

Examples

SSM parameter basic info

Explore the basic information of AWS SSM parameters to understand their types, data types, tiers, and the regions they are located in. This can help in managing and organizing these parameters efficiently.

select
  name,
  type,
  data_type,
  tier,
  region
from
  aws_ssm_parameter;

select
  name,
  type,
  data_type,
  tier,
  region
from
  aws_ssm_parameter;

Policy details of advanced tier ssm parameter

Explore the policy details of advanced tier parameters within AWS's Simple Systems Manager (SSM). This query can be used to understand the policy type, status, and text, providing valuable insights into the configuration and usage of these parameters.

select
  name,
  tier,
  p ->> 'PolicyType' as policy_type,
  p ->> 'PolicyStatus' as Policy_status,
  p ->> 'PolicyText' as policy_text
from
  aws_ssm_parameter,
  jsonb_array_elements(policies) as p;

select
  name,
  tier,
  json_extract(p.value, '$.PolicyType') as policy_type,
  json_extract(p.value, '$.PolicyStatus') as policy_status,
  json_extract(p.value, '$.PolicyText') as policy_text
from
  aws_ssm_parameter,
  json_each(policies) as p;

List of SSM parameters which do not have owner or app_id tag key

Determine the areas in which AWS SSM parameters are missing essential tags such as 'owner' or 'app_id'. This is useful in identifying potential gaps in your tagging strategy, which could impact resource management and cost allocation.

select
  name
from
  aws_ssm_parameter
where
  tags -> 'owner' is null
  or tags -> 'app_id' is null;

select
  name
from
  aws_ssm_parameter
where
  json_extract(tags, '$.owner') is null
  or json_extract(tags, '$.app_id') is null;

Schema for aws_ssm_parameter

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.
arn	text		The Amazon Resource Name (ARN) of the parameter.
data_type	text	=	The data type of the parameter, such as text or aws:ec2:image. The default is text.
key_id	text	=	The ID of the query key used for this parameter.
last_modified_date	timestamp with time zone		Date the parameter was last changed or updated.
last_modified_user	text		Amazon Resource Name (ARN) of the AWS user who last changed the parameter.
name	text	=	The parameter name.
partition	text		The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
policies	jsonb		A list of policies associated with a parameter. Parameter policies help you manage a growing set of parameters by enabling you to assign specific criteria to a parameter such as an expiration date or time to live. Parameter policies are especially helpful in forcing you to update or delete passwords and configuration data stored in Parameter Store.
region	text		The AWS Region in which the resource is located.
selector	text		Either the version number or the label used to retrieve the parameter value.
source_result	text		SourceResult is the raw result or response from the source. Applies to parameters that reference information in other AWS services.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tags	jsonb		A map of tags for the resource.
tags_src	jsonb		A list of tags assigned to the parameter.
tier	text	=	The parameter tier.
title	text		Title of the resource.
type	text	=	The type of parameter. Valid parameter types include the following: String, StringList, and SecureString.
value	text		The value of parameter.
version	bigint		The parameter version.

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_parameter