Relevant links and experiments with AWS Athena and CloudTrail

If you’re trying to investigate what operation gave birth to a given Amazon Web Services (AWS) resource, specially one that you will pay for, but are not sure if it is worth paying for, the best way to investigate about it is using AWS CloudTrail.

However, the query abilities of CloudTrail are not that big, and most of the time you will want to dig into them, in particular if you want to make use of things like resource names, or Amazon Resource Names (ARNs).

While I complete the post, here is a list of links, and relevant code that I find useful:

• Athena: Querying AWS CloudTrail Logs: https://docs.aws.amazon.com/athena/latest/ug/cloudtrail-logs.html
• Athena: Extracting JSON data: https://docs.aws.amazon.com/athena/latest/ug/querying-JSON.html
• Analyzing CloudTrail with Athena, by Andreas Wittig: https://cloudonaut.io/analyzing-cloudtrail-with-athena/
• Athena: Querying Arrays with Complex Types and Nested Structures: https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html
• Athena: Flattening Arrays: https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html

And this is a query that is successful in retrieving resource names for those items that include resource names:

    SELECT uarn,
             eventname,
             sourceipaddress,
             eventtime,
             resources[1].arn as res_arn
    FROM 
        (SELECT useridentity.arn AS uarn,
             eventname,
             sourceipaddress,
             eventtime,
             resources
        FROM cloudtrail_logs_aws_cloudtrail_do_pocops
        WHERE readonly = 'false'
                AND cardinality(resources) >= 1 ) AS t 
    WHERE resources[1].arn like '%resource_id%'
    LIMIT 100;

I’ll keep ellaborating this draft.