steampipe plugin install aws

Table: aws_ec2_instance - Query AWS EC2 Instances using SQL

The AWS EC2 Instance is a virtual server in Amazon's Elastic Compute Cloud (EC2) for running applications on the Amazon Web Services (AWS) infrastructure. It provides scalable computing capacity in the AWS cloud, eliminating the need to invest in hardware up front, so you can develop and deploy applications faster. With EC2, you can launch as many or as few virtual servers as you need, configure security and networking, and manage storage.

Table Usage Guide

The aws_ec2_instance table in Steampipe provides you with information about EC2 Instances within AWS Elastic Compute Cloud (EC2). This table allows you, as a DevOps engineer, to query instance-specific details, including instance state, launch time, instance type, and associated metadata. You can utilize this table to gather insights on instances, such as instances with specific tags, instances in a specific state, instances of a specific type, and more. The schema outlines the various attributes of the EC2 instance for you, including the instance ID, instance state, instance type, and associated tags.

Examples

Instance count in each availability zone

Discover the distribution of instances across different availability zones and types within your AWS EC2 service. This helps in understanding load balancing and can aid in optimizing resource utilization.

select
placement_availability_zone as az,
instance_type,
count(*)
from
aws_ec2_instance
group by
placement_availability_zone,
instance_type;
select
placement_availability_zone as az,
instance_type,
count(*)
from
aws_ec2_instance
group by
placement_availability_zone,
instance_type;

List instances whose detailed monitoring is not enabled

Determine the areas in which detailed monitoring is not enabled for your AWS EC2 instances. This is useful for identifying potential blind spots in your system's monitoring coverage.

select
instance_id,
monitoring_state
from
aws_ec2_instance
where
monitoring_state = 'disabled';
select
instance_id,
monitoring_state
from
aws_ec2_instance
where
monitoring_state = 'disabled';

Count the number of instances by instance type

Determine the distribution of your virtual servers based on their configurations, allowing you to assess your resource allocation and optimize your infrastructure management strategy.

select
instance_type,
count(instance_type) as count
from
aws_ec2_instance
group by
instance_type;
select
instance_type,
count(instance_type) as count
from
aws_ec2_instance
group by
instance_type;

List instances stopped for more than 30 days

Determine the areas in which AWS EC2 instances have been stopped for over 30 days. This can be useful for identifying and managing instances that may be unnecessarily consuming resources or costing money.

select
instance_id,
instance_state,
launch_time,
state_transition_time
from
aws_ec2_instance
where
instance_state = 'stopped'
and state_transition_time <= (current_date - interval '30' day);
select
instance_id,
instance_state,
launch_time,
state_transition_time
from
aws_ec2_instance
where
instance_state = 'stopped'
and state_transition_time <= date('now', '-30 day');

List of instances without application tag key

Determine the areas in which EC2 instances are lacking the 'application' tag. This is useful to identify instances that may not be following your organization's tagging strategy, ensuring better resource management and cost tracking.

select
instance_id,
tags
from
aws_ec2_instance
where
not tags :: JSONB ? 'application';
select
instance_id,
tags
from
aws_ec2_instance
where
json_extract(tags, '$.application') is null;

Get maintenance options for each instance

Determine the status of each instance's automatic recovery feature to plan for potential maintenance needs. This can help in understanding the instances' resilience and ensure uninterrupted services.

select
instance_id,
instance_state,
launch_time,
maintenance_options ->> 'AutoRecovery' as auto_recovery
from
aws_ec2_instance;
select
instance_id,
instance_state,
launch_time,
json_extract(maintenance_options, '$.AutoRecovery') as auto_recovery
from
aws_ec2_instance;

Get license details for each instance

Determine the license details associated with each of your instances to better manage and track your licensing agreements. This can help ensure compliance and avoid potential legal issues.

