Follow Athena doesn’t store logs itself. It queries log files in S3, such as CloudTrail, VPC Flow Logs, load balancer and WAF logs, through tables you define. Normal query results are saved as CSV in your results bucket. To get JSON instead, wrap the query in UNLOAD … WITH (format = ‘JSON’): Athena writes newline-delimited JSON to an S3 prefix, and you download it.
Plenty of AWS teams keep months of logs in S3 and query them with Athena rather than shipping everything to a SIEM. Prime Core has more than 24,000 unique detection rules, mapped to 676 MITRE ATT&CK techniques. Below is how to get a slice out, sanitize it, and investigate it for free in LogTotal.
Before you start
Permissions. You need to run Athena queries (athena:StartQueryExecution and related), read the table definitions in Glue, read the source bucket, and write to an export bucket. Add kms:Decrypt if the logs are encrypted with KMS.
Know the table and its partitions. A CloudTrail table is usually partitioned by region and date, either with partition projection or with partitions you’ve added. Athena bills by data scanned, so filter on partitions every time. A query without them can scan years of logs.
A workgroup. Athena runs queries in a workgroup with a results location. UNLOAD writes to its own target, but the workgroup still needs to be set up.
Export with UNLOAD
UNLOAD (
SELECT *
FROM cloudtrail_logs
WHERE region = 'eu-west-1'
AND day = '2026/10/19'
AND useridentity.arn LIKE '%suspicious-user%'
)
TO 's3://my-athena-exports/ct-1019/'
WITH (format = 'JSON', compression = 'NONE')
Adapt the partition columns (region, day) to your table definition. The target prefix must be empty.
From the command line:
QID=$(aws athena start-query-execution –work-group primary \
–query-string “$(cat unload.sql)” \
–query ‘QueryExecutionId’ –output text)
# wait until SUCCEEDED
until [ “$(aws athena get-query-execution –query-execution-id “$QID” \
–query ‘QueryExecution.Status.State’ –output text)” = “SUCCEEDED” ]; do sleep 5; done
aws s3 cp s3://my-athena-exports/ct-1019/ ./ct-1019/ –recursive
zcat -f ./ct-1019/* > ct_1019.jsonl
zcat -f handles both compressed and uncompressed output, in case compression was left at the default. If the query fails, the wait loop never ends. Check for FAILED too if you script this regularly.
Why not get-query-results? It works, but it returns rows as lists of strings, 1,000 per page, which you then have to stitch back into objects. UNLOAD gives you clean JSON in one step.
Upload, sanitize and review in LogTotal
- Upload. Open logtotal.com and drop in the .jsonl file.
- Sanitize.
- Review.
When it goes wrong
| Symptom | Likely cause | Fix |
|---|---|---|
| Query scans terabytes | No partition filter | Filter on the partition columns |
| Error about the output location | Target prefix isn’t empty | Use a new prefix |
| Access Denied | Missing S3, Glue or KMS permission | Check each one; KMS is the usual suspect |
| Zero rows | Partitions not loaded, or the wrong partition format | Check partition projection or run MSCK REPAIR TABLE |
| Output files won’t open as text | GZIP compression | Use zcat, or set compression = ‘NONE’ |
FAQ
Is this the same as Amazon Security Lake?
Security Lake uses Athena too, but with its own OCSF tables and Lake Formation permissions.
Can I export raw CloudTrail files instead?
Yes. If you only need whole days, copying the files from S3 is simpler. Athena earns its keep when you need to filter.
