SQL for security data lakes, hands-on

One query language, many places to run it

SQL is a language for asking questions of tables, which hold data in rows and columns, like a spreadsheet. 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 (queries run on a timer that return a row for each alert) over logs kept outside a SIEM (security information and event management system), the kind of product that gathers security logs in one place and raises alerts from them.

Snowflake and BigQuery are data warehouses: they 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, which record the calls made to an AWS account. Logs kept as files like that, and queried where they lie, are what this page means by a data lake.

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, and steps 11 and 12 cover AWS's two managed services that run SQL over CloudTrail: CloudTrail Lake and Amazon Security Lake.

How to use this guide

Beginner here means new to SQL. You should be comfortable typing commands into a terminal. You do not need to know AWS: the AWS terms the lab depends on are explained as they come up, and terms with a glossary entry link to it.

Read it in order, because each part uses the one before. The next section shows how a query is put together. Its example needs the table that step 2 loads, so read it first and run it once step 2 is done. In the walk-through, step 1 installs DuckDB, and step 2 creates a file of 24 CloudTrail events, opens DuckDB and loads the events into a table. Steps 3 to 7 build four of the five detections listed at the top of the walk-through, with a stop at step 6 for NULL, SQL's marker for a missing value, which breaks a sensible-looking change to one of them. Steps 4 and 5 start with a query that looks right and returns the wrong answer without an error, then show why and fix it. Step 8 turns one detection into a query that a scheduler could run, and tests it at two times. The exercises then ask you to write the fifth detection and repair broken queries yourself, with each answer hidden until you click Show the answer.

A code block labelled BASH is something to paste into your terminal, and each block has a copy button. A block labelled SQL is a query. Up to the end of step 8, and in Exercises 1 to 3, type or paste it at the D prompt of the DuckDB shell that step 2 opens; in steps 9 to 12 and Exercises 4 and 5 it is written for the service the step describes. The unlabelled block under it, when there is one, shows what you should see. Step 2's BASH block contains the line cat > cloudtrail-lab.jsonl <<'EOF', which writes every line down to the one that says only EOF into a file called cloudtrail-lab.jsonl. Paste the whole block at once, the EOF line included.

Up to the end of step 8, your output should match the page line for line, apart from the version line in step 1. A script ran every command and query there as printed on DuckDB 1.5.5 and pasted the output, then ran them again on DuckDB 2.0.0, the release the install script now fetches, with the same results. Nothing in steps 9 to 12 has run on Snowflake, BigQuery or any AWS service: those queries ran on emulators and on Trino, the engine Athena takes its SQL functions from, or were checked against the vendors' documentation only, and an emulator is not the service it imitates.

Steps 9 to 12 show the same detections in Snowflake, BigQuery and Athena, and in CloudTrail Lake and Amazon Security Lake, AWS's two managed services that run SQL over CloudTrail. They cannot run in the lab on your computer, so read them. If you use none of those services yet, they can wait for a second reading, along with Exercises 4 and 5, which go with them. And the 24 lines of lab data in step 2 can be pasted without reading them, since the hash check under the block tells you whether the paste worked.

If your output differs from the page, check that you started DuckDB in ~/sql-lab, the folder step 2 creates, because the lab file and lab.duckdb, which holds the table, are there. Then check the file's hash under step 2 and the count of 24 events. If every time is shifted by some hours, check that the load included the timestampformat line; step 2 explains what DuckDB 2.0 does without it.

A query describes the table you want back

Suppose a leaked key was used from the address 203.0.113.50, and you want the first three calls made from there, in time order. You do not tell the database how to find them. You describe the result you want: which columns, from which table, which rows, in what order and how many. A SQL query is that description.

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; an access key is the credential a program signs its AWS requests with. 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 was run on 1.5.5, shown here, and again on 2.0.0 with the same results:

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, a fingerprint of the file's exact contents, 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',
                        timestampformat = '%Y-%m-%dT%H:%M:%SZ');

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. timestampformat spells out how CloudTrail writes a time, so eventTime is read as a timestamp in UTC (Coordinated Universal Time). Without it, DuckDB 2.0 reads the final Z as a time zone and shows every time in your computer's zone, and unless that zone is UTC, step 8's test at 10:10 finds nothing. 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 CSPM 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 (application programming interface) calls, Security Hub CSPM 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.

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:

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.

