### Get Configuration Details of Each Add-on Version

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

Explore specific configuration details for each add-on version to understand its setup and function. This can help identify potential issues or areas for improvement in your AWS EKS environment.

```APIDOC
## GET /aws_eks_addon_version/configuration

### Description
Retrieves detailed configuration information for each add-on version, including definitions, properties, and compatibility details.

### Method
GET

### Endpoint
/aws_eks_addon_version/configuration

### Parameters
#### Query Parameters
- **addon_name** (text) - Optional - Filters results by add-on name.
- **addon_version** (text) - Optional - Filters results by add-on version.
- **limit** (integer) - Optional - Limits the number of results returned.

### Request Example
```sql
select
  addon_name,
  addon_version,
  addon_configuration -> '$defs' -> 'extraVolumeTags' ->> 'description' as addon_configuration_def_description,
  addon_configuration -> '$defs' -> 'extraVolumeTags' -> 'propertyNames' as addon_configuration_def_property_names,
  addon_configuration -> '$defs' -> 'extraVolumeTags' -> 'patternProperties' as addon_configuration_def_pattern_properties,
  addon_configuration -> 'properties' as addon_configuration_properties
from
  aws_eks_addon_version limit 10;
```

### Response
#### Success Response (200)
- **addon_name** (text) - The name of the add-on.
- **addon_version** (text) - The version of the add-on.
- **addon_configuration_def_description** (text) - Description of the add-on configuration definition.
- **addon_configuration_def_property_names** (jsonb) - Property names within the add-on configuration definition.
- **addon_configuration_def_pattern_properties** (jsonb) - Pattern properties within the add-on configuration definition.
- **addon_configuration_properties** (jsonb) - Properties of the add-on configuration.

#### Response Example
```json
[
  {
    "addon_name": "vpc-cni",
    "addon_version": "v1.13.1-eksbuild.1",
    "addon_configuration_def_description": "Tags added to the EBS volumes.",
    "addon_configuration_def_property_names": null,
    "addon_configuration_def_pattern_properties": null,
    "addon_configuration_properties": {
      "env": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "name": {
              "type": "string"
            },
            "value": {
              "type": "string"
            }
          }
        }
      }
    }
  }
]
```
```

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

### Exporter CLI Installation

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

Instructions on how to install and use the Steampipe Exporter CLI for AWS EC2 spot prices.

```APIDOC
## Steampipe Exporter CLI for AWS

### Description
This section details how to install and use the standalone Exporter CLI to extract AWS EC2 spot price data without a database.

### Installation
Use the provided script to install the exporter:

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

### Usage
Pass the configuration to the command with the `--config` argument:

```bash
steampipe_export_aws --config '<your_config>' aws_ec2_spot_price
```

Replace `<your_config>` with your Steampipe configuration details.
```

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

### Get Basic Resource Cost Overview (SQLite)

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

Retrieves a basic overview of resource costs with essential fields, including resource ID, period start and end, dimension key and value, and blended/unblended costs. This query is optimized for SQLite and defaults to the last 14 days of data.

```sql
select
  resource_id,
  period_start,
  period_end,
  dimension_key,
  dimension_value,
  cast(blended_cost_amount as decimal) as blended_cost,
  cast(unblended_cost_amount as decimal) as unblended_cost
from
  aws_cost_by_resource_monthly
where
  period_start >= date('now', '-14 days')
order by
  period_start desc;
```

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

### Get Basic Resource Cost Overview (PostgreSQL)

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

Retrieves a basic overview of resource costs with essential fields, including resource ID, period start and end, dimension key and value, and blended/unblended costs. This query is optimized for PostgreSQL and defaults to the last 14 days of data.

```sql
select
  resource_id,
  period_start,
  period_end,
  dimension_key,
  dimension_value,
  blended_cost_amount::numeric::money as blended_cost,
  unblended_cost_amount::numeric::money as unblended_cost
from
  aws_cost_by_resource_monthly
where
  period_start >= current_date - interval '14 days'
order by
  period_start desc;
```

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

### Get Attribute Information for All Instances

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

Combine attribute data with instance information to get a comprehensive view of Connect instances and their configurations.

```APIDOC
## Get Attribute Information for All Instances

### Description
Combine attribute data with instance information to get a comprehensive view of Connect instances and their configurations.

### Method
SELECT

### Endpoint
aws_connect_instance_attribute, aws_connect_instance

### Parameters
#### Path Parameters
- **a.instance_id** (text) - Used for joining with aws_connect_instance.
- **i.id** (text) - Used for joining with aws_connect_instance_attribute.

### Request Example
```sql
select
  a.instance_id,
  i.instance_alias,
  i.instance_status,
  a.attribute_type,
  a.value
