If you can read a CloudTrail log, you already understand the data model. SQL just gives you a faster, repeatable way to ask questions of it — and a language for turning those questions into detection rules.

This lab starts from scratch. No SQL background assumed. By the end you'll have five working detection queries covering brute force, privilege escalation, post-compromise enumeration, after-hours access, and impossible travel — all running against a simulated CloudTrail dataset in a local SQLite database.

What you'll learn

  • Set up a SQLite database from a realistic simulated CloudTrail CSV
  • Write SELECT, WHERE, GROUP BY, ORDER BY, and subquery statements
  • Frame every SQL concept as a concrete log analysis task
  • Build 5 detection queries credible in a SOC or detection engineering context
  • Understand why structured querying scales where manual log review doesn't

Prerequisites

  • Ubuntu Linux (or WSL on Windows)
  • Python 3 installed (python3 --version to check)
  • SQLite3 (sudo apt install sqlite3)
  • No SQL experience required

Background: why SQL for log analysis

Manual log review works up to a point. grep, jq, and eyeballing JSON gets you through an incident when you know roughly what you're looking for. It breaks down when:

  • You need to count occurrences across thousands of events
  • You want to correlate two patterns — failed logins followed by IAM changes
  • You want a rule you can re-run tomorrow with one command

SQL is a query language designed for exactly this. It runs against structured data — tables with rows and columns — and lets you filter, group, count, and join that data in ways that map directly to detection logic.

CloudTrail is already structured. Every API call produces a record with a known set of fields: who called it, when, from where, what resource, whether it succeeded. A table of those records is a natural fit for SQL.

SQLite

SQLite is a file-based database — no server, no configuration, no credentials. You point it at a .db file and start querying. For local labs and offline analysis it's the right tool.

The dataset

The simulated dataset covers 28 days of AWS API activity across 5 user accounts and ~393 events. It's generated with a Python script so you can reproduce or modify it. The dataset includes seeded anomalies that your detection queries will uncover:

  • Brute force: 18 rapid ConsoleLogin failures from an external IP, followed by a successful login
  • Privilege escalation: IAM policy attachment granting AdministratorAccess to a non-admin user
  • Post-compromise enumeration: 10 distinct read-only enumeration calls across IAM, EC2, S3, and RDS within 2 minutes
  • After-hours access: A regular user accessing payroll data at 3am
  • Impossible travel: The same user account authenticating from London and Tokyo 4 minutes apart

The dataset fields are:

Field Description
event_time ISO 8601 timestamp of the API call
event_name AWS API action (e.g. ConsoleLogin, AttachUserPolicy)
source_ip IP address of the caller
user_identity Full IAM ARN of the caller
username Short username extracted from the ARN
aws_region AWS region where the call was made
error_code Empty string for success; error string for failures
user_agent Client that made the call (console, CLI, SDK)
resource_arn ARN of the resource targeted by the call

Setup

1. Install SQLite

sudo apt install sqlite3

2. Create a working directory

mkdir ~/sql-lab && cd ~/sql-lab

3. Download the lab files

Download these three files into ~/sql-lab/:

4. Generate the dataset (optional — skip if using the pre-generated CSV)

python3 generate_dataset.py

Expected output:

Generated 393 rows

Top 10 event types:
  ListUsers: 57
  GetSecretValue: 56
  ...

5. Load the database

python3 load_to_sqlite.py

Expected output:

✓ Loaded 393 rows into cloudtrail.db

6. Open SQLite and configure output formatting

sqlite3 cloudtrail.db

Then inside the SQLite shell, run these two formatting commands:

.mode column
.headers on

You should now be able to run:

SELECT COUNT(*) AS total_events FROM cloudtrail;

And see 393. You're ready.

Note: Every query below can be run directly in the SQLite shell. Exit at any time with .quit.

Module 1 — SELECT: reading the log table

SELECT is how you read data from a table. The basic shape is:

SELECT column1, column2 FROM table_name;

The * wildcard selects all columns. LIMIT caps the number of rows returned — always use it when exploring an unfamiliar table.

Check the schema

Before querying, confirm what columns exist:

PRAGMA table_info(cloudtrail);

This returns the column names, types, and constraints — your map of the data.

Read the first 5 rows

SELECT * FROM cloudtrail LIMIT 5;

Select only the columns relevant for triage

SELECT event_time, username, event_name, source_ip, error_code
FROM cloudtrail
LIMIT 10;

Most error_code values will be empty — those are successful API calls. Non-empty values are where calls failed or were denied.

Count total events

SELECT COUNT(*) AS total_events FROM cloudtrail;

COUNT(*) counts all rows. AS total_events renames the output column. Result: 393 — your baseline for this 28-day window.

Module 2 — WHERE: filtering for suspicious events

WHERE adds a condition that every returned row must satisfy. This is how you cut noise — isolating failures, specific users, or known-bad IPs.

Show only failed events

SELECT event_time, username, event_name, source_ip, error_code
FROM cloudtrail
WHERE error_code != '';

!= means "not equal to". Empty error_code means success, so this returns only failures. You should see ~18 rows — all from one user, all Failed authentication. One account generating all authentication failures across 28 days is worth investigating.

Filter by user

SELECT event_time, event_name, source_ip, error_code
FROM cloudtrail
WHERE username = 'eve';

This returns eve's full 33-event history. Read it chronologically — you should be able to see the shape of an account compromise: rapid authentication failures, a successful login, then IAM enumeration.

Filter by time window

SELECT event_time, username, event_name, resource_arn
FROM cloudtrail
WHERE event_time >= '2024-06-18T02:00:00Z'
  AND event_time <= '2024-06-18T04:00:00Z';

AND chains conditions — both must be true. This surfaces what happened between 2am and 4am on June 18th: 8 events from alice, all accessing company-data/payroll/.

Filter by IP

SELECT event_time, username, event_name, aws_region
FROM cloudtrail
WHERE source_ip = '185.220.101.45';

In a real SOC you'd often start from a known-bad IP (from a threat intel feed or an alert) and work backwards to what it touched. This returns all activity from that IP.

Combine conditions

SELECT event_time, username, event_name, error_code
FROM cloudtrail
WHERE username = 'eve'
  AND event_name = 'ConsoleLogin'
  AND error_code != '';

This isolates exactly 18 rows: eve's failed login attempts only. This is the core filter of a brute force detection rule.

Module 3 — GROUP BY and ORDER BY: turning events into patterns

GROUP BY collapses rows that share a value into a single summary row, so you can count or measure them. ORDER BY sorts the results. DESC sorts highest-first — useful for finding outliers.

A single failed login is noise. 18 from the same IP in 2 minutes is a brute force attack. GROUP BY makes that visible.

Count events per user

SELECT username, COUNT(*) AS event_count
FROM cloudtrail
GROUP BY username
ORDER BY event_count DESC;

Eve has the fewest total events but her 33 are concentrated in a short window and focused on IAM. Volume alone doesn't tell the story — but total event counts per user is always a useful baseline.

Count failed logins per user

SELECT username, COUNT(*) AS failed_logins
FROM cloudtrail
WHERE error_code != ''
GROUP BY username
ORDER BY failed_logins DESC;

Only eve appears — 18 failures, everyone else zero across the full 28-day window. One user accounting for 100% of authentication failures is a significant signal.

Behavioural fingerprint per user

SELECT username, event_name, COUNT(*) AS count
FROM cloudtrail
GROUP BY username, event_name
ORDER BY username, count DESC;

Grouping by two columns gives you a per-user profile. Scroll to eve — her top events are ConsoleLogin failures followed by IAM enumeration calls. No legitimate user's top activity is IAM enumeration.

Busiest source IPs

SELECT source_ip, COUNT(*) AS request_count
FROM cloudtrail
GROUP BY source_ip
ORDER BY request_count DESC;

Normal user IPs will cluster in the 80–100 range. In a real environment you'd join this output against a threat intel feed to flag known-bad IPs immediately.

Activity by hour of day

SELECT 
    strftime('%H', event_time) AS hour_of_day,
    COUNT(*) AS event_count
FROM cloudtrail
GROUP BY hour_of_day
ORDER BY hour_of_day;

strftime('%H', ...) extracts the hour from a timestamp in 24h format. Most activity clusters in hours 08–17. The spikes at 02 and 03 are alice's payroll access and eve's brute force — both happening in the middle of the night.

