By using AWS re:Post, you agree to the Terms of Use

Config Advanved Query Editor - Return ConfigRuleName

0

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:

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.