select
instance_id,
instance_type,
instance_state,
l ->> 'LicenseConfigurationArn' as license_configuration_arn
from
aws_ec2_instance,
jsonb_array_elements(licenses) as l;
select
instance_id,
instance_type,
instance_state,
json_extract(l.value, '$.LicenseConfigurationArn') as license_configuration_arn
from
aws_ec2_instance,
json_each(licenses) as l;

Get placement group details for each instance

This query can be used to gain insights into the geographic distribution and configuration of your AWS EC2 instances. It helps in managing resources efficiently by understanding their placement details such as affinity, availability zone, and tenancy.

select
instance_id,
instance_state,
placement_affinity,
placement_group_id,
placement_group_name,
placement_availability_zone,
placement_host_id,
placement_host_resource_group_arn,
placement_partition_number,
placement_tenancy
from
aws_ec2_instance;
select
instance_id,
instance_state,
placement_affinity,
placement_group_id,
placement_group_name,
placement_availability_zone,
placement_host_id,
placement_host_resource_group_arn,
placement_partition_number,
placement_tenancy
from
aws_ec2_instance;

List of EC2 instances provisioned with undesired(for example t2.large and m3.medium is desired) instance type(s).

Identify instances where EC2 instances have been provisioned with types other than the desired ones, such as t2.large and m3.medium. This can help you manage your resources more effectively by spotting any instances that may not meet your specific needs or standards.

select
instance_type,
count(*) as count
from
aws_ec2_instance
where
instance_type not in ('t2.large', 'm3.medium')
group by
instance_type;
select
instance_type,
count(*) as count
from
aws_ec2_instance
where
instance_type not in ('t2.large', 'm3.medium')
group by
instance_type;

List EC2 instances having termination protection safety feature enabled

Identify instances where the termination protection safety feature is enabled in EC2 instances. This is beneficial for preventing accidental terminations and ensuring system stability.

select
instance_id,
disable_api_termination
from
aws_ec2_instance
where
not disable_api_termination;
select
instance_id,
disable_api_termination
from
aws_ec2_instance
where
disable_api_termination = 0;

Find instances which have default security group attached

Discover the segments that have the default security group attached to them in order to identify potential security risks. This is useful for maintaining optimal security practices and ensuring that instances are not using default settings, which may be more vulnerable.

select
instance_id,
sg ->> 'GroupId' as group_id,
sg ->> 'GroupName' as group_name
from
aws_ec2_instance
cross join jsonb_array_elements(security_groups) as sg
where
sg ->> 'GroupName' = 'default';
select
instance_id,
json_extract(sg.value, '$.GroupId') as group_id,
json_extract(sg.value, '$.GroupName') as group_name
from
aws_ec2_instance,
json_each(aws_ec2_instance.security_groups) as sg
where
json_extract(sg.value, '$.GroupName') = 'default';

List the unencrypted volumes attached to the instances

Identify instances where data storage volumes attached to cloud-based virtual servers are not encrypted. This is useful for enhancing security measures by locating potential vulnerabilities where sensitive data might be exposed.

select
i.instance_id,
vols -> 'Ebs' ->> 'VolumeId' as vol_id,
vol.encrypted
from
aws_ec2_instance as i
cross join jsonb_array_elements(block_device_mappings) as vols
join aws_ebs_volume as vol on vol.volume_id = vols -> 'Ebs' ->> 'VolumeId'
where
not vol.encrypted;
select
i.instance_id,
json_extract(vols.value, '$.Ebs.VolumeId') as vol_id,
vol.encrypted
from
aws_ec2_instance as i,
json_each(i.block_device_mappings) as vols
join aws_ebs_volume as vol on vol.volume_id = json_extract(vols.value, '$.Ebs.VolumeId')
where
not vol.encrypted;

List instances with secrets in user data

Discover the instances that might contain sensitive information in their user data. This is beneficial in identifying potential security risks and ensuring data privacy compliance.

