Last updated 2026-09-27 · By Shawn Nunley · Vendor-neutral

Time: ~2 hours · Difficulty: Beginner · You need: a terminal and DuckDB, a free database that runs on your own computer. No cloud account.

How this page was checked: a script extracted every command and query on this page and ran it as printed: the DuckDB steps on DuckDB 1.5.5 (macOS, Apple silicon), and every output shown is pasted from that run. No Snowflake, Google Cloud or AWS account was used, so nothing here has run on Snowflake, BigQuery or Amazon Athena themselves. Instead, the Athena queries ran on Trino 483 in Docker (Athena engine version 3 takes its SQL functions from Trino), the BigQuery queries on the open-source BigQuery emulator 0.8.1, and the Snowflake queries on fakesnow 0.11.18, a Snowflake emulator built on DuckDB. Emulators catch syntax errors but are not the services, so every construct in steps 9 and 10 was also checked against the vendor's documentation, and the Snowflake alert in step 10 was checked against the documentation only.

One query language, many places to run it

SQL is a language for asking questions of tables. You describe the answer you want (which rows, which columns, counted or grouped how) and the database works out how to get it. Security teams write it to hunt through logs, to answer incident questions such as "what did this key do?", and to run scheduled detections over logs kept outside a SIEM (security information and event management system). Snowflake and BigQuery store data in tables and run SQL over them. Amazon Athena stores nothing itself: it runs SQL over files in Amazon S3 (Simple Storage Service), such as the CloudTrail logs that AWS (Amazon Web Services) delivers there.

The core of the language (choosing rows, counting them in groups, matching one event with another) is the same in all three, so this page teaches it in DuckDB, a free database that runs on your computer and reads the lab file directly. What changes between them is narrower: how a value inside nested JSON (JavaScript Object Notation) is reached, how time is handled, and how a query runs on a schedule. Steps 9 and 10 cover those.

A query describes the table you want back

A table is rows and columns. In the lab table, cloudtrail, each row is one event and each column one field: eventTime, eventName, sourceIPAddress and so on. A query never changes the table. It describes a new table, and the database builds it. Once step 2 has loaded the data, this is a complete query:

SELECT eventTime, eventName, errorCode   -- which columns
FROM cloudtrail                          -- from which table
WHERE sourceIPAddress = '203.0.113.50'   -- which rows
ORDER BY eventTime                       -- in what order
LIMIT 3;                                 -- and how many

┌─────────────────────┬───────────────────┬──────────────┐
│      eventTime      │     eventName     │  errorCode   │
│      timestamp      │      varchar      │   varchar    │
├─────────────────────┼───────────────────┼──────────────┤
│ 2026-09-15 10:02:05 │ GetCallerIdentity │ NULL         │
│ 2026-09-15 10:02:31 │ ListUsers         │ AccessDenied │
│ 2026-09-15 10:02:38 │ ListRoles         │ AccessDenied │
└─────────────────────┴───────────────────┴──────────────┘

Read it as a sentence. Keywords such as SELECT ignore case, text values go in single quotes, -- starts a comment and a semicolon ends the query. You must write the clauses in this order, but the database works in another: FROM first, then WHERE, then SELECT, then ORDER BY and LIMIT. Step 5 relies on that. The result is itself a table, so it can be saved and queried in turn, which step 8 does. And NULL in the first row is not text from the log: GetCallerIdentity succeeded, so it has no errorCode at all. Step 6 shows why that matters.

Walk-through: five detections in DuckDB

The lab is 24 AWS CloudTrail events, the record of calls made to AWS in one account. A continuous integration user's access key, build-bot, has leaked. From 203.0.113.50 someone checks whose key it is, tries to list IAM (Identity and Access Management) users, EC2 (Elastic Compute Cloud) resources and secrets and is mostly refused, then creates a user, backup-svc, gives it administrator rights and an access key, and uses it to stop CloudTrail logging. Around that sits routine work, including a console login without MFA (multi-factor authentication) and an application refused one S3 file three times. The walk-through builds five detections, the same five as the other guides in this series:

D1, trail tampering: CloudTrail logging stopped, deleted or changed.
D2, denied-call burst: one source address refused five or more times.
D3, new user given a key: a user created and handed an access key within 10 minutes.
D4, console login without MFA: a successful console sign-in without multi-factor authentication.
D5, administrator policy attached: AdministratorAccess attached to a user.

