How do I resolve errors with limitations for AWS Config advanced query?

Resolution

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:

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.

Related information

AWS Config launches ability to save advanced queries

Querying the current configuration state of AWS resources