CloudTrail Lake and Amazon Security Lake

AWS runs two services that store CloudTrail events and answer SQL about them, with no table of your own to define. CloudTrail Lake keeps CloudTrail's field names. Amazon Security Lake converts every event to OCSF (the Open Cybersecurity Schema Framework), a schema shared across vendors whose names are not CloudTrail's, and that conversion is where a ported detection can fail without an error.

11. CloudTrail Lake: the event data store is the table

CloudTrail Lake closed to new customers on 31 May 2026. Existing customers can keep using it; AWS says it now receives only critical bug fixes and security updates, and points newcomers to Amazon CloudWatch. It bills for the events it ingests and retains, under a one-year extendable or a seven-year retention pricing option, and for each query by the data it scans (CloudTrail pricing).

A query names an event data store, CloudTrail Lake's collection of events, by its ID: the last part of the store's Amazon Resource Name (ARN), which the console fills in when you pick the store. Only SELECT is allowed, with every Trino function and operator. So step 8's CREATE VIEW and SET VARIABLE have no equivalent, and for DuckDB's own functions, such as time_bucket, use the Athena column of step 9's table. Field names are CloudTrail's, and userIdentity is a struct reached with a dot, but requestParameters, responseElements and additionalEventData are maps from text to text. Read them with element_at, as AWS's examples do: in Trino, the other form, additionalEventData['MFAUsed'], fails the query with an error when a map lacks the key. D1 and D4, with AWS's example ID in the FROM line:

