CloudTrail & Athena

Go to Athena

Create a table in the default sampledb database using the CloudTrail SerDe. The easiest way to create the table is to copy and paste the following query into the Athena query editor, modify the LOCATION value, and then run the query.

Replace:

LOCATION 's3://<Your CloudTrail s3 bucket>/AWSLogs/<optional – AWS_Account_ID>/'

with the S3 bucket where your CloudTrail log files are delivered. For example, if your CloudTrail S3 bucket is named “aws -sai-sriparasa” and you set up a log file prefix of “/datalake/cloudtrail/” you would edit the LOCATION statement as follows:

CREATE EXTERNAL TABLE cloudtrail_logs (
eventversion STRING,
userIdentity STRUCT<
  type:STRING,
  principalid:STRING,
  arn:STRING,
  accountid:STRING,
  invokedby:STRING,
  accesskeyid:STRING,
  userName:STRING,
  sessioncontext:STRUCT<
    attributes:STRUCT<
      mfaauthenticated:STRING,
      creationdate:STRING>,
    sessionIssuer:STRUCT<
      type:STRING,
      principalId:STRING,
      arn:STRING,
      accountId:STRING,
      userName:STRING>>>,
eventTime STRING,
eventSource STRING,
eventName STRING,
awsRegion STRING,
sourceIpAddress STRING,
userAgent STRING,
errorCode STRING,
errorMessage STRING,
requestParameters STRING,
responseElements STRING,
additionalEventData STRING,
requestId STRING,
eventId STRING,
resources ARRAY<STRUCT<
  ARN:STRING,accountId:
  STRING,type:STRING>>,
eventType STRING,
apiVersion STRING,
readOnly STRING,
recipientAccountId STRING,
serviceEventDetails STRING,
sharedEventID STRING,
vpcEndpointId STRING
)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<Your CloudTrail s3 bucket>/AWSLogs/<optional – AWS_Account_ID>/';

Security use cases:

Identify any security group changes for our EC2 instance:

select eventname, useridentity.username, sourceIPAddress, eventtime, requestparameters from cloudtrail_logs where (requestparameters like '%sg-5887f224%' or requestparameters like '%sg-e214609e%' or requestparameters like '%eni-6c5ca5a8%') and eventtime > '2017-02-15T00:00:00Z' order by eventtime asc;

Console Sign-in activity:

select useridentity.username, sourceipaddress, eventtime, additionaleventdata from default.cloudtrail_logs where eventname = 'ConsoleLogin' and eventtime >= '2017-02-17T00:00:00Z' and eventtime < '2017-02-18T00:00:00Z';

Non Corporate IP Logins:

select useridentity.username, sourceipaddress, json_extract_scalar(additionaleventdata, '$.MobileVersion') as MobileVersion, eventtime, additionaleventdata from default.cloudtrail_logs where eventname = 'ConsoleLogin' and (json_extract_scalar(additionaleventdata, '$.MobileVersion') = 'Yes' or sourceipaddress not like '192.198.%' and eventtime >= '2017-02-17T00:00:00Z' and eventtime < '2017-02-17T05:00:00Z');

Error CloudTrail Logs

select count (*) as TotalEvents, eventname, errorcode, errormessage from cloudtrail_logs where errorcode is not null and eventtime >= '2017-01-01T00:00:00Z' group by eventname, errorcode, errormessage order by TotalEvents desc limit 10;

Top IAM users select count (*) as TotalEvents, useridentity.username, eventname from cloudtrail_logs where eventtime >= '2017-01-01T00:00:00Z' and useridentity.type = 'IAMUser' group by useridentity.username, eventname order by TotalEvents desc;

Root Login

select count (*) as TotalEvents, eventname, useridentity.invokedby from cloudtrail_logs where eventtime >= '2017-01-01T00:00:00Z' and useridentity.type = 'Root' group by useridentity.username, eventname, useridentity.invokedby order by TotalEvents desc;