turbot/aws

GitHub
steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_account_alternate_contactaws_account_contactaws_acm_certificateaws_amplify_appaws_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_routeaws_api_gatewayv2_stageaws_appautoscaling_targetaws_appconfig_applicationaws_appstream_imageaws_athena_query_executionaws_athena_workgroupaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_frameworkaws_backup_legal_holdaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudformation_stack_resourceaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_functionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudfront_response_headers_policyaws_cloudsearch_domainaws_cloudtrail_channelaws_cloudtrail_event_data_storeaws_cloudtrail_importaws_cloudtrail_queryaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_cloudwatch_log_subscription_filteraws_cloudwatch_metricaws_cloudwatch_metric_data_pointaws_cloudwatch_metric_statistic_data_pointaws_codeartifact_domainaws_codeartifact_repositoryaws_codebuild_buildaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codedeploy_appaws_codedeploy_deployment_configaws_codedeploy_deployment_groupaws_codepipeline_pipelineaws_config_aggregate_authorizationaws_config_configuration_recorderaws_config_conformance_packaws_config_retention_configurationaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_record_type_dailyaws_cost_by_record_type_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_by_tagaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dax_parameteraws_dax_parameter_groupaws_dax_subnet_groupaws_directory_service_directoryaws_dlm_lifecycle_policyaws_dms_replication_instanceaws_docdb_clusteraws_docdb_cluster_instanceaws_drs_jobaws_drs_recovery_instanceaws_drs_recovery_snapshotaws_drs_source_serveraws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_dynamodb_table_exportaws_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_client_vpn_endpointaws_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_launch_templateaws_ec2_launch_template_versionaws_ec2_load_balancer_listeneraws_ec2_managed_prefix_listaws_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_spot_priceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_imageaws_ecr_image_scan_findingaws_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_fargate_profileaws_eks_identity_provider_configaws_eks_node_groupaws_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_dailyaws_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_reserved_cache_nodeaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_block_public_access_configurationaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instanceaws_emr_instance_fleetaws_emr_instance_groupaws_eventbridge_busaws_eventbridge_ruleaws_fsx_file_systemaws_glacier_vaultaws_globalaccelerator_acceleratoraws_globalaccelerator_endpoint_groupaws_globalaccelerator_listeneraws_glue_catalog_databaseaws_glue_catalog_tableaws_glue_connectionaws_glue_crawleraws_glue_data_catalog_encryption_settingsaws_glue_data_quality_rulesetaws_glue_dev_endpointaws_glue_jobaws_glue_security_configurationaws_guardduty_detectoraws_guardduty_filteraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_memberaws_guardduty_publishing_destinationaws_guardduty_threat_intel_setaws_health_affected_entityaws_health_eventaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_attachmentaws_iam_policy_simulatoraws_iam_roleaws_iam_saml_provideraws_iam_server_certificateaws_iam_service_specific_credentialaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_useraws_inspector2_coverageaws_inspector2_coverage_statisticsaws_inspector2_findingaws_inspector2_memberaws_inspector_assessment_runaws_inspector_assessment_targetaws_inspector_assessment_templateaws_inspector_exclusionaws_inspector_findingaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_aliasaws_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_lightsail_instanceaws_macie2_classification_jobaws_media_store_containeraws_mgn_applicationaws_msk_clusteraws_msk_serverless_clusteraws_neptune_db_clusteraws_networkfirewall_firewallaws_networkfirewall_firewall_policyaws_networkfirewall_rule_groupaws_oam_linkaws_oam_sinkaws_opensearch_domainaws_organizations_accountaws_organizations_policyaws_pinpoint_appaws_pipes_pipeaws_pricing_productaws_pricing_service_attributeaws_ram_principal_associationaws_ram_resource_associationaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_rds_db_instanceaws_rds_db_instance_automated_backupaws_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_proxyaws_rds_db_snapshotaws_rds_db_subnet_groupaws_rds_reserved_db_instanceaws_redshift_clusteraws_redshift_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_redshiftserverless_namespaceaws_redshiftserverless_workgroupaws_regionaws_resource_explorer_indexaws_resource_explorer_searchaws_resource_explorer_supported_resource_typeaws_route53_domainaws_route53_health_checkaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_traffic_policyaws_route53_traffic_policy_instanceaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_s3_multi_region_access_pointaws_s3_objectaws_sagemaker_appaws_sagemaker_domainaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_action_targetaws_securityhub_findingaws_securityhub_finding_aggregatoraws_securityhub_hubaws_securityhub_insightaws_securityhub_memberaws_securityhub_productaws_securityhub_standards_controlaws_securityhub_standards_subscriptionaws_securitylake_data_lakeaws_securitylake_subscriberaws_serverlessapplicationrepository_applicationaws_service_discovery_instanceaws_service_discovery_namespaceaws_service_discovery_serviceaws_servicecatalog_portfolioaws_servicecatalog_productaws_servicequotas_default_service_quotaaws_servicequotas_service_quotaaws_servicequotas_service_quota_change_requestaws_ses_domain_identityaws_ses_email_identityaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_simspaceweaver_simulationaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_document_permissionaws_ssm_inventoryaws_ssm_inventory_entryaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_managed_instance_patch_stateaws_ssm_parameteraws_ssm_patch_baselineaws_ssoadmin_account_assignmentaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_sts_caller_identityaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_eip_address_transferaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_peering_connectionaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_verified_access_endpointaws_vpc_verified_access_groupaws_vpc_verified_access_instanceaws_vpc_verified_access_trust_provideraws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_waf_rule_groupaws_waf_web_aclaws_wafregional_ruleaws_wafregional_rule_groupaws_wafregional_web_aclaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_answeraws_wellarchitected_check_detailaws_wellarchitected_check_summaryaws_wellarchitected_consolidated_reportaws_wellarchitected_lensaws_wellarchitected_lens_reviewaws_wellarchitected_lens_review_improvementaws_wellarchitected_lens_review_reportaws_wellarchitected_lens_shareaws_wellarchitected_milestoneaws_wellarchitected_notificationaws_wellarchitected_share_invitationaws_wellarchitected_workloadaws_wellarchitected_workload_shareaws_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';

Get maintenance options for each instance

select
instance_id,
instance_state,
launch_time,
maintenance_options ->> 'AutoRecovery' as auto_recovery
from
aws_ec2_instance;

Get license details for each instance

select
instance_id,
instance_type,
instance_state,
l ->> 'LicenseConfigurationArn' as license_configuration_arn
from
aws_ec2_instance,
jsonb_array_elements(licenses) as l;

Get placement group details for each 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).

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

Get launch template data for the instances

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;

Query examples

Control examples

.inspect aws_ec2_instance

AWS EC2 Instance

NameTypeDescription
_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_specificationtextInformation 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.
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_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_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_affinitytextThe affinity setting for the instance on the Dedicated Host.
placement_availability_zonetextThe Availability Zone of the instance.
placement_group_idtextThe ID of the placement group that the instance is in.
placement_group_nametextThe 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_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.
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_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.
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_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.
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_typetextThe virtualization type of the instance.
vpc_idtextThe ID of the VPC in which the instance is running.