Table: awscfn_resource - Query AWS CloudFormation Resources using SQL

AWS CloudFormation is a service that helps you model and set up your Amazon Web Services resources. You can create a template that describes the AWS resources that you want to use. The service then takes care of provisioning and configuring those resources for you.

Table Usage Guide

The awscfn_resource table provides insights into AWS resources in a stack. As a DevOps engineer, explore resource-specific details through this table, including the logical and physical resource IDs and the type of resource. Utilize it to uncover information about resources, such as their current status, stack ID, and the time when the resource was last updated.

The properties_src column contains the raw resource properties, while the properties column uses AWS' goformation library to resolve CloudFormation instrinsic functions and references. In some cases, goformation is unable to parse the CloudFormation template or is unable to resolve property values.

For example, the sample AutoScalingScheduledAction CloudFormation template includes a SecurityGroup resource:

"InstanceSecurityGroup": {
  "Type": "AWS::EC2::SecurityGroup",
  "Properties": {
    "GroupDescription": "Enable SSH access and HTTP access on the configured port",
    "SecurityGroupIngress": [ {
      "IpProtocol": "tcp",
      "FromPort": "22",
      "ToPort": "22",
      "CidrIp": { "Ref": "SSHLocation" }
    }, {
      "IpProtocol": "tcp",
      "FromPort": "80",
      "ToPort": "80",
      "CidrIp": "0.0.0.0/0"
    } ],
    "VpcId": { "Ref" : "VpcId" }
  }
}

Because the FromPort and ToPort property values are of type String (which is valid per CloudFormation), but the AWS::EC2::SecurityGroup Ingress schema defines their type as Integer, goformation is unable to parse the CloudFormation template and properties will be returned as null:

select
  name,
  jsonb_pretty(properties_src) as properties_src,
  properties
from
  awscfn_resource
where
  name = 'InstanceSecurityGroup';

+-----------------------+-------------------------------------------------+------------+
| name                  | properties_src                                  | properties |
+-----------------------+-------------------------------------------------+------------+
| InstanceSecurityGroup | {                                               | <null>     |
|                       |     "VpcId": {                                  |            |
|                       |         "Ref": "VpcId"                          |            |
|                       |     },                                          |            |
|                       |     "GroupDescription": "Enable SSH access...", |            |
|                       |     "SecurityGroupIngress": [                   |            |
|                       |         {                                       |            |
|                       |             "CidrIp": {                         |            |
|                       |                 "Ref": "SSHLocation"            |            |
|                       |             },                                  |            |
|                       |             "ToPort": "22",                     |            |
|                       |             "FromPort": "22",                   |            |
|                       |             "IpProtocol": "tcp"                 |            |
|                       |         },                                      |            |
|                       |         {                                       |            |
|                       |             "CidrIp": "0.0.0.0/0",              |            |
|                       |             "ToPort": "80",                     |            |
|                       |             "FromPort": "80",                   |            |
|                       |             "IpProtocol": "tcp"                 |            |
|                       |         }                                       |            |
|                       |     ]                                           |            |
|                       | }                                               |            |
+-----------------------+-------------------------------------------------+------------+

Examples

Basic info

Analyze the settings of AWS CloudFormation resources to understand their types and configurations. This can be particularly useful to assess the elements within your infrastructure and their current status.

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource;

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource;

List AWS IAM users

Explore which AWS Identity and Access Management (IAM) users are active in your system. This provides a comprehensive view of user access, aiding in security and compliance management.

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource
where
  type = 'AWS::IAM::User';

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource
where
  type = 'AWS::IAM::User';

List AWS CloudTrail trails that are not encrypted

Explore which AWS CloudTrail trails lack encryption to enhance security measures. This helps in identifying potential vulnerabilities and ensuring compliance with security best practices.

select
  name,
  path
from
  awscfn_resource
where
  type = 'AWS::CloudTrail::Trail'
  and (
    (
      properties is not null
      and properties -> 'KMSKeyId' is null
    )
    or properties_src -> 'KMSKeyId' is null
  );

select
  name,
  path
from
  awscfn_resource
where
  type = 'AWS::CloudTrail::Trail'
  and (
    (
      properties is not null
      and json_extract(properties, '$.KMSKeyId') is null
    )
    or json_extract(properties_src, '$.KMSKeyId') is null
  );

Get S3 bucket BucketName property value

Determine the default name assigned to your AWS S3 bucket resources. This is useful for keeping track of your buckets and ensuring they are named according to your organizational standards. For instance, if a CloudFormation template is defined as:

Parameters:
  WebBucketName:
    Type: String
    Default: 'TestWebBucket'
Resources:
  DevBucket:
    Type: "AWS::S3::Bucket"
    Condition: CreateDevBucket
    Properties:
      AccessControl: PublicRead
      BucketName: !Ref WebBucketName
      WebsiteConfiguration:
        IndexDocument: index.html

select
  name as resource_map_name,
  type as resource_type,
  properties_src ->> 'BucketName' as bucket_name_src,
  default_value as bucket_name
from
  awscfn_resource
where
  type = 'AWS::S3::Bucket';

select
  name as resource_map_name,
  type as resource_type,
  json_extract(properties_src, '$.BucketName') as bucket_name_src,
  default_value as bucket_name
from
  awscfn_resource
where
  type = 'AWS::S3::Bucket';

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

Schema for awscfn_resource

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
condition	text		Specifies the resource conditions.
creation_policy	jsonb		Specifies the associated creation_policy with a resource to prevent its status from reaching create complete until AWS CloudFormation receives a specified number of success signals or the timeout period is exceeded.
deletion_policy	text		With the deletion_policy attribute you can preserve, and in some cases, backup a resource when its stack is deleted. You specify a deletion_policy attribute for each resource that you want to control.
depends_on	text		With the depends_on attribute you can specify that the creation of a specific resource follows another. When you add a depends_on attribute to a resource, that resource is created only after the creation of the resource specified in the depends_on attribute.
metadata	jsonb		The metadata attribute enables you to associate structured data with a resource. By adding a metadata attribute to a resource, you can add data in JSON or YAML to the resource declaration.
name	text		An identifier for the resource.
path	text	=	Path to the file.
properties	jsonb		Specifies the resource properties with calculated values as per given condition or parameter reference.
properties_src	jsonb		Specifies the resource properties defined in the template.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
start_line	bigint		Starting line number.
type	text		The resource type identifies the type of resource that you are declaring.
update_policy	jsonb		Use the update_policy attribute to specify how AWS CloudFormation handles updates to specific resources.
update_replace_policy	text		Use the update_replace_policy attribute to retain or, in some cases, backup the existing physical instance of a resource when it's replaced during a stack update operation.

Export

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.

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:

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

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

steampipe_export_awscfn --config '<your_config>' awscfn_resource