AWS + Steampipe

https://hub.steampipe.io/plugins/turbot/aws
Admin
AWS + Steampipe is an open-source plugin that integrates Amazon Web Services APIs with the Steampipe...
Tokens:1,126,725
Snippets:14,498
Trust Score:9.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Get installed application names and versions

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_emr_cluster

Track software versions across clusters to ensure environment consistency.

```sql
select
  name,
  cluster_arn,
  a ->> 'Name' as application_name,
  a ->> 'Version' as application_version
from
  aws_emr_cluster,
  jsonb_array_elements(applications) as a;
```

```sql
select
  name,
  cluster_arn,
  json_extract(a.value, '$.Name') as application_name,
  json_extract(a.value, '$.Version') as application_version
from
  aws_emr_cluster,
  json_each(applications) as a;
```

--------------------------------

### CloudFormation Mappings Example

Source: https://hub.steampipe.io/plugins/turbot/awscfn/tables/awscfn_mapping

Example Mappings section used for the queries in this documentation.

```yaml

Mappings:
  RegionMap:
    us-east-1:
      "HVM64": "ami-0ff8a91507f77f867"
    us-west-1:
      "HVM64": "ami-0bdb828fd58c52235"
    eu-west-1:
      "HVM64": "ami-047bb4163c506cd98"
    ap-southeast-1:
      "HVM64": "ami-08569b978cc4dfa10"
    ap-northeast-1:
      "HVM64": "ami-06cd52961ce9f0d85"

```

--------------------------------

### Query Output Example

Source: https://hub.steampipe.io/plugins/turbot/awscfn/queries/aws_cloud_formation/example_get_s3_bucket_bucketname_property_value

Sample output showing the extracted resource name, type, source property, and default value.

```text
+---------------+-----------------+--------------------------+----------------+| resource_name | resource_type   | bucket_name_src          | bucket_name    |+---------------+-----------------+--------------------------+----------------+| DevBucket     | AWS::S3::Bucket | {"Ref": "WebBucketName"} | TestWebBucket  |+---------------+-----------------+--------------------------+----------------+
```

--------------------------------

### List all budgets

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_budgets_budget

Retrieve all budgets in your AWS account to get an overview of your cost management setup.

```sql

select
  name,
  type,
  limit_amount,
  time_unit
from
  aws_budgets_budget;

```

--------------------------------

### Retrieve basic launch configuration details

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_launch_configuration

Fetches core configuration attributes such as image ID, instance type, and monitoring status.

```sql

select
  name,
  created_time,
  associate_public_ip_address,
  ebs_optimized,
  image_id,
  instance_monitoring_enabled,
  instance_type,
  key_name
from
  aws_ec2_launch_configuration;

```

```sql

select
  name,
  created_time,
  associate_public_ip_address,
  ebs_optimized,
  image_id,
  instance_monitoring_enabled,
  instance_type,
  key_name
from
  aws_ec2_launch_configuration;

```

--------------------------------

### Get instance configuration summary

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_connect_instance

Retrieves a summary of Connect instance configurations including status, creation time, and service role.

```SQL

select
  instance_alias,
  id,
  instance_status,
  created_time,
  service_role,
  identity_management_type
from
  aws_connect_instance
order by
  created_time desc;

```

--------------------------------

### Get quiet time for application

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/pinpoint/example_get_quiet_time_for_application

Extracts quiet time start and end values from the Pinpoint application configuration.

```Steampipe CLI / Postgres FDW
select
  id,
  quiet_time -> 'Start' as start_time,
  quiet_time -> 'End' as end_time
from
  aws_pinpoint_app;
```

```SQLite Extension
select
  id,
  json_extract(quiet_time, '$.Start') as start_time,
  json_extract(quiet_time, '$.End') as end_time
from
  aws_pinpoint_app;
```

--------------------------------

### List details of successfully provisioned products with product info

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_servicecatalog_provisioned_product

Joins provisioned products with product metadata to provide a consolidated view of support information.

```sql

select
  pr.id as provisioning_id,
  p.name as product_name,
  p.id as product_view_id,
  p.product_id,
  p.type as product_type,
  p.support_url as product_support_url,
  p.support_email as product_support_email
from
  aws_servicecatalog_provisioned_product as pr,
  aws_servicecatalog_product as p
where
  pr.product_id = p.product_id
  and last_successful_provisioning_record_id is not null;

```

