Daily Hack #day74 - SQL in AWS Config

AWS Config allows you to view all your cloud resources at a glance, track how their configuration changes over time, and establish configuration rules that automatically check whether your services match the desired configuration settings. Any violation of the rules you defined will trigger an alert informing you about non-compliant resources.

But AWS Config is not only a great resource to enforce compliance. It also gives you an overview of all resources in your AWS account. One of the most impressive features that I’ve recently encountered on AWS is the SQL query editor within the AWS Config. It allows you to easily group your resources by service or filter for only resources from a specific region.

Here is an example of a query:

SELECT
  resourceId,
  resourceType,
  awsRegion,
  resourceCreationTime,
  tags,
  configuration.state.value
WHERE
  resourceType NOT IN ('AWS::EC2::SecurityGroup',
    'AWS::EC2::Subnet', 'AWS::EC2::VPC',
    'AWS::EC2::NetworkAcl', 'AWS::EC2::RouteTable')
ORDER BY
  resourceType

For instance, in the query above, we are retrieving all resources with corresponding ID, region name, time of creation, tags, and current state while filtering out all network and security group resources.

Feel free to copy / modify this query to suit your needs.