Steampipe Hub Plugins

https://hub.steampipe.io/plugins
Admin
Steampipe Hub Plugins is a central repository offering 153 plugins that enable users to query...
Tokens:456,495
Snippets:4,766
Trust Score:9.7
Update:10 months ago
Show doc for...
Tokens:
Raw
### Get Basic Resource Costs Overview (SQLite)

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

Retrieves a basic overview of resource costs, including essential fields like resource ID, period, cost amounts, and dimension details. This query filters for the last 14 days and orders by start period in descending order. It uses SQLite-specific date functions and casting.

```SQLite
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 Costs Overview (PostgreSQL)

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

Retrieves a basic overview of resource costs, including essential fields like resource ID, period, cost amounts, and dimension details. This query filters for the last 14 days and orders by start period in descending order. It uses PostgreSQL-specific casting for currency formatting.

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

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

### AWS Credentials File Example

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

An example structure of an AWS credentials file showing named profiles with access key ID, secret access key, and region configuration.

```ini
[account_a]
aws_access_key_id = AKIA4YFAKEKEYXTDS252
aws_secret_access_key = SH42YMW5p3EThisIsNotRealzTiEUwXN8BOIOF5J8m
region = us-west-2

[account_b]
aws_access_key_id = AKIA4YFAKEKEYJ7HS98F
aws_secret_access_key = Apf938vDKd8ThisIsNotRealzTiEUwXj9nKLWP9mg4
```

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

### Install and Use Steampipe Exporter CLI for AWS Costs

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

Install the Steampipe Exporter CLI for AWS and use it to extract `aws_cost_by_account_daily` data. The installation is done via a shell script, and data extraction requires providing a configuration.

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

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

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

### Get AWS Bedrock Knowledge Base Configuration Details

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

Fetch the configuration settings for AWS Bedrock knowledge bases, specifically focusing on their embedding and storage configurations. This query is valuable 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;
```

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

### Retrieve Daily AWS Costs By Account

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

This example demonstrates how to query the aws_cost_by_account_daily table to retrieve daily AWS costs for each linked account. It selects account ID, start period, and various cost amounts, ordering the results by account and period. This is useful for tracking and analyzing cost trends over time.

```PostgreSQL
select
  linked_account_id,
  period_start,
  blended_cost_amount::numeric::money,
  unblended_cost_amount::numeric::money,
  amortized_cost_amount::numeric::money,
  net_unblended_cost_amount::numeric::money,
  net_amortized_cost_amount::numeric::money
from
  aws_cost_by_account_daily
order by
  linked_account_id,
  period_start;
```

```SQLite
select
  linked_account_id,
  period_start,
  CAST(blended_cost_amount AS REAL) AS blended_cost_amount,
  CAST(unblended_cost_amount AS REAL) AS unblended_cost_amount,
  CAST(amortized_cost_amount AS REAL) AS amortized_cost_amount,
  CAST(net_unblended_cost_amount AS REAL) AS net_unblended_cost_amount,
  CAST(net_amortized_cost_amount AS REAL) AS net_amortized_cost_amount
from
  aws_cost_by_account_daily
order by
  linked_account_id,
  period_start;
```

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

### Get Basic AWS Connect Instance Information

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

This example demonstrates how to retrieve fundamental details of AWS Connect instances, including their alias, ID, ARN, status, identity management type, and region. This query is useful for gaining a quick overview of your Connect instance configurations and their operational state.

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

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

### Install and Use Steampipe Exporter CLI for AWS

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

Provides instructions and commands to install the Steampipe Exporter CLI for AWS and use it to export data from the `aws_wellarchitected_workload_share` table. This allows for 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_wellarchitected_workload_share
```

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

### Get Hourly Cost for Specific EC2 Instance in a Region

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

Queries hourly cost details for a specific EC2 instance within a given region. This example demonstrates filtering by `resource_id`, `dimension_key` ('REGION'), and `dimension_value`, along with `period_start` to analyze intraday spending patterns. It includes various cost and usage metrics and adheres to the 14-day data limit for resource-level information.

```Postgres FDWSQLite Extension
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,
  net_unblended_cost_amount::numeric::money as net_unblended_cost,
  amortized_cost_amount::numeric::money as amortized_cost,
  net_amortized_cost_amount::numeric::money as net_amortized_cost,
  usage_quantity_amount,
  usage_quantity_unit,
  normalized_usage_amount,
  normalized_usage_unit