```sql

select
  pr.id as provisioning_id,
  p.name as product_name,
  p.id as product_view_id,
  p.product_id,
  p.type as product_type,
  p.support_url as product_support_url,
  p.support_email as product_support_email
from
  aws_servicecatalog_provisioned_product as pr
join
  aws_servicecatalog_product as p on pr.product_id = p.product_id
where
  pr.last_successful_provisioning_record_id is not null;

```

--------------------------------

### Get shared directory details using SQL

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/directory_service/example_get_details_about_the_shared_directories

Use these queries to retrieve shared directory information. The first example is optimized for Steampipe CLI and Postgres FDW, while the second is tailored for SQLite.

```sql
select
  name,
  directory_id,
  sd ->> 'ShareMethod' share_method,
  sd ->> 'ShareStatus' share_status,
  sd ->> 'SharedAccountId' shared_account_id,
  sd ->> 'SharedDirectoryId' shared_directory_id
from
  aws_directory_service_directory,
  jsonb_array_elements(shared_directories) sd;
```

```sql
select
  name,
  directory_id,
  json_extract(sd.value, '$.ShareMethod') as share_method,
  json_extract(sd.value, '$.ShareStatus') as share_status,
  json_extract(sd.value, '$.SharedAccountId') as shared_account_id,
  json_extract(sd.value, '$.SharedDirectoryId') as shared_directory_id
from
  aws_directory_service_directory
  join json_each(shared_directories) as sd;
```

--------------------------------

### Get a specific Route 53 Resolver endpoint

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/route_53/example_get_a_specific_endpoint

Use this query to fetch details for a specific endpoint by providing its ID. Note that the first example contains a potential column alias error in the select list.

```sql
select
  name,
  id,
  direction,
  ip_address_count status
from
  aws_route53_resolver_endpoint
where
  id = 'rslvr-out-ebb7db0b7498463eb';
```

```sql
select
  name,
  id,
  direction,
  ip_address_count,
  status
from
  aws_route53_resolver_endpoint
where
  id = 'rslvr-out-ebb7db0b7498463eb';
```

--------------------------------

### List agents with foundation models and instructions

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_bedrock_agent

Analyze agent behavior and underlying model configurations.

```sql

select
  agent_name,
  foundation_model,
  instruction,
  agent_status
from
  aws_bedrock_agent;

```

--------------------------------

### Get SAML properties for a WorkSpaces directory

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/work_spaces/example_get_the_saml_properties_of_a_particular_directory

Use these queries to inspect SAML configuration settings for a specific directory. The first example uses standard PostgreSQL JSON operators, while the second uses SQLite-compatible JSON functions.

```SQL
select
  name,
  directory_id,
  arn,
  saml_properties ->> 'RelayStateParameterName' as saml_relay_state_parameter_name,
  saml_properties ->> 'Status' as saml_status,
  saml_properties ->> 'UserAccessUrl' as saml_user_access_url
from
  aws_workspaces_directory
where
  directory_id = 'd-96676995ea';
```

```SQL
select
  name,
  directory_id,
  arn,
  json_extract(saml_properties, '$.RelayStateParameterName') as saml_relay_state_parameter_name,
  json_extract(saml_properties, '$.Status') as saml_status,
  json_extract(saml_properties, '$.UserAccessUrl') as saml_user_access_url
from
  aws_workspaces_directory
where
  directory_id = 'd-96676995ea';
```

--------------------------------

### Get launch configuration settings of all source servers

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_drs_source_server

Fetches launch configuration details such as instance type, tags, and private IP settings for all source servers.

```Postgres
select
  arn,
  title,
  launch_configuration ->> 'Name' as launch_configuration_name,
  launch_configuration ->> 'CopyPrivateIp' as launch_configuration_copy_private_ip,
  launch_configuration ->> 'CopyTags' as launch_configuration_copy_tags,
  launch_configuration ->> 'Ec2LaunchTemplateID' as launch_configuration_ec2_launch_template_id,
  launch_configuration ->> 'LaunchDisposition' as launch_configuration_disposition,
  launch_configuration ->> 'TargetInstanceTypeRightSizingMethod' as launch_configuration_target_instance_type_right_sizing_method,
  launch_configuration -> 'Licensing' as launch_configuration_licensing,
  launch_configuration -> 'ResultMetadata' as launch_configuration_result_metadata
from
  aws_drs_source_server;
```

