Config Advanved Query Editor - Return ConfigRuleName

Question

I am using the AWS Config Service across multiple Accounts within my Organization. My goal is to write a query which will give me a full list of non-compliant resources in all regions, in all accounts. I have an Aggregator which has the visibility for this task. The Advanced Query I am using is similar to the AWS [Example in the docs:](https://docs.aws.amazon.com/config/latest/developerguide/example-query.html)

```
SELECT
  configuration.targetResourceId,
  configuration.targetResourceType,
  configuration.complianceType,
  configuration.configRuleList,
  accountId,
  awsRegion
WHERE
  configuration.configRuleList.complianceType = 'NON_COMPLIANT'
```

However, the ConfigRuleName is nested within `configuration.configRuleList` - as there could be multiple config rules, (hence the list)  assigned to `configuration.targetResourceId`

How can I write a query that picks apart the JSON list returned this way? Because the results returned do not export to csv for example very well at all. Exporting a JSON object within a csv provides an unsuitable method if we wanted to import this into a spreadsheet for example, for viewership.

I have tried to use `configuration.configRuleList.configRuleName` and this only returns `-` even when the list has a single object within. If there is a better way to create a centralised place to view all my Org's Non-Compliant Resources, I would like to learn about it.
Thanks in Advance.

Answer

Hello, this is a great question.

Upon testing in my environment, I have been able to yield results using the following query:

``` sql
SELECT
  configuration.configRuleList.configRuleId,
  configuration.configRuleList.configRuleName,
  configuration.targetResourceId,
  configuration.targetResourceType,
  configuration.complianceType,
  configuration.configRuleList.configRuleArn,
  configuration.complianceType,
  accountId,
  awsRegion
WHERE
  configuration.complianceType = 'NON_COMPLIANT'
```

Feel free to rearrange the columnization, or order, of the line items above (`configuration.configRuleList.configRuleId`, `configuration.configRuleList.configRuleName`, or `configuration.configRuleList.configRuleArn`, etc.).

This should present each value in a .csv friendly format.  If there are any questions that you may have, please feel free to ask.

Thank you!

Config Advanved Query Editor - Return ConfigRuleName

Relevant content