from
  aws_cost_by_resource_hourly
where
  resource_id = 'i-1234567890abcdef0'
  and dimension_key = 'REGION'
  and dimension_value = 'us-east-1'
  and period_start >= current_date - interval '14 days'
order by
  period_start desc;
```

```Postgres FDWSQLite Extension
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,
  cast(net_unblended_cost_amount as decimal) as net_unblended_cost,
  cast(amortized_cost_amount as decimal) as amortized_cost,
  cast(net_amortized_cost_amount as decimal) as net_amortized_cost,
  usage_quantity_amount,
  usage_quantity_unit,
  normalized_usage_amount,
  normalized_usage_unit
from
  aws_cost_by_resource_hourly
where
  resource_id = 'i-1234567890abcdef0'
  and dimension_key = 'REGION'
  and dimension_value = 'us-east-1'
  and period_start >= date('now', '-14 days')
order by
  period_start desc;
```

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

### Install and Use Steampipe Exporter for AWS

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

Install the Steampipe Exporter CLI for AWS and export data from the aws_cloudwatch_log_stream table. The exporter allows data extraction without a database, and configuration can be passed via the --config argument.

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

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

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

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

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

Retrieves specific configuration details for each add-on version in AWS EKS. This allows for a deeper understanding of how each version is configured and functions.

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

### Description
Retrieves specific configuration details for each add-on version in AWS EKS. This allows for a deeper understanding of how each version is configured and functions.

### Method
SELECT

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **limit** (integer) - Optional - Limits the number of results returned.

#### Request Body
None

### 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 from the add-on configuration definitions.
- **addon_configuration_def_property_names** (jsonb) - Property names from the add-on configuration definitions.
- **addon_configuration_def_pattern_properties** (jsonb) - Pattern properties from the add-on configuration definitions.
- **addon_configuration_properties** (jsonb) - Properties of the add-on configuration.

#### Response Example
```json
{
  "addon_name": "kube-proxy",
  "addon_version": "1.28.1-eksbuild.1",
  "addon_configuration_def_description": "Tags to apply to all created volumes.",
  "addon_configuration_def_property_names": null,
  "addon_configuration_def_pattern_properties": null,
  "addon_configuration_properties": {
    "image": {
      "type": "string"
    }
  }
}
```
```

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

### List EC2 c5.2xlarge Pricing Offers (Linux, No Software)

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

Retrieves pricing offers for on-demand, shared EC2 c5.2xlarge instances running Linux without pre-installed software. Useful for cost planning and budgeting AWS resources.

```PostgreSQL
select
  term,
  purchase_option,
  lease_contract_length,
  unit,
  price_per_unit::numeric::money,
  currency,
  begin_range,
  end_range,
  effective_date,
  description,
  attributes ->> 'instanceType',
  attributes ->> 'vcpu',
  attributes ->> 'memory',
  attributes ->> 'operatingSystem',
  attributes ->> 'preInstalledSw'
from
  aws_pricing_product
where
  service_code = 'AmazonEC2'
  and filters = '{
  "regionCode": "eu-west-3",
  "locationType": "AWS Region",
  "instanceType": "c5.2xlarge",
  "operatingSystem": "Linux",
  "tenancy": "Shared",
  "preInstalledSw": "NA",
  "capacityStatus": "Used" }'::jsonb;
```

```SQLite
select
  term,
  purchase_option,
  lease_contract_length,
  unit,
  CAST(price_per_unit AS REAL) as price_per_unit,
  currency,
  begin_range,
  end_range,
  effective_date,
  description,
  json_extract(attributes, '$.instanceType'),
  json_extract(attributes, '$.vcpu'),
  json_extract(attributes, '$.memory'),
  json_extract(attributes, '$.operatingSystem'),
  json_extract(attributes, '$.preInstalledSw')
from
  aws_pricing_product
where
  service_code = 'AmazonEC2'
  and filters = '{
  "regionCode": "eu-west-3",
  "locationType": "AWS Region",
  "instanceType": "c5.2xlarge",
  "operatingSystem": "Linux",
  "tenancy": "Shared",
  "preInstalledSw": "NA",
  "capacityStatus": "Used" }';
```

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