```SQLite
select
  arn,
  title,
  json_extract(launch_configuration, '$.Name') as launch_configuration_name,
  json_extract(launch_configuration, '$.CopyPrivateIp') as launch_configuration_copy_private_ip,
  json_extract(launch_configuration, '$.CopyTags') as launch_configuration_copy_tags,
  json_extract(launch_configuration, '$.Ec2LaunchTemplateID') as launch_configuration_ec2_launch_template_id,
  json_extract(launch_configuration, '$.LaunchDisposition') as launch_configuration_disposition,
  json_extract(launch_configuration, '$.TargetInstanceTypeRightSizingMethod') as launch_configuration_target_instance_type_right_sizing_method,
  json_extract(launch_configuration, '$.Licensing') as launch_configuration_licensing,
  json_extract(launch_configuration, '$.ResultMetadata') as launch_configuration_result_metadata
from
  aws_drs_source_server;
```

--------------------------------

### Install Exporter CLI

Source: https://hub.steampipe.io/plugins/turbot/awscfn/tables/awscfn_mapping

Command to install the awscfn exporter using the installation script.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- awscfn
```

--------------------------------

### Retrieve basic launch template information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_launch_template

Query the name, ID, creation time, creator, and version numbers for all launch templates.

```sql

select
  launch_template_name,
  launch_template_id,
  create_time,
  created_by,
  default_version_number,
  latest_version_number
from
  aws_ec2_launch_template;

```

--------------------------------

### Install AWS Steampipe Plugin

Source: https://hub.steampipe.io/plugins/turbot/aws/mods

Command to install the AWS plugin for Steampipe.

```bash
steampipe plugin install aws
```

--------------------------------

### Get instance configuration summary

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/connect/example_get_instance_configuration_summary

Retrieves instance alias, ID, status, creation time, service role, and identity management type for all Connect instances.

```sql
select
  instance_alias,
  id,
  instance_status,
  created_time,
  service_role,
  identity_management_type
from
  aws_connect_instance
order by
  created_time desc;
```

```sql
select
  instance_alias,
  id,
  instance_status,
  created_time,
  service_role,
  identity_management_type
from
  aws_connect_instance
order by
  created_time desc;
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_rds_db_cluster

Examples of schema fields available in the aws_rds_db_cluster table.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
activity_stream_kinesis_stream_name
```

```text
activity_stream_kms_key_id
```

```text
activity_stream_mode
```

```text
activity_stream_status
```

```text
akas
```

```text
allocated_storage
```

```text
bigint
```

```text
arn
```

```text
associated_roles
```

```text
auto_minor_version_upgrade
```

```text
boolean
```

--------------------------------

### Retrieve basic instance information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_vpc_verified_access_instance

Fetch core details such as instance ID, creation time, and trust providers for all verified access instances.

```sql

select
  verified_access_instance_id,
  creation_time,
  description,
  last_updated_time,
  verified_access_trust_providers
from
  aws_vpc_verified_access_instance;
```

--------------------------------

### Retrieve basic deployment configuration information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_codedeploy_deployment_config

Fetches core details such as ARN, ID, name, compute platform, and creation time for all deployment configurations.

```sql

select
  arn,
  deployment_config_id,
  deployment_config_name,
  compute_platform,
  create_time,
  region
from
  aws_codedeploy_deployment_config;

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_opensearch_domain

Examples of schema fields and their types for the aws_opensearch_domain table.

```text
_ctx
```

```text
jsonb
```

```text
access_policies
```

```text
text
```

```text
account_id
```

```text
=, !=, ~~, ~~*, !~~, !~~*
```

```text
advanced_options
```

```text
advanced_security_options
```

```text
akas
```

```text
arn
```

```text
auto_software_update_enabled
```

```text
boolean
```

```text
auto_tune_options
```

```text
change_progress_details
```

--------------------------------

### Retrieve basic namespace information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_quicksight_namespace

Fetch fundamental details for all QuickSight namespaces.

```SQL
select
  name,
  arn,
  capacity_region,
  creation_status,
  identity_store
from
  aws_quicksight_namespace;
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_eks_fargate_profile

Examples of schema fields used in the aws_eks_fargate_profile table.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
cluster_name
```

```text
created_at
```

```text
timestamp with time zone
```

```text
fargate_profile_arn
```

```text
fargate_profile_name
```

```text
partition
```

```text
pod_execution_role_arn
```