Brute force threshold detection

SELECT username, source_ip, COUNT(*) AS failed_attempts
FROM cloudtrail
WHERE error_code != ''
GROUP BY username, source_ip
HAVING failed_attempts >= 5
ORDER BY failed_attempts DESC;

HAVING filters after grouping — you can't use WHERE to filter on COUNT(*) because the count doesn't exist until the group is formed. This returns only accounts where failed attempts crossed the threshold. Result: eve, 18 failures, one IP.

Module 4 — Subqueries: multi-step detection logic

A subquery is a SELECT nested inside another query. The inner query runs first and produces a result that the outer query uses. This lets you answer questions that require two steps — like "show me all activity from users who previously failed authentication."

All activity from users who had failed logins

SELECT event_time, username, event_name, source_ip
FROM cloudtrail
WHERE username IN (
    SELECT DISTINCT username
    FROM cloudtrail
    WHERE error_code != ''
)
ORDER BY event_time;

DISTINCT removes duplicates from the inner result. The outer query returns the full history for any user who appears there — failures and successes combined. This gives you the complete attack timeline, not just the failed login phase.

Activity after a successful post-brute-force login

SELECT event_time, username, event_name, resource_arn
FROM cloudtrail
WHERE username = 'eve'
  AND event_time > (
      SELECT MIN(event_time)
      FROM cloudtrail
      WHERE username = 'eve'
        AND event_name = 'ConsoleLogin'
        AND error_code = ''
  )
ORDER BY event_time;

MIN(event_time) returns the earliest matching timestamp. The outer query returns everything eve did after her first successful login — isolating the post-access phase from the brute force noise.

Users active outside business hours who also have daytime activity

SELECT DISTINCT username
FROM cloudtrail
WHERE strftime('%H', event_time) NOT IN (
    '08','09','10','11','12','13','14','15','16','17'
)
AND username IN (
    SELECT DISTINCT username
    FROM cloudtrail
    WHERE strftime('%H', event_time) IN (
        '08','09','10','11','12','13','14','15','16','17'
    )
);

The inner query identifies regular users — those with some business-hours activity. The outer query finds which of those users also appear outside business hours. This targets anomalous after-hours access by established users.

Result: alice. Note that eve doesn't appear here — her entire session was at 2am, so she has no business-hours baseline to compare against. Detection rules encode assumptions about attacker behaviour. A pure external actor with no daytime footprint evades this rule entirely.

Module 5 — Detection queries

Five detection queries built from the SQL you've learned. Each targets a named threat pattern with threshold logic credible in a SOC context.

Detection 1: brute force login attack

Threat: Repeated authentication failures from the same IP against the same account — automated credential stuffing or manual password guessing.

SELECT 
    username,
    source_ip,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt
FROM cloudtrail
WHERE event_name = 'ConsoleLogin'
  AND error_code != ''
GROUP BY username, source_ip
HAVING failed_attempts >= 5
ORDER BY failed_attempts DESC;

The timestamp spread matters as much as the count. 18 failures over 3 weeks could be a forgetful user. 18 failures in 144 seconds is automation. Both the count and the first_attempt/last_attempt spread should feed your alert logic.

Expected result: Eve — 18 failed attempts, first and last timestamps ~2 minutes apart.

Detection 2: privilege escalation via policy attachment

Threat: A user attaching an admin-level policy to their own account after initial access — a standard post-exploitation move to gain persistent elevated privileges.

SELECT
    event_time,
    username,
    source_ip,
    resource_arn,
    user_agent
FROM cloudtrail
WHERE event_name = 'AttachUserPolicy'
  AND resource_arn LIKE '%AdministratorAccess%'
ORDER BY event_time;

LIKE '%AdministratorAccess%' matches any resource_arn containing that string. This query has near-zero false positive rate in most environments — there is no operational reason a non-admin user should be calling AttachUserPolicy against the AdministratorAccess managed policy.

Expected result: One row — eve, 2:15am, attaching AdministratorAccess to herself.

Detection 3: post-compromise enumeration

Threat: Rapid broad enumeration of the AWS environment after gaining access — listing users, roles, buckets, functions, and databases to map what the compromised account can reach.