### Install Steampipe Exporter CLI for AWS

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

Installs the Steampipe Exporter CLI for AWS using a shell script. This tool allows you to export data from AWS resources, such as Audit Manager controls, without needing a full Steampipe database setup.

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

```

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

### Retrieve Basic Cost Optimization Recommendations (SQL)

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

Fetches core details of cost optimization recommendations, including IDs, resource identifiers, estimated monthly savings, and savings percentages. This query provides a general overview of potential cost savings.

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

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

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

### Get AWS IAM OpenID Connect Providers

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

This example shows how to list all AWS IAM OpenID Connect providers. It utilizes the `aws_iam_open_id_connect_provider` table from the Steampipe AWS plugin.

```sql
SELECT
  arn,
  client_id_list,
  thumbprint_list
FROM
  aws_iam_open_id_connect_provider;

```

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

### Calculate Min, Max, and Average Daily Unblended Cost By Account

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

This example shows how to calculate the minimum, maximum, and average daily unblended cost for each AWS account. It groups the results by linked account ID and orders them. This query is valuable for monitoring the financial performance of different accounts and identifying cost optimization opportunities.

```PostgreSQL
select
  linked_account_id,
  min(unblended_cost_amount)::numeric::money as min,
  max(unblended_cost_amount)::numeric::money as max,
  avg(unblended_cost_amount)::numeric::money as average
from
  aws_cost_by_account_daily
group by
  linked_account_id
order by
  linked_account_id;
```

```SQLite
select
  linked_account_id,
  min(unblended_cost_amount) as min,
  max(unblended_cost_amount) as max,
  avg(unblended_cost_amount) as average
from
  aws_cost_by_account_daily
group by
  linked_account_id
order by
  linked_account_id;
```

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

### Find Recommendations Requiring Resource Restart (SQL)

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

Filters recommendations to find those that necessitate a resource restart for implementation. This is useful for planning maintenance windows and understanding implementation complexity.

```sql
select
  recommendation_id,
  resource_id,
  implementation_effort,
  restart_needed
from
  aws_costoptimizationhub_recommendation
where
  restart_needed = true;
```

```sql
select
  recommendation_id,
  resource_id,
  implementation_effort,
  restart_needed
from
  aws_costoptimizationhub_recommendation
where
  restart_needed = 1;
```

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

### Get Description for a Specific AWS IAM Action (Steampipe CLI)

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

This example shows how to retrieve the description for a specific IAM action, 's3:deleteobject', using the Steampipe CLI. This helps in understanding the functionality and implications of individual IAM actions within your AWS environment.

```sql
select
  description
from
  aws_iam_action
where
  action = 's3:deleteobject';
```

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

### Get IP Address Configuration of Service Discovery Instances

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

Retrieves the IP address configuration, including IPv4, IPv6, and port details, for service discovery instances. This query is useful for understanding the network setup and connectivity of discovered services.

```sql
select
  id,
  service_id,
  ec2_instance_id,
  instance_ipv4,
  instance_ipv6,
  instance_port
from
  aws_service_discovery_instance;
```

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

### List Build Phase Details (SQLite)

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

Retrieves detailed information about each build phase, including start and end times, duration, and status. This query uses the json_each function to iterate over the 'phases' JSON array for analysis. Useful for identifying build process bottlenecks.

```sql
select
  aws_codebuild_build.id,
  json_extract(p, '$.EndTime') as end_time,
  json_extract(p, '$.Contexts') as contexts,
  json_extract(p, '$.PhaseType') as phase_type,
  json_extract(p, '$.StartTime') as start_time,
  json_extract(p, '$.DurationInSeconds') as duration_in_seconds,
  json_extract(p, '$.PhaseStatus') as phase_status
