Querying the Current Configuration State of AWS Resources

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.

No support for ALL, AS, DISTINCT, FROM, HAVING, JOIN, and UNION keywords in a query. NULL value queries are not supported.

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.

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. For example, to search for 10.0.0.0/24 and only that IP block, you can do:

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'
    

For 192.168.0.2/32, you can search in a similar fashion:

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'
    

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_compliant’, 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.

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:

Aggregation limitations:

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: