Have You Heard About AWS Config Advanced Queries?

#compliance #inventory #assets #config

Well, I haven't until recently.
So what are they and why are they useful?

Let's first recap on what AWS Config is and how to use queries on top of that

AWS Config Introduction

AWS Config is known to most as a continuous compliance monitoring tool that tracks the configuration of resources and also their compliance against a set of Config rules that might be further grouped in Conformance packs.

AWS Config Aggregator

Where AWS Config really excels is a multi-account multi-region environment that is nowadays the most common scenario for most companies. To enable AWS Config and its features in such an environment you first need to create an Aggregator. It is a resource that collects data about the resources supported by AWS Config across multiple regions and multiple accounts usually within one AWS Organization. Most often this is done for the whole organization which means that resources from all existing and also future AWS accounts will be visible in the Aggregator.

Two side notes worth mentioning here.

If you use Control Tower to govern your multi account environment, the Organization-wide aggregator is setup automatically.

It is a best practice to first setup a Delegated Administrator account for AWS Config and setup the actual aggregator in this account instead of using the Management AWS account. This is to further enforce the multi-account narrative and limit using the Management account only to the bare essentials.

This is what a properly setup Aggregator looks like. It shows a summary info about your landscape and most noncompliant rules or accounts.

AWS Config Advanced Queries

This feature is not really new, quite the opposite, however it's been somehow flying under the radar for even though I've set up and used AWS Config on numerous occasions.

Where it comes handy is when you want to do a one-time query on your resources and get a consolidated report on their configuration or just certain properties. This is quite the opposite of what AWS Config does by its nature - a continuous tracking of configuration changes and compliance.

Three Ways to Ask

There are essentially three ways to get the desired results.

Built-in Queries

The easiest way to start is to use the existing built-in queries after clicking on the Advanced Queries in the Config console.
A few dozens of queries are provided that cover some most of the most common queries and are good to get a grasp of the SQL-like syntax.
You will also see your own queries here if you decide to save them. Then the Creator column will show Custom instead of AWS.

Write Your Own Queries

The SQL syntax used by queries makes it easy to write your own queries rather quickly. What wasn't very intuitive previously was the actual resource schema, i.e. the list of resources, their properties and data types. It is published on GitHub, however it wasn't that easy to find. Finally, some weeks ago a link has appeared directly in the Query Editor:

AI-assisted

Some time ago there used to be a Natural language query processor section which is still mentioned in the documentation. It allowed you to write queries in a natural language without knowing the syntax, resource types or their properties and still get the desired results.

Recently this feature has been switched off and now you are referred to using Amazon Q Developer instead.

You can use Amazon Q developer as a VSCode extension and ask it to write your queries in a chat. The only downside here is that you need to manually copy and paste the query in the Config web console. Here a sample request:

Create an AWS Config Advanced query to list all EC2 instances, their id, name and status, which are not running an ami id ami-00xxyy

The Q extension returns the following query which can be pasted in the AWS Console

SELECT
  resourceId,
  resourceName,
  configuration.state.name
WHERE
  resourceType = 'AWS::EC2::Instance'
  AND configuration.imageId != 'ami-00xxyy'

There are 40 results and they seem to be correct. You can add additional fields in the SELECT part or further conditions in the WHERE clause.

An easier way seemingly is to ask directly in the AWS Console as instructed by a note in AWS Config, however this seems to be working only for the resources in the current account and furthermore is limited by the paginations. It returned only 5(!) results instead of 40 from Although the results look nicer than a plain table, asking Q in VSCode seems to be more reliable way to work with AWS Config Advanced Queries.