SELECT
    username,
    source_ip,
    COUNT(DISTINCT event_name) AS unique_enum_calls,
    MIN(event_time) AS window_start,
    MAX(event_time) AS window_end
FROM cloudtrail
WHERE event_name IN (
    'ListBuckets', 'ListUsers', 'ListRoles', 'ListPolicies',
    'DescribeInstances', 'DescribeSecurityGroups', 'DescribeSubnets',
    'ListFunctions', 'DescribeDBInstances', 'GetAccountAuthorizationDetails'
)
GROUP BY username, source_ip
HAVING unique_enum_calls >= 5
ORDER BY unique_enum_calls DESC;

The distinguishing feature of attacker enumeration isn't any single call — it's the breadth and speed. A legitimate admin might call 2–3 of these over a workday. Calling all 10 within 2 minutes is automated reconnaissance. COUNT(DISTINCT event_name) measures breadth, not just volume.

Expected result: Eve — 10 distinct enumeration call types, window spanning ~2 minutes.

Detection 4: after-hours access to sensitive resources

Threat: Access to sensitive data outside business hours — indicating either compromised credentials being used by an actor in a different timezone, or an insider threat operating outside normal patterns.

SELECT
    event_time,
    username,
    source_ip,
    event_name,
    resource_arn
FROM cloudtrail
WHERE strftime('%H', event_time) NOT IN (
    '06','07','08','09','10','11','12','13','14',
    '15','16','17','18','19','20','21'
)
AND (
    resource_arn LIKE '%payroll%'
    OR resource_arn LIKE '%secret%'
    OR resource_arn LIKE '%password%'
    OR resource_arn LIKE '%AdministratorAccess%'
)
ORDER BY event_time;

The resource_arn patterns target high-value data: payroll files, secrets manager entries, stored credentials, and admin policy attachments. Tune the keyword list to match your environment's naming conventions.

Expected result: Alice's 8 payroll accesses at 3am, plus eve's AdministratorAccess attachment — two different threat scenarios caught by the same query.

Detection 5: impossible travel

Threat: The same user account authenticating successfully from two geographically distant IPs within a window too short for physical travel — indicating credential sharing, session theft, or account compromise.

SELECT
    a.username,
    a.source_ip AS ip_one,
    a.event_time AS login_one,
    b.source_ip AS ip_two,
    b.event_time AS login_two,
    ROUND(
        (julianday(b.event_time) - julianday(a.event_time)) * 24 * 60,
        1
    ) AS minutes_apart
FROM cloudtrail a
JOIN cloudtrail b
    ON a.username = b.username
    AND a.source_ip != b.source_ip
    AND b.event_time > a.event_time
    AND (julianday(b.event_time) - julianday(a.event_time)) * 24 * 60 <= 60
WHERE a.event_name = 'ConsoleLogin' AND a.error_code = ''
  AND b.event_name = 'ConsoleLogin' AND b.error_code = ''
ORDER BY minutes_apart;

This uses a self-join — the cloudtrail table is joined against itself, aliased as a (first login) and b (second login). julianday() converts timestamps to decimal day numbers for arithmetic. The join condition matches pairs from the same user, different IPs, where the second login follows the first within 60 minutes.

Expected result: Bob — London IP and Tokyo IP — 4 minutes apart.

Key takeaways

  • WHERE filters rows; GROUP BY aggregates them; HAVING filters the aggregated result
  • DISTINCT removes duplicates; COUNT(DISTINCT column) measures breadth, not just volume
  • Subqueries let you use one query's output as another's input — essential for multi-step detection logic
  • Self-joins let you compare rows within the same table — useful for session correlation and sequence detection
  • Every detection rule encodes assumptions about normal vs. anomalous behaviour — document those assumptions alongside the query

Going further

  • Translate these queries into AWS Athena SQL and run them against real CloudTrail logs in S3 — the syntax is largely compatible
  • Explore Sigma rules{:target="_blank"} — a vendor-neutral detection rule format that maps directly to the query patterns you've written here
  • Try building a false positive analysis: modify the dataset to add legitimate after-hours service account activity, then adjust the detection queries to exclude it without losing coverage