Lion Cipher Lab

Engineer detections over raw CloudTrail logs with SQL

• Cloud Security
• Advanced
• ~90 min
• Free · no account required

Why it matters

Managed detectors like GuardDuty are excellent, but they are somebody else's opinion about your logs — and they stop where their rules stop. Every serious cloud security team eventually writes its own detections directly over raw CloudTrail, because the raw log is the ground truth: it records who called which API, from where, with which credential, and whether it worked. The engineer who can open that JSON, model an attack in SQL, tune out the false positives, and map each query to a MITRE ATT&CK technique is doing the highest-leverage work in cloud defense — one query can catch a whole class…

What you'll learn

• Reading the raw CloudTrail record schema (userIdentity, accessKeyId, readOnly, errorCode)
• The management-events vs data-events blind spot
• Loading and querying JSON logs in DuckDB with SQL
• Writing SQL detections mapped to MITRE ATT&CK (Cloud) techniques
• First-seen / rare-value anomaly logic vs static thresholds
• Tuning detections against false positives (allowlisting automation)
• Reconstructing an attack timeline from evidence
• Detection engineering as a portable craft (local harness -> SIEM/Athena/CloudTrail Lake)

Steps in this lab

1. The job: you are the detector, not the detected-for
2. The CloudTrail record schema, field by field
3. The blind spot you must know before you trust any query: management vs data events
4. Set up the harness: install DuckDB (offline analytics in one command)
5. Generate the sample CloudTrail log (benign baseline + a staged attack)
6. Load and flatten the log into DuckDB
7. Detection 1 — trail tampering (ATT&CK T1685.002, formerly T1562.008: disable or modify cloud logs)
8. Detection 2 — a burst of AccessDenied from one principal (ATT&CK T1087, Discovery)
9. Detection 3 — the privilege-escalation API set, then tune out the false positives (ATT&CK T1098)
10. Detection 4 — a known key from a new IP: first-seen logic beats thresholds (ATT&CK T1078)
11. Detection 5 — root usage, readOnly noise-cutting, and normalizing assumed roles
12. Reconstruct the attack timeline from the evidence
13. Prove the blind spot, then take the craft to scale
14. What you can now do

Open the full interactive lab →