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