select
instance_id,
user_data
from
aws_ec2_instance
where
user_data like any (array [ '%pass%', '%secret%', '%token%', '%key%' ])
or user_data ~ '(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]';
select
instance_id,
user_data
from
aws_ec2_instance
where
user_data like '%pass%'
or user_data like '%secret%'
or user_data like '%token%'
or user_data like '%key%'
or (
user_data REGEXP '[a-z]'
and user_data REGEXP '[A-Z]'
and user_data REGEXP '\d'
and user_data REGEXP '[@$!%*?&]'
);

Get launch template data for the instances

Analyze the settings to understand the configuration and specifications of your cloud instances. This can help you assess the elements within your instances, such as network interfaces and capacity reservation specifications, which can be useful for optimizing resource usage and management.

select
instance_id,
launch_template_data -> 'ImageId' as image_id,
launch_template_data -> 'Placement' as placement,
launch_template_data -> 'DisableApiStop' as disable_api_stop,
launch_template_data -> 'MetadataOptions' as metadata_options,
launch_template_data -> 'NetworkInterfaces' as network_interfaces,
launch_template_data -> 'BlockDeviceMappings' as block_device_mappings,
launch_template_data -> 'CapacityReservationSpecification' as capacity_reservation_specification
from
aws_ec2_instance;
select
instance_id,
json_extract(launch_template_data, '$.ImageId') as image_id,
json_extract(launch_template_data, '$.Placement') as placement,
json_extract(launch_template_data, '$.DisableApiStop') as disable_api_stop,
json_extract(launch_template_data, '$.MetadataOptions') as metadata_options,
json_extract(launch_template_data, '$.NetworkInterfaces') as network_interfaces,
json_extract(launch_template_data, '$.BlockDeviceMappings') as block_device_mappings,
json_extract(
launch_template_data,
'$.CapacityReservationSpecification'
) as capacity_reservation_specification
from
aws_ec2_instance;

Get subnet details for each instance

Explore the association between instances and subnets in your AWS environment. This can be helpful in understanding how resources are distributed and for planning infrastructure changes or improvements.

select
i.instance_id,
i.vpc_id,
i.subnet_id,
s.tags ->> 'Name' as subnet_name
from
aws_ec2_instance as i,
aws_vpc_subnet as s
where
i.subnet_id = s.subnet_id;
select
i.instance_id,
i.vpc_id,
i.subnet_id,
json_extract(s.tags, '$.Name') as subnet_name
from
aws_ec2_instance as i,
aws_vpc_subnet as s
where
i.subnet_id = s.subnet_id;

