# Querying the Current Configuration State of AWS Resources with AWS Config
| |
| --- |
| Introducing a preview feature for advanced queries that allows you to use generative artificial intelligence (generative AI) capabilities to enter prompts in plain English and convert them into a ready-to-use query format. For more information, see [Natural language query processor for advanced queries](https://docs.aws.amazon.com/config/latest/developerguide/query-assistant.html). |
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 property-based queries against current AWS resource state metadata across a list of resources that AWS Config supports. For more information on the list of supported resource types, see [Supported Resource Types for Advanced Queries](https://github.com/awslabs/aws-config-resource-schema/tree/master/config/properties/resource-types).
*Advanced queries* provides a single query endpoint and a 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.
**Topics**
+ [Features](#query-features)
+ [Query Components](query-components.md)
+ [Query Editor (Console)](query-using-sql-editor-console.md)
+ [Query Editor (AWS CLI)](query-using-sql-editor-cli.md)
+ [Natural language query processor](query-assistant.md)
+ [Examples Queries](example-query.md)
+ [Example Relationship Queries](examplerelationshipqueries.md)
+ [Limitations](#query-limitations)
+ [CIDR notation/IP range](#query-cidr-notation)
+ [Multiple properties within an array](#array-property-query-behavior)
+ [Region Support](#query-regionsupport)
## Features
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 matches against tag and/or resource identifiers, to more complex queries, such as viewing all Amazon 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.
It supports aggregation functions such as `AVG`, `COUNT`, `MAX`, `MIN`, and `SUM`.
You can use advanced query for:
+ Inventory management; for example, to retrieve a list of Amazon EC2 instances of a particular size.
+ Security and operational intelligence; for example, to retrieve a list of resources that have a specific configuration property enabled or disabled.
+ Cost optimization; for example, to identify a list of Amazon EBS volumes that are not attached to any EC2 instance.
+ Compliance data; for example, to retrieve a list of all your conformance packs and their compliance status.
For information about how to use the AWS SQL Query Language, see [What Is SQL (Structured Query Language)?](https://aws.amazon.com/what-is/sql/).
# Query Components for AWS Config
The SQL `SELECT` query components for AWS Config advanced queries are as follows.
## Synopsis
```
SELECT property [, ...]
[ WHERE condition ]
[ GROUP BY property ]
[ ORDER BY property [ ASC | DESC ] [, property [ ASC | DESC ] ...] ]
```
## Parameters
**[ WHERE condition ]**
Filters results according to the `condition` you specify.
**Comparison operators**
+ `=` (equals)
+ `IN` (list membership)
+ `BETWEEN` (range check)
**Logic operators**
+ `AND`
+ `OR`
+ `NOT`
**[ GROUP BY property ]**
Aggregates the result set into groups of rows with matching values for the given property.
The GROUP BY clause is applicable to aggregations.
**[ ORDER BY property [ ASC \$1 DESC ] [, property [ ASC \$1 DESC ] ...] ]**
Sorts a result set by one or more output `properties`.
When the clause contains multiple properties, the result set is sorted according to the first `property`, then according to the second `property` for rows that have matching values for the first property, and so on.
## Examples
```
SELECT resourceId WHERE resourceType='AWS::EC2::Instance'
```
```
SELECT configuration.complianceType, COUNT(*) WHERE resourceType = 'AWS::Config::ResourceCompliance' GROUP BY configuration.complianceType
```
# Query Using the SQL Query Editor for AWS Config (Console)
| |
| --- |
| Introducing a preview feature for advanced queries that allows you to use generative artificial intelligence (generative AI) capabilities to enter prompts in plain English and convert them into a ready-to-use query format. For more information, see [Natural language query processor for advanced queries](https://docs.aws.amazon.com/config/latest/developerguide/query-assistant.html). |
You can either use AWS sample queries or you can create your own query called as custom queries.
## Considerations
**Prerequisites**
If you are using the one of the following AWS managed policies, you will have the necessary permissions to run and save a query: [AWSServiceRoleForConfig](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWSServiceRoleForConfig) (service-linked role) or [AWS\$1ConfigRole](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWS_ConfigRole).
Otherwise, you must have the permissions included in the [AWSConfigUserAccess](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWSConfigUserAccess) AWS managed policy.
**List of properties that you can query**
An updated list of properties and their data types is available in [GitHub](https://github.com/awslabs/aws-config-resource-schema).
**Advanced queries and aggregators**
To run a query on an aggregator, create an aggregator. For more information, see [Creating Aggregators for AWS Config](aggregated-create.md).
If you already have an aggregator set up, in the query scope, choose the aggregator to run an advanced query on that aggregator. When you select an aggregator, consider adding the AWS account ID and AWS Region in the query statement to view that information in the results.
## Use an AWS Sample Query
1. Sign in to the AWS Management Console and open the AWS Config console at [https://console.aws.amazon.com/config/home](https://console.aws.amazon.com/config/home).
1. Choose **Advanced queries** from the left navigation to query your resource configurations for a single account and Region or for multiple accounts and Regions.
1. On the **Advanced queries** page, choose an appropriate query from the list of queries. You can filter through the list of queries either by the name, description, creator, or tags. To filter for AWS queries, choose **Creator**, and enter **AWS**. The query that you select is displayed in the SQL query editor. You can edit the selected query to fit your needs.
1. To save this query to a new query, choose **Save As**.
+ In the **Query Name** field, update the name of the query.
+ In the **Description** field, update the description of the query.
+ Enter up to 50 unique tags for this query.
+ Choose **Save**.
1. Choose **Run**. The query results are displayed in the table below the query editor.
1. Choose **Export as** to export the query results in CSV or JSON format.
## Create your custom query
1. Sign in to the AWS Management Console and open the AWS Config console at [https://console.aws.amazon.com/config/home](https://console.aws.amazon.com/config/home).
1. Choose **Advanced queries** from the left navigation to query your resource configurations for a single account and Region or for multiple accounts and Regions.
1. To create your custom query, choose **New query**.
To view or edit a custom query, filter a query either by the name, description, creator or tags. To filter custom queries, choose **Creater** and enter **Custom**.
1. On the **Query editor** page, create your own query for this account and Region. You can also select an appropriate aggregator to create a query for multiple accounts and Regions.
1. Edit if you wish you make changes to this query. Choose **Save Query** to save this query.
+ In the **Query Name** field, update the name of the query.
+ In the **Description** field, update the description of the query.
+ Enter up to 50 unique tags for this query.
+ Choose **Save**.
1. Choose **Run**. The query results are displayed in the table below the query editor.
1. Choose **Export as** to export the query results in CSV or JSON format.
# Query Using the SQL Query Editor for AWS Config (AWS CLI)
The AWS CLI is a unified tool to manage your AWS services. With just one tool to download and configure, you can control multiple AWS services from the command line and use scripts to automate them. For more information about the AWS CLI and for instructions on installing the AWS CLI tools, see the following in the *AWS Command Line Interface User Guide*.
+ [AWS Command Line Interface User Guide](https://docs.aws.amazon.com/cli/latest/userguide/)
+ [Getting Set Up with the AWS Command Line Interface](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html)
If necessary, enter `aws configure` to configure the AWS CLI to use an AWS Region where advanced queries are available.
## Considerations
**Prerequisites**
If you are using the one of the following AWS managed policies, you will have the necessary permissions to run and save a query: [AWSServiceRoleForConfig](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWSServiceRoleForConfig) (service-linked role) or [AWS\$1ConfigRole](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWS_ConfigRole).
Otherwise, you must have the permissions included in the [AWSConfigUserAccess](https://docs.aws.amazon.com/config/latest/developerguide/security-iam-awsmanpol.html#security-iam-awsmanpol-AWSConfigUserAccess) AWS managed policy.
**List of properties that you can query**
An updated list of properties and their data types is available in [GitHub](https://github.com/awslabs/aws-config-resource-schema).
**Advanced queries and aggregators**
To run a query on an aggregator, create an aggregator. For more information, see [Creating Aggregators for AWS Config](aggregated-create.md).
If you already have an aggregator set up, in the query scope, choose the aggregator to run an advanced query on that aggregator. When you select an aggregator, consider adding the AWS account ID and AWS Region in the query statement to view that information in the results.
## Query Resource Configuration Data
**To query your resource configuration data using the query editor (AWS CLI) for a single account and Region**
1. Open a command prompt or a terminal window.
1. Enter the following command to query your resource configuration data.
```
aws configservice select-resource-config --expression "SELECT resourceId WHERE resourceType='AWS::EC2::Instance'"
```
Depending on your query, the output looks like the following.
```
{
"QueryInfo": {
"SelectFields": [
{
"Name": "resourceId"
}
]
},
"Results": [
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}"
]
}
```
**To query your resource configuration data using the query editor (AWS CLI) for multiple accounts and Regions**
1. Open a command prompt or a terminal window.
1. Enter the following command to query your resource configuration data.
```
aws configservice select-aggregate-resource-config --expression "SELECT resourceId WHERE resourceType='AWS::EC2::Instance'" --configuration-aggregator-name my-aggregator
```
Depending on your query, the output looks like the following.
```
{
"QueryInfo": {
"SelectFields": [
{
"Name": "resourceId"
}
]
},
"Results": [
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}",
"{\"resourceId\":\"ResourceId\"}"
]
}
```
**Note**
While using the `AWS::IAM::User`, `AWS::IAM::Group`, `AWS::IAM::Role`, and `AWS::IAM::Policy` resource types in an advanced query, use `awsRegion = 'global'`.
## Save a Query
1. Open a command prompt or a terminal window.
1. Enter the following command to save a query.
```
aws configservice put-stored-query --stored-query "{\"QueryName\": \"cli-test\", \"Expression\": \"SELECT *\", \"Description\": \"cli test query\" }"
--tags "[{ \"Key\": \"first-tag\", \"Value\": \"\" }, { \"Key\": \"second-tag\", \"Value\": \"non-empty-tag-value\" }]"
```
1. Depending on your query, the output looks like the following.
```
{
"QueryArn": "arn:aws:config:eu-central-1:Account ID:stored-query/cli-test/query-e65mijt4rmam5pab"
}
```
**Note**
`--tags` is optional. When you pass the tags, the saved tags will not be returned by either `list-stored-queries` or `get-stored-query`. You must use `list-tag-for-resources` to retrieve the associated tags for a saved query.
`--description` is optional while creating or updating a query.
## View all the Saved Queries
1. Enter the following command to view the list of all saved queries.
```
aws configservice list-stored-queries
```
1. Depending on your query, the output looks like the following.
```
{
"StoredQueryMetadata": [
{
"QueryId": "query-e65mijt4rmam5pab",
"QueryArn": "arn:aws:config:eu-central-1:Account ID:stored-query/cli-test/query-e65mijt4rmam5pab",
"QueryName": "cli-test"
},
{
"QueryId": "query-rltwlewlqfivadxq",
"QueryArn": "arn:aws:config:eu-central-1:Account ID:stored-query/cli-test-2/query-rltwlewlqfivadxq",
"QueryName": "cli-test-2",
"Description": "cli test query"
}
]
}
}
```
## Get Details of a Saved Query
1. Enter the following command to get details of a specific saved query.
```
aws configservice get-stored-query --query-name cli-test
```
1. Depending on your query, the output looks like the following.
```
{
"StoredQuery": {
"QueryId": "query-e65mijt4rmam5pab",
"QueryArn": "arn:aws:config:eu-central-1:Account ID:stored-query/cli-test/query-e65mijt4rmam5pab",
"QueryName": "cli-test",
"Description": "cli test query",
"Expression": "SELECT *"
}
}
```
## Delete a Saved Query
+ Enter the following command to delete your saved query.
```
aws configservice delete-stored-query --query-name cli-test
```
If successful, the command runs with no additional output.
# Natural language query processor for AWS Config advanced queries
**Note**
The public preview of AWS Config Natural Language Query Processor will be discontinued by January 15, 2026. Additionally, you can chat about your AWS resources using natural language prompts with Amazon Q Developer. For more information, see [Chatting about your resources with Amazon Q Developer](https://docs.aws.amazon.com/amazonq/latest/qdeveloper-ug/chat-actions.html).
The natural language query processor for advanced queries uses [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/userguide/what-is-bedrock.html), a generative artificial intelligence (generative AI) technology which allows you to enter prompts in plain English and convert them into a ready-to-use query format. With the natural language query processor, you can query your AWS account or across an AWS organization.
A prompt can be a question or a statement. For example, you can enter prompts such as "Which load balancers are created after January 1, 2024?" and “List all my lambda function that is running node js 16.”
## Considerations
The natural language query processor cannot do the following actions:
+ Generate queries from languages other than English.
+ Generate queries from prompts that do not relate to advanced queries.
+ Generate queries from prompts with more than 1000 characters.
+ Generate queries from follow-up corrections or from previous sessions.
+ Explain code that has been generated.
## Query using the natural language query processor
1. Sign in to the AWS Management Console and open the AWS Config console at [https://console.aws.amazon.com/config/home](https://console.aws.amazon.com/config/home).
1. Choose **Advanced queries** from the left navigation to query your resource configurations for a single account and Region or for multiple accounts and Regions.
1. On the **Advanced queries** page, choose **New query** to go to the Query editor.
+ (Optional) To run a query on an aggregator, create an aggregator. For more information, see [Creating Aggregators for AWS Config](aggregated-create.md).
+ If you've already set up an aggregator, in the query scope, choose the aggregator to run an advanced query on that aggregator. When you select an aggregator, consider adding the AWS account ID and AWS Region in the query statement to view that information in the results.
1. In the Query editor, go to the **Natural language query processor** section. Enter a prompt in plain English, and then choose **Generate**. For examples, see [Example prompts](https://docs.aws.amazon.com/config/latest/developerguide/query-assistant.html#example-prompts).
1. After you generate a query, you can choose **Copy** (functionally equivalent to Ctrl\$1C) to copy the query to your clipboard, or choose **Populate to editor**. If populated to the editor, you can edit the generated query to fit your needs.
1. To save this query to a new query, choose **Save As**.
+ In the **Query Name** field, update the name of the query.
+ In the **Description** field, update the description of the query.
+ Enter up to 50 unique tags for this query.
+ Choose **Save**.
1. Choose **Run**. The query results display in the table below the query editor.
1. Choose **Export as** to export the query results in CSV or JSON format.
**Note**
The query results are paginated. When you export, up to 500 results are exported.
You can also use the APIs to retrieve all the results. The results are paginated and you can retrieve 100 results at a time.
## Example prompts
The following are prompts and the generate queries which you can use as examples.
------
#### [ Example: What load balancers were created after January 1, 2024? ]
**Prompt**: What load balancers were created after January 1, 2024?
**Generated query**:
```
SELECT
resourceId,
resourceType,
resourceCreationTime
WHERE
resourceType IN ('AWS::ElasticLoadBalancing::LoadBalancer', 'AWS::ElasticLoadBalancingV2::LoadBalancer')
AND
resourceCreationTime > '2024-01-01T00:00:00.0Z'
ORDER BY
resourceCreationTime
DESC
```
------
#### [ Example: Which of my RDS database instances are publicly accessible? ]
**Prompt**: Which of my RDS database instances are publicly accessible?
**Generated query**:
```
SELECT
resourceId,
resourceType,
configuration.publiclyAccessible
WHERE
resourceType = 'AWS::RDS::DBInstance'
AND
configuration.publiclyAccessible = true
```
------
#### [ Example: How many EC2 instances were created in last one year? ]
**Prompt**: How many EC2 instances were created in last one year?
**Generated query**:
```
SELECT
COUNT(*)
WHERE
resourceType = 'AWS::EC2::Instance'
AND
resourceCreationTime
BETWEEN
'2023-05-10T16:56:25.000Z'
AND
'2024-05-10T16:56:25.000Z'
```
------
#### [ Example: How many EC2 instances do I have in each of my AWS accounts? ]
**Prompt**: How many EC2 instances do I have in each of my AWS accounts?
**Generated query**:
```
SELECT
accountId,
COUNT(*)
WHERE
resourceType = 'AWS::EC2::Instance'
GROUP BY
accountId'
```
------
#### [ Example: Which IAM roles have the managed policy AdministratorAccess attached? ]
**Prompt**: Which IAM roles have the managed policy AdministratorAccess attached?
**Generated query**:
```
SELECT
resourceId,
resourceName,
resourceType
WHERE
resourceType = 'AWS::IAM::Role'
AND
configuration.attachedManagedPolicies.policyName = 'AdministratorAccess'
```
------
#### [ Example: EBS volumes without encryption ]
**Prompt**: EBS volumes without encryption
**Generated query**:
```
SELECT
accountId
WHERE
resourceType = 'AWS::EC2::Volume'
AND
configuration.encrypted = false
```
------
#### [ Example: Get noncompliant resources ]
**Prompt**: Get noncompliant resources
**Generated query**:
```
SELECT
resourceId,
resourceType
WHERE
resourceType = 'AWS::Config::ResourceCompliance'
AND
configuration.complianceType = 'NON_COMPLIANT'
```
------
#### [ Example: List all my lambda function that is running node js 16 ]
**Prompt**: List all my lambda function that is running node js 16
**Generated query**:
```
SELECT
accountId,
resourceName
WHERE
resourceType = 'AWS::Lambda::Function'
AND
configuration.runtime = 'nodejs16.x'
```
------
#### [ Example: What ACM certificates expire the next 30 days? ]
**Prompt**: What ACM certificates expire the next 30 days?
**Generated query**:
```
SELECT
resourceId,
configuration.domainName,
configuration.notAfter
WHERE
resourceType = 'AWS::ACM::Certificate'
AND
configuration.notAfter
BETWEEN
'2024-05-10 16:59:53'
AND
'2024-06-10 16:59:53’
```
**Note**
The times in the generated query depend on the time when you enter the prompt.
------
## Providing feedback
You can provide overall feedback about the natural language query processor or feedback about a specific generated query.
**Providing feedback on the natural language query processor**
Choose the **Provide feedback** button that appears above natural language query processor to the right. You can enter your satisfaction or dissatisfaction and provide feedback on how AWS Config can make the natural language query more helpful.
**Note**
Do not disclose any personal, commercially sensitive, or confidential information.
**Providing feedback on a specific generated query**
You can provide your feedback on a generated query by choose the thumbs up or thumbs down button that appears below the generated query.
## Region Support
The natural language query processor is supported in the following Regions.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/config/latest/developerguide/query-assistant.html)
# Example Queries for AWS Config
View the following example queries.
------
#### [ Query to list all EC2 instances with AMI ID ami-12345 ]
Query:
```
SELECT
resourceId,
resourceType,
configuration.instanceType,
configuration.placement.tenancy,
configuration.imageId,
availabilityZone
WHERE
resourceType = 'AWS::EC2::Instance'
AND
configuration.imageId = 'ami-12345'
```
Results:
```
{
"QueryInfo": {
"SelectFields": [
{
"Name": "resourceId"
},
{
"Name": "resourceType"
},
{
"Name": "configuration.instanceType"
},
{
"Name": "configuration.placement.tenancy"
},
{
"Name": "configuration.imageId"
},
{
"Name": "availabilityZone"
}
]
},
"Results": [
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2c\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2a\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2c\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t1.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2a\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2c\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2c\",\"resourceType\":\"AWS::EC2::Instance\"}",
"{\"resourceId\":\"resourceid\",\"configuration\":{\"imageId\":\"ami-12345\",\"instanceType\":\"t2.micro\",\"placement\":{\"tenancy\":\"default\"}},\"availabilityZone\":\"us-west-2c\",\"resourceType\":\"AWS::EC2::Instance\"}"
]
}
```
------
#### [ Query for count of resources grouped by their AWS Config rules compliance status ]
Query:
```
SELECT
configuration.complianceType,
COUNT(*)
WHERE
resourceType = 'AWS::Config::ResourceCompliance'
GROUP BY
configuration.complianceType
```
Results:
```
{
"QueryInfo": {
"SelectFields": [
{
"Name": "configuration.complianceType"
},
{
"Name": "COUNT(*)"
}
]
},
"Results": [
"{\"COUNT(*)\":163,\"configuration\":{\"complianceType\":\"NON_COMPLIANT\"}}",
"{\"COUNT(*)\":2,\"configuration\":{\"complianceType\":\"COMPLIANT\"}}"
]
}
```
------
#### [ Query for the compliance status of AWS Conformance packs ]
Query:
```
SELECT
resourceId,
resourceName,
resourceType,
configuration.complianceType
WHERE
resourceType = 'AWS::Config::ConformancePackCompliance'
```
Results:
```
{
"QueryInfo": {
"SelectFields": [
{
"Name": "resourceId"
},
{
"Name": "resourceName"
},
{
"Name": "resourceType"
},
{
"Name": "configuration.complianceType"
}
]
},
"Results": [
"{\"resourceId\":\"conformance-pack-conformance-pack-ID\",\"configuration\":{\"complianceType\":\"COMPLIANT\"},\"resourceName\":\"MyConformancePack1\",\"resourceType\":\"AWS::Config::ConformancePackCompliance\"}",
"{\"resourceId\":\"conformance-pack-conformance-pack-ID\",\"configuration\":{\"complianceType\":\"NON_COMPLIANT\"},\"resourceName\":\"MyConformancePack2\",\"resourceType\":\"AWS::Config::ConformancePackCompliance\"}",
"{\"resourceId\":\"conformance-pack-conformance-pack-ID\",\"configuration\":{\"complianceType\":\"NON_COMPLIANT\"},\"resourceName\":\"MyConformancePack3\",\"resourceType\":\"AWS::Config::ConformancePackCompliance\"}"
]
}
```
------
#### [ Query to get counts of AWS resources grouped by account ID ]
Query:
```
aws configservice select-aggregate-resource-config --expression "SELECT COUNT(*), accountId group by accountId" --configuration-aggregator-name my-aggregator
```
Results:
```
{
"Results": [
"{\"COUNT(*)\":2407,\"accountId\":\"accountId\"}",
"{\"COUNT(*)\":726,\"accountId\":\"accountId\"}"
],
"QueryInfo": {
"SelectFields": [
{
"Name": "COUNT(*)"
},
{
"Name": "accountId"
}
]
}
}
```
------
#### [ Query to list all EC2 volumes that are not in use ]
Query:
```
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 = 'available'
```
Results:
```
{
"Results": [
"{\"accountId\":\"accountId\",\"resourceId\":\"vol-0174de9c962f6581c\",\"awsRegion\":\"us-west-2\",\"configuration\":{\"volumeType\":\"gp2\",\"encrypted\":false,\"size\":100.0,\"state\":{\"value\":\"available\"},\"availabilityZone\":\"us-west-2a\"},\"resourceCreationTime\":\"2020-02-21T07:39:43.771Z\",\"tags\":[],\"resourceType\":\"AWS::EC2::Volume\"}",
"{\"accountId\":\"accountId\",\"resourceId\":\"vol-0cbeb652a74af2f8f\",\"awsRegion\":\"us-east-1\",\"configuration\":{\"volumeType\":\"gp2\",\"encrypted\":false,\"size\":100.0,\"state\":{\"value\":\"available\"},\"availabilityZone\":\"us-east-1a\"},\"resourceCreationTime\":\"2020-02-21T07:28:40.639Z\",\"tags\":[],\"resourceType\":\"AWS::EC2::Volume\"}"
"{\"accountId\":\"accountId\",\"resourceId\":\"vol-0a49952d528ec8ba2\",\"awsRegion\":\"ap-south-1\",\"configuration\":{\"volumeType\":\"gp2\",\"encrypted\":false,\"size\":100.0,\"state\":{\"value\":\"available\"},\"availabilityZone\":\"ap-south-1a\"},\"resourceCreationTime\":\"2020-02-21T07:39:31.800Z\",\"tags\":[],\"resourceType\":\"AWS::EC2::Volume\"}",
],
"QueryInfo": {
"SelectFields": [
{
"Name": "resourceId"
},
{
"Name": "accountId"
},
{
"Name": "awsRegion"
},
{
"Name": "resourceType"
},
{
"Name": "configuration.volumeType"
},
{
"Name": "configuration.size"
},
{
"Name": "resourceCreationTime"
},
{
"Name": "tags"
},
{
"Name": "configuration.encrypted"
},
{
"Name": "configuration.availabilityZone"
},
{
"Name": "configuration.state.value"
}
]
}
}
```
------
# Example Relationship Queries for AWS Config
View the following example relationship queries.
------
#### [ Find EIPs related to an EC2 instance ]
```
SELECT
resourceId
WHERE
resourceType = 'AWS::EC2::EIP'
AND relationships.resourceId = 'i-abcd1234'
```
------
#### [ Find EIPs related to an EC2 network interface ]
```
SELECT
resourceId
WHERE
resourceType = 'AWS::EC2::EIP'
AND relationships.resourceId = 'eni-abcd1234'
```
------
#### [ Find EC2 instances and network interfaces related to a security group ]
```
SELECT
resourceId
WHERE
resourceType IN ('AWS::EC2::Instance', 'AWS::EC2::NetworkInterface')
AND relationships.resourceId = 'sg-abcd1234'
```
OR
```
SELECT
resourceId
WHERE
resourceType = 'AWS::EC2::Instance'
AND relationships.resourceId = 'sg-abcd1234'
SELECT
resourceId
WHERE
resourceType = 'AWS::EC2::NetworkInterface'
AND relationships.resourceId = 'sg-abcd1234'
```
------
#### [ Find EC2 instances, network ACLs, network interfaces and route tables related to a subnet ]
```
SELECT
resourceId
WHERE
resourceType IN ('AWS::EC2::Instance', 'AWS::EC2::NetworkACL', 'AWS::EC2::NetworkInterface', 'AWS::EC2::RouteTable')
AND relationships.resourceId = 'subnet-abcd1234'
```
------
#### [ Find EC2 instances, internet gateways, network ACLs, network interfaces, route tables, subnets and security groups related to a VPC ]
```
SELECT
resourceId
WHERE
resourceType IN ('AWS::EC2::Instance', 'AWS::EC2::InternetGateway', 'AWS::EC2::NetworkACL', 'AWS::EC2::NetworkInterface', 'AWS::EC2::RouteTable', 'AWS::EC2::Subnet', 'AWS::EC2::SecurityGroup')
AND relationships.resourceId = 'vpc-abcd1234'
```
------
#### [ Find EC2 route tables related to a VPN gateway ]
```
SELECT
resourceId
WHERE
resourceType = 'AWS::EC2::RouteTable'
AND relationships.resourceId = 'vgw-abcd1234'
```
------
## Limitations
**Note**
Advanced query does not support querying resources which have not been configured to be recorded by the configuration recorder. AWS Config creates Configuration Items (CIs) with `ResourceNotRecorded` in the `configurationItemStatus` when a resource has been discovered but is not configured to be recorded by the configuration recorder. While an aggregator will aggregate these CIs, advanced query does not support querying CIs with `ResourceNotRecorded`. Update your recorder settings to enable recording of the resource types that you want to query.
As a subset of SQL `SELECT`, the query syntax has following limitations:
+ No support for `ALL`, `AS`, `DISTINCT`, `FROM`, `HAVING`, `JOIN`, and `UNION` keywords in a query. `NULL` value queries are not supported.
+ No support for complex `CASE` statements for creating a priority field directly in the query.
+ No support for querying on third-party resources. Third-party resources retrieved using advanced queries will have the configuration field set as `NULL`.
+ No support for nested structures (such as tags) to be unpacked with SQL queries.
+ No support for querying deleted resources. To discover deleted resources, see [Looking Up Resources That Are Discovered by AWS Config](https://docs.aws.amazon.com/config/latest/developerguide/looking-up-discovered-resources.html).
+ The `SELECT` all columns shorthand (that is `SELECT *`) selects only the top-level, scalar properties of a CI. The scalar properties returned are `accountId`, `awsRegion`, `arn`, `availabilityZone`, `configurationItemCaptureTime`, `resourceCreationTime`, `resourceId`, `resourceName`, `resourceType`, and `version`.
+ Wildcard limitations:
+ Wildcards are supported only for property values and not for property keys (for example, `...WHERE someKey LIKE 'someValue%'` is supported but `...WHERE 'someKey%' LIKE 'someValue%'` is not supported).
+ Support for only suffix wildcards (for example, `...LIKE 'AWS::EC2::%'` and `...LIKE 'AWS::EC2::_'` is supported but `...LIKE '%::EC2::Instance'` and `...LIKE '_::EC2::Instance'`is not supported).
+ Wildcard matches must be at least three characters long (for example, `...LIKE 'ab%'` and `...LIKE 'ab_'` is not allowed but `...LIKE 'abc%'` and `...LIKE 'abc_'` is allowed).
**Note**
The "`_`" (single underscore) is also treated as a wildcard.
+ Aggregation limitations:
+ Aggregate functions can accept only a single argument or property.
+ Aggregate functions cannot take other functions as arguments.
+ `GROUP BY` with an `ORDER BY` clause referencing aggregate functions may contain only a single property.
+ For all other aggregations `GROUP BY` clauses may contain up to three properties.
+ Pagination is supported for all aggregate queries except when `ORDER BY` clause has an aggregate function. For example, `GROUP BY X, ORDER BY Y` does not work if `Y` is an aggregate function.
+ No support for `HAVING` clauses in aggregations.
+ Mismatched identifier limitations:
Mismatched identifiers are properties that have the same spelling but different cases (upper and lower case). Advanced query does not support processing queries that contain mismatched identifiers. For example:
+ Two properties that have the exact same spelling but with different casing (`configuration.dbclusterIdentifier` and `configuration.dBClusterIdentifier`).
+ Two properties where one property is a subset of the other, and they have different casing (`configuration.ipAddress` and `configuration.ipaddressPermissions`).
## CIDR notation/IP range behavior for advanced queries
CIDR notation is converted to IP ranges for search.
This means that `"="` and `"BETWEEN"` search for any range that includes the provided IP, instead of for an exact one.
To search for an exact IP range, you need to add in additional conditions to exclude IPs outside of the range.
**Example Searching for exact CIDR block 10.0.0.0/24**
```
SELECT * WHERE resourceType = 'AWS::EC2::SecurityGroup'
AND configuration.ipPermissions.ipRanges BETWEEN '10.0.0.0'
AND '10.0.0.255'
AND NOT configuration.ipPermissions.ipRanges < '10.0.0.0'
AND NOT configuration.ipPermissions.ipRanges > '10.0.0.255'
```
**Example Searching for exact IP address 192.168.0.2/32**
```
SELECT * WHERE resourceType = 'AWS::EC2::SecurityGroup'
AND configuration.ipPermissions.ipRanges = '192.168.0.2'
AND NOT configuration.ipPermissions.ipRanges > '192.168.0.2'
AND NOT configuration.ipPermissions.ipRanges < '192.168.0.2'
```
## Multiple properties within an array behavior for advanced queries
When querying against multiple properties within an array of objects, matches are computed against* all the array elements*.
For example, for a resource R with rules A and B, the resource is compliant to rule A but noncompliant to rule B. The resource R is stored as:
```
{
configRuleList: [
{
configRuleName: 'A', complianceType: 'compliant'
},
{
configRuleName: 'B', complianceType: 'non_compliant'
}
]
}
```
R will be returned by this query:
```
SELECT configuration WHERE configuration.configRuleList.complianceType = 'non_compliant'
AND configuration.configRuleList.configRuleName = 'A'
```
The first condition `configuration.configRuleList.complianceType = 'non_compliant'` is applied to ALL elements in R.configRuleList, because R has a rule (rule B) with complianceType = 'non\$1compliant', the condition is evaluated as true.
The second condition `configuration.configRuleList.configRuleName` is applied to ALL elements in R.configRuleList, because R has a rule (rule A) with configRuleName = 'A', the condition is evaluated as true. As both conditions are true, R will be returned.
## Region Support
Advanced queries is supported in the following Regions:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/config/latest/developerguide/querying-AWS-resources.html)