By using AWS re:Post, you agree to the Terms of Use
/Querying nested json arrays in Cloudtrail logs to find open security groups - Is there a better way?/

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

0

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')
1 Answers
1

To come at the question (finding open security groups) from another angle, have you considered AWS Config? You can create a rule which will detect security groups with open rules and flag them as non-compliant. Config also supports a SQL-like query syntax 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.

EXPERT
answered a month ago
  • James, thanks for your suggestion. It will certainly help for this specific use-case. Do you (or anyone else) also happen to know if there is a better method to query nested arrays in general in Athena?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions