Querying nested json arrays in Cloudtrail logs to find open security groups - Is there a better way?

Question

I am using Athena to query my Cloudtrail logs to find security groups that have rules that allow access to/from the internet. To achieve this, I am looking to see if any cidrIp in requestParameters.ipPermissions.items[].ipRanges.items[] equals '0.0.0.0/0' or '::/0'. I have constructed a query that does this (pasted below), but it is very lengthy. I would like to know if there is a better way to write this specific query or query nested arrays in general.

```
WITH parent as (SELECT items
FROM "default"."my_cloudtrail_logs"
CROSS JOIN UNNEST (cast(json_extract(requestParameters,'$.ipPermissions.items') AS array(json))) AS i (items)
WHERE eventname = 'AuthorizeSecurityGroupIngress'
  AND eventtime >= '2021-04-06T02:00:00.000'
 order by eventtime desc)
Select * FROM parent
CROSS JOIN UNNEST (cast(json_extract(items,'$.ipRanges.items') AS array(json))) AS t (cidr_items)
WHERE json_extract_scalar(cidr_items, '$.cidrIp') in ('0.0.0.0/0', '::/0')
```

James_S · Answer

To come at the question (finding open security groups) from another angle, have you considered [AWS Config](https://aws.amazon.com/config/)? You can create a [rule](https://docs.aws.amazon.com/config/latest/developerguide/evaluate-config_use-managed-rules.html) which will detect security groups with open rules and flag them as non-compliant. Config also supports a SQL-like [query syntax](https://docs.aws.amazon.com/config/latest/developerguide/querying-AWS-resources.html) to search for resources based on their properties, relationships and compliance status. It can also work cross-account and cross-region to give you a centralised view.

Querying nested json arrays in Cloudtrail logs to find open security groups - Is there a better way?

Relevant content