Schema for aws_ec2_instance

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.
ami_launch_indexbigintThe AMI launch index, which can be used to find this instance in the launch group.
architecturetextThe architecture of the image.
arntextThe Amazon Resource Name (ARN) specifying the instance.
block_device_mappingsjsonbBlock device mapping entries for the instance.
boot_modetextThe boot mode of the instance.
capacity_reservation_idtextThe ID of the Capacity Reservation.
capacity_reservation_specificationjsonbInformation about the Capacity Reservation targeting option.
client_tokentextThe idempotency token you provided when you launched the instance, if applicable.
cpu_options_core_countbigintThe number of CPU cores for the instance.
cpu_options_threads_per_corebigintThe number of threads per CPU core.
disable_api_terminationbooleanIf the value is true, instance can't be terminated through the Amazon EC2 console, CLI, or API.
ebs_optimizedbooleanIndicates whether the instance is optimized for Amazon EBS I/O. This optimization provides dedicated throughput to Amazon EBS and an optimized configuration stack to provide optimal I/O performance. This optimization isn't available with all instance types.
elastic_gpu_associationsjsonbThe Elastic GPU associated with the instance.
elastic_inference_accelerator_associationsjsonbThe elastic inference accelerator associated with the instance.
ena_supportbooleanSpecifies whether enhanced networking with ENA is enabled.
enclave_optionsjsonbIndicates whether the instance is enabled for Amazon Web Services Nitro Enclaves.
hibernation_optionsjsonbIndicates whether the instance is enabled for hibernation.
hypervisortext=The hypervisor type of the instance. The value xen is used for both Xen and Nitro hypervisors.
iam_instance_profile_arntext=The Amazon Resource Name (ARN) of IAM instance profile associated with the instance, if applicable.
iam_instance_profile_idtextThe ID of the instance profile associated with the instance, if applicable.
image_idtext=The ID of the AMI used to launch the instance.
instance_idtext=The ID of the instance.
instance_initiated_shutdown_behaviortextIndicates whether an instance stops or terminates when you initiate shutdown from the instance (using the operating system command for system shutdown).
instance_lifecycletext=Indicates whether this is a spot instance or a scheduled instance.
instance_statetext=The state of the instance (pending | running | shutting-down | terminated | stopping | stopped).
instance_statusjsonbThe status of an instance. Instance status includes scheduled events, status checks and instance state information.
instance_typetext=The instance type.
kernel_idtextThe kernel ID
key_nametextThe name of the key pair, if this instance was launched with an associated key pair.
launch_template_datajsonbThe configuration data of the specified instance.
launch_timetimestamp with time zoneThe time the instance was launched.
licensesjsonbThe license configurations for the instance.
maintenance_optionsjsonbThe metadata options for the instance.
metadata_optionsjsonbThe metadata options for the instance.
monitoring_statetext=Indicates whether detailed monitoring is enabled (disabled | enabled).
network_interfacesjsonbThe network interfaces for the instance.
outpost_arntext=The Amazon Resource Name (ARN) of the Outpost, if applicable.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
placement_affinitytextThe affinity setting for the instance on the Dedicated Host.
placement_availability_zonetext=The Availability Zone of the instance.
placement_group_idtextThe ID of the placement group that the instance is in.
placement_group_nametext=The name of the placement group the instance is in.
placement_host_idtextThe ID of the Dedicated Host on which the instance resides.
placement_host_resource_group_arntextThe ARN of the host resource group in which to launch the instances.
placement_partition_numberbigintThe ARN of the host resource group in which to launch the instances.
placement_tenancytext=The tenancy of the instance (if the instance is running in a VPC). An instance with a tenancy of dedicated runs on single-tenant hardware.
platformtextThe value is 'Windows' for Windows instances; otherwise blank.
platform_detailstextThe platform details value for the instance.
private_dns_nametextThe private DNS hostname name assigned to the instance. This DNS hostname can only be used inside the Amazon EC2 network. This name is not available until the instance enters the running state.
private_dns_name_optionsjsonbThe options for the instance hostname.
private_ip_addressinetThe private IPv4 address assigned to the instance.
product_codesjsonbThe product codes attached to this instance, if applicable.
public_dns_nametext=The public DNS name assigned to the instance. This name is not available until the instance enters the running state.
public_ip_addressinetThe public IPv4 address, or the Carrier IP address assigned to the instance, if applicable.
ram_disk_idtext=The RAM disk ID.
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 root device type used by the AMI. The AMI can use an EBS volume or an instance store volume.
security_groupsjsonbThe security groups for the instance.
source_dest_checkbooleanSpecifies whether to enable an instance launched in a VPC to perform NAT. This controls whether source/destination checking is enabled on the instance.
spot_instance_request_idtextIf the request is a Spot Instance request, the ID of the request.
sriov_net_supporttextIndicates whether enhanced networking with the Intel 82599 Virtual Function interface is enabled.
state_codebigintThe reason code for the state change.
state_transition_reasontextThe reason for the most recent state transition.
state_transition_timetimestamp with time zoneThe date and time, the instance state was last modified.
subnet_idtext=The ID of the subnet in which the instance is running.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags assigned to the instance.
titletextTitle of the resource.
tpm_supporttextIf the instance is configured for NitroTPM support, the value is v2.0.
usage_operationtextThe usage operation value for the instance.
usage_operation_update_timetextThe time that the usage operation was last updated.
user_datatextThe user data of the instance.
virtualization_typetext=The virtualization type of the instance.
vpc_idtext=The ID of the VPC in which the instance is running.

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_instance