steampipe plugin install aws

Table: aws_ec2_ami - Query AWS EC2 AMI using SQL

The AWS EC2 AMI (Amazon Machine Image) provides the information necessary to launch an instance, which is a virtual server in the cloud. You specify an AMI when you launch an instance, and you can launch as many instances from the AMI as you need. AMIs are designed to provide a stable, secure, and high performance execution environment for applications running on Amazon EC2.

Table Usage Guide

The aws_ec2_ami table in Steampipe provides you with information about AMIs (Amazon Machine Images) within Amazon Elastic Compute Cloud (Amazon EC2). This table allows you, as a DevOps engineer, system administrator, or other technical professional, to query AMI-specific details, including its attributes, block device mappings, and associated tags. You can utilize this table to gather insights on AMIs, such as identifying unused or outdated AMIs, verifying AMI permissions, and more. The schema outlines the various attributes of the AMI for you, including the AMI ID, creation date, owner, and visibility status.

Important Notes

  • The aws_ec2_ami table only lists images in your account. To list other images shared with you, please use the aws_ec2_ami_shared table.

Examples

Basic info

Explore the different Amazon Machine Images (AMIs) in your AWS EC2 environment to understand their status, location, creation date, visibility, and root device. This is useful for auditing your resources, ensuring security compliance, and managing your infrastructure.

select
name,
image_id,
state,
image_location,
creation_date,
public,
root_device_name
from
aws_ec2_ami;
select
name,
image_id,
state,
image_location,
creation_date,
public,
root_device_name
from
aws_ec2_ami;

List public AMIs

Discover the segments that contain public Amazon Machine Images (AMIs) to help manage and maintain your AWS resources more effectively.

select
name,
image_id,
public
from
aws_ec2_ami
where
public;
select
name,
image_id,
public
from
aws_ec2_ami
where
public = 1;

List failed AMIs

Determine the areas in which Amazon Machine Images (AMIs) have failed. This can be useful for troubleshooting and identifying potential issues within your AWS EC2 instances.

select
name,
image_id,
public,
state
from
aws_ec2_ami
where
state = 'failed';
select
name,
image_id,
public,
state
from
aws_ec2_ami
where
state = 'failed';

Get volume info for each AMI

Explore the characteristics of each Amazon Machine Image (AMI), such as volume size and type, encryption status, and deletion policy. This information is vital for managing storage resources efficiently and ensuring data security within your AWS EC2 environment.

select
name,
image_id,
mapping -> 'Ebs' ->> 'VolumeSize' as volume_size,
mapping -> 'Ebs' ->> 'VolumeType' as volume_type,
mapping -> 'Ebs' ->> 'Encrypted' as encryption_status,
mapping -> 'Ebs' ->> 'KmsKeyId' as kms_key,
mapping -> 'Ebs' ->> 'DeleteOnTermination' as delete_on_termination
from
aws_ec2_ami
cross join jsonb_array_elements(block_device_mappings) as mapping;
select
name,
image_id,
json_extract(mapping.value, '$.Ebs.VolumeSize') as volume_size,
json_extract(mapping.value, '$.Ebs.VolumeType') as volume_type,
json_extract(mapping.value, '$.Ebs.Encrypted') as encryption_status,
json_extract(mapping.value, '$.Ebs.KmsKeyId') as kms_key,
json_extract(mapping.value, '$.Ebs.DeleteOnTermination') as delete_on_termination
from
aws_ec2_ami,
json_each(block_device_mappings) as mapping;

Schema for aws_ec2_ami

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.
architecturetext=The architecture of the image.
block_device_mappingsjsonbAny block device mapping entries.
creation_datetimestamp with time zoneThe date and time when the image was created.
descriptiontext=The description of the AMI that was provided during image creation.
ena_supportboolean=, !=Specifies whether enhanced networking with ENA is enabled.
hypervisortext=The hypervisor type of the image.
image_idtext=The ID of the AMI.
image_locationtextThe location of the AMI.
image_owner_aliastextThe AWS account alias (for example, amazon, self) or the AWS account ID of the AMI owner.
image_typetext=The type of image.
imds_supporttextIf v2.0, it indicates that IMDSv2 is specified in the AMI.
kernel_idtext=The kernel associated with the image, if any. Only applicable for machine images.
launch_permissionsjsonbThe users and groups that have the permissions for creating instances from the AMI.
nametext=The name of the AMI that was provided during image creation.
owner_idtextThe AWS account ID of the image owner.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
platformtext=This value is set to windows for Windows AMIs; otherwise, it is blank.
platform_detailstextThe platform details associated with the billing code of the AMI. For more information, see Obtaining Billing Information (https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ami-billing-info.html) in the Amazon Elastic Compute Cloud User Guide.
product_codesjsonbAny product codes associated with the AMI.
publicboolean=, !=Indicates whether the image has public launch permissions. The value is true if this image has public launch permissions or false if it has only implicit and explicit launch permissions.
ramdisk_idtext=The RAM disk associated with the image, if any. Only applicable for machine images.
regiontextThe AWS Region in which the resource is located.
root_device_nametext=The device name of the root device volume (for example, /dev/sda1).
root_device_typetext=The type of root device used by the AMI. The AMI can use an EBS volume or an instance store volume.
sriov_net_supporttext=Specifies whether enhanced networking with the Intel 82599 Virtual Function interface is enabled.
statetext=The current state of the AMI. If the state is available, the image is successfully registered and can be used to launch an instance.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags attached to the AMI.
titletextTitle of the resource.
usage_operationtextThe operation of the Amazon EC2 instance and the billing code that is associated with the AMI. For the list of UsageOperation codes, see Platform Details and [Usage Operation Billing Codes](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ami-billing-info.html#billing-info) in the Amazon Elastic Compute Cloud User Guide.
virtualization_typetext=The type of virtualization of the AMI.

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_ec2_ami