I was querying CloudTrail event history for a specific user,
projectID-AppUser, and it only listed two events when I knew there were more. The AWS Console had a message explaining why:
Your event history contains the create, modify, and delete activities for supported services taken by people, groups, or AWS services in your AWS account. To view a complete log of your CloudTrail events, create a trail and then go to your Amazon S3 bucket or CloudWatch Logs.
How do I find all the events? I know the exact day and time (if I can convert to UTC), but didn’t want to download 300 .gz files from an S3 bucket and search through them. Then I remembered Amazon Athena (serverless, interactive query service) and found exactly the blog post that could help me: Analyze Security, Compliance, and Operational Activity Using AWS CloudTrail and Amazon Athena | AWS Big Data Blog. I followed the instructions, but because I only cared about a specific day of CloudTrail logs for a specific region, I created a smaller table using that restriction. The lines from the “create table” SQL I changed were:
CREATE EXTERNAL TABLE cloudtrail_logs_useast1_20170928 LOCATION 's3://123456789012-cloudtrail-acct-logs/localadmin/AWSLogs/123456789012/CloudTrail/us-east-1/2017/09/28/';
Now, my “User name” of “projectID-AppUser” query would find 15 events (Run time: 6.15 seconds, Data scanned: 28.85MB).
select useridentity.username, eventid, eventtime, eventname from sampledb.cloudtrail_logs_useast1_20170928 where useridentity.username = 'projectID-AppUser' order by eventtime;
I could even get the full details on the two
eventid entries I saw from the AWS Console CloudTrail Event history.
select * from sampledb.cloudtrail_logs_useast1_20170928 where eventid = 'bbc83507-e7a0-4464-8684-51d32757d3c8' or eventid = '5a602970-f337-4441-be78-0470e827b28f';
If I wanted everything related to
projectID-AppUser, my query would find 40 events (Run time: 20.94 seconds, Data scanned: 28.87MB)
select eventid, eventtime, eventname, requestparameters from sampledb.cloudtrail_logs_useast1_20170928 where requestparameters like '%projectID-AppUser%' order by eventtime;
I used the web console for my interactive queries, but Accessing Amazon Athena with JDBC is also possible.
This seems like we should “turn on” for the whole account — more work to add to the backlog.