How do I resolve errors with limitations for AWS Config advanced query?
Last updated: 2021-02-02
How do I resolve errors with limitations for AWS Config advanced query with aggregated or account resources?
Use the following workarounds for your use case with advanced query.
Amazon EC2 instance operating system versions
Advanced query can't get the list of all operating systems running in Amazon Elastic Compute Cloud (Amazon EC2) instances across all AWS Regions. To check the OS, see How can I find the OS platform or version my EC2 instance is using?
Querying deleted resources
You can't use advanced query for deleted resources. To look up deleted resources, see Looking up resources that are discovered by AWS Config.
Amazon S3 queries
Advanced query can't get the results for Amazon Simple Storage Service (Amazon S3) buckets if public access is blocked. This is because the AWS Config resource type AWS::S3::AccountPublicAccessBlock returns results only if Amazon S3 Block Public Access is enabled at the account level. You can use a SQL query to return the name and attributes of an S3 bucket using a query similar to the following:
SELECT resourceId, resourceType, configuration, supplementaryConfiguration WHERE resourceType = 'AWS::S3::Bucket'
SQL null values
Advanced query doesn't support SQL null values—you must explicitly include values.
You can retrieve a list of Amazon EC2 Instances with a public IP address associated with the use of SQL BETWEEN operator similar to the following:
SELECT accountId, resourceId, configuration.publicDnsName, configuration.publicIpAddress WHERE resourceType = 'AWS::EC2::Instance' AND ( configuration.publicIpAddress BETWEEN '0.0.0.0' AND '255.255.255.255' OR configuration.ipv6Addresses BETWEEN '0:0:0:0:0:0:0:0' AND 'ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff' )
Nested array queries
Advanced query doesn't support nested array queries. For more information, see advanced query limitations.
1. As a workaround, you can use a custom query similar to the following:
SELECT configuration.targetResourceId, configuration.targetResourceType, configuration.complianceType, configuration.configRuleList WHERE configuration.complianceType = 'NON_COMPLIANT' AND configuration.configRuleList.configRuleName = 'required-tags'
2. Then, follow the instructions to export the output as JSON.
You can then use the command line JSON processor jq to filter and query the nested array. For more information and to download jq, see JSON output format.