```text
region
```

--------------------------------

### Query Result Output

Source: https://hub.steampipe.io/plugins/turbot/awscfn/queries/aws_cloud_formation/example_list_s3_buckets_with_bucketname_properties_that_reference_a_parameter

Example output table showing the resource name, type, source property, and resolved parameter value.

```text
+---------------+-----------------+--------------------------+----------------+
| resource_name | resource_type   | bucket_name_src          | bucket_name    |
+---------------+-----------------+--------------------------+----------------+
| DevBucket     | AWS::S3::Bucket | {"Ref": "WebBucketName"} | TestWebBucket  |
+---------------+-----------------+--------------------------+----------------+
```

--------------------------------

### Schema field examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_availability_zone

Examples of schema fields used in the table definition.

```text
_ctx
```

```text
jsonb
```

```text
akas
```

```text
jsonb
```

```text
group_name
```

```text
text
```

```text
messages
```

```text
text
```

```text
name
```

```text
text
```

```text
opt_in_status
```

```text
text
```

--------------------------------

### Retrieve basic notebook instance information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_sagemaker_notebook_instance

Fetches core details such as name, ARN, creation time, instance type, and status for all notebook instances.

```sql

select
  name,
  arn,
  creation_time,
  instance_type,
  notebook_instance_status
from
  aws_sagemaker_notebook_instance;

```

--------------------------------

### List launch template versions created in the last 30 days

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_launch_template_version

Filters for recent versions to track configuration changes.

```sql

select
  launch_template_name,
  launch_template_id,
  create_time,
  default_version,
  version_number
from
  aws_ec2_launch_template_version
where
  create_time >= now() - interval '30' day;
```

```sql

select
  launch_template_name,
  launch_template_id,
  create_time,
  default_version,
  version_number
from
  aws_ec2_launch_template_version
where
  create_time >= datetime('now', '-30 day');
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_vpc_endpoint_service

Examples of field names used in the aws_vpc_endpoint_service table schema.

```text
_ctx
```

```text
jsonb
```

```text
acceptance_required
```

```text
boolean
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
availability_zones
```

```text
base_endpoint_dns_names
```

```text
manages_vpc_endpoints
```

```text
owner
```

```text
partition
```

```text
private_dns_name
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_securityhub_member

Examples of schema fields and types used in the aws_securityhub_member table.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
administrator_id
```

```text
email
```

```text
invited_at
```

```text
timestamp with time zone
```

```text
master_id
```

```text
member_account_id
```

```text
member_status
```

```text
partition
```

--------------------------------

### Check early media configuration

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_connect_instance_attribute

Retrieve the configuration status for the early media feature across instances.

```sql

select
  instance_id,
  attribute_type,
  value
from
  aws_connect_instance_attribute
where
  attribute_type = 'EARLY_MEDIA';
```

--------------------------------

### Schema Column Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_redshift_cluster

Examples of column names used in the aws_redshift_cluster table schema.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
akas
```

```boolean
boolean
```

```jsonb
jsonb
```

```text
arn
```

```bigint
bigint
```

```text
availability_zone
```

```text
availability_zone_relocation_status
```

--------------------------------

### List user-managed deployment configurations

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_codedeploy_deployment_config

Filters for configurations that have a defined creation time.

```sql

select
  arn,
  deployment_config_id,
  deployment_config_name
  compute_platform,
  create_time,
  region
from
  aws_codedeploy_deployment_config
where
  create_time is not null;

```

```sql

select
  arn,
  deployment_config_id,
  deployment_config_name,
  compute_platform,
  create_time,
  region
from
  aws_codedeploy_deployment_config
where
  create_time is not null;

```

--------------------------------

### Schema Column Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_rds_db_instance

Examples of column names used in the aws_rds_db_instance table schema.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
text
```

```jsonb
jsonb
```

```bigint
bigint
```

```text
text
```

```jsonb
jsonb
```

```boolean
boolean
```

```text
text
```

```bigint
bigint
```

```text
text
```

--------------------------------

### Retrieve basic instance information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_connect_instance

Fetch core details like alias, ID, ARN, status, and region for all Connect instances.

```sql

select
  instance_alias,
  id,
  arn,
  instance_status,
  identity_management_type,
  region
from
  aws_connect_instance;

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_memorydb_cluster