from
  aws_codebuild_build,
  json_each(phases) as p;
```

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

### List AWS IAM Actions Matching a Pattern (Steampipe CLI)

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

This example demonstrates how to list IAM actions that match a pattern, specifically those starting with 's3:d'. It utilizes a `glob` function for pattern matching and joins with the `aws_iam_action` table. This is useful for discovering actions that fit a certain naming convention.

```sql
select
  a.action,
  a.description
from
  aws_iam_action as a,
  glob('s3:d*') as action_name
where
  a.action like action_name;
```

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

### Export AWS ECS Service Data with Steampipe CLI

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

Example command to export AWS ECS service data using the Steampipe Exporter CLI. This command requires the exporter to be installed and takes a configuration argument for authentication and resource targeting. Replace '<your_config>' with your actual Steampipe configuration.

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

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

### Review Full CloudFront Logging Configuration (PostgreSQL/SQLite)

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

Compares logging targets for CloudFront distributions, including S3 buckets, CloudWatch Log groups, and Firehose delivery streams. It displays the distribution ID, domain name, and the identified destination name.

```sql
select
  cf.id as distribution_id,
  cf.domain_name,
  s.name as source_name,
  d.id as delivery_id,
  d.delivery_destination_type,
  case
    when d.delivery_destination_type = 'S3' then b.name
    when d.delivery_destination_type = 'CloudWatchLogs' then split_part(d.delivery_destination_arn, ':', 7)
    when d.delivery_destination_type = 'Firehose' then split_part(d.delivery_destination_arn, '/', 2)
    else 'Unknown'
  end as destination_name
from
  aws_cloudfront_distribution cf
join
  aws_cloudwatch_log_delivery_source s
    on s.resource_arns @> to_jsonb(array[cf.arn])
    and s.service = 'cloudfront'
join
  aws_cloudwatch_log_delivery d
    on s.name = d.delivery_source_name
left join
  aws_s3_bucket b
    on b.arn = d.delivery_destination_arn
    and d.delivery_destination_type = 'S3';
```

```sql
select
  cf.id as distribution_id,
  cf.domain_name,
  s.name as source_name,
  d.id as delivery_id,
  d.delivery_destination_type,
  case
    when d.delivery_destination_type = 'S3' then b.name
    when d.delivery_destination_type = 'CloudWatchLogs' then split(d.delivery_destination_arn, ':')[7]
    when d.delivery_destination_type = 'Firehose' then split(d.delivery_destination_arn, '/')[2]
    else 'Unknown'
  end as destination_name
from
  aws_cloudwatch_log_delivery_source s
join
  json_each(s.resource_arns) as ra
    on ra.value = cf.arn
join
  aws_cloudfront_distribution cf
    on true
join
  aws_cloudwatch_log_delivery d
    on s.name = d.delivery_source_name
left join
  aws_s3_bucket b
    on b.arn = d.delivery_destination_arn
    and d.delivery_destination_type = 'S3'
where
  s.service = 'cloudfront';
```

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

### Get AWS EKS Add-on Configuration Details (SQL - Postgres FDWSQLite Extension)

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

Fetches specific configuration details for AWS EKS add-on versions, including descriptions and properties from the addon_configuration JSONB field. This query uses the -> and ->> operators common in Postgres-like SQL dialects and is limited to the first 10 results.

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

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

### Get VPC and Subnet Info for AWS RDS DB Instance Automated Backups

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

Retrieves VPC and subnet information for each AWS RDS DB instance automated backup. This query joins automated backups with VPC data to analyze infrastructure setup and backup distribution across VPCs.

```sql
select
  b.arn,
  b.vpc_id,
  v.cidr_block,
  v.is_default,
  v.instance_tenancy
from
  aws_rds_db_instance_automated_backup as b,
  aws_vpc as v
where
  v.vpc_id = b.vpc_id;
```

```sql
select
  b.arn,
  b.vpc_id,
  v.cidr_block,
  v.is_default,
  v.instance_tenancy
from
  aws_rds_db_instance_automated_backup as b
join
  aws_vpc as v