from
  aws_connect_instance_attribute as a
  left join aws_connect_instance as i on a.instance_id = i.id
order by
  a.instance_id,
  a.attribute_type;
```

### Response
#### Success Response (200)
- **instance_id** (text) - The ID of the Connect instance.
- **instance_alias** (text) - The alias of the Connect instance.
- **instance_status** (text) - The status of the Connect instance.
- **attribute_type** (text) - The type of the attribute.
- **value** (text) - The value of the attribute.
```

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

### Get AWS EKS Add-on Version Configuration Details (Postgres/SQLite)

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

Retrieves specific configuration details for the first 10 add-on versions in AWS EKS. This query helps in inspecting the setup and functionality of add-ons, identifying potential issues or areas for improvement. It demonstrates querying nested JSON structures.

```sql
select
  addon_name,
  addon_version,
  addon_configuration -> '$defs' -> 'extraVolumeTags' ->> 'description' as addon_configuration_def_description,
  addon_configuration -> '$defs' -> 'extraVolumeTags' -> 'propertyNames' as addon_configuration_def_property_names,
  addon_configuration -> '$defs' -> 'extraVolumeTags' -> 'patternProperties' as addon_configuration_def_pattern_properties,
  addon_configuration -> 'properties' as addon_configuration_properties
from
  aws_eks_addon_version limit 10;
```

```sql
select
  addon_name,
  addon_version,
  json_extract(addon_configuration, '$.$defs.extraVolumeTags.description') as addon_configuration_def_description,
  json_extract(addon_configuration, '$.$defs.extraVolumeTags.propertyNames') as addon_configuration_def_property_names,
  json_extract(addon_configuration, '$.$defs.extraVolumeTags.patternProperties') as addon_configuration_def_pattern_properties,
  json_extract(addon_configuration, '$.properties') as addon_configuration_properties
from
  aws_eks_addon_version limit 10;
```

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

### Install Steampipe Exporter CLI for AWS

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

Installs the Steampipe Exporter CLI for AWS using a provided script. This tool allows for data extraction using Steampipe plugins without a database. The AWS exporter can be configured using the `--config` argument.

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

```bash
steampipe_export_aws --config '<your_config>' aws_ecs_cluster_metric_cpu_utilization_hourly
```

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

### Install and Use Steampipe Exporter for AWS

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

Installs the Steampipe exporter for AWS using a shell script and demonstrates how to use it to export data for the 'aws_cloudwatch_log_resource_policy' table. This allows data extraction without a running Steampipe database.

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

```bash
steampipe_export_aws --config '<your_config>' aws_cloudwatch_log_resource_policy
```

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

### List Detailed Information for Successfully Provisioned Products

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

Displays detailed information for successfully provisioned products, including name, type, support URL, and email, alongside their provisioning ID. Useful for a consolidated view of product details and provisioning status.

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

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

### Install Steampipe Exporter CLI for AWS

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

Installs the Steampipe Exporter CLI for AWS using a shell script. This allows data extraction without a full Steampipe database setup.

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

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

### Get AWS DRS Job Status and Origin

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

Retrieves the title, ARN, status, and initiator of AWS DRS jobs. This helps in monitoring ongoing tasks and identifying who started them for accountability.

```sql
select
  title,
  arn,
  status,
  initiated_by
from
  aws_drs_job;
```

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

### AWS Spot Fleet Requests with Launch Template Configs (Postgres/SQLite)

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

Analyzes AWS EC2 Spot Fleet requests that utilize launch templates for instance configuration. This provides insights into how instances are launched and configured. The query selects requests where `launch_template_configs` or `launch_specifications` are not null. It relies on the Steampipe AWS plugin and either the Postgres or SQLite extension.

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

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

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

### Get Application Names and Versions for Each AWS EMR Cluster (SQLite)

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

Retrieves the names and versions of applications installed on each AWS EMR cluster. It unnests the 'applications' JSON array using json_each for SQLite compatibility.

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

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

### Get Launch Path Details for AWS Service Catalog Products (CLI / Postgres / SQLite)

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

Extracts launch path identifiers and names for each AWS Service Catalog product. This helps in understanding product launch configurations and managing products efficiently.

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

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

### Get Application Names and Versions for Each AWS EMR Cluster (Postgres/CLI)

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

Retrieves the names and versions of applications installed on each AWS EMR cluster. It unnests the 'applications' JSON array using jsonb_array_elements for Postgres compatibility.

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

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

### Get Provisioning Artifact Details for AWS Service Catalog Products (CLI / Postgres / SQLite)

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

Retrieves detailed information about provisioning artifacts for each AWS Service Catalog product, including IDs, names, creation times, descriptions, and guidance. This is crucial for managing product versions and configurations.

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

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

### Get Quiet Time for AWS Pinpoint Applications

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

Discover the quiet time settings (start and end times) for AWS Pinpoint applications. This helps in identifying periods of low activity for potential maintenance scheduling.

```sql
select
  id,
  quiet_time -> 'Start' as start_time,
  quiet_time -> 'End' as end_time
from
  aws_pinpoint_app;
```

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

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

### List AWS QuickSight Datasets Created in the Last 30 Days

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

Lists datasets in AWS QuickSight that were created within the last 30 days, ordered by creation time. This helps in monitoring new data resources and tracking recent changes in the environment.

```sql
select
  name,
  dataset_id,
  created_time
from
  aws_quicksight_data_set
where
  created_time > now() - interval '30 days'
order by
  created_time desc;
```

```sql
select
  name,
  dataset_id,
  created_time
from
  aws_quicksight_data_set
where
  created_time > datetime('now', '-30 days')
order by
  created_time desc;
```

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

### Get AWS Bedrock Knowledge Base Configuration Details

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

Retrieves the configuration settings for AWS Bedrock knowledge bases, focusing on embeddings and storage configurations. This is useful for understanding the technical setup of your knowledge bases.

```sql
select
  name,
  knowledge_base_id,
  knowledge_base_configuration,
  storage_configuration
from
  aws_bedrock_knowledge_base;
```

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

### Get AWS CloudWatch Alarms Based on Metric Math Expression (PostgreSQL)

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

Extracts details of CloudWatch alarms configured using metric math expressions, specifically for PostgreSQL. This helps in understanding complex performance monitoring setups.

```sql
select
  name,
  metric ->> 'Id' as metric_id,
  metric ->> 'Expression' as metric_expression,
  metric -> 'MetricStat' -> 'Metric' ->> 'MetricName' as metric_name,
  metric -> 'MetricStat' -> 'Metric' ->> 'Namespace' as metric_namespace,
  metric -> 'MetricStat' -> 'Metric' ->> 'Dimensions' as metric_dimensions,
  metric ->> 'ReturnData' as metric_return_data
from
  aws_cloudwatch_alarm,
  jsonb_array_elements(metrics) as metric;
```

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

### Run Steampipe Exporter CLI with Configuration

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

Demonstrates how to run the Steampipe Exporter CLI for AWS, passing configuration options. The `--config` argument specifies the configuration to use, followed by the connection name for the AWS resources.

```bash
steampipe_export_aws --config '<your_config>' aws_vpc_internet_gateway
```

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

### Get Basic Resource Overview (SQL)

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

Retrieves a basic overview of AWS resources, including ARN, resource type, service, region, and title. This query is useful for a quick scan of available resources.

```sql
select
  arn,
  resource_type,
  service,
  region,
  title
from
  aws_resource_explorer_resource
limit 10;
```

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

### Get Basic Info for Amazon WorkSpaces Workspaces

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

This example demonstrates how to retrieve basic information for each Amazon WorkSpaces Workspace, including its name, ID, ARN, and current state. This is useful for quickly listing and understanding the status of your WorkSpaces.

```sql
select
  name,
  workspace_id,
  arn,
  state
from
  aws_workspaces_workspace;
```

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

### Get VPC Details for AWS MSK Serverless Clusters

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

Extracts VPC configuration details, including subnet IDs and security group IDs, for each AWS MSK Serverless cluster. This query aids in understanding and verifying the network security and setup of your clusters.

```sql
select
  arn,
  cluster_name,
  state,
  vpc ->> 'SubnetIds' as subnet_ids,
  vpc ->> 'SecurityGroupIds' as security_group_ids
from
  aws_msk_serverless_cluster,
  jsonb_array_elements(serverless -> 'VpcConfigs') as vpc
```

```sql
select
  arn,
  cluster_name,
  state,
  json_extract(vpc.value, '$.SubnetIds') as subnet_ids,
  json_extract(vpc.value, '$.SecurityGroupIds') as security_group_ids
from
  aws_msk_serverless_cluster,
  json_each(json_extract(serverless, '$.VpcConfigs')) as vpc
```

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

### List Available Partition Placement Groups

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

Finds all partition placement groups that are currently available, useful for identifying groups for high-availability workloads.

