steampipe plugin install aws

Table: aws_ssm_inventory - Query AWS Systems Manager Inventory using SQL

The AWS Systems Manager Inventory provides visibility into your Amazon EC2 and on-premises compute infrastructure. It collects metadata from your managed instances about applications, files, Windows updates, network configurations, and other details. This collected data assists in managing your systems, tracking software inventory, and applying patches.

Table Usage Guide

The aws_ssm_inventory table in Steampipe provides you with information about managed instances within AWS Systems Manager. This table enables you, as a DevOps engineer, to query instance-specific details, including instance name, type, platform type, and associated metadata. You can utilize this table to gather insights on instances, such as their status, their associated tags, and more. The schema outlines for you the various attributes of the managed instance, including the instance ID, instance type, platform type, and associated tags.

Examples

Basic info

Explore which AWS Simple Systems Manager (SSM) inventory items have been captured at a specific time, allowing you to understand the historical state of your resources and their schema versions across different regions. This information can aid in resource management and tracking changes in your AWS environment.

select
id,
type_name,
capture_time,
schema_version,
content,
region
from
aws_ssm_inventory;
select
id,
type_name,
capture_time,
schema_version,
content,
region
from
aws_ssm_inventory;

Get content details of a managed instance

Explore the essential characteristics of a particular managed instance, such as its platform type, agent version, and status. This information can be useful for understanding the instance's current configuration and performance, as well as for troubleshooting potential issues.

select
si.id,
c ->> 'AgentType' as agent_type,
c ->> 'IpAddress' as ip_address,
c ->> 'AgentVersion' as agent_version,
c ->> 'ComputerName' as computer_name,
c ->> 'PlatformName' as platform_name,
c ->> 'PlatformType' as platform_type,
c ->> 'ResourceType' as resource_type,
c ->> 'InstanceStatus' as instance_status,
c ->> 'PlatformVersion' as platform_version
from
aws_ssm_inventory as si,
jsonb_array_elements(content) as c
where
id = 'i-0665a65b1a1c2b47g';
select
si.id,
json_extract(c, '$.AgentType') as agent_type,
json_extract(c, '$.IpAddress') as ip_address,
json_extract(c, '$.AgentVersion') as agent_version,
json_extract(c, '$.ComputerName') as computer_name,
json_extract(c, '$.PlatformName') as platform_name,
json_extract(c, '$.PlatformType') as platform_type,
json_extract(c, '$.ResourceType') as resource_type,
json_extract(c, '$.InstanceStatus') as instance_status,
json_extract(c, '$.PlatformVersion') as platform_version
from
aws_ssm_inventory as si,
json_each(content) as c
where
id = 'i-0665a65b1a1c2b47g';

List schema definitions of inventories

This query helps you gain insights into the structure and organization of your AWS Systems Manager (SSM) inventories. It's useful for understanding the types of data stored in each inventory and how they are presented, which can aid in managing and utilizing your SSM resources effectively.

select
id,
s ->> 'Version' as schema_version,
s ->> 'TypeName' as type_name,
s ->> 'DisplayName' as display_name,
jsonb_pretty(s -> 'Attributes') as attributes
from
aws_ssm_inventory,
jsonb_array_elements(schema) as s
order by
id;
select
si.id,
json_extract(s.value, '$.Version') as schema_version,
json_extract(s.value, '$.TypeName') as type_name,
json_extract(s.value, '$.DisplayName') as display_name,
json_extract(s.value, '$.Attributes') as attributes
from
aws_ssm_inventory as si,
json_each(schema) as s
order by
si.id;

Get inventory details from the last 10 days

Explore recent changes in your AWS inventory by identifying items that have been added or modified in the last 10 days. This is useful for keeping track of inventory updates and ensuring system integrity.

select
id,
type_name,
capture_time,
schema_version,
content
from
aws_ssm_inventory
where
capture_time >= now() - interval '10' day;
select
id,
type_name,
capture_time,
schema_version,
content
from
aws_ssm_inventory
where
capture_time >= datetime('now', '-10 day');

Get inventory content of all running instances

Explore the specific attributes of all operational instances, including details such as their agent type, IP address, platform, and status. This is useful for effectively managing and monitoring your active instances in a cloud environment.

select
v.id,
i.instance_state,
i.instance_type,
c ->> 'AgentType' as agent_type,
c ->> 'IpAddress' as ip_address,
c ->> 'AgentVersion' as agent_version,
c ->> 'ComputerName' as computer_name,
c ->> 'PlatformName' as platform_name,
c ->> 'PlatformType' as platform_type,
c ->> 'ResourceType' as resource_type,
c ->> 'InstanceStatus' as instance_status,
c ->> 'PlatformVersion' as platform_version
from
aws_ssm_inventory as v,
aws_ec2_instance as i,
jsonb_array_elements(content) as c
where
v.id = i.instance_id
and i.instance_state = 'running';
select
v.id,
i.instance_state,
i.instance_type,
json_extract(c.value, '$.AgentType') as agent_type,
json_extract(c.value, '$.IpAddress') as ip_address,
json_extract(c.value, '$.AgentVersion') as agent_version,
json_extract(c.value, '$.ComputerName') as computer_name,
json_extract(c.value, '$.PlatformName') as platform_name,
json_extract(c.value, '$.PlatformType') as platform_type,
json_extract(c.value, '$.ResourceType') as resource_type,
json_extract(c.value, '$.InstanceStatus') as instance_status,
json_extract(c.value, '$.PlatformVersion') as platform_version
from
aws_ssm_inventory as v,
aws_ec2_instance as i,
json_each(v.content) as c
where
v.id = i.instance_id
and i.instance_state = 'running';

Schema for aws_ssm_inventory

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
application_attribute_keytext=The attribute key of the type name AWS:Application.
application_attribute_valuetext=The value for the attribute key of the type name AWS:Application.
capture_timetimestamp with time zoneThe time that inventory information was collected for the managed node(s).
compliance_attribute_keytext=The attribute key of the type name AWS:ComplianceItem.
compliance_attribute_valuetext=The value for the attribute key of the type name AWS:ComplianceItem.
component_attribute_keytext=The attribute key that are supported for type name AWS:AWSComponent, Possible values are: Name,ApplicationType,Publisher,Version,InstalledTime,Architecture and URL.
component_attribute_valuetext=The value for the component attribute key.
contentjsonbContains all the inventory data of the item type. Results include attribute names and values.
file_attribute_keytext=The attribute key of the type name AWS:File.
file_attribute_valuetext=The value for the attribute key of the type name AWS:File.
filter_keytext=The name of the filter key. Example: inventory filter key where managed node ID 'AWS:InstanceInformation.InstanceId'.
filter_valuetext=, !=, >, <, >=, <=Inventory filter values. Example: inventory filter where managed node IDs are specified as values 'i-a12b3c4d5e6g'.
idtext=ID of the inventory result entity.
instance_detailed_information_attribute_keytext=The attribute key of the type name AWS:InstanceDetailedInformation.
instance_detailed_information_attribute_valuetext=The value for the attribute key of the type name AWS:InstanceDetailedInformation.
instance_information_attribute_keytext=The attribute key of the type name AWS:InstanceInformation.
instance_information_attribute_valuetext=The value for the attribute key of the type name AWS:InstanceInformation.
network_attribute_keytext=The attribute key of the type name AWS:Network.
network_attribute_valuetext=The value for the attribute key of the type name AWS:Network.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
patch_compliance_attribute_keytext=The attribute key of the type name AWS:PatchCompliance.
patch_compliance_attribute_valuetext=The value for the attribute key of the type name AWS:PatchCompliance.
patch_summary_attribute_keytext=The attribute key of the type name AWS:PatchSummary.
patch_summary_attribute_valuetext=The value for the attribute key of the type name AWS:PatchSummary.
regiontextThe AWS Region in which the resource is located.
resource_group_attribute_keytext=The attribute key of the type name AWS:ResourceGroup.
resource_group_attribute_valuetext=The value for the attribute key of the type name AWS:ResourceGroup.
schemajsonbThe inventory item schema definition. Users can use this to compose inventory query filters.
schema_versiontextThe inventory schema version used by the managed node(s).
service_attribute_keytext=The attribute key of the type name AWS:Service.
service_attribute_valuetext=The value for the attribute key of the type name AWS:Service.
tag_attribute_keytext=The attribute key of the type name AWS:Tag.
tag_attribute_valuetext=The value for the attribute key of the type name AWS:Tag.
titletextTitle of the resource.
type_nametext=The type of inventory item returned by the request.
windows_registry_attribute_keytext=The attribute key of the type name AWS:WindowsRegistry.
windows_registry_attribute_valuetext=The value for the attribute key of the type name AWS:WindowsRegistry.
windows_role_attribute_keytext=The attribute key of the type name AWS:WindowsRole.
windows_role_attribute_valuetext=The value for the attribute key of the type name AWS:WindowsRole.
windows_update_attribute_keytext=The attribute key of the type name AWS:WindowsUpdate.
windows_update_attribute_valuetext=The value for the attribute key of the type name AWS:WindowsUpdate.

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_inventory