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

Resolution

Use these 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 query editor (Console).

Amazon EC2 instance operating system versions

Advanced query can't get the list of all operating systems that run 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 that my EC2 Linux instance is using?

Deleted resources

You can't use advanced query for deleted resources. This is because advanced query can only get the current configuration. 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 with a query similar to this:

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 this:

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 Limitations.

1. As a workaround, you can use a custom query similar to this:

   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