on
  v.vpc_id = b.vpc_id;
```

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

### Get AWS API Gateway v2 API Details

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

Retrieves essential details of your AWS API Gateway v2 configurations, including name, ID, endpoint, protocol type, and key/route selection expressions. This query helps in understanding the fundamental setup of your APIs.

```sql
select
  name,
  api_id,
  api_endpoint,
  protocol_type,
  api_key_selection_expression,
  route_selection_expression
from
  aws_api_gatewayv2_api;
```

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

### Run Steampipe Exporter with Configuration (Shell)

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

Executes the Steampipe Exporter CLI, passing a configuration string and specifying the resource to export. This command requires the exporter binary to be installed and accessible in the PATH. The output is the data for the specified resource.

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

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

### Export AWS QuickSight Data using Steampipe Exporter CLI

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

Demonstrates how to export AWS QuickSight dataset information using the Steampipe Exporter CLI. The `--config` argument allows passing specific configuration details for the export.

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

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

### Review Input and Output Schemas for AWS Glue ML Transforms

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

Examines the input and output schemas for AWS Glue ML Transforms to understand the data structure requirements for each transform. This includes input record tables, schema details, and parameters.

```sql
select
  name,
  transform_id,
  input_record_tables,
  schema,
  parameters
from
  aws_glue_ml_transform;
```

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

### Get DLM Lifecycle Policies with Specific Tags

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

This example shows how to filter AWS DLM lifecycle policies based on specific tags, retrieving policies that have a tag with the key 'environment' and value 'production'. This is useful for managing policies associated with particular resources or environments.

```sql
SELECT
  name,
  policy_id,
  tags
FROM
  aws_dlm_lifecycle_policy
WHERE
  tags ->> 'environment' = 'production';
```

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

### List Build File System Location Details (SQLite)

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

Retrieves details about the file system locations used in builds, including identifier, location, mount options, and type. This query uses json_each to iterate over the 'file_system_locations' JSON array. Aids in understanding build organization.

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

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

### List AWS IAM Actions Matching a Pattern (SQL LIKE)

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

This example shows an alternative method to list IAM actions matching a pattern, specifically those starting with 's3:d%'. It uses the standard SQL `LIKE` operator for filtering directly on the `aws_iam_action` table. This provides a more direct way to filter actions by a wildcard pattern.

```sql
select
  a.action,
  a.description
from
  aws_iam_action as a
where
  a.action like 's3:d%';
```

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

### Get Daily AWS Costs by Record Type

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

Retrieves daily AWS costs, including linked account, record type, period start, and various cost amounts. This query helps understand spending patterns across different categories. It supports casting cost amounts to numeric or real types for further analysis.

```sql
select
  linked_account_id,
  record_type,
  period_start,
  blended_cost_amount::numeric::money,
  unblended_cost_amount::numeric::money,
  amortized_cost_amount::numeric::money,
  net_unblended_cost_amount::numeric::money,
  net_amortized_cost_amount::numeric::money
from
  aws_cost_by_record_type_daily
order by
  linked_account_id,
  period_start;
```

```sql
select
  linked_account_id,
  record_type,
  period_start,
  CAST(blended_cost_amount AS REAL) AS blended_cost_amount,
  CAST(unblended_cost_amount AS REAL) AS unblended_cost_amount,
  CAST(amortized_cost_amount AS REAL) AS amortized_cost_amount,
  CAST(net_unblended_cost_amount AS REAL) AS net_unblended_cost_amount,
  CAST(net_amortized_cost_amount AS REAL) AS net_amortized_cost_amount
from
  aws_cost_by_record_type_daily
order by
  linked_account_id,
  period_start;
```

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

### List All AWS QuickSight Datasets

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

Retrieves the name, dataset ID, creation time, and last updated time for all datasets in the AWS QuickSight environment. This query helps in getting a comprehensive overview of available data sets.

```sql
select
  name,
  dataset_id,
  created_time,
  last_updated_time
from
  aws_quicksight_data_set;
```

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

### Export SageMaker Training Job Data using Exporter CLI

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

Demonstrates how to use the Steampipe Exporter CLI to export data for AWS SageMaker training jobs. The `--config` argument allows passing specific configurations to the command.

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

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

### Get Daily Amortized Cost Details (PostgreSQL)

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

This query retrieves daily amortized cost details, including linked account ID, record type, period start, and amortized cost amount, filtered by a specific date range. It uses PostgreSQL syntax and casts the cost amount to money for better readability. Dependencies include the Steampipe AWS plugin.

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

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

### List Build Phase Details (Postgresql)

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

Retrieves detailed information about each build phase, including start and end times, duration, and status. This query utilizes the jsonb_array_elements function to expand the 'phases' JSONB array for analysis. Useful for identifying build process bottlenecks.

```sql
select
  id,
  p ->> 'EndTime' as end_time,
  p ->> 'Contexts' as contexts,
  p ->> 'PhaseType' as phase_type,
  p ->> 'StartTime' as start_time,
  p ->> 'DurationInSeconds' as duration_in_seconds,
  p ->> 'PhaseStatus' as phase_status
from
  aws_codebuild_build,
  jsonb_array_elements(phases) as p;
```

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

### Run Steampipe Exporter with Configuration

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

Executes the Steampipe exporter with a specified configuration and targets a specific resource. The '--config' argument takes the configuration details, and the last argument specifies the resource to export.

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

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

### Retrieve Basic AWS App Runner Service Information

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

This example demonstrates how to retrieve fundamental details for all AWS App Runner services within your account. It selects the service name, Amazon Resource Name (ARN), AWS region, creation timestamp, and last update timestamp. This query is useful for getting an overview of your deployed services.

```sql
select
  service_name,
  arn,
  region,
  created_at,
  updated_at
from
  aws_app_runner_service;
```

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

### List Build File System Location Details (Postgresql)

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

Retrieves details about the file system locations used in builds, including identifier, location, mount options, and type. This query expands the 'file_system_locations' JSONB array using jsonb_array_elements. Helps in understanding build organization.

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

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

### Analyze AWS Managed Instance Patch Installation Status

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

Analyze the status of patch installation in AWS managed instances. This query helps identify instances where patch installation has failed, aiding in timely troubleshooting and ensuring system security. It retrieves instance ID, baseline ID, operation type, patch group, and counts of failed, installed, and other installed patches.

```sql
select
  instance_id,
  baseline_id,
  operation,
  patch_group,
  failed_count,
  installed_count,
  installed_other_count
from
  aws_ssm_managed_instance_patch_state;
```

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

### Install Steampipe AWS Plugin

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

Command to install the Steampipe AWS plugin. This command requires the Steampipe CLI to be installed and configured.

```bash
steampipe plugin install aws
```

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

### Export Data with Steampipe Exporter CLI using Configuration

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

Demonstrates how to use the Steampipe Exporter CLI to export specific data. The `--config` argument is used to pass connection and export details, followed by the resource to export.

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

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

### Get Build Source Details (Postgresql)

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

Retrieves source configuration details for builds, such as authentication method, buildspec, source identifier, and location. This query selects specific fields from the 'source' JSONB object. Helpful for analyzing build configurations and security.

```sql
select
  id,
  source ->> 'Auth' as source_auth,
  source ->> 'BuildStatusConfig' as source_BuildStatusConfig,
  source ->> 'Buildspec' as source_buildspec,
  source ->> 'GitCloneDepth' as source_git_clone_depth,
  source ->> 'GitSubmodulesConfig' as source_git_submodules_config,
  source ->> 'GitCloneDepth' as source_git_clone_depth,
  source ->> 'InsecureSsl' as source_insecure_ssl,
  source ->> 'Location' as source_location,
  source ->> 'ReportBuildStatus' as source_report_build_status,
  source ->> 'SourceIdentifier' as source_identifier,
  source ->> 'Type' as source_type
from
  aws_codebuild_build;
```

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

### Install Steampipe Postgres FDW for AWS Plugin

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

Installs the Steampipe Postgres Foreign Data Wrapper (FDW) for the AWS plugin using a provided installation script. This allows querying AWS resources directly from PostgreSQL. The script handles downloading and installing necessary files based on OS, architecture, and Postgres version.

```Installation command
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- aws
```

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

### List runtime settings for each function

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

Provides details on the runtime, handler, and architectures for each AWS Lambda function, aiding in performance optimization and configuration understanding.

```APIDOC
## GET /aws_lambda_function/runtime_settings

### Description
Retrieves the runtime settings (runtime, handler, architectures) for all AWS Lambda functions.

### Method
GET

### Endpoint
/aws_lambda_function/runtime_settings

### Query Parameters
None

### Request Body
None

### Request Example
```
GET /aws_lambda_function/runtime_settings
```

### Response
#### Success Response (200)
- **name** (text) - The name of the Lambda function.
- **runtime** (text) - The runtime environment for the Lambda function (e.g., 'nodejs18.x').
- **handler** (text) - The function within your code that Lambda calls to begin execution.
- **architectures** (jsonb) - The instruction set architecture that the function supports.

#### Response Example
```json
[
  {
    "name": "my-function",
    "runtime": "nodejs18.x",
    "handler": "index.handler",
    "architectures": ["x86_64"]
  }
]
```
```

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

### List Monthly AWS Discounts and Credits by Account (Postgres)

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

This query lists monthly AWS account discounts and credits using Steampipe with Postgres. It requires the Steampipe AWS plugin. The input is the `aws_cost_usage` table, and the output includes period start date, account ID, record type, and net unblended cost formatted as money, filtered for 'DiscountedUsage' and 'Credit' record types.

```sql
select
  period_start,
  dimension_1 as account_id,
  dimension_2 as record_type,
  net_unblended_cost_amount::numeric::money
from
  aws_cost_usage
where
  granularity = 'MONTHLY'
  and dimension_type_1 = 'LINKED_ACCOUNT'
  and dimension_type_2 = 'RECORD_TYPE'
  and dimension_2 in ('DiscountedUsage', 'Credit')
order by
  dimension_1,
  period_start;
```

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

### Retrieve Schedules with Start and End Dates

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

Identifies schedules that have both start and end dates defined.

```APIDOC
## GET /aws_scheduler_schedule

### Description
Fetches schedules that have both a start date and an end date specified.

### Method
GET

### Endpoint
/aws_scheduler_schedule

### Query Parameters
- **start_date** (timestamp with time zone) - Required - Filters schedules that have a start date defined.
- **end_date** (timestamp with time zone) - Required - Filters schedules that have an end date defined.

### Request Example
```sql
SELECT
  name,
  start_date,
  end_date,
  state
FROM
  aws_scheduler_schedule
WHERE
  start_date IS NOT NULL
  AND end_date IS NOT NULL;
```

### Response
#### Success Response (200)
- **name** (text) - The name of the schedule.
- **start_date** (timestamp with time zone) - The date when the schedule becomes effective.
- **end_date** (timestamp with time zone) - The date after which the schedule will no longer invoke its target.
- **state** (text) - The current state of the schedule.

#### Response Example
```json
[
  {
    "name": "my-bounded-schedule",
    "start_date": "2023-01-01T00:00:00Z",
    "end_date": "2023-12-31T23:59:59Z",
    "state": "ENABLED"
  }
]
```
```

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

### Retrieve Basic AWS QuickSight VPC Connection Info

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

Fetches fundamental details of QuickSight VPC connections, including name, ID, status, and associated VPC ID. This query is useful for a general overview of existing VPC connections.

```sql
select
  name,
  vpc_connection_id,
  status,
  availability_status,
  vpc_id
from
  aws_quicksight_vpc_connection;
```

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

### Exporter CLI Installation and Usage

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

Instructions on how to install and use the Steampipe Exporter CLI to extract data for `aws_organizations_delegated_services_for_account`.

```APIDOC
## Export AWS Organizations Delegated Services

### Description
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

### Installation

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the `steampipe_export_installer.sh` script:

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

### Usage

You can pass the configuration to the command with the `--config` argument:

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

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

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

### List All AWS Serverless Applications

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

