I recommend starting with the article below before beginning the Hands-on lab.😉🕵️‍♂️

Objective

In this lab, you will analyze flaws.cloud AWS CloudTrail dataset using Structured query language (SQL) in Amazon Athena service to identify unusual patterns, detect potential security threats, and perform compliance checks.

Disclaimer

This Hands-on Lab gives credit to Scott Piper for creating an amazing vulnerable platform called flaws.cloud and making the CloudTrail logs of the platform public. Following the instructions in this lab may result in AWS billing. Consider prompt deletion of resources after completing the lab.

CloudTrail Log Dataset Summary

The Log Dataset used in this lab is the Public dataset of Cloudtrail logs from flaws.cloud which can be accessed Here.

You can Read through the blog post Here.

• md5: 4f0481c6be700ffc7610dbbf8cee5578

• Size: ~240MB of log data

The logs from flAWS game encompass 1,939,207 events, spanning from February 12, 2017, to October 7, 2020. It records 9,402 unique IP addresses and 8,811 unique user agents, which can be roughly considered as representing different 'attackers'. Attempts to call 1,242 different APIs were also logged.

Scott Piper, the author of the game, concatenated all the log events into one large file. He used the Wernicke tool, developed by the Latacora team, to anonymize sensitive information while ensuring that the IP addresses, account IDs, and access keys retained their recognizable formats (with access keys starting with 'AKIA'). He then divided this large file into smaller chunks, each containing 100,000 lines, to facilitate easier processing. These chunks were formatted to match the usual CloudTrail format, which typically looks like {Records[...]}. Finally, the files were compressed using gzip.

Lab Task

Using the Dataset Guide

• Download the gzip Log files Here to a folder.

• Decompress the gzip Log files back into their original format.

  There 2 ways to do this.

  • On Windows, you can use the .7zip tool to decompress the files

• On Linux, you can load the zip files in a folder and use the gunzip tool to decompress the log files to JSON by running the command.

    $ find . -type f -exec gunzip {} \;
    $ ls

• Upload the decompressed Log files to an S3 bucket for Amazon Athena service to easily access it.

  • Navigate to the Amazon S3 bucket and create a bucket called flaws-logs-dataset

• Retain all default settings, scroll down, and click Create bucket.

Upload Log Dataset

Ensure you have extracted or decompressed the Dataset file as shown in previous steps.

• Click on the S3 bucket name flaws-logs-dataset

• On the Upload page, click Add Folder and Select the Folder where you stored the decompressed log files in JSON format only.

• On the Console, you will see a pop-up message confirming if you want to upload the files. Click the upload button.

• Scroll down to the bottom and click the Upload button

• It will take a while before it completely uploads.

Grab a drink or a cup of coffee 😉

• Finally, All files were uploaded successfully

Setup Amazon Athena

• On the search box, Navigate to Amazon Athena service

• On the Amazon Athena landing Page, Under Get Started, check the radio button “Query your data with Trino SQL” and click on the “Launch query editor”

• You will be directed to the Amazon Athena Query Editor view

• Click on the Edit Settings button to set up a query result location in Amazon S3.

• On the Manage Settings page, click on Browse S3 and choose the created S3 bucket (flaws-logs-dataset) from the pop-up window, then click on the Save button.

• Navigate to the Editor tab and Run the command below in Athena. Replace the placeholder YOURBUCKET with the name of your S3 bucket.

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://YOURBUCKET/';

• Here in this lab, the bucket name is s3://flaws-logs-dataset/ . Paste the below Code in Query 1 and click on the run button.**

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://flaws-logs-dataset/';

• After Running the query, you will notice that a new Table is created successfully

Investigating the logs

Scott Piper and George Fekkas suggested various Queries we can look into for identifying unusual patterns and detecting potential security threats within the log files.

• Attempts to Launch EC2 instances by a specific user ARN

• Who has Root access through the console

• Hunting for sign-in failures to the AWS console

• Investigating Created events (IAM, S3, and EC2) resources via AWS Management Console.

• Investigating Created events (IAM, S3, and EC2) resources via AWS Management Console, AWS CLI, SDKs, or direct API calls

• Hunting for a CloudTrail disruption

• Hunting for unauthorized calls

• Hunting for “whoami” identities

• Efforts to compromise accounts by creating IAM users

• Hunting for access secret in Secrets Manager

• Hunting for xlarge EC2 Instances

• Hunting for S3 Buckets Brute Force attempts

• Hunting for suspicious user agents (Kali, Parrot, PowerShell)

• Hunting for permanent key creation

• Creation of public S3 buckets

• Brute force attempts to assume roles

• Attempts to perform recon actions on accounts

Attempts to Launch EC2 instances by a specific user ARN

This SQL query counts and lists the occurrences of each unique event (eventname) performed by a specific user (identified by the ARN 'arn:aws:iam::811596193553:user/Level6') from a specific IP address ('5.205.62.253'), in the cloudtrail_logs table.

SELECT 
  eventname, 
  count(*) AS eventcount 
FROM cloudtrail_logs WHERE 
  useridentity.arn = 'arn:aws:iam::811596193553:user/Level6' 
  AND sourceipaddress='5.205.62.253'
GROUP BY eventname ORDER BY eventcount DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

You can Add New Query tab (e.g Query 3, 4, 5 etc) by clicking on the + (plus) button as shown below.

Query Result

• Scroll down to view the Query result. You will notice that they are all unique event calls made by the user ARN (arn:aws:iam::811596193553:user/Level6).

  The Major security concern here is that the User attempted to launch EC2 instances Six hundred seventy-six thousand one hundred twenty-seven (676,127) times under RunInstances eventname probably for crypto-mining or other nefarious activities.

Likewise, other potential threats identified from the query include

• CreateUser (2 events), CreateRole (2 events), and CreateAccessKey (4 events): These events are related to the creation of new users, roles, or access keys. If these actions are not part of normal administrative tasks, they could indicate attempts to establish backdoor access to the AWS environment.

• DeleteAccessKey (2 events): This might indicate an attempt to cover tracks after accessing an account or performing unauthorized activities.

• GetSessionToken (2 events): This event is used to obtain a session token for AWS temporary credentials. While not inherently suspicious, in the context of other unusual activities, it could indicate attempts to gain unauthorized access or escalate privileges.

• ListAccessKeys (2 events), ListRoles (2 events), and ListBuckets (2 events): These events indicate a listing of access keys, roles, and S3 buckets, which could be part of reconnaissance by an attacker trying to understand the environment and find targets for exploitation.

Root access through the console

This SQL query selects unique records of **'ConsoleLogin'** events by root users from the cloudtrail_logs, including details like event time, source, MFA usage, IP address, and user agent, and orders them by these attributes.

SELECT DISTINCT
    eventTime,
    eventSource,
    additionalEventData as MFAUsed,
    sourceIPAddress,
    userAgent
FROM
    cloudtrail_logs
WHERE
    eventName = 'ConsoleLogin' 
    AND userIdentity.type = 'Root'
ORDER BY
    eventTime, eventSource, MFAUsed, sourceIPAddress, userAgent;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 3

Query Result

• Scroll down to view the Query result.

  It's important to note that using the Root user account to access your cloud environment is considered bad practice, as it allows unrestricted access to all resources and configurations, heightening the risk of accidental or malicious changes and security breaches.

  According to Mitre Att&ck Framework

  • Tactic:Privilege Escalation (TA0004)

  • Name: Valid Accounts

  • ID: T1078

  • Sub-ID: .004 ⇒ Valid accounts in cloud environments may allow adversaries to perform actions to achieve Initial Access, Persistence, Privilege Escalation, or Defense Evasion.

Hunting for sign-in failures to the AWS console

This SQL code retrieves details of console login attempts from AWS CloudTrail logs, including the time of the event, source IP address, any error messages, AWS region, the username of the user attempting the login, and flags if multi-factor authentication (MFA) was used, filtering for events generated by sign-ins to the AWS Management Console.

SELECT 
    eventTime,
    sourceIPAddress,
    errorMessage,
    awsRegion,
    userIdentity.UserName,
    additionalEventData as MFAUsed
FROM 
    cloudtrail_logs
WHERE 
    eventSource = 'signin.amazonaws.com' AND
    eventName = 'ConsoleLogin';

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 4

Query Result

• Scroll down to view the Query result.

  It is worth noting that for enhanced security, AWS does not log IAM user names in CloudTrail and GuardDuty for sign-in failures caused by incorrect usernames, instead marking them as HIDDEN_DUE_TO_SECURITY_REASONS.

  

  READ MORE

Investigating Created events (IAM, S3, and EC2) resources via AWS Management Console.

This SQL code retrieves CloudTrail logs for events related to creating EC2 instances, S3 buckets, and IAM users specifically initiated via the AWS Management Console, including details like event time, region, source IP, and user agent.

SELECT
  eventTime,
  eventName,
  awsRegion,
  sourceIPAddress,
  userAgent,
  userIdentity.arn,
  requestParameters,
  responseElements
FROM
  cloudtrail_logs
WHERE
  (eventName IN ('RunInstances', 'CreateBucket', 'CreateUser')) AND
  eventSource IN ('ec2.amazonaws.com', 's3.amazonaws.com', 'iam.amazonaws.com') AND
  userAgent LIKE '%console.amazonaws.com%';

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 5

Query Result

• Scroll down to view the Query result.

Investigating Created events (IAM, S3, and EC2) resources via AWS Management Console, AWS CLI, SDKs, or direct API calls

This SQL code retrieves CloudTrail logs for events related to creating EC2 instances, S3 buckets, and IAM users, filtering by specific AWS services (ec2.amazonaws.com, s3.amazonaws.com, iam.amazonaws.com) including access made through the AWS Management Console, AWS CLI, SDKs, or direct API calls

SELECT
  eventTime,
  eventName,
  awsRegion,
  sourceIPAddress,
  userAgent,
  userIdentity.arn,
  requestParameters,
  responseElements
FROM
    cloudtrail_logs
WHERE
  (eventName IN ('CreateBucket', 'RunInstances', 'CreateUser')) AND
  eventSource IN ('ec2.amazonaws.com', 's3.amazonaws.com', 'iam.amazonaws.com');

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 6

• Scroll down to view the Query result.

File is too large, Can't upload file to Github

Hunting for a CloudTrail disruption

This SQL code retrieves specific details from CloudTrail logs, including event time, error messages, user ARN, source IP, event names, user agents, and AWS regions, for events related to stopping or modifying CloudTrail Logs.

SELECT
  from_iso8601_timestamp(eventTime) AS event_time,
  errorMessage,
  userIdentity.arn AS user_arn,
  sourceIPAddress AS source_ip,
  eventName,
  userAgent,
  awsRegion
FROM
  cloudtrail_logs
WHERE
  eventName IN ('StopLogging', 'DeleteTrail', 'UpdateTrail');

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 7

• Scroll down to view the Query result.

Hunting for unauthorized calls

This SQL code counts occurrences of AWS CloudTrail events with specific error codes ('AccessDenied' or 'UnauthorizedOperation'), grouping them by event name and user ARN, and orders the results by the number of occurrences in descending order.

SELECT COUNT(*) AS occurrences,
       events.event_name,
       events.user_arn
FROM (
    SELECT eventName AS event_name,
           userIdentity.arn AS user_arn
    FROM cloudtrail_logs
    WHERE errorCode IN ('AccessDenied', 'UnauthorizedOperation')
) AS events
GROUP BY events.event_name, events.user_arn
ORDER BY occurrences DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 9

• Scroll down to view the Query result.

Hunting for “whoami”

This SQL code counts occurrences of AWS CloudTrail events with the eventName **'GetCallerIdentity'**, grouping them by user ARN, source IP address, and user agent, and orders the results by the number of occurrences in descending order.

SELECT COUNT(*) AS occurrences,
       events.user_arn,
       events.source_ip_address,
       events.user_agent
FROM (
    SELECT userIdentity.arn AS user_arn,
           sourceIPAddress AS source_ip_address,
           userAgent AS user_agent
    FROM cloudtrail_logs
    WHERE eventName = 'GetCallerIdentity'
) AS events
GROUP BY events.user_arn, events.source_ip_address, events.user_agent
ORDER BY occurrences DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 10

• Scroll down to view the Query result.

Efforts to compromise accounts by creating IAM users

This SQL query retrieves details of user creation events from CloudTrail logs, specifically the time, name, AWS region, user ARN, user type, and request parameters, for events where a new IAM user was created, ordering the results by the time of the event in descending order, to analyze or audit IAM user creation activities.

SELECT
  eventTime,
  eventName,
  awsRegion,
  userIdentity.arn,
  userIdentity.type,
  requestParameters
FROM
  cloudtrail_logs
WHERE
  eventName = 'CreateUser'
  AND eventSource = 'iam.amazonaws.com'
ORDER BY
  eventTime DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 19

• Scroll down to view the Query result.

Hunting for access secret in Secrets Manager

This SQL code retrieves specific fields (userIdentity.arn, errorMessage, errorCode, sourceIPAddress, userAgent) from the cloudtrail_logs table where the eventName is **'GetSecretValue'**.

SELECT userIdentity.arn AS user_arn,
       errorMessage, 
       errorCode,
       sourceIPAddress, 
       userAgent
FROM 
    cloudtrail_logs
WHERE eventName = 'GetSecretValue';

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 11

• Scroll down to view the Query result.

An adversary might seek to acquire sensitive information from Secrets Manager, aiming to pilfer certificates, credentials, and other sensitive data. This action aligns with the MITRE ATT&CK Framework, specifically under the Credential Access tactic (TA0006), with the specific technique identified as "Steal Application Access Token" (T1528).

Hunting for xlarge EC2 Instances

This SQL query analyzes CloudTrail logs to count and list the types of instances launched, grouped and sorted by the most common instance types, aiming to understand the usage patterns and identify large EC2 instances launched as recorded in CloudTrail logs.

SELECT
  JSON_EXTRACT_SCALAR(requestParameters, '$.instanceType') AS instance_type,
  COUNT(*) AS count
FROM
  cloudtrail_logs
WHERE
  eventName = 'RunInstances'
  AND JSON_EXTRACT_SCALAR(requestParameters, '$.instanceType') IS NOT NULL
GROUP BY
  JSON_EXTRACT_SCALAR(requestParameters, '$.instanceType')
ORDER BY
  count DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 18

• Scroll down to view the Query result.

Hunting for S3 Buckets Brute Force Attempts

This SQL code identifies and summarizes repeated attempts to access bucket ACLs from CloudTrail logs, filtering for sources with more than five attempts, including their first and last attempt times, to highlight potential security concerns or audit frequent access patterns.

SELECT
  sourceIPAddress,
  eventName,
  eventSource,
  awsRegion,
  COUNT(*) AS request_count,
  MIN(eventTime) AS first_attempt_time,
  MAX(eventTime) AS last_attempt_time
FROM
  cloudtrail_logs
WHERE
  eventName = 'GetBucketAcl'
  AND eventSource = 's3.amazonaws.com'
GROUP BY
  sourceIPAddress, eventName, eventSource, awsRegion
HAVING
  COUNT(*) > 5
ORDER BY
  request_count DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 20

• Scroll down to view the Query result.

Hunting for suspicious user agents (Kali, Parrot, PowerShell)

This SQL query identifies and summarizes CloudTrail log entries based on specific user agent strings (Kali, Parrot, PowerShell), grouping the results by user agent, source IP, event name, and AWS region, and orders them by frequency to detect potential security threats or suspicious activities.

SELECT
  COUNT(*) AS request_count,
  sourceIPAddress,
  eventName,
  useragent,
  awsRegion,
  MIN(eventTime) AS first_seen,
  MAX(eventTime) AS last_seen
FROM
  cloudtrail_logs
WHERE
  LOWER(useragent) LIKE '%kali%'
  OR LOWER(useragent) LIKE '%parrot%' 
  OR LOWER(useragent) LIKE '%powershell%'
GROUP BY
  useragent, sourceIPAddress, eventName, awsRegion
ORDER BY
  request_count DESC;

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 21

• Scroll down to view the Query result.

Hunting for permanent key creation

This SQL query extracts detailed information about 'CreateAccessKey' events from cloudtrail_logs, focusing on when they occurred, who initiated them, and specific details about the created access keys, to analyze IAM user activities regarding AWS access key creation.

SELECT
  eventTime,
  eventName,
  sourceIPAddress,
  userIdentity.arn,
  JSON_EXTRACT(responseElements, '$.accessKey.createDate') AS createDate,
  JSON_EXTRACT(responseElements, '$.accessKey.status') AS status,
  JSON_EXTRACT(responseElements, '$.accessKey.accessKeyId') AS accessKeyId,
  errorCode,
  errorMessage
FROM
  cloudtrail_logs
WHERE
  eventName = 'CreateAccessKey'
  AND userIdentity.type = 'IAMUser';

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 23

• Scroll down to view the Query result.

Creation of EC2 instance that does not require IMDSv2

No presence of instanceMetadata in requestParameters properties.

Consider verifying the CloudTrail logs for the presence of instanceMetadataOptions in requestParameters and ensure your logs are comprehensive and include data for the period you're interested in investigating.

SELECT
  eventTime,
  eventName,
  awsRegion,
  sourceIPAddress,
  userIdentity.arn,
  requestParameters,
  JSON_EXTRACT_SCALAR(requestParameters, '$.instanceMetadataOptions.httpTokens') AS httpTokens
FROM
  cloudtrail_logs
WHERE
  eventName = 'RunInstances'
  AND JSON_EXTRACT_SCALAR(requestParameters, '$.instanceMetadataOptions.httpTokens') != 'required'
ORDER BY
  eventTime DESC;

Identifying the Creation of public S3 buckets

This SQL query retrieves information about specific S3 bucket permission modification events from CloudTrail logs. It aims to identify instances where a bucket's ACL (Access Control List) was set to allow public access or access to all authenticated AWS users, focusing on events in the 'us-west-2' AWS region.

SELECT
  eventtime,
  useridentity.arn AS user_arn,
  JSON_EXTRACT(requestParameters, '$.bucketName') AS bucketName,
  JSON_EXTRACT(requestParameters, '$.acl') AS bucket_acl,
  sourceipaddress,
  eventname,
  awsregion,
  json_extract(requestparameters, '$.AccessControlPolicy.AccessControlList.Grant') AS acl_details,
  useragent
FROM
  cloudtrail_logs
WHERE
  eventsource = 's3.amazonaws.com'
  AND eventname = 'PutBucketAcl'
  AND (
    lower(requestparameters) LIKE '%allusers%'
    OR lower(requestparameters) LIKE '%authenticatedusers%'
  )
  AND awsregion = 'us-west-2'

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 27

• Scroll down to view the Query result.

Brute force attempts to assume roles

This SQL code retrieves and ranks records of failed AssumeRole attempts (where there was an error) from AWS CloudTrail logs, grouping them by various details like time, user identity, and source IP, and only includes groups with more than three attempts. It aims to identify and analyze suspicious or unsuccessful access attempts.

SELECT
  eventtime,
  useridentity.arn AS user_arn,
  useridentity.type AS user_type,
  eventname,
  awsregion,
  errorcode,
  sourceipaddress,
  useragent,
  COUNT(*) AS attempt_count
FROM
  cloudtrail_logs
WHERE
  eventsource = 'sts.amazonaws.com'
  AND eventname = 'AssumeRole'
  AND errorcode IS NOT NULL
GROUP BY
  eventtime,
  useridentity.arn,
  useridentity.type,
  eventname,
  awsregion,
  errorcode,
  sourceipaddress,
  useragent
HAVING
  COUNT(*) > 3 
ORDER BY
  attempt_count DESC

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 28

• Scroll down to view the Query result.

Attempts to perform recon actions on accounts

This SQL code identifies and lists frequent AWS CloudTrail log entries for specific user actions, filtering by event source and name within a certain period, and shows details like user, action, location, and request count, only for entries with more than five requests.

SELECT
  eventtime,
  useridentity.arn AS user_arn,
  eventname,
  awsregion,
  sourceipaddress,
  errorcode,
  COUNT(*) AS request_count,
  useragent
FROM
  cloudtrail_logs
WHERE
  eventsource IN ('iam.amazonaws.com', 'ec2.amazonaws.com', 's3.amazonaws.com')
  AND eventname IN (
    'ListUsers',
    'ListRoles',
    'DescribeSecurityGroups',
    'ListBuckets',
    'DescribeInstances',
    'GetUser',
    'ListAccessKeys',
    'DescribeKeyPairs',
    'ListPolicies',
    'ListGroups'
  )
  AND eventtime BETWEEN '2018-02-25T00:00:00Z' AND '2020-05-05T23:59:59Z'
GROUP BY
  eventtime,
  useridentity.arn,
  eventname,
  awsregion,
  sourceipaddress,
  errorcode,
  useragent
HAVING
  COUNT(*) > 5
ORDER BY
  request_count DESC

• Copy and paste the above SQL code to the Query Editor and click on the run button.

Click on the (+) plus Icon to Add new tab of Query e.g Query 29

• Scroll down to view the Query result.

Special Shout out to Andre Rall, the Cloud Security Engineer @ Uptycs.com, for His incredible work on Athena GPT, which has significantly boosted my productivity in analyzing Cloudtrail logs. You can access it Here.

Referenced Resources

• https://summitroute.com/blog/2020/10/09/public_dataset_of_cloudtrail_logs_from_flaws_cloud/

• https://medium.com/@george.fekkas/quick-and-dirty-cloudtrail-threat-hunting-log-analysis-b64af10ef923

• https://chat.openai.com/g/g-5BxCT0zgW-athena-incident-response-expert

• https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html