```APIDOC
## GET /aws_ec2_placement_group?strategy=partition&state=available

### Description
Lists all available partition placement groups.

### Method
GET

### Endpoint
/aws_ec2_placement_group

### Parameters
#### Query Parameters
- **region** (text) - Optional - The AWS Region to query.
- **account_id** (text) - Optional - The AWS Account ID to query.
- **strategy** (text) - Required - Must be 'partition'.
- **state** (text) - Required - Must be 'available'.

### Request Example
```
GET /aws_ec2_placement_group?strategy=partition&state=available
```

### Response
#### Success Response (200)
- **group_name** (text) - The name of the placement group.
- **partition_count** (bigint) - The number of partitions for the group.
- **state** (text) - The current state of the placement group.

#### Response Example
```json
[
  {
    "group_name": "my-partition-group",
    "partition_count": 3,
    "state": "available"
  }
]
```
```

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

### List All AWS Backup Frameworks

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

Retrieve comprehensive details for all AWS backup frameworks, including account ID, ARN, creation time, deployment status, controls, description, name, status, number of controls, region, and tags. This is useful for a complete overview of backup configurations.

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

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

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

### List CloudWatch Log Delivery Destinations

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

Retrieves basic information for all CloudWatch log delivery destinations, including their name, ARN, destination resource ARN, type, and output format. This query is useful for getting a general overview of your log delivery setup.

```sql
select
  name,
  arn,
  destination_resource_arn,
  delivery_destination_type,
  output_format
from
  aws_cloudwatch_log_delivery_destination;
```

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

### Retrieve Basic Cost Optimization Recommendation Details

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

Fetches fundamental details of cost optimization recommendations, including their IDs, associated resource IDs, estimated monthly savings, savings percentage, and implementation effort. This query is useful for a quick overview of potential cost savings.

```Postgres
select
  recommendation_id,
  resource_id,
  estimated_monthly_savings,
  estimated_savings_percentage,
  implementation_effort
from
  aws_costoptimizationhub_recommendation;
```

```SQLite
select
  recommendation_id,
  resource_id,
  estimated_monthly_savings,
  estimated_savings_percentage,
  implementation_effort
from
  aws_costoptimizationhub_recommendation;
```

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

### Get Patch Summary Details for Security Finding (SQLite)

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

Retrieves detailed patch summary information for a specific Security Hub finding, including counts of installed, failed, and missing patches, as well as operation details. This query uses SQLite-specific JSON functions.

```sql
select
  title,
  id,
  json_extract(patch_summary, '$.Id') as patch_id,
  json_extract(patch_summary, '$.FailedCount') as failed_count,
  json_extract(patch_summary, '$.InstalledCount') as installed_count,
  json_extract(patch_summary, '$.InstalledOtherCount') as installed_other_count,
  json_extract(patch_summary, '$.InstalledPendingReboot') as installed_pending_reboot,
  json_extract(patch_summary, '$.InstalledRejectedCount') as installed_rejected_count,
  json_extract(patch_summary, '$.MissingCount') as missing_count,
  json_extract(patch_summary, '$.Operation') as operation,
  json_extract(patch_summary, '$.OperationEndTime') as operation_end_time,
  json_extract(patch_summary, '$.OperationStartTime') as operation_start_time,
  json_extract(patch_summary, '$.RebootOption') as reboot_option
from
  aws_securityhub_finding
where
  title = 'EC2 instance involved in SSH brute force attacks.';

```

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

### Get Basic AWS EC2 Launch Configuration Info

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

Retrieves core details of AWS EC2 launch configurations, such as name, creation time, public IP association, EBS optimization, image ID, monitoring status, instance type, and key name. This information is useful for auditing and optimizing cloud resources.

```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 Patch Summary Details for Security Finding (Postgres)

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

Retrieves detailed patch summary information for a specific Security Hub finding, including counts of installed, failed, and missing patches, as well as operation details. This query uses Postgres-specific JSON operators.

```sql
select
  title,
  id,
  patch_summary ->> 'Id' as patch_id,
  patch_summary ->> 'FailedCount' as failed_count,
  patch_summary ->> 'InstalledCount' as installed_count,
  patch_summary ->> 'InstalledOtherCount' as installed_other_count,
  patch_summary ->> 'InstalledPendingReboot' as installed_pending_reboot,
  patch_summary ->> 'InstalledRejectedCount' as installed_rejected_count,
  patch_summary ->> 'MissingCount' as missing_count,
  patch_summary ->> 'Operation' as operation,
  patch_summary ->> 'OperationEndTime' as operation_end_time,
  patch_summary ->> 'OperationStartTime' as operation_start_time,
  patch_summary ->> 'RebootOption' as reboot_option
from
  aws_securityhub_finding
where
  title = 'EC2 instance involved in SSH brute force attacks.';

```

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

### Get Basic AWS API Gateway REST API Information

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

Retrieves fundamental configuration details for your API Gateway REST APIs, such as the API key source and compression settings. This query helps in understanding the basic setup of your APIs. It is compatible with PostgreSQL FDW.

```sql
select
  name,
  api_id,
  api_key_source,
  minimum_compression_size,
  binary_media_types
