Querying S3 access logs with Athena, at zero infrastructure cost

S3 access logs tend to get enabled and then forgotten: compliance says keep them, so they pile up, and nobody ever runs a single query. You don’t need a SIEM or an OpenSearch cluster to change that. The logs are already sitting in a bucket, and Athena can query them directly. This is the minimal setup I actually use.

First: give the logs date-based paths

By default, S3 server access logging drops every log object under one flat prefix with no date structure — and a flat prefix means every Athena query is a full scan. Before anything else, open the logging settings on the source bucket and enable date-based partitioning. New logs will land like this:

s3://my-log-bucket/logs/123456789012/ap-northeast-1/my-data-bucket/2026/08/10/

You can partition by event time or delivery time; for security investigations, use event time. Note this only affects logs written after the change — the old ones stay flat. Either build a separate table for them or accept that you can only query the new ones.

The table: partition projection, no Glue crawler

With dates in the path, partition projection lets Athena derive partitions straight from the key structure. No crawler, no MSCK REPAIR:

CREATE EXTERNAL TABLE s3_access_logs (
  bucketowner STRING, bucket_name STRING, requestdatetime STRING,
  remoteip STRING, requester STRING, requestid STRING, operation STRING,
  key STRING, request_uri STRING, httpstatus STRING, errorcode STRING,
  bytessent BIGINT, objectsize BIGINT, totaltime STRING,
  turnaroundtime STRING, referrer STRING, useragent STRING,
  versionid STRING, hostid STRING, sigv STRING, ciphersuite STRING,
  authtype STRING, endpoint STRING, tlsversion STRING
)
PARTITIONED BY (`dt` STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ("[^"]*"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ("[^"]*"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$'
)
LOCATION 's3://my-log-bucket/logs/123456789012/ap-northeast-1/my-data-bucket/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.format' = 'yyyy/MM/dd',
  'projection.dt.interval' = '1',
  'projection.dt.interval.unit' = 'DAYS',
  'projection.dt.range' = '2026/01/01,NOW',
  'storage.location.template' = 's3://my-log-bucket/logs/123456789012/ap-northeast-1/my-data-bucket/${dt}'
);

The columns and regex are the standard ones from the official docs — copy them as-is. You only edit the LOCATION paths and the start date in projection.dt.range. From here on, every query carries a dt condition and Athena reads only those days.

The three things I check first

Who touched a sensitive prefix — the first question in any security review:

SELECT requestdatetime, requester, remoteip, key
FROM s3_access_logs
WHERE dt BETWEEN '2026/08/01' AND '2026/08/10'
  AND key LIKE 'confidential/%'
  AND operation LIKE 'REST.GET%'
ORDER BY requestdatetime;

Whether 4xx responses spike in some window — the signal for scanning and brute-force attempts. Aggregate first, then look up requester and remoteip:

SELECT dt, httpstatus, count(*) AS hits
FROM s3_access_logs
WHERE dt >= '2026/08/01' AND httpstatus LIKE '4%'
GROUP BY dt, httpstatus
ORDER BY hits DESC;

When each object was last read — cross-check against S3 Inventory, and any key that never shows up here is a candidate for a lifecycle policy:

SELECT key, max(requestdatetime) AS last_read
FROM s3_access_logs
WHERE dt >= '2026/01/01' AND operation LIKE 'REST.GET%'
GROUP BY key;

Cost

Athena bills by data scanned, at 5 USD per TB. With partitions and date conditions in place, a month of access logs usually costs a few cents to query. The expensive query is the one where you forget the dt condition and scan the whole bucket — so keep the routine queries as saved queries with the conditions baked in.

Know the boundaries

• Server access logs are best-effort: occasional gaps, occasional duplicates. Good enough for trends and investigations, not admissible as audit evidence — that’s what CloudTrail data events are for (billed separately).
• Delivery lags by hours. This is not a real-time alerting source.
• Give the log bucket its own lifecycle policy, or it becomes the fattest line on the bill. And don’t point the log bucket’s access logging at itself — that recurses.