Examples of schema fields and their types used in the aws_memorydb_cluster table.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
acl_name
```

```text
akas
```

```text
arn
```

```text
auto_minor_version_upgrade
```

```text
boolean
```

```text
availability_mode
```

```text
cluster_endpoint
```

```text
data_tiering
```

```text
description
```

--------------------------------

### Retrieve basic distribution information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_cloudfront_distribution

Fetch core configuration details for all CloudFront distributions.

```SQL

select
  id,
  arn,
  status,
  domain_name,
  enabled,
  e_tag,
  http_version,
  is_ipv6_enabled
from
  aws_cloudfront_distribution;

```

--------------------------------

### Get launch path details of each product

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/service_catalog/example_get_launch_path_details_of_each_product

Retrieves launch path IDs and names for Service Catalog products using JSON array expansion.

```SQL
select
  name,
  id,
  owner,
  short_description,
  l ->> 'Id' as launch_path_id,
  l ->> 'Name' as launch_path_name
from
  aws_servicecatalog_product,
  jsonb_array_elements(launch_paths) as l;
```

```SQL
select
  sp.name,
  sp.id,
  sp.owner,
  short_description,
  json_extract(l.value, '$.Id') as launch_path_id,
  json_extract(l.value, '$.Name') as launch_path_name
from
  aws_servicecatalog_product as sp,
  json_each(aws_servicecatalog_product.launch_paths) as l;
```

--------------------------------

### Schema Column Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_inspector_assessment_template

Examples of column names used in the aws_inspector_assessment_template table schema.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
arn
```

```text
assessment_run_count
```

```text
bigint
```

```text
assessment_target_arn
```

```text
created_at
```

```text
timestamp with time zone
```

```text
duration_in_seconds
```

```text
event_subscriptions
```

```text
last_assessment_run_arn
```

--------------------------------

### List file system location details of builds

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_codebuild_build

Retrieves file system mount points and configuration details for builds.

```sql

select
  id,
  f ->> 'Identifier' as file_system_identifier,
  f ->> 'Location' as file_system_location,
  f ->> 'MountOptions' as file_system_mount_options,
  f ->> 'MountPoint' as file_system_mount_point,
  f ->> 'Type' as file_system_type
from
  aws_codebuild_build,
  jsonb_array_elements(file_system_locations) as f;
```

```sql

select
  aws_codebuild_build.id,
  json_extract(f.value, '$.Identifier') as file_system_identifier,
  json_extract(f.value, '$.Location') as file_system_location,
  json_extract(f.value, '$.MountOptions') as file_system_mount_options,
  json_extract(f.value, '$.MountPoint') as file_system_mount_point,
  json_extract(f.value, '$.Type') as file_system_type
from
  aws_codebuild_build,
  json_each(file_system_locations) as f;
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_fms_policy

Examples of field names used in the aws_fms_policy table schema.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
text
```

```jsonb
akas
```

```text
arn
```

```boolean
boolean
```

```text
delete_unused_fm_managed_resources
```

```jsonb
exclude_map
```

```boolean
exclude_resource_tags
```

```jsonb
include_map
```

```text
partition
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_emr_studio

Examples of schema fields used in the aws_emr_studio table definition.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
text
```

```jsonb
akas
```

```text
arn
```

```text
auth_mode
```

```timestamp with time zone
timestamp with time zone
```

```text
creation_time
```

```text
default_s3_location
```

```text
description
```

```text
engine_security_group_id
```

```text
idp_auth_url
```

--------------------------------

### List Spot Fleet Requests with Launch Template Configurations

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_spot_fleet_request

Filters for requests using either launch templates or launch specifications.

```sql

select
  spot_fleet_request_id,
  spot_fleet_request_state,
  launch_template_configs,
  launch_specifications
from
  aws_ec2_spot_fleet_request
where
  launch_template_configs is not null
  or launch_specifications is not null;

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_emr_instance

Examples of field names used in the aws_emr_instance table schema.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
cluster_id
```

```text
ebs_volumes
```

```text
ec2_instance_id
```

```text
id
```

```text
instance_fleet_id
```

```text
instance_group_id
```

```text
instance_type
```

--------------------------------

### Get license details for each instance using SQL

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/ec_2/example_get_license_details_for_each_instance

Use this query to list instance IDs and their associated license configuration ARNs. The first variant is optimized for PostgreSQL/Steampipe CLI, while the second is for SQLite.

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

```sql
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;
```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_eks_node_group

Examples of schema fields used in the aws_eks_node_group table definition.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
ami_type
```

```text
arn
```

```text
capacity_type
```

```text
cluster_name
```

```text
created_at
```

```text
timestamp with time zone
```

```text
disk_size
```

```text
bigint
```

```text
health
```

```text
instance_types
```

--------------------------------

### Get provisioning artifact details for each product

Source: https://hub.steampipe.io/plugins/turbot/aws/queries/service_catalog/example_get_provisioning_artifact_details_for_each_product

Retrieves provisioning artifact details using JSONB array elements for PostgreSQL or standard SQL environments.

```SQL
select
  name,
  id,
  p ->> 'Id' as provisioning_artifact_id,
  p ->> 'Name' as provisioning_artifact_name,
  p ->> 'CreatedTime' as provisioning_artifact_created_time,
  p ->> 'Description' as provisioning_artifact_description,
  p ->> 'Guidance' as provisioning_artifact_guidance
from
  aws_servicecatalog_product,
  jsonb_array_elements(provisioning_artifacts) as p;
```

```SQL
select
  sp.name,
  sp.id,
  json_extract(p.value, '$.Id') as provisioning_artifact_id,
  json_extract(p.value, '$.Name') as provisioning_artifact_name,
  json_extract(p.value, '$.CreatedTime') as provisioning_artifact_created_time,
  json_extract(p.value, '$.Description') as provisioning_artifact_description,
  json_extract(p.value, '$.Guidance') as provisioning_artifact_guidance
from
  aws_servicecatalog_product as sp,
  json_each(provisioning_artifacts) as p;
```

--------------------------------

### Schema Column Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ecs_task_definition

Examples of column names used in the aws_ecs_task_definition table schema.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
compatibilities
```

```text
container_definitions
```

```text
cpu
```

```text
bigint
```

```text
deregistered_at
```

```text
timestamp with time zone
```

```text
ephemeral_storage_size_in_gib
```

```text
execution_role_arn
```

--------------------------------

### Retrieve basic provisioned product information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_servicecatalog_provisioned_product

Fetches core details for all provisioned products to assist with inventory management and auditing.

```sql

select
  name,
  id,
  arn,
  type,
  product_id,
  status,
  created_time,
  last_provisioning_record_id
from
  aws_servicecatalog_provisioned_product;

```

--------------------------------

### Retrieve basic framework information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_backup_framework

Fetches core details including status, creation time, and control counts for all backup frameworks.

```sql

select
  account_id,
  arn,
  creation_time,
  deployment_status,
  framework_controls,
  framework_description,framework_name,
  framework_status,
  number_of_controls,
  region,
  tags
from
  aws_backup_framework;

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_dax_subnet_group

Examples of schema field types used in the table definition.

```json
_ctx
```

```json
jsonb
```

```text
account_id
```

```text
text
```

```text
akas
```

```text
description
```

```text
partition
```

```text
region
```

```text
sp_connection_name
```

```text
sp_ctx
```

```text
subnet_group_name
```

```text
subnets
```

```text
title
```

```text
vpc_id
```

--------------------------------

### Retrieve basic usage plan information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_api_gateway_usage_plan

Fetches core details like name, ID, product code, and API stages for all usage plans.

```sql

select
  name,
  id,
  product_code,
  description,
  api_stages
from
  aws_api_gateway_usage_plan;

```

--------------------------------

### Schema Column Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_codebuild_project

Examples of column names used in the aws_codebuild_project table schema.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
text
```

```jsonb
akas
```

```text
arn
```

```jsonb
artifacts
```

```jsonb
badge
```

```jsonb
build_batch_config
```

```jsonb
cache
```

```bigint
bigint
```

```timestamp
timestamp with time zone
```

--------------------------------

### Retrieve basic source server information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_drs_source_server

Fetches the ARN, last launch result, ID, and title for all source servers.

```sql

select
  arn,
  last_launch_result,
  source_server_id,
  title
from
  aws_drs_source_server;

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_cloudfront_distribution

Examples of field names used in the aws_cloudfront_distribution table schema.

```text
_ctx
```

```jsonb
jsonb
```

```text
account_id
```

```text
active_trusted_key_groups
```

```text
active_trusted_signers
```

```text
akas
```

```text
alias_icp_recordals
```

--------------------------------

### List instance profiles with roles that have specific permissions

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_iam_instance_profile

Join instance profiles with roles and policies to find specific permission sets.

```Postgres

