steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_acm_certificateaws_api_gateway_api_authorizeraws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_stageaws_appautoscaling_targetaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codepipeline_pipelineaws_config_configuration_recorderaws_config_conformance_packaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_directory_service_directoryaws_dms_replication_instanceaws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_application_load_balancer_metric_request_countaws_ec2_application_load_balancer_metric_request_count_dailyaws_ec2_autoscaling_groupaws_ec2_capacity_reservationaws_ec2_classic_load_balanceraws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_load_balancer_listeneraws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_network_load_balancer_metric_net_flow_countaws_ec2_network_load_balancer_metric_net_flow_count_dailyaws_ec2_regional_settingsaws_ec2_reserved_instanceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_cluster_metric_cpu_utilizationaws_ecs_cluster_metric_cpu_utilization_dailyaws_ecs_cluster_metric_cpu_utilization_hourlyaws_ecs_container_instanceaws_ecs_serviceaws_ecs_taskaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_eks_identity_provider_configaws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_redis_metric_cache_hits_hourlyaws_elasticache_redis_metric_curr_connections_hourlyaws_elasticache_redis_metric_engine_cpu_utilization_hourlyaws_elasticache_redis_metric_get_type_cmds_hourlyaws_elasticache_redis_metric_list_based_cmds_hourlyaws_elasticache_redis_metric_new_connections_hourlyaws_elasticache_replication_groupaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instance_groupaws_eventbridge_busaws_eventbridge_ruleaws_fsx_file_systemaws_glacier_vaultaws_glue_catalog_databaseaws_guardduty_detectoraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_threat_intel_setaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_simulatoraws_iam_roleaws_iam_server_certificateaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_useraws_inspector_assessment_targetaws_inspector_assessment_templateaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_keyaws_lambda_aliasaws_lambda_functionaws_lambda_function_metric_duration_dailyaws_lambda_function_metric_errors_dailyaws_lambda_function_metric_invocations_dailyaws_lambda_layeraws_lambda_layer_versionaws_lambda_versionaws_macie2_classification_jobaws_media_store_containeraws_organizations_accountaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_rds_db_instanceaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_snapshotaws_rds_db_subnet_groupaws_redshift_clusteraws_redshift_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_regionaws_route53_domainaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_hubaws_securityhub_productaws_securityhub_standards_subscriptionaws_serverlessapplicationrepository_applicationaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_parameteraws_ssm_patch_baselineaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_workloadaws_workspaces_workspace

Table: aws_ec2_instance

An AWS EC2 instance is a virtual server in the AWS cloud.

Examples

Instance count in each availability zone

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

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

Count the number of instances 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

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);

List of instances without application tag key

select
instance_id,
tags
from
aws_ec2_instance
where
not tags :: JSONB ? 'application';

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

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

select
instance_id,
disable_api_termination
from
aws_ec2_instance
where
not disable_api_termination;

Find instances which have default security group attached

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';

List the unencrypted volumes attached to the instances

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;

List instances with secrets in user data

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@$!%*?&]';

Control examples

.inspect aws_ec2_instance

AWS EC2 Instance

NameTypeDescription
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Amazon Resource Name (ARN) specifying the instance.
block_device_mappingsjsonbBlock device mapping entries for the instance.
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.
hypervisortextThe hypervisor type of the instance. The value xen is used for both Xen and Nitro hypervisors.
iam_instance_profile_arntextThe 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_idtextThe ID of the AMI used to launch the instance.
instance_idtextThe 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_lifecycletextIndicates whether this is a spot instance or a scheduled instance.
instance_statetextThe 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_typetextThe instance type.
kernel_idtextThe kernel ID
key_nametextThe name of the key pair, if this instance was launched with an associated key pair.
launch_timetimestamp without time zoneThe time the instance was launched.
metadata_optionsjsonbThe metadata options for the instance.
monitoring_statetextIndicates whether detailed monitoring is enabled (disabled | enabled).
network_interfacesjsonbThe network interfaces for the instance.
outpost_arntextThe 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_availability_zonetextThe Availability Zone of the instance.
placement_group_nametextThe name of the placement group the instance is in.
placement_tenancytextThe tenancy of the instance (if the instance is running in a VPC). An instance with a tenancy of dedicated runs on single-tenant hardware.
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_ip_addressinetThe private IPv4 address assigned to the instance.
product_codesjsonbThe product codes attached to this instance, if applicable.
public_dns_nametextThe 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_idtextThe RAM disk ID.
regiontextThe AWS Region in which the resource is located.
root_device_nametextThe device name of the root device volume (for example, /dev/sda1).
root_device_typetextThe 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.
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_timetimestamp without time zoneThe date and time, the instance state was last modified.
subnet_idtextThe 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.
user_datatextThe user data of the instance.
virtualization_typetextThe virtualization type of the instance.
vpc_idtextThe ID of the VPC in which the instance is running.