from
  aws_api_gateway_rest_api;
```

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

### Get Data Replication Info for Recovery Instances (SQLite)

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

Retrieve details about data replication for each AWS DRS recovery instance. This includes replication start and next attempt times, error status, and the state of replication. Requires the Steampipe AWS plugin and SQLite extension.

```sql
select
  recovery_instance_id,
  arn,
  json_extract(data_replication_info, '$.DataReplicationInitiation.StartDateTime') as data_replication_start_date_time,
  json_extract(data_replication_info, '$.DataReplicationInitiation.NextAttemptDateTime') as data_replication_next_attempt_date_time,
  json_extract(data_replication_info, '$.DataReplicationError') as data_replication_error,
  json_extract(data_replication_info, '$.DataReplicationState') as data_replication_state,
  json_extract(data_replication_info, '$.ReplicatedDisks') as data_replication_replicated_disks
from
  aws_drs_recovery_instance;
```

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

### Configure and Run Steampipe AWS Exporter CLI

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

Demonstrates how to run the Steampipe AWS Exporter CLI with a specific configuration and target. The `--config` argument takes a configuration string, and the subsequent argument specifies the AWS resource to export (e.g., `aws_cloudwatch_alarm`).

```shell
steampipe_export_aws --config '<your_config>' aws_cloudwatch_alarm
```

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

### Get Data Replication Info for Recovery Instances (Postgres/FDW)

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

Retrieve details about data replication for each AWS DRS recovery instance. This includes replication start and next attempt times, error status, and the state of replication. Requires the Steampipe AWS plugin and Postgres FDW.

```sql
select
  recovery_instance_id,
  arn,
  data_replication_info -> 'DataReplicationInitiation' ->> 'StartDateTime' as data_replication_start_date_time,
  data_replication_info -> 'DataReplicationInitiation' ->> 'NextAttemptDateTime' as data_replication_next_attempt_date_time,
  data_replication_info ->> 'DataReplicationError' as data_replication_error,
  data_replication_info ->> 'DataReplicationState' as data_replication_state,
  data_replication_info ->> 'ReplicatedDisks' as data_replication_replicated_disks
from
  aws_drs_recovery_instance;
```

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

### Run AWS Exporter CLI with Configuration

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

Demonstrates how to run the AWS exporter CLI with a custom configuration. The `--config` argument allows specifying a configuration file or inline configuration to customize the data export process for AWS Roles Anywhere Trust Anchors.

```bash
steampipe_export_aws --config '<your_config>' aws_rolesanywhere_trust_anchor
```

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

### Get Budget Details for AWS Service Catalog Products (CLI / Postgres / SQLite)

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

Retrieves budget details for each AWS Service Catalog product, including product information and the budget name. This aids in financial planning and resource management by showing where funds are allocated.

```sql
select
  sp.name,
  sp.id,
  sp.owner,
  sp.product_id,
  sp.short_description,
  b ->> 'BudgetName' as budget_name
from
  aws_servicecatalog_product as sp,
  jsonb_array_elements(budgets) as b;
```

```sql
select
  sp.name,
  sp.id,
  sp.owner,
  sp.product_id,
  sp.short_description,
  json_extract(b.value, '$.BudgetName') as budget_name
from
  aws_servicecatalog_product as sp,
  json_each(budgets) as b;
```

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

### List All Portfolio Shares by Type (Steampipe CLI)

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

This example demonstrates how to list all portfolio shares of a specific type, such as 'ACCOUNT', using the Steampipe CLI. It helps in auditing portfolio access and permissions by filtering shares based on their sharing configuration.

```sql
select
  portfolio_display_name,
  portfolio_id,
  type,
  principal_id,
  accepted,
  share_principals,
  share_tag_options,
  region
from
  aws_servicecatalog_portfolio_share
where
  type = 'ACCOUNT';
```

```sql
select
  portfolio_display_name,
  portfolio_id,
  type,
  principal_id,
  accepted,
  share_principals,
  share_tag_options,
  region
from
  aws_servicecatalog_portfolio_share
where
  type = 'ACCOUNT';
```

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

### Get S3 Bucket Details for AWS Security Lake Subscribers

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

Retrieves configuration details for S3 buckets linked to AWS Security Lake subscribers, including ARNs, event notification configurations, server-side encryption configurations, and ACLs. This query helps ensure proper storage setup and security.

```sql
select
  s.subscriber_name,
  s.subscription_id,
  b.arn,
  b.event_notification_configuration,
  b.server_side_encryption_configuration,
  b.acl