-- CloudTrail Lake, D1 (checked against AWS's documentation, not run)
SELECT eventTime, eventName, userIdentity.userName AS actor, sourceIPAddress
FROM EXAMPLE-f852-4e8f-8bd1-bcf6cEXAMPLE
WHERE eventTime >= '2026-09-15 00:00:00' AND eventTime < '2026-09-16 00:00:00'
  AND eventName IN ('StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors');

-- CloudTrail Lake, D4 (checked against AWS's documentation, not run)
SELECT eventTime, userIdentity.userName AS user_name, sourceIPAddress
FROM EXAMPLE-f852-4e8f-8bd1-bcf6cEXAMPLE
WHERE eventTime >= '2026-09-15 00:00:00' AND eventTime < '2026-09-16 00:00:00'
  AND eventName = 'ConsoleLogin'
  AND element_at(responseElements, 'ConsoleLogin') = 'Success'
  AND element_at(additionalEventData, 'MFAUsed') = 'No';

The eventTime range on the first WHERE line is the cost control. Each query is billed by the data it scans, a store can keep up to ten years of events, and AWS's advice is to give every query a starting and ending eventTime. eventTime is a timestamp here, and AWS's examples compare it with text written as '2026-09-15 00:00:00', as above. Map keys keep CloudTrail's capitals, and in Trino a key must match exactly, so element_at(additionalEventData, 'mfaused') would return NULL on every event: Exercise 4's trap again.

12. Amazon Security Lake: the same events in OCSF

Security Lake converts the logs of the AWS services you enable to OCSF, stores them as Apache Parquet files in S3, and gives each source a table per Region that you query with Athena. Each AWS source comes in two versions: version 1 writes OCSF 1.0.0-rc.2 and version 2 writes OCSF 1.1.0. This step uses version 2, whose CloudTrail management events sit in a table ending cloud_trail_mgmt_2_0. OCSF also sorts events into classes, and that one table holds three: Authentication (class 3002) for sign-ins such as ConsoleLogin, Account Change (3001) for changes to users and their keys and policies, such as CreateUser, and API Activity (6003) for every other call. The fields the lab uses are renamed:

To test the queries, this page converted the 24 lab events to OCSF 1.1.0 by following the AWS CloudTrail mapping in the OCSF project's examples repository, and ran them on Trino 484. The copy is a reconstruction, not Security Lake's own output. On it, each of these ran without an error. The first three gave a wrong answer, and the fourth gives the right one at a cost the result does not show:

• The wrong class. API Activity holds every call that is not a sign-in or an account change, so class_name = 'API Activity' AND api.operation = 'ConsoleLogin' looks right and returns nothing: sign-ins are filed under Authentication.
• A CloudTrail name looked up in OCSF. Raw fields that OCSF has no place for go into unmapped, keyed by their CloudTrail path, such as additionalEventData.MobileVersion. MFAUsed is not among them, because OCSF moved it to is_mfa, so element_at(unmapped, 'additionalEventData.MFAUsed') = 'No' matches no event.
• Milliseconds read as seconds. Compared with to_unixtime(TIMESTAMP '2026-09-15 10:00:00'), which counts seconds, every time is larger, so the filter kept all 24 events where 16 is right. from_unixtime(time) reads it as seconds too, and puts 10:09:03 in the year 58675.
• No partition filter. Version 2 is partitioned by time_dt, region and accountid, which AWS says restricts the data each query scans, and Athena bills by the data scanned. Without a filter on them the rows that come back are the same, so nothing in the result shows the difference. AWS also says that when retention is set, every query must carry a time filter so that it reads only data that has not expired.

So filter on time_dt, as AWS's examples do. D1 and D4:

-- Security Lake source version 2 in Athena, D1 (ran on Trino 484 and DuckDB)
SELECT time_dt, api.operation, actor.user.name AS actor, src_endpoint.ip
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt >= TIMESTAMP '2026-09-15 00:00:00'
  AND time_dt <  TIMESTAMP '2026-09-16 00:00:00'
  AND api.operation IN ('StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors');

-- Security Lake source version 2 in Athena, D4 (ran on Trino 484 and DuckDB)
SELECT time_dt, actor.user.name AS user_name, src_endpoint.ip
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt >= TIMESTAMP '2026-09-15 00:00:00'
  AND time_dt <  TIMESTAMP '2026-09-16 00:00:00'
  AND class_uid = 3002
  AND api.operation = 'ConsoleLogin'
  AND status = 'Success'
  AND is_mfa = false;

D1 returns backup-svc's StopLogging at 10:09:03 from 203.0.113.50, and D4 returns bob at 09:15:47 from 198.51.100.21, the same answers as DuckDB gave on the raw events. When an OCSF query returns nothing, the control is the one from step 5: drop the filter and look at what is there. Grouping the day's events by class and operation shows where every sign-in went:

-- Control (ran on Trino 484 and DuckDB)
SELECT class_name, api.operation, count(*) AS events
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt >= TIMESTAMP '2026-09-15 00:00:00'
  AND time_dt <  TIMESTAMP '2026-09-16 00:00:00'
GROUP BY class_name, api.operation
ORDER BY class_name, events DESC;

It lists 15 rows. The three ConsoleLogin events are the whole of Authentication; Account Change holds CreateUser and CreateAccessKey twice each and AttachUserPolicy once; the other 16 events are API Activity.

Hands-on exercises

-- 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';

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

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

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

-- Security Lake source version 2 in Athena, D2 (ran on Trino 484 and DuckDB)
SELECT src_endpoint.ip, count(*) AS denials
FROM "amazon_security_lake_glue_db_us_east_1"."amazon_security_lake_table_us_east_1_cloud_trail_mgmt_2_0"
WHERE time_dt >= TIMESTAMP '2026-09-15 10:00:00'
  AND time_dt <  TIMESTAMP '2026-09-15 10:10:00'
  AND api.response.error IN ('AccessDenied', 'AccessDeniedException',
                             'Client.UnauthorizedOperation', 'UnauthorizedOperation')
GROUP BY src_endpoint.ip
HAVING count(*) >= 5;

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, CloudTrail Lake 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 (time_dt in Security Lake, eventTime in CloudTrail Lake), and in BigQuery name only the columns you need.
• Looking for sign-ins under API Activity in Security Lake. OCSF files ConsoleLogin under Authentication, in the same table, so a query limited to the API Activity class returns nothing every day.
• Looking up a CloudTrail field by its old name in OCSF. A field OCSF renamed, such as MFAUsed, is not in unmapped, and an element_at or JSON lookup that misses returns NULL rather than an error. Check the mapping, then show the values without filtering on them.
• Treating OCSF's time as seconds. It counts milliseconds, so comparing it with to_unixtime() or converting it with from_unixtime() makes every event look later than the date you compare it with. Filter on time_dt.