Athena and CloudTrail: A Marriage made in the Cloud

One of the first things which came to mind when AWS announced AWS Athena at re:Invent 2016 was querying CloudTrail logs. Over the course of the past month, I have had intended to set this up, but current needs dictated I had to do it quickly. When I went looking at JSON imports for Hive/Presto, I was quite confused. Of course, as a trusty technologist I went to Google. Much to my surprise, no one had published an article about using Athena to do this, I was only able to locate EMR based posts which used a custom serde to support the nested CloudTrail format.

I had mild success at first, but thanks to some Athena guru’s, I was able to get the magic piece in place.

I have to provide credit to AWS for their help with a few issues and amazing documentation on the event types.

I have provided references at the end of each field section and the end of the post with specific and broader details for the event fields and their uses.

To set all of this up, you first must have your CloudTrail logs in a single S3 bucket, this will work with a single account or many, but I purposely set up delivery to a single bucket but I created a table per source in Athena under a common database.

This is an example create table which will provide the table/field sytax formats I used in the tables below.

CREATE EXTERNAL TABLE my_table_name (
         Records ARRAY< STRUCT< eventName: STRING,
         requestParameters: STRUCT< instancesSet: STRUCT< items: ARRAY< STRUCT< instanceId: STRING >>>,
         volumeSet: STRUCT< items: ARRAY< STRUCT< volumeId: STRING > > > >,
         eventType: STRING,
         eventSource: STRING,
         sourceIPAddress: STRING,
         userIdentity: STRUCT< arn: STRING,
         principalId: STRING,
         accountId: STRING,
         invokedBy: STRING,
         TYPE: STRING,
         sessionContext: STRUCT< sessionIssuer: STRUCT< arn: STRING,
         principalId: STRING,
         accountId: STRING,
         TYPE: STRING,
         userName: STRING >,
         attributes: STRUCT< creationDate: STRING,
         mfaAuthenticated: STRING > > >,
         eventVersion: STRING,
         responseElements: STRUCT< credentials: STRUCT< accessKeyId: STRING,
         expiration: STRING,
         sessionToken: STRING >,
         assumedRoleUser: STRUCT< arn: STRING,
         assumedRoleId: STRING > >,
         userAgent: STRING,
         eventID: STRING,
         awsRegion: STRING,
         sharedEventID: STRING,
         eventTime: STRING,
         resources: ARRAY< STRUCT< accountId: STRING,
         TYPE: STRING,
         ARN: STRING > >,
         requestID: STRING,
         recipientAccountId: STRING >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH serdeproperties( 'ignore.malformed.json' = 'true' )
LOCATION 's3://my_consolidated_bucket/my-cross-account-prefix/AWSLogs/'

CloudTrail Record Query Columns

These are the columns you can reference in your queries, I have grouped them by purpose. This is not a full list of all CloudTrail fields, so if you need others such as VpcEndpoint, you should add that to the schema.

Event ID Fields

Event Details

Request Details

Resource Details

Response Details

References
http://docs.aws.amazon.com/IAM/latest/UserGuide/cloudtrail-integration.html#stscloudtrailexample http://docs.aws.amazon.com/kms/latest/developerguide/logging-using-cloudtrail.html

Miscellaneous

User Identity

Reference: http://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-user-identity.html#cloudtrail-event-reference-user-identity-fields

Example Queries

Find all event names by ARN by IP address and count them up as the highest totals

SELECT record.eventName, record.userIdentity.arn, record.sourceIPAddress, COUNT(*)
FROM
(SELECT record
FROM my_table_name
CROSS JOIN UNNEST(records) AS t (record)) AS records
GROUP BY record.eventName, record.userIdentity.arn, record.sourceIPAddress
ORDER BY COUNT(*) DESC
LIMIT 20;

Find all events where cross-account access occurred, group them by the source and the ARN and count the totals

SELECT record.eventName, record.eventSource, record.userIdentity.arn, COUNT(*)
FROM
(SELECT record
FROM my_table_name
CROSS JOIN UNNEST(records) AS t (record)) AS records
WHERE record.recipientAccountId <> record.userIdentity.accountId
GROUP BY record.eventName, record.eventSource, record.userIdentity.arn
ORDER BY COUNT(*) DESC
LIMIT 20;

Document Reference: http://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference.html