VPC Flow Logs in Athena - cheat sheet of queries for rejected traffic, top talkers and who reached what

#Turn on flow logs to S3

aws ec2 create-flow-logs \
  --resource-type VPC --resource-ids vpc-0abc123 \
  --traffic-type ALL \
  --log-destination-type s3 \
  --log-destination arn:aws:s3:::my-flowlogs-bucket/vpc \
  --max-aggregation-interval 60

• --traffic-type ALL captures accepted and rejected traffic. Use REJECT only if cost matters more than visibility.
• Use a 60-second aggregation interval for incident work. The default of 600 seconds makes timing less precise.
• Files land under vpc/AWSLogs/<account>/vpcflowlogs/<region>/YYYY/MM/DD/.

#Athena table (default format, partition projection)

CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs (
  version int, account_id string, interface_id string,
  srcaddr string, dstaddr string, srcport int, dstport int,
  protocol bigint, packets bigint, bytes bigint,
  start bigint, `end` bigint, action string, log_status string
)
PARTITIONED BY (region string, day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LOCATION 's3://my-flowlogs-bucket/vpc/AWSLogs/111122223333/vpcflowlogs/'
TBLPROPERTIES (
  'skip.header.line.count'='1',
  'projection.enabled'='true',
  'projection.region.type'='enum',
  'projection.region.values'='eu-west-1,us-east-1',
  'projection.day.type'='date',
  'projection.day.format'='yyyy/MM/dd',
  'projection.day.range'='2025/01/01,NOW',
  'projection.day.interval'='1',
  'projection.day.interval.unit'='DAYS',
  'storage.location.template'='s3://my-flowlogs-bucket/vpc/AWSLogs/111122223333/vpcflowlogs/${region}/${day}'
);

If you use a custom log format, the columns must match your format's field order. Always filter on day (and region) so Athena only scans the days you need.

Tip

Protocol numbers: 6 = TCP, 17 = UDP, 1 = ICMP. start and end are Unix seconds, so use from_unixtime(start) to read them.

#Rejected traffic

Top rejected flows in the last day:

SELECT srcaddr, dstaddr, dstport, protocol, count(*) AS flows
FROM vpc_flow_logs
WHERE day = date_format(current_date, '%Y/%m/%d') AND region = 'eu-west-1'
  AND action = 'REJECT'
GROUP BY 1,2,3,4
ORDER BY flows DESC
LIMIT 50;

Why can't app A reach database B?

SELECT from_unixtime(start) AS ts, srcaddr, srcport, dstaddr, dstport, action, interface_id
FROM vpc_flow_logs
WHERE day = '2026/10/26' AND region = 'eu-west-1'
  AND srcaddr = '10.0.1.25' AND dstaddr = '10.0.20.40' AND dstport = 5432
ORDER BY start DESC
LIMIT 100;

How to read the result:

• REJECT on the database's ENI means its security group or NACL blocked the traffic.
• No rows at all means the traffic never reached the VPC: check routing, DNS, or the client itself.
• ACCEPT but the connection still fails means the problem is in the application or the OS firewall.

#Top talkers

Bytes by source and destination, last hour:

SELECT srcaddr, dstaddr, sum(bytes)/1024/1024 AS mb
FROM vpc_flow_logs
WHERE day = date_format(current_date, '%Y/%m/%d') AND region = 'eu-west-1'
  AND start > to_unixtime(current_timestamp - interval '1' hour)
GROUP BY 1,2
ORDER BY mb DESC
LIMIT 20;

ENIs sending the most data (useful for NAT gateway and cross-AZ cost questions):

SELECT interface_id, sum(bytes)/1024/1024/1024 AS gb
FROM vpc_flow_logs
WHERE day BETWEEN '2026/10/20' AND '2026/10/26' AND region = 'eu-west-1'
GROUP BY 1 ORDER BY gb DESC LIMIT 20;

Map an ENI to its owner:

aws ec2 describe-network-interfaces --network-interface-ids eni-0abc \
  --query "NetworkInterfaces[0].{desc:Description,type:InterfaceType,attach:Attachment.InstanceId,sg:Groups[].GroupId}"

#Security questions

Private hosts talking to public IPs on unusual ports:

SELECT srcaddr, dstaddr, dstport, sum(bytes) AS bytes, count(*) AS flows
FROM vpc_flow_logs
WHERE day = date_format(current_date, '%Y/%m/%d') AND region = 'eu-west-1'
  AND action = 'ACCEPT'
  AND regexp_like(srcaddr, '^10\.')
  AND NOT regexp_like(dstaddr, '^(10\.|172\.(1[6-9]|2[0-9]|3[01])\.|192\.168\.)')
  AND dstport NOT IN (80, 443, 123, 53)
GROUP BY 1,2,3
ORDER BY bytes DESC LIMIT 50;

Everything an IP talked to (incident scoping):

SELECT dstaddr, dstport, action, min(from_unixtime(start)) AS first_seen,
       max(from_unixtime(`end`)) AS last_seen, sum(bytes) AS bytes
FROM vpc_flow_logs
WHERE day BETWEEN '2026/10/24' AND '2026/10/26' AND region = 'eu-west-1'
  AND srcaddr = '10.0.3.77'
GROUP BY 1,2,3 ORDER BY bytes DESC;

Inbound SSH/RDP attempts from the internet:

SELECT srcaddr, dstaddr, dstport, action, count(*) AS attempts
FROM vpc_flow_logs
WHERE day = date_format(current_date, '%Y/%m/%d') AND region = 'eu-west-1'
  AND dstport IN (22, 3389)
  AND NOT regexp_like(srcaddr, '^10\.')
GROUP BY 1,2,3,4 ORDER BY attempts DESC LIMIT 50;

#Gotchas

#Checklist

• Flow logs on, ALL traffic, 60-second interval for critical VPCs
• Athena table with partition projection
• Saved queries for rejected traffic, top talkers and IP scoping
• S3 lifecycle rule to expire old logs