Retrieves a list of all serverless applications in the AWS Serverless Application Repository, including their name, ARN, author, creation time, and description. This query helps in understanding the distribution of serverless applications.

```sql
select
  name,
  arn,
  author,
  creation_time,
  description
from
  aws_serverlessapplicationrepository_application;
```

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

### Install Steampipe SQLite Extension for AWS Plugin

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

Installs the Steampipe SQLite Extension for the AWS plugin using a provided installation script. This makes AWS tables available as virtual tables within SQLite. The script determines the correct package for your OS and architecture.

```Installation command
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- aws
```

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

### Count Patches Installed Outside AWS Baseline

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

Determine the number of patches installed outside of the defined baseline in AWS managed instances. This helps in understanding potential security vulnerabilities or inconsistencies in system management. It returns the instance ID, baseline ID, and the count of patches installed outside the baseline.

```sql
select
  instance_id,
  baseline_id,
  installed_other_count
from
  aws_ssm_managed_instance_patch_state;
```

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

### List AWS QuickSight Datasets with SPICE Import Mode

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

Identifies datasets that exclusively use SPICE (Super-fast, Parallel, In-memory Calculation Engine) for data import. This is useful for optimizing query performance by leveraging QuickSight's in-memory engine.

```sql
select
  name,
  dataset_id,
  import_mode
from
  aws_quicksight_data_set
where
  import_mode = 'SPICE';
```

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

### Count Patches Installed from AWS Baseline

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

Determine the total number of installed patches from a specific baseline in AWS managed instances. This helps in assessing the level of system updates and identifying systems that may be lagging, aiding in security and performance maintenance. It returns the instance ID, baseline ID, and the count of installed patches.

```sql
select
  instance_id,
  baseline_id,
  installed_count
from
  aws_ssm_managed_instance_patch_state;
```

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

### Retrieve AWS Schedules with Start and End Dates

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

Identifies AWS schedules that have both a start date and an end date defined. This helps in managing schedules with a specific operational period.

```Postgresql
select
  name,
  start_date,
  end_date,
  state
from
  aws_scheduler_schedule
where
  start_date is not null
  and end_date is not null;
```

```Sqlite
select
  name,
  start_date,
  end_date,
  state
from
  aws_scheduler_schedule
where
  start_date is not null
  and end_date is not null;
```

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

### List URL Configuration Details for AWS Lambda Aliases

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

Examines the URL configurations linked to each AWS Lambda alias. This allows for understanding and managing how functions are accessed, enhancing control over serverless applications.

```sql
select
  name,
  function_name,
  jsonb_pretty(url_config) as url_config
from
  aws_lambda_alias;
```

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

### Get AWS EKS Add-on Names and Versions (SQL)

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

Retrieves the name, version, and type of all available add-ons for AWS EKS. This query helps in identifying add-ons and their versions for compatibility checks and performance monitoring.

```sql
select
  addon_name,
  addon_version,
  type
from
  aws_eks_addon_version;
```

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

### Query AWS S3 Account Settings

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

This example demonstrates how to query all available information for AWS S3 account settings using the aws_s3_account_settings table. It selects all columns for the resource.

```sql
select
  *
from
  aws_s3_account_settings
```

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

### Retrieve AWS EFS File Systems

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

This example shows how to list all AWS EFS file systems. It uses the `aws_efs_file_system` table available through the Steampipe AWS plugin.

```sql
SELECT
  file_system_id,
  creation_token,
  life_cycle_state,
  performance_mode
FROM
  aws_efs_file_system;

```

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

### Install Steampipe Exporter CLI for AWS

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

Installs the Steampipe Exporter CLI for AWS using a shell script. This tool allows for standalone data extraction from Steampipe plugins without requiring a database.

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

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

### Get All AWS Organizational Units

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

Retrieves basic information for all organizational units within an AWS Organization. This query is useful for getting an overview of the OU structure. It returns fields like name, ID, ARN, parent ID, title, and aliases.

```sql
select
  name,
  id,
  arn,
  parent_id,
  title,
  akas
from
  aws_organizations_organizational_unit;
```
Tokens:
Raw