1. Install DuckDB

On macOS and Linux, DuckDB's own install script puts it in your home directory. On Windows, run winget install DuckDB.cli instead; on a Mac, brew install duckdb also works. Piping a script into bash runs whatever it contains, so read it first: install.duckdb.org shows it as plain text.

curl https://install.duckdb.org | bash
export PATH="$HOME/.duckdb/cli/latest:$PATH"
duckdb --version

The script installs the newest release and suggests the export line, which lets the shell find duckdb. This page used 1.5.5:

v1.5.5 (Variegata) d8cdaa33fd

2. Create the lab file and load it

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

cat > cloudtrail-lab.jsonl <<'EOF'
{"eventTime":"2026-09-15T08:55:12Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"responseElements":{"ConsoleLogin":"Success"},"additionalEventData":{"MFAUsed":"Yes"}}
{"eventTime":"2026-09-15T09:02:40Z","eventSource":"iam.amazonaws.com","eventName":"CreateUser","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"requestParameters":{"userName":"svc-reporting"},"readOnly":false}
{"eventTime":"2026-09-15T09:03:05Z","eventSource":"s3.amazonaws.com","eventName":"ListBuckets","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"readOnly":true}
{"eventTime":"2026-09-15T09:10:22Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.10","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/ci-deploy/runner-4821"},"readOnly":true}
{"eventTime":"2026-09-15T09:15:47Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.21","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/bob","userName":"bob"},"responseElements":{"ConsoleLogin":"Success"},"additionalEventData":{"MFAUsed":"No"}}
{"eventTime":"2026-09-15T09:20:03Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.77","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"errorMessage":"Failed authentication","responseElements":{"ConsoleLogin":"Failure"},"additionalEventData":{"MFAUsed":"No"}}
{"eventTime":"2026-09-15T09:31:10Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T09:47:55Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T10:01:30Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T10:02:05Z","eventSource":"sts.amazonaws.com","eventName":"GetCallerIdentity","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"readOnly":true}
{"eventTime":"2026-09-15T10:02:31Z","eventSource":"iam.amazonaws.com","eventName":"ListUsers","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:02:38Z","eventSource":"iam.amazonaws.com","eventName":"ListRoles","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:02:52Z","eventSource":"iam.amazonaws.com","eventName":"GetAccountAuthorizationDetails","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:03:05Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:09Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeVpcs","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:14Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeSecurityGroups","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:22Z","eventSource":"secretsmanager.amazonaws.com","eventName":"ListSecrets","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:03:40Z","eventSource":"s3.amazonaws.com","eventName":"ListBuckets","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"readOnly":true}
{"eventTime":"2026-09-15T10:06:12Z","eventSource":"iam.amazonaws.com","eventName":"CreateUser","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc"},"readOnly":false}
{"eventTime":"2026-09-15T10:06:30Z","eventSource":"iam.amazonaws.com","eventName":"AttachUserPolicy","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc","policyArn":"arn:aws:iam::aws:policy/AdministratorAccess"},"readOnly":false}
{"eventTime":"2026-09-15T10:06:51Z","eventSource":"iam.amazonaws.com","eventName":"CreateAccessKey","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc"},"responseElements":{"accessKey":{"userName":"backup-svc","accessKeyId":"AKIAIOSFODNN7EXAMPLE","status":"Active"}},"readOnly":false}
{"eventTime":"2026-09-15T10:09:03Z","eventSource":"cloudtrail.amazonaws.com","eventName":"StopLogging","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/backup-svc","userName":"backup-svc"},"requestParameters":{"name":"arn:aws:cloudtrail:us-east-1:111122223333:trail/org-trail"},"readOnly":false}
{"eventTime":"2026-09-15T10:40:18Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.10","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/ci-deploy/runner-4821"},"readOnly":true}
{"eventTime":"2026-09-15T11:40:26Z","eventSource":"iam.amazonaws.com","eventName":"CreateAccessKey","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"requestParameters":{"userName":"svc-reporting"},"responseElements":{"accessKey":{"userName":"svc-reporting","accessKeyId":"AKIAI44QH8DHBEXAMPLE","status":"Active"}},"readOnly":false}
EOF

shasum -a 256 cloudtrail-lab.jsonl
duckdb lab.duckdb

The hash must be 35b7c80582d0650ec4850acf84f84fa4b8e5d37169ac3f7276c76f29b67c3319; anything else means the paste changed the file. The last command opens DuckDB's shell on a database file, lab.duckdb, creating it. Type the SQL in the rest of this page at its D prompt; .exit leaves. Each line of the file is one event as JSON. Load them all into a table and count them:

CREATE TABLE cloudtrail AS
SELECT * FROM read_json('cloudtrail-lab.jsonl');

SELECT count(*) AS events FROM cloudtrail;   -- expect 24

read_json works out a column for every field and a type for every column, and count(*) counts rows. The table is stored in lab.duckdb, so it is still there the next time you open the shell.

3. Filter rows: trail tampering (D1)

-- D1: CloudTrail logging stopped, deleted or changed
SELECT eventTime, eventName, userIdentity.userName AS actor, sourceIPAddress
FROM cloudtrail
WHERE eventName IN ('StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors');

┌─────────────────────┬─────────────┬────────────┬─────────────────┐
│      eventTime      │  eventName  │   actor    │ sourceIPAddress │
│      timestamp      │   varchar   │  varchar   │     varchar     │
├─────────────────────┼─────────────┼────────────┼─────────────────┤
│ 2026-09-15 10:09:03 │ StopLogging │ backup-svc │ 203.0.113.50    │
└─────────────────────┴─────────────┴────────────┴─────────────────┘

One row: backup-svc stopped logging at 10:09:03 from 203.0.113.50. The other 23 events are the negative case, and none appears. IN tests a value against a list, and AS names an output column. Comparisons of text are exact, so 'stoplogging' would match nothing. In MITRE ATT&CK (Adversarial Tactics, Techniques, and Common Knowledge) v19.2 this is T1685.002 Disable or Modify Cloud Log; rules written before v19 tag it T1562.008, which v19 revoked.

4. Nested fields: console logins without MFA (D4)

Some fields are objects inside the event, such as userIdentity. DuckDB stored each one as a STRUCT, a column holding named fields of its own, and a dot reaches inside: userIdentity.userName. It also recognised eventTime as a TIMESTAMP, a point in time rather than text, which step 7 needs. A first attempt at D4:

-- D4, first attempt: console logins without MFA
SELECT eventTime, userIdentity.userName AS user_name, responseElements.ConsoleLogin AS result
FROM cloudtrail
WHERE eventName = 'ConsoleLogin'
  AND additionalEventData.MFAUsed = 'No'
ORDER BY eventTime;

┌─────────────────────┬───────────┬─────────┐
│      eventTime      │ user_name │ result  │
│      timestamp      │  varchar  │ varchar │
├─────────────────────┼───────────┼─────────┤
│ 2026-09-15 09:15:47 │ bob       │ Success │
│ 2026-09-15 09:20:03 │ alice     │ Failure │
└─────────────────────┴───────────┴─────────┘

The second row is wrong: alice's sign-in at 09:20:03 failed. CloudTrail records MFAUsed on failed attempts too, so the rule must also ask for success. AND keeps a row only when every condition holds:

-- D4: a successful console sign-in without MFA
SELECT eventTime, userIdentity.userName AS user_name, sourceIPAddress
FROM cloudtrail
WHERE eventName = 'ConsoleLogin'
  AND responseElements.ConsoleLogin = 'Success'
  AND additionalEventData.MFAUsed = 'No';

Now only bob fires, at 09:15:47 from 198.51.100.21; alice's sign-in with MFA and her failed one are the negative cases. AWS's own check, Security Hub control CloudWatch.3, pairs the same two conditions. ATT&CK: T1078.004 Valid Accounts: Cloud Accounts.

5. Count in groups: the burst that never fires (D2)

GROUP BY collapses the rows that share a value into one row per value, and count(*) then counts the rows in each group. D2 wants groups of five or more, and WHERE cannot test a count, because it runs before the counting; HAVING filters the groups afterwards:

-- D2, first attempt: AccessDenied per source address, five or more
SELECT sourceIPAddress, count(*) AS denials
FROM cloudtrail
WHERE errorCode = 'AccessDenied'
GROUP BY sourceIPAddress
HAVING count(*) >= 5;

┌─────────────────┬─────────┐
│ sourceIPAddress │ denials │
│     varchar     │  int64  │
└─────────────────┴─────────┘
           0 rows

Nothing. Delete the HAVING line and you see why: 203.0.113.50 has 4 and 192.0.2.20 has 3. Did the attacker really make only four denied calls, or can the query not see the rest? Run a control: drop the filter and list the error codes that exist at all.

-- Control: which error codes are in the data?
SELECT errorCode, count(*) AS events
FROM cloudtrail
GROUP BY errorCode
ORDER BY events DESC;

┌──────────────────────────────┬────────┐
│          errorCode           │ events │
│           varchar            │ int64  │
├──────────────────────────────┼────────┤
│ NULL                         │     14 │
│ AccessDenied                 │      7 │
│ Client.UnauthorizedOperation │      3 │
└──────────────────────────────┴────────┘

EC2 reports a refusal as Client.UnauthorizedOperation, as AWS's example query for unauthorised attempts shows, so three of the attacker's seven denials were invisible to the rule. AWS's alarm for unauthorised API calls, Security Hub control CloudWatch.2, matches every code that starts with AccessDenied or ends in UnauthorizedOperation, which also takes in AccessDeniedException and a bare UnauthorizedOperation. The fix lists all four:

-- D2: every denial code, per source address, five or more
SELECT sourceIPAddress, count(*) AS denials
FROM cloudtrail
WHERE errorCode IN ('AccessDenied', 'AccessDeniedException',
                    'Client.UnauthorizedOperation', 'UnauthorizedOperation')
GROUP BY sourceIPAddress
HAVING count(*) >= 5;

┌─────────────────┬─────────┐
│ sourceIPAddress │ denials │
│     varchar     │  int64  │
├─────────────────┼─────────┤
│ 203.0.113.50    │       7 │
└─────────────────┴─────────┘

203.0.113.50 with 7 fires; 192.0.2.20, the reports application, stays under the threshold with 3. To match the families the way AWS's alarm does, LIKE compares text with a pattern in which % stands for any run of characters: errorCode LIKE 'AccessDenied%' OR errorCode LIKE '%UnauthorizedOperation' gives the same 7, and also catches codes you have not met yet.

A query that returns nothing and a query that cannot work return the same thing. Before you trust an empty result, run a control that must return something: the same query without its threshold, or a count of the values that really exist. The control above also showed a row you did not ask about, NULL with 14 events, and step 6 is about that row.

6. NULL: the value that is not there

NULL means "no value", not an empty string. Any comparison with it, even NULL = NULL, gives neither true nor false but unknown, and WHERE keeps only the rows where its condition is true. That breaks a reasonable-looking refinement of D1: alert only when tampering worked, by skipping attempts that were denied.

-- D1, tightened (wrong): skip tampering that was denied
SELECT eventTime, eventName, sourceIPAddress
FROM cloudtrail
WHERE eventName IN ('StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors')
  AND errorCode <> 'AccessDenied';

<> means "not equal", and this returns nothing. The StopLogging call succeeded, so its errorCode is NULL, the comparison is unknown, and the row goes: the rule now misses the one event it exists for, and looks exactly like a quiet day. Across the whole table, errorCode <> 'AccessDenied' keeps 3 rows, not 17. Ask for the missing value directly: AND errorCode IS NULL (success means no error code) brings the row back. When you mean "anything but this value, including no value", write errorCode IS DISTINCT FROM 'AccessDenied', which keeps all 17. Counting skips NULL as well: count(errorCode) gives 10 where count(*) gives 24.

7. Match one event with another: new user given a key (D3)

D3 is about two events, a CreateUser and a later CreateAccessKey for the same new user. A JOIN pairs rows from two tables wherever its ON condition holds, and here both tables are cloudtrail, under two short names: c for the creation and k for the key. BETWEEN includes both ends, and subtracting two times gives the gap between them.

-- D3: a user created and handed an access key within 10 minutes
SELECT c.requestParameters.userName AS new_user, c.userIdentity.userName AS actor,
       c.eventTime AS created, k.eventTime - c.eventTime AS gap
FROM cloudtrail AS c
JOIN cloudtrail AS k
  ON k.requestParameters.userName = c.requestParameters.userName
WHERE c.eventName = 'CreateUser'
  AND k.eventName = 'CreateAccessKey'
  AND k.eventTime BETWEEN c.eventTime AND c.eventTime + INTERVAL '10 minutes'
ORDER BY created;

┌────────────┬───────────┬─────────────────────┬──────────┐
│  new_user  │   actor   │       created       │   gap    │
│  varchar   │  varchar  │      timestamp      │ interval │
├────────────┼───────────┼─────────────────────┼──────────┤
│ backup-svc │ build-bot │ 2026-09-15 10:06:12 │ 00:00:39 │
└────────────┴───────────┴─────────────────────┴──────────┘

build-bot created backup-svc and gave it a key 39 seconds later. Now the control: delete the BETWEEN line and run it again.

┌───────────────┬───────────┬─────────────────────┬──────────┐
│   new_user    │   actor   │       created       │   gap    │
│    varchar    │  varchar  │      timestamp      │ interval │
├───────────────┼───────────┼─────────────────────┼──────────┤
│ svc-reporting │ alice     │ 2026-09-15 09:02:40 │ 02:37:46 │
│ backup-svc    │ build-bot │ 2026-09-15 10:06:12 │ 00:00:39 │
└───────────────┴───────────┴─────────────────────┴──────────┘

Without the window, alice's routine work joins the results: she created svc-reporting and gave it a key 2 hours 37 minutes later. Any user given a key months after creation would match too, so the window is the detection, and svc-reporting is its negative case. ATT&CK: T1136.003 Create Account: Cloud Account, then T1098.001 Additional Cloud Credentials.

8. Make it a scheduled detection, and test it both ways

A detection is a query that returns no rows when all is well and one row per alert when not, run on a schedule over the most recent events. DuckDB has no scheduler of its own (cron or a pipeline job would run it), but you can build and test the query one would run. A VIEW saves a query under a name, and a variable stands in for the time of each run (getvariable() has no type until the variable is set, hence the casts):

-- D2 denied-call burst (ATT&CK T1087.004, T1580): any row is an alert
CREATE VIEW d2_denied_call_burst AS
SELECT sourceIPAddress, count(*) AS denials, min(eventTime) AS first_seen,
       'T1087.004, T1580' AS attack_techniques
FROM cloudtrail
WHERE errorCode IN ('AccessDenied', 'AccessDeniedException',
                    'Client.UnauthorizedOperation', 'UnauthorizedOperation')
  AND eventTime >= getvariable('run_time')::TIMESTAMP - INTERVAL '10 minutes'
  AND eventTime <  getvariable('run_time')::TIMESTAMP
GROUP BY sourceIPAddress
HAVING count(*) >= 5;

The ATT&CK techniques ride along in every alert row: T1087.004 Account Discovery: Cloud Account and T1580 Cloud Infrastructure Discovery, as numbered in ATT&CK v19.2. The window includes its start and excludes its end, so back-to-back runs neither overlap nor leave a gap. Now run it as the scheduler would, for 10:10, just after the burst, and for 09:50, when only the reports application had been refused:

SET VARIABLE run_time = TIMESTAMP '2026-09-15 10:10:00';
SELECT * FROM d2_denied_call_burst;

SET VARIABLE run_time = TIMESTAMP '2026-09-15 09:50:00';
SELECT * FROM d2_denied_call_burst;

┌─────────────────┬─────────┬─────────────────────┬───────────────────┐
│ sourceIPAddress │ denials │     first_seen      │ attack_techniques │
│     varchar     │  int64  │      timestamp      │      varchar      │
├─────────────────┼─────────┼─────────────────────┼───────────────────┤
│ 203.0.113.50    │       7 │ 2026-09-15 10:02:31 │ T1087.004, T1580  │
└─────────────────┴─────────┴─────────────────────┴───────────────────┘
┌─────────────────┬─────────┬────────────┬───────────────────┐
│ sourceIPAddress │ denials │ first_seen │ attack_techniques │
│     varchar     │  int64  │ timestamp  │      varchar      │
└─────────────────┴─────────┴────────────┴───────────────────┘
                            0 rows

It fires once, for 203.0.113.50, and stays silent at 09:50. The 10:10 run also saw the reports application's 10:01:30 denial and rightly left it out. One limit remains: a burst that straddles a run, say three denials before 10:10 and three after, is split between two windows and stays under five in both. Grouping by time_bucket(INTERVAL '10 minutes', eventTime), which cuts the day into ten-minute slots that start on the clock, has the same limit. Running every 5 minutes over the last 10 closes it, at the cost of some bursts alerting twice.

What changes in Snowflake, BigQuery and Athena

9. The same question in three dialects

Each warehouse speaks its own dialect of SQL, and the differences cluster where the data is nested or timed. How nested JSON looks depends on how the table was built, so the examples assume one layout each: in Snowflake, a view like the one in Snowflake's CloudTrail ingestion guide, with each nested object in a VARIANT column; in BigQuery, a table whose nested objects are JSON columns (this page's own choice); and in Athena, the table from the Athena documentation's CloudTrail page, where useridentity is a struct but requestparameters, responseelements and additionaleventdata hold JSON as plain text. D4 in each:

-- Snowflake (ran on fakesnow)
SELECT eventTime, userIdentity:userName::STRING AS user_name, sourceIPAddress
FROM cloudtrail
WHERE eventName = 'ConsoleLogin'
  AND responseElements:ConsoleLogin::STRING = 'Success'
  AND additionalEventData:MFAUsed::STRING = 'No';

-- BigQuery (ran on the BigQuery emulator)
SELECT eventTime, JSON_VALUE(userIdentity, '$.userName') AS user_name, sourceIPAddress
FROM security.cloudtrail
WHERE eventName = 'ConsoleLogin'
  AND JSON_VALUE(responseElements, '$.ConsoleLogin') = 'Success'
  AND JSON_VALUE(additionalEventData, '$.MFAUsed') = 'No';

-- Amazon Athena (ran on Trino)
SELECT eventtime, useridentity.username AS user_name, sourceipaddress
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin'
  AND json_extract_scalar(responseelements, '$.ConsoleLogin') = 'Success'
  AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No';

Each returned one row, bob from 198.51.100.21. In Snowflake, : steps into a VARIANT and ::STRING turns what it finds into text. In BigQuery, JSON_VALUE takes a JSON column and a path in which $ is the top of the object. Athena lowercases every column name and reaches struct fields with a dot, but its JSON text needs json_extract_scalar. The rest of what changes, each item checked on the three engines above and in the vendors' documentation:

	DuckDB	Snowflake	BigQuery	Athena