select
  ip.instance_profile_name as instance_profile_name,
  role ->> 'RoleName' as role_name,
  p.policy_name
from
  aws_iam_instance_profile as ip,
  jsonb_array_elements(roles) as role,
  aws_iam_role as r,
  jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
  aws_iam_policy as p
where
  r.arn = role ->> 'Arn'
  and p.arn = policy_arn
  and p.policy_name like '%EC2%';

```

```SQLite

select
  ip.instance_profile_name as instance_profile_name,
  json_extract(role.value, '$.RoleName') as role_name,
  p.policy_name
from
  aws_iam_instance_profile as ip,
  json_each(roles) as role,
  aws_iam_role as r,
  json_each(r.attached_policy_arns) as policy_arn,
  aws_iam_policy as p
where
  r.arn = json_extract(role.value, '$.Arn')
  and p.arn = policy_arn.value
  and p.policy_name like '%EC2%';

```

--------------------------------

### Schema Field Examples

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_appautoscaling_policy

Examples of schema fields used in the aws_appautoscaling_policy table definition.

```text
_ctx
```

```text
jsonb
```

```text
account_id
```

```text
text
```

```text
alarms
```

```text
creation_time
```

```text
timestamp with time zone
```

```text
partition
```

```text
policy_arn
```

```text
policy_name
```

```text
policy_type
```

```text
region
```

```text
resource_id
```

--------------------------------

### Install and run Steampipe exporter

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_vpc_network_acl

Commands to install the Steampipe exporter and run it for the aws_vpc_network_acl table.

```bash

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

```

```bash

steampipe_export_aws --config '<your_config>' aws_vpc_network_acl

```

--------------------------------

### Retrieve basic accelerator information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_globalaccelerator_accelerator

Fetch key operational details like status, DNS name, and enabled state for all accelerators.

```sql
select
  name,
  created_time,
  dns_name,
  enabled,
  ip_address_type,
  last_modified_time,
  status
from
  aws_globalaccelerator_accelerator;
```

--------------------------------

### Count patches installed from baseline

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssm_managed_instance_patch_state

Returns the number of patches installed specifically from the assigned baseline.

```sql

select
  instance_id,
  baseline_id,
  installed_count
from
  aws_ssm_managed_instance_patch_state;

```

--------------------------------

### Retrieve basic build information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_codebuild_build

Fetch core details such as build status, duration, and project names for completed builds.

```sql

select
  arn,
  id,
  build_complete,
  timeout_in_minutes,
  project_name,
  build_status,
  encryption_key,
  end_time,
  region
from
  aws_codebuild_build;

```

--------------------------------

### Install and run Steampipe exporter

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_fms_app_list

Commands to install the standalone exporter and execute it for the aws_fms_app_list table.

```bash

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

```

```bash

steampipe_export_aws --config '<your_config>' aws_fms_app_list

```

--------------------------------

### List configuration parameters

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_redshiftserverless_workgroup

Expands the configuration parameters array to list individual key-value pairs.

```sql

select
  workgroup_arn,
  p ->> 'ParameterKey' as parameter_key,
  p ->> 'ParameterValue' as parameter_value
from
  aws_redshiftserverless_workgroup,
  jsonb_array_elements(config_parameters) p;

```

```sql

select
  workgroup_arn,
  json_extract(p.value, '$.ParameterKey') as parameter_key,
  json_extract(p.value, '$.ParameterValue') as parameter_value
from
  aws_redshiftserverless_workgroup,
  json_each(config_parameters) as p;

```

--------------------------------

### Retrieve basic launch template version information

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_launch_template_version

Fetches core details for all launch template versions, including names, IDs, and version numbers.

```sql

select
  launch_template_name,
  launch_template_id,
  created_by,
  default_version,
  version_description,
  version_number
from
  aws_ec2_launch_template_version;
```

--------------------------------

### Install AWS CloudFormation Plugin

Source: https://hub.steampipe.io/plugins/turbot/awscfn

Use this command to install the latest version of the awscfn plugin.

```bash
steampipe plugin install awscfn
```

--------------------------------

### Query basic stack output information

Source: https://hub.steampipe.io/plugins/turbot/awscfn/tables/awscfn_output

Retrieve key details including name, description, value, and path for all stack outputs.

```sql
select
  name,
  description,
  value,
  path
from
  awscfn_output;
```
Tokens:
Raw