from
  aws_securitylake_subscriber as s,
  aws_s3_bucket as b
where
  s.s3_bucket_arn = b.arn;
```

```sql
select
  s.subscriber_name,
  s.subscription_id,
  b.arn,
  b.event_notification_configuration,
  b.server_side_encryption_configuration,
  b.acl
from
  aws_securitylake_subscriber as s
join
  aws_s3_bucket as b
on
  s.s3_bucket_arn = b.arn;
```

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

### Get Input Parameter Details of Conformance Packs (PostgreSQL)

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

Determine the settings for each conformance pack in AWS Config by extracting input parameter details. This helps in understanding configurations and identifying optimization opportunities.

```postgresql
select
  name,
  inp ->> 'ParameterName' as parameter_name,
  inp ->> 'ParameterValue' as parameter_value,
  title,
  akas
from
  aws_config_conformance_pack,
  jsonb_array_elements(input_parameters) as inp;
```

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

### Run Steampipe AWS Exporter with Configuration

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

Executes the Steampipe AWS Exporter with a specified configuration and targets the 'aws_lightsail_instance' resource. This demonstrates how to pass custom configurations to the exporter.

```bash
steampipe_export_aws --config '<your_config>' aws_lightsail_instance
```

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

### Get AWS S3 Bucket Intelligent Tiering Configuration - CLI, PostgreSQL, SQLite

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

Retrieves the bucket name, ID, status, and tierings of AWS S3 bucket intelligent tiering configurations. This query helps to understand the current setup for intelligent tiering on your S3 buckets.

```postgres
select
  bucket_name,
  id,
  status,
  tierings
from
  aws_s3_bucket_intelligent_tiering_configuration;
```

```sqlite
select
  bucket_name,
  id,
  status,
  tierings
from
  aws_s3_bucket_intelligent_tiering_configuration;
```

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

### Get Network Details for AWS Elasticsearch Domains

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

Fetches network configuration details such as availability zones, security group IDs, subnet IDs, and VPC ID for AWS Elasticsearch domains. This query helps in understanding and managing the network setup of your domains. It queries the `vpc_options` column of the `aws_elasticsearch_domain` table.

```Postgres FDG
select
  domain_name,
  vpc_options ->> 'AvailabilityZones' as availability_zones,
  vpc_options ->> 'SecurityGroupIds' as security_group_ids,
  vpc_options ->> 'SubnetIds' as subnet_ids,
  vpc_options ->> 'VPCId' as vpc_id
from
  aws_elasticsearch_domain
where
  vpc_options ->> 'AvailabilityZones' is not null;
```

```SQLite
select
  domain_name,
  json_extract(vpc_options.value, '$.AvailabilityZones') as availability_zones,
  json_extract(vpc_options.value, '$.SecurityGroupIds') as security_group_ids,
  json_extract(vpc_options.value, '$.SubnetIds') as subnet_ids,
  json_extract(vpc_options.value, '$.VPCId') as vpc_id
from
  aws_elasticsearch_domain,
  json_each(vpc_options) as vpc_options
where
  json_extract(vpc_options.value, '$.AvailabilityZones') is not null;
```

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

### Query Basic AWS QuickSight Data Source Info (Steampipe CLI)

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

This example demonstrates how to query the basic details of AWS QuickSight data sources using the Steampipe CLI. It retrieves essential information such as the data source name, ID, ARN, creation and update times, status, and type.

```sql
select
  name,
  data_source_id,
  arn,
  created_time,
  last_updated_time,
  status,
  type
from
  aws_quicksight_data_source;
```

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

### List Launch Templates Created in the Last 30 Days

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

Identifies recently created launch templates within the past month. Useful for monitoring new additions.

```APIDOC
## GET /aws/ec2/launch_templates?since=[date]

### Description
Identifies recently created launch templates within the past month. This is useful for monitoring new additions and ensuring proper configuration and usage.

### Method
GET

### Endpoint
/aws/ec2/launch_templates

### Parameters
#### Query Parameters
- **since** (date) - Optional - Filters launch templates created on or after this date (e.g., '2023-10-01'). If not provided, defaults to the last 30 days.

### Request Example
```json
{
  "query": "SELECT launch_template_name, launch_template_id, create_time FROM aws_ec2_launch_template WHERE create_time >= now() - interval '30' day;"
}
```

### Response
#### Success Response (200)
- **launch_template_name** (text) - The name of the launch template.
- **launch_template_id** (text) - The ID of the launch template.
- **create_time** (timestamp with time zone) - The time launch template was created.

#### Response Example
```json
{
  "launch_templates": [
    {
      "launch_template_name": "new-template-october",
      "launch_template_id": "lt-0123456789abcdef0",
      "create_time": "2023-10-20T11:00:00Z"
    },
    {
      "launch_template_name": "another-new-template",
      "launch_template_id": "lt-0fedcba987654321",
      "create_time": "2023-10-15T14:30:00Z"
    }
  ]
}
```
```

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

### Get AWS API Gateway Account Configuration

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

Retrieves basic configuration details for your AWS API Gateway account, including CloudWatch role ARN, API key version, throttle limits, supported features, region, and account ID. This is useful for understanding your account's current setup and optimization opportunities.

```sql
select
  cloudwatch_role_arn,
  api_key_version,
  throttle_burst_limit,
  throttle_rate_limit,
  features,
  region,
  account_id
from
  aws_api_gateway_account;
```

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

### Get Basic Daily Resource Costs (SQLite)

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

Retrieves a basic overview of daily resource costs, including essential fields like resource ID, period, cost amounts, and dimension details. This query uses SQLite date functions for filtering.

```sql
select
  resource_id,
  period_start,
  period_end,
  dimension_key,
  dimension_value,
  cast(blended_cost_amount as decimal) as blended_cost,
  cast(unblended_cost_amount as decimal) as unblended_cost
from
  aws_cost_by_resource_daily
where
  period_start >= date('now', '-14 days')
order by
  period_start desc;
```

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

### Get EC2 Instance License Details

Source: https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_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. Queries are provided for PostgreSQL and SQLite.

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

```SQLite
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 AWS EC2 Load Balancer Action Configuration Details

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

Explores the configuration details of load balancer actions, such as authentication, fixed responses, and target group stickiness. This helps assess the security and efficiency of the load balancing setup. It handles JSON data extraction differently for Postgres (using `->>`) and SQLite (using `json_extract`).

```sql
select
  title,
  arn,
  action ->> 'AuthenticateCognitoConfig' as authenticate_cognito_config,
  action ->> 'AuthenticateOidcConfig' as authenticate_Oidc_config,
  action ->> 'FixedResponseConfig' as fixed_response_config,
  action -> 'ForwardConfig' -> 'TargetGroupStickinessConfig' ->> 'DurationSeconds' as duration_seconds,
  action -> 'ForwardConfig' -> 'TargetGroupStickinessConfig' ->> 'Enabled' as target_group_stickiness_config_enabled
from
  aws_ec2_load_balancer_listener
  cross join jsonb_array_elements(default_actions) as action;
```

```sql
select
  title,
  arn,
  json_extract(action.value, '$.AuthenticateCognitoConfig') as authenticate_cognito_config,
  json_extract(action.value, '$.AuthenticateOidcConfig') as authenticate_Oidc_config,
  json_extract(action.value, '$.FixedResponseConfig') as fixed_response_config,
  json_extract(action.value, '$.ForwardConfig.TargetGroupStickinessConfig.DurationSeconds') as duration_seconds,
  json_extract(action.value, '$.ForwardConfig.TargetGroupStickinessConfig.Enabled') as target_group_stickiness_config_enabled
from
  aws_ec2_load_balancer_listener,
  json_each(default_actions) as action;
```

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

### Get Summary of AWS Connect Instance Configurations (Postgres FDG, SQLite Extension)

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

Retrieves a summary of AWS Connect instance configurations, including alias, ID, status, creation time, service role, and identity management type. This provides a concise overview of instance settings, ordered by creation time in descending order.

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

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

### Get Basic Daily Resource Costs (Postgres)

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

Retrieves a basic overview of daily resource costs, including essential fields like resource ID, period, cost amounts, and dimension details. This query uses standard SQL date functions for filtering.

```sql
select
  resource_id,
  period_start,
  period_end,
  dimension_key,
  dimension_value,
  blended_cost_amount::numeric::money as blended_cost,
  unblended_cost_amount::numeric::money as unblended_cost
from
  aws_cost_by_resource_daily
where
  period_start >= current_date - interval '14 days'
order by
  period_start desc;
```

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

### Export AWS Resources with Configuration (Shell)

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

Demonstrates how to use the installed Steampipe exporter to export AWS resource data. The command requires a configuration argument and specifies the resource type to export.

```shell
steampipe_export_aws --config '<your_config>' aws_resource_explorer_resource
```

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

### Get Monthly Amortized Cost Details (PostgreSQL)

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

This query retrieves amortized cost details from the 'aws_cost_by_record_type_monthly' table for a specific date range. It filters by 'period_start' and 'period_end' and returns the linked account ID, record type, period start date, and the amortized cost amount formatted as money. Requires the Steampipe AWS plugin and a PostgreSQL database.

