Part 1 Using Athena to query S3 buckets

While its great to push all the logs data gathered from various sources like your load balancers, cloudtrail, application logs etc to the S3 buckets. But as your infrastructure grows in size it becomes difficult to analyze such huge amount of data of months or year.

You can use the Athena service of the Amazon AWS to query the S3 service data without the need of downloading and processing it manually. This saves the requirement of extra processing, space requirement etc. We are going to cover the query details of most of the effective queries which can help you analyze and meaningful information from your s3 logs data.

 Question:- Identifying all the users,events,accounts accessing a particular s3 bucket  

 Answer:-

 SELECT DISTINCT  
    account,

    eventname,

    useridentity.arn,

    useragent,

    vpcendpointid,

    json_extract_scalar(requestparameters, '$.bucketName') AS bucketName,

    sourceipaddress

 FROM unixcloudfusion_cloudtrail

 WHERE year = '2019'

  AND month = '10'

  AND day = '09'

  AND eventsource = 's3.amazonaws.com'

  AND json_extract_scalar(requestparameters, '$.bucketName') = 'unixcloudfusion.analytics'