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.
Note: You must have permission for the SelectResourceConfig and SelectAggregateResourceConfig APIs to use advanced query. For more information, see Query using the SQL editor (Console).
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:
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:
resourceType = 'AWS::EC2::Instance'
configuration.publicIpAddress BETWEEN '0.0.0.0'
OR configuration.ipv6Addresses BETWEEN '0:0:0:0:0:0:0:0'
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:
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.
AWS Config launches ability to save advanced queries
Querying the current configuration state of AWS resources