Nested names are case-sensitive	No	Yes	Yes, in JSON	Struct fields no, JSON paths yes
The event time	`TIMESTAMP`, detected when read	`TIMESTAMP` in the guide's view	`TIMESTAMP` column	Text: use `from_iso8601_timestamp(eventtime)`
Ten-minute bucket	`time_bucket(INTERVAL '10 minutes', t)`	`TIME_SLICE(t, 10, 'MINUTE')`	`TIMESTAMP_BUCKET(t, INTERVAL 10 MINUTE)`	`from_unixtime(floor(to_unixtime(t) / 600) * 600)`
Ten minutes after `t`	`t + INTERVAL '10 minutes'`	`DATEADD(minute, 10, t)`	`TIMESTAMP_ADD(t, INTERVAL 10 MINUTE)`	`t + INTERVAL '10' MINUTE`
`GROUP BY` an output name	Yes	Yes	Yes	No: repeat the expression or use its position
`"x"` in double quotes is	A column name	A column name	Text	A column name

The first row is the one that bites. DuckDB ignores case in struct field names, so a D4 written with additionalEventData.mfaused passes every local test, and the same lowercase path finds nothing in the three warehouses (Exercise 4).

10. Put it on a schedule

Only Snowflake expresses the schedule in SQL itself. An alert runs a condition on a schedule and, when the condition returns any row, runs an action. This is step 8's detection as an alert on the guide's view (not run, because Snowflake was not available):

