Advanced query

You can use AWS Config to query the current configuration state of AWS resources based on configuration properties for a single account and region, or across multiple accounts and regions. You can perform ad hoc, property-based queries against current AWS resource state metadata across all resources that AWS Config supports. The advanced query feature provides a single query endpoint and a powerful query language to get current resource state metadata without performing service-specific describe API calls. You can use configuration aggregators to run the same queries from a central account across multiple accounts and AWS regions.

AWS Config uses a subset of structured query language (SQL) SELECT syntax to perform property-based queries and aggregations on the current configuration item (CI) data. The queries range in complexity from simple matches against tag and/or resource identifiers, to more complex queries, such as viewing all S3 buckets that have versioning disabled. This allows you to query exactly the current resource state you need without performing AWS service-specific API calls.

This lab assumes that AWS Config is currently enabled and collecting data in your environment.

Create the desired instance type rule

Before beginning, you will need to have a rule that will be used for querying resources using the advanced query interface.

  1. Browse to the rules page and then click Add rule. Add Rule 1
  2. Then search for the desired-instance-type rule and click Next. Add Rule 2
  3. Scroll to the bottom of the page and in the value field, next to the instanceType key, enter this string: t3.xlarge,m5.large Add Rule 3

Then click Next and complete the rule creation wizard.

Create the Lab Environment

Before proceeding, you must create a CloudFormation stack that includes the resources required for this lab.

File name Purpose Template download
advancedquery.yaml Creates the resources used in this lab Download the lab CloudFormation template

The stack will create these resources for you:

Lab Architecture Diagram

To create this stack, open the CloudFormation Console and then click on Create Stack, and then With new resources (standard).

Create stack

When prompted for the template, click on Upload a template file, and then provide the path to the file you just downloaded.

Specify template location

On the next pages, give the stack a unique name (such as AdvancedQueryLab), and enter the same S3 bucket as the one you created in the Setup section.

Please wait several minutes before proceeding as the resources created above take a short time to initialize.

Execute Basic Queries

Now that you have the lab stack and rule created, your account is ready to begin using advanced queries. Start by going to the Advanced queries portion of the Config console.

Advanced Query 1

Click in the search box, and then click Name, and then select EC2 instances by type. Finally, click on the Copy to editor button.

Advanced Query 2

Change the instance type on the last line to t3.small. The complete, new query will look like this:

SELECT
  resourceId,
  resourceName,
  resourceType,
  configuration.instanceType,
  tags,
  availabilityZone
WHERE
  resourceType = 'AWS::EC2::Instance'
  AND configuration.instanceType = 't3.small'

These results are simple, but do not show the relationships between resources. Let’s run a more interesting query that reveals more about the environment that the instance has been created in. Copy the resourceId from the previous query and execute a new one with that as a parameter.

SELECT
  *
WHERE
  relationships.resourceId = 'your server id'

Scrolling-down to the output you can now see a more detailed list of resources that are related to this server, including its VPC, attached EBS volume, subnet, security group, elastic network interface, and the CloudFormation stack that created it.

You can create groupings and aggregations through Advanced Query as well:

SELECT
    configuration.complianceType,
    COUNT(*)
WHERE
    resourceType = 'AWS::Config::ResourceCompliance'
GROUP BY
    configuration.complianceType

And unused EBS volumes:

SELECT
  resourceId,
  accountId,
  awsRegion,
  resourceType,
  configuration.volumeType,
  configuration.size,
  resourceCreationTime,
  tags,
  configuration.encrypted,
  configuration.availabilityZone,
  configuration.state.value
WHERE
  resourceType = 'AWS::EC2::Volume'
  AND configuration.state.value <> 'in-use'

The results from any and all of these queries can be exported to either CSV or JSON using the Export as button.

Advanced Query 3

Finally, you can send queries to Config using the AWS Command Line Interface. This approach gives you a highly extensible method of scripting your data extraction. A simple example is this command:

aws configservice select-resource-config --expression "SELECT resourceId WHERE resourceType='AWS::EC2::Instance'" --output yaml

Which will return formatted YAML like this:

QueryInfo:
  SelectFields:
  - Name: resourceId
Results:
- '{"resourceId":"i-025aa9696215499"}'

… whereas the text output format is designed for easier machine parsing:

SELECTFIELDS    resourceId
RESULTS {"resourceId":"i-025aa9696215499"}

A full list of output formats is available here: https://docs.aws.amazon.com/cli/latest/userguide/cli-usage-output.html

What did we learn?

  • How to use advanced query to view resources, their relationships, and their compliance status