```sql
select
  linked_account_id,
  record_type,
  period_start,
  amortized_cost_amount::numeric::money
from
  aws_cost_by_record_type_monthly
where
  period_start = '2023-05-01T05:30:00+05:30'
  and period_end = '2023-05-05T05:30:00+05:30'
order by
  linked_account_id,
  period_start;
```

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

### Get Input Parameter Details of Conformance Packs (SQLite)

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

Determine the settings for each conformance pack in AWS Config by extracting input parameter details. This helps in understanding configurations and identifying optimization opportunities.

```sqlite
select
  aws_config_conformance_pack.name,
  json_extract(inp.value, '$.ParameterName') as parameter_name,
  json_extract(inp.value, '$.ParameterValue') as parameter_value,
  title,
  akas
from
  aws_config_conformance_pack,
  json_each(input_parameters) as inp;
```

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

### Get Monthly Amortized Cost Details (SQLite)

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

This query retrieves amortized cost details from the 'aws_cost_by_record_type_monthly' table for a specific date range, formatted for SQLite. It filters by 'period_start' and 'period_end' and returns the linked account ID, record type, period start date, and the amortized cost amount cast as a real number. Requires the Steampipe AWS plugin and a SQLite database.

```sql
select
  linked_account_id,
  record_type,
  period_start,
  case(amortized_cost_amount as real) as amortized_cost_amount
from
  aws_cost_by_record_type_monthly
where
  period_start = '2023-05-01T05:30:00+05:30'
  and period_end = '2023-05-05T05:30:00+05:30'
order by
  linked_account_id,
  period_start;
```

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

### Get Daily Amortized Cost Details (Postgres FDw/SQLite)

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

Retrieve daily amortized cost details, including linked account ID, record type, period start, and amortized cost amount, filtered by a specific time frame. This query uses PostgreSQL foreign data wrapper (FDW) or SQLite syntax for date handling and type casting. It leverages the `aws_cost_by_record_type_daily` table.

```sql
select
  linked_account_id,
  record_type,
  period_start,
  amortized_cost_amount::numeric::money
from
  aws_cost_by_record_type_daily
where
  period_start = '2023-05-01T05:30:00+05:30'
  and period_end = '2023-05-05T05:30:00+05:30'
order by
  linked_account_id,
  period_start;
```

```sql
select
  linked_account_id,
  record_type,
  period_start,
  case(amortized_cost_amount as real) as amortized_cost_amount
from
  aws_cost_by_record_type_daily
where
  period_start = '2023-05-01T05:30:00+05:30'
  and period_end = '2023-05-05T05:30:00+05:30'
order by
  linked_account_id,
  period_start;
```

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

### Get Basic AWS QuickSight Account Settings

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

Retrieves fundamental configuration details for AWS QuickSight accounts in a specified region. This includes account name, edition, default namespace, notification email, termination protection status, and public sharing status. It requires the AWS QuickSight plugin for Steampipe to be configured.

```sql
select
  account_name,
  edition,
  default_namespace,
  notification_email,
  termination_protection_enabled,
  public_sharing_enabled
from
  aws_quicksight_account_setting
where
  region = 'us-east-1';
```

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

### Install Turbot AWS Plugin

Source: https://hub.steampipe.io/plugins/turbot/aws/queries_table=aws_api_gatewayv2_route

Installs the Turbot AWS plugin for Steampipe. This is a prerequisite for querying AWS resources using Steampipe. No specific inputs or outputs are described beyond the successful installation of the plugin.

```bash
steampipe plugin install aws
```

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

### Get AWS Service Catalog Products (CLI / Postgres / SQLite)

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

Retrieves basic information for all AWS Service Catalog products, including their names, IDs, product IDs, types, ARNs, support URLs, and support emails. Useful for an overview of available products.

```sql
select
  name,
  id,
  product_id,
  type,
  akas,
  support_url,
  support_email
from
  aws_servicecatalog_product;
```

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

### Count Resources by Type and Region (SQL)

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

Provides a summary of resource distribution by grouping resources by their type and region, and counting the occurrences. This helps in understanding resource allocation.

```sql
select
  resource_type,
  region,
  count(*) as resource_count
from
  aws_resource_explorer_resource
group by
  resource_type,
  region
order by
  resource_count desc;
```

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

### List All AWS SimSpaceWeaver Simulations (SQL)

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

Retrieves basic information for all AWS SimSpaceWeaver simulations, including name, ARN, creation time, status, execution ID, and schema errors. This query helps in getting an overview of existing simulations.

```sql
select
  name,
  arn,
  creation_time,
  status,
  execution_id,
  schema_error
from
  aws_simspaceweaver_simulation;
```