CREATE ALERT d2_denied_call_burst
  WAREHOUSE = security_wh
  SCHEDULE = '10 MINUTE'
  COMMENT = 'D2 denied-call burst. ATT&CK T1087.004, T1580.'
  IF (EXISTS (
    SELECT sourceIPAddress, COUNT(*) AS denials
    FROM cloudtrail
    WHERE errorCode IN ('AccessDenied', 'AccessDeniedException',
                        'Client.UnauthorizedOperation', 'UnauthorizedOperation')
      AND eventTime >= DATEADD(minute, -10,
            CONVERT_TIMEZONE('UTC', SNOWFLAKE.ALERT.SCHEDULED_TIME())::TIMESTAMP_NTZ)
      AND eventTime <  CONVERT_TIMEZONE('UTC', SNOWFLAKE.ALERT.SCHEDULED_TIME())::TIMESTAMP_NTZ
    GROUP BY sourceIPAddress
    HAVING COUNT(*) >= 5))
  THEN CALL SYSTEM$SEND_EMAIL('soc_email', 'soc@example.com',
         'D2 denied-call burst', 'Five or more denied calls from one address.');
ALTER ALERT d2_denied_call_burst RESUME;   -- new alerts start suspended

SNOWFLAKE.ALERT.SCHEDULED_TIME() plays the part of run_time. The conversion around it matters: the view's eventTime holds UTC (Coordinated Universal Time) with no time zone attached, the function returns a time in the session's zone, and Snowflake's default zone is America/Los_Angeles, so comparing the two directly would put the window hours away from the events. soc_email is an email notification integration you create first.

In BigQuery, a scheduled query runs at intervals of five minutes or more and passes each run's intended time as @run_time, so the window becomes eventTime >= TIMESTAMP_SUB(@run_time, INTERVAL 10 MINUTE) AND eventTime < @run_time (on the emulator, that fired at 10:10 and stayed silent at 09:50). The result goes to a table, and a run can be announced on Pub/Sub or, if it fails, by email; nothing reports whether the detection found anything, so something must read the result. Athena has no scheduler at all: AWS suggests an Amazon EventBridge rule that starts an AWS Lambda function, AWS Step Functions, or cron, and whatever starts the query must supply the window.

Hands-on exercises

Exercise 1

Write D5: every time the AdministratorAccess managed policy was attached to a user, showing who attached it, to whom, and from where.

Show the answer

-- D5: AdministratorAccess attached to a user
SELECT eventTime, userIdentity.userName AS actor,
       requestParameters.userName AS target_user, sourceIPAddress
FROM cloudtrail
WHERE eventName = 'AttachUserPolicy'
  AND requestParameters.policyArn = 'arn:aws:iam::aws:policy/AdministratorAccess';

One row: build-bot attached it to backup-svc at 10:06:30 from 203.0.113.50. Both userIdentity and requestParameters contain a userName: the first is who acted, the second who was acted on, and the AS names keep them apart. The technique is T1098.003 Additional Cloud Roles. For real coverage, also consider AttachRolePolicy, AttachGroupPolicy and inline policies written with PutUserPolicy.

