Uses AWS Athena to query CloudTrail, VPC Flow Logs, S3 access logs, and ALB logs for forensic investigation. Covers CREATE TABLE DDL with partition projection, forensic SQL queries for detecting unauthorized access, data exfiltration, lateral movement, and privilege escalation. Use when investigating AWS security incidents or building cloud-native forensic workflows at scale.
Uses AWS Athena to query CloudTrail, VPC Flow Logs, S3 access logs, and ALB logs for forensic investigation. Covers CREATE TABLE DDL with partition projection, forensic SQL queries for detecting unauthorized access, data exfiltration, lateral movement, and privilege escalation. Use when investigating AWS security incidents or building cloud-native forensic workflows at scale.
Vendored verbatim from an external Apache-2.0 security-skill library, pinned by commit. Exceeds the internal 300-line skill guideline (agent-code-constraints.md) -- kept as-is because this is vendored reference material (forensics/threat-intel procedure), not Yana AI-authored content, and trimming would damage technical accuracy.
Performing Cloud Log Forensics with AWS Athena
When to Use
When investigating AWS security incidents that require querying massive volumes of cloud logs
When performing forensic analysis across CloudTrail, VPC Flow Logs, S3 access logs, and ALB logs
When building reusable Athena tables with partition projection for ongoing incident response
When hunting for indicators of compromise across multiple AWS log sources simultaneously
When creating evidence-grade SQL queries for compliance audits or legal proceedings
Prerequisites
AWS account with Athena, S3, and Glue permissions
CloudTrail configured to deliver logs to an S3 bucket
VPC Flow Logs enabled and publishing to S3
S3 server access logging enabled on target buckets
ALB access logging enabled and publishing to S3
Python 3.8+ with boto3 installed
Appropriate IAM permissions for Athena queries and S3 access
Instructions
Phase 1: Create Athena Database and CloudTrail Table
Create a dedicated forensics database and CloudTrail table using partition projection
to automatically discover partitions without manual ALTER TABLE statements.
SELECT
eventtime,
useridentity.arn AS caller_arn,
useridentity.accountid AS account,
eventsource,
eventname,
errorcode,
errormessage,
sourceipaddress,
useragent
FROM cloud_forensics.cloudtrail_logs
WHERE errorcode IN ('AccessDenied', 'UnauthorizedAccess', 'Client.UnauthorizedAccess')
ANDtimestampBETWEEN'2024/01/01'AND'2024/12/31'ORDERBY eventtime DESC
LIMIT 1000;
Detect Privilege Escalation Attempts
SELECT
eventtime,
useridentity.arn AS actor,
eventname,
eventsource,
json_extract_scalar(requestparameters, '$.policyArn') AS policy_arn,
json_extract_scalar(requestparameters, '$.roleName') AS role_name,
json_extract_scalar(requestparameters, '$.userName') AS target_user,
sourceipaddress
FROM cloud_forensics.cloudtrail_logs
WHERE eventname IN (
'AttachUserPolicy', 'AttachRolePolicy', 'AttachGroupPolicy',
'PutUserPolicy', 'PutRolePolicy', 'PutGroupPolicy',
'CreatePolicyVersion', 'SetDefaultPolicyVersion',
'AddUserToGroup', 'UpdateAssumeRolePolicy',
'CreateAccessKey', 'CreateLoginProfile',
'UpdateLoginProfile', 'AssumeRole'
)
ANDtimestampBETWEEN'2024/01/01'AND'2024/12/31'ORDERBY eventtime DESC;
Detect Data Exfiltration via S3
SELECT
eventtime,
useridentity.arn AS actor,
eventname,
json_extract_scalar(requestparameters, '$.bucketName') AS bucket,
json_extract_scalar(requestparameters, '$.key') AS object_key,
sourceipaddress,
useragent
FROM cloud_forensics.cloudtrail_logs
WHERE eventsource ='s3.amazonaws.com'AND eventname IN ('GetObject', 'CopyObject', 'PutBucketPolicy',
'PutBucketAcl', 'PutObjectAcl', 'SelectObjectContent')
AND sourceipaddress NOTLIKE'10.%'AND sourceipaddress NOTLIKE'172.%'AND sourceipaddress NOTLIKE'192.168.%'ANDtimestampBETWEEN'2024/01/01'AND'2024/12/31'ORDERBY eventtime DESC;
Detect Lateral Movement via VPC Flow Logs
SELECT
srcaddr,
dstaddr,
dstport,
protocol,
SUM(packets) AS total_packets,
SUM(bytes) AS total_bytes,
COUNT(*) AS connection_count,
MIN(from_unixtime(start)) AS first_seen,
MAX(from_unixtime("end")) AS last_seen
FROM cloud_forensics.vpc_flow_logs
WHERE action ='ACCEPT'AND srcaddr LIKE'10.%'AND dstport IN (22, 3389, 5985, 5986, 445, 135, 139)
ANDdateBETWEEN'2024/06/01'AND'2024/06/30'GROUPBY srcaddr, dstaddr, dstport, protocol
HAVINGCOUNT(*) >100ORDERBY connection_count DESC;
Detect Port Scanning Activity
SELECT
srcaddr,
COUNT(DISTINCT dstport) AS unique_ports_scanned,
COUNT(DISTINCT dstaddr) AS unique_targets,
SUM(packets) AS total_packets,
MIN(from_unixtime(start)) AS first_seen,
MAX(from_unixtime("end")) AS last_seen
FROM cloud_forensics.vpc_flow_logs
WHERE action ='REJECT'ANDdateBETWEEN'2024/06/01'AND'2024/06/30'GROUPBY srcaddr
HAVINGCOUNT(DISTINCT dstport) >25ORDERBY unique_ports_scanned DESC;
Detect Suspicious S3 Bulk Downloads
SELECT
remote_ip,
requester,
bucket_name,
COUNT(*) AS request_count,
SUM(bytes_sent) AS total_bytes_downloaded,
COUNT(DISTINCT key) AS unique_objects,
MIN(request_datetime) AS first_request,
MAX(request_datetime) AS last_request
FROM cloud_forensics.s3_access_logs
WHERE operation LIKE'%GET%'AND http_status =200GROUPBY remote_ip, requester, bucket_name
HAVINGCOUNT(*) >500ORDERBY total_bytes_downloaded DESC;
Correlate findings across log sources for comprehensive incident timelines.
-- Correlate suspicious CloudTrail actor with VPC Flow LogsWITH suspicious_ips AS (
SELECTDISTINCT sourceipaddress AS ip
FROM cloud_forensics.cloudtrail_logs
WHERE errorcode ='AccessDenied'ANDtimestampBETWEEN'2024/06/01'AND'2024/06/30'
)
SELECT
v.srcaddr,
v.dstaddr,
v.dstport,
v.protocol,
SUM(v.bytes) AS total_bytes,
COUNT(*) AS flow_count
FROM cloud_forensics.vpc_flow_logs v
JOIN suspicious_ips s ON v.srcaddr = s.ip
WHERE v.date BETWEEN'2024/06/01'AND'2024/06/30'GROUPBY v.srcaddr, v.dstaddr, v.dstport, v.protocol
ORDERBY total_bytes DESC;
Examples
# Quick-start: run the forensics agent for a full investigation
python agent.py \
--action full_investigation \
--database cloud_forensics \
--start-date 2024-06-01 \
--end-date 2024-06-30 \
--output forensics_report.json
# Run specific queries only
python agent.py \
--action privilege_escalation \
--database cloud_forensics \
--start-date 2024-06-15 \
--end-date 2024-06-16# Create all forensic tables from scratch
python agent.py \
--action setup_tables \
--cloudtrail-bucket my-cloudtrail-logs \
--vpc-flow-bucket my-vpc-flow-logs \
--s3-access-bucket my-s3-access-logs \
--alb-bucket my-alb-logs \
--account-id123456789012 \
--regions us-east-1,us-west-2
