Querying nested json arrays in Cloudtrail logs to find open security groups - Is there a better way?
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')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.
Relevant questions
Querying nested json arrays in Cloudtrail logs to find open security groups - Is there a better way?
asked a month agoUsing Cloud Trail Console to view all events in multi-account CloudTrail ( created via Organizations )
Accepted Answerasked 15 days agoSpecific Cloudwatch log groups not responding to queries
asked 3 months agoAnalyze historical cloudtrail data for S3
asked 4 months agotracking access to a CloudWatch log group ?
asked 3 years agoHow to reduce the cost for cloudtrail logging
asked 4 months agoS3 server access logs to Cloudwatch?
Accepted Answerasked 4 years agoHow to Stop someone from accessing the lightsail Instance.
asked a month agoHow do you find the EBS Volume IDS for a Volume that was created and attached at EC2 Instance Launch Time ?
asked 12 days agoCloudTrail Lake queries
asked 14 days 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?