Exercise 2

This query is meant to list the denied calls from 203.0.113.50. It returns 10 rows, three of them from 192.0.2.20. Why, and what is the fix?

SELECT eventTime, eventName, sourceIPAddress, errorCode
FROM cloudtrail
WHERE errorCode = 'AccessDenied' OR errorCode = 'Client.UnauthorizedOperation'
  AND sourceIPAddress = '203.0.113.50';

Show the answer

AND binds more tightly than OR, as multiplication does before addition, so the condition reads "AccessDenied from anywhere, or Client.UnauthorizedOperation from 203.0.113.50". The three extra rows are the reports application's. Parentheses say what was meant, and an IN list avoids the question: WHERE errorCode IN ('AccessDenied', 'Client.UnauthorizedOperation') AND sourceIPAddress = '203.0.113.50' returns the attacker's 7. This mistake returned too much rather than nothing, and it is just as quiet: no error, only a count slightly too big. Checking a result against a number you already know, such as step 5's seven, is how you notice.

Exercise 3

Find every source address used by more than one identity, which is how a leaked key followed by a new user looks from the network side. Should you count userIdentity.userName or userIdentity.arn (the Amazon Resource Name)?

Show the answer

SELECT sourceIPAddress, count(DISTINCT userIdentity.arn) AS identities
FROM cloudtrail
GROUP BY sourceIPAddress
HAVING count(DISTINCT userIdentity.arn) > 1;

One row: 203.0.113.50 with 2. DISTINCT inside count counts different values rather than rows: here build-bot and the backup-svc user it created. Count the ARN, which every event has. Roles have no userName, and counting skips NULL, so count(DISTINCT userIdentity.userName) gives 0 for the ci-deploy role's address, 192.0.2.10, and an address shared by one user and one role would count 1 and pass for a single identity.

Exercise 4

A colleague wrote D4 in DuckDB with lowercase names (additionaleventdata.mfaused, responseelements.consolelogin), tested it and saw bob. Moved to Snowflake as additionalEventData:mfaused::STRING = 'No' and so on, it returns nothing, every day. What is wrong, and which control finds it in one query?

Show the answer

DuckDB ignores case in column names and struct fields alike, so the local test passed. Snowflake ignores case in column names but not in the element names inside a VARIANT: the key is MFAUsed, so mfaused finds nothing, every comparison is with NULL, and no row survives. The control is to show the values without filtering on them:

-- Snowflake (ran on fakesnow)
SELECT additionalEventData:mfaused::STRING AS lower_case,
       additionalEventData:MFAUsed::STRING AS as_logged
FROM cloudtrail
WHERE eventName = 'ConsoleLogin';

It returned three rows with lower_case empty (NULL) in every one, beside Yes, No and No. A column of nothing but NULL is the signature of a wrong path, in BigQuery's JSON_VALUE and Athena's json_extract_scalar as well.

Common mistakes

Comparing with NULL using = or <>. The comparison is unknown and the row silently drops out. Use IS NULL, or IS DISTINCT FROM when "no value" should count as different.
Believing an empty result. A missing error code, a wrong key name and a quiet account all return nothing. Remove the threshold, or list the values that exist, before you believe it.
Matching one denial code. EC2 says Client.UnauthorizedOperation, so a rule that knows only AccessDenied misses every refused EC2 call.
Joining events without a time limit. A user created months ago and given a key today matches too; the window is the detection.
Testing only in DuckDB. It ignores case in nested names and the warehouses do not. Run the final query, and its control, where it will be deployed.
Treating Athena's eventtime as a time. In the documented table it is text, so eventtime >= '2026-09-15 10:00:00' compares characters: the T in 2026-09-15T10:09:03Z sorts after a space, and on Trino it matched all 24 events instead of 16. Convert with from_iso8601_timestamp() first.
Mixing time zones in Snowflake. A TIMESTAMP_NTZ carries no zone, and the default session zone is America/Los_Angeles. Convert before comparing.
A window that ends exactly now. CloudTrail delivers logs on average about 5 minutes after the call, and does not guarantee even that, so a run at 10:10 may not yet hold the 10:08 events, and the next run's window has moved past them. Lag the window, or overlap it and remove duplicate alerts.
Forgetting that every run is billed. Athena, and BigQuery's on-demand pricing, charge by the data a query reads, and a detection that runs every ten minutes runs 144 times a day. Bound it by time and partition, and in BigQuery name only the columns you need.

Where next

Splunk SPL, KQL and Elastic ES|QL and EQL run the same lab and the same five detections, a quick way to see which ideas belong to SQL and which to every query language.
The CloudTrail to SIEM lab sets up a real trail and queries it with Athena, the next step after this page's local copy.
Sigma and YARA for writing a detection once and converting it, and detection engineering for testing, tuning and retiring detections.
DuckDB's JSON documentation and its SQL introduction, for the rest of the language.
Snowflake on querying semi-structured data, BigQuery on working with JSON and Athena on querying CloudTrail logs.

SQL for security data lakes, hands-on

One query language, many places to run it

On this page

A query describes the table you want back

Walk-through: five detections in DuckDB

1. Install DuckDB

2. Create the lab file and load it

3. Filter rows: trail tampering (D1)

4. Nested fields: console logins without MFA (D4)

5. Count in groups: the burst that never fires (D2)

6. NULL: the value that is not there

7. Match one event with another: new user given a key (D3)

8. Make it a scheduled detection, and test it both ways

What changes in Snowflake, BigQuery and Athena

9. The same question in three dialects

10. Put it on a schedule

Hands-on exercises

Common mistakes

Where next