Time: ~2 hours · Difficulty: Beginner · You need: Docker with 4 GB of memory to spare and several gigabytes of disk, and Python 3. No Azure account. On an Apple silicon Mac, turn on Docker Desktop's Rosetta setting first (step 1); without Docker, use a free Azure Data Explorer cluster.
How this page was checked: every command and query was run as printed against the Kusto emulator (Azure Data Explorer engine 1.0.9766.21061, the latest image of 27 September 2026) in Docker Desktop on an Apple silicon Mac under Rosetta, and every output is pasted from that run. Not run: the free-cluster route, and the analytics rule inside a Microsoft Sentinel workspace. The rule's query runs here like every other, and the rule file uses only fields that Microsoft's own published CloudTrail rules use.
One query language across Microsoft's security data
Kusto is the query engine behind Azure Data Explorer, and KQL is the language you use to ask it questions. The same language runs in Azure Monitor Logs, in Microsoft Sentinel (which keeps its data in Log Analytics workspaces), in advanced hunting in Microsoft Defender XDR, and in Microsoft Fabric. Analysts in a security operations centre (SOC) write it to hunt through logs; detection engineers write it so that a security information and event management (SIEM) system can run it on a schedule and raise alerts.
The distinction that matters most: a query only reads. It names a table, reshapes it and hands back a table, and it cannot change any data. A management command starts with a dot (.create, .ingest, .show) and changes or inspects the database itself. Commands belong to Azure Data Explorer and Fabric, which is why this lab can use a few of them to build its table. In Sentinel you never create AWSCloudTrail: Sentinel's Amazon Web Services (AWS) connectors fill it, and you write only queries.
So the lab runs on the Kusto emulator, the Azure Data Explorer engine in a free Docker container, and builds a table with Sentinel's name for it, using Sentinel's column names and types. Every query operator and function used here is one Microsoft documents as available in Sentinel, so what you write carries across.
On this page
Tables in, tables out
A KQL query starts with the name of a table. Each line after that begins with a pipe, |, and one operator. The pipe hands the table so far to the operator; the operator does one job, such as dropping rows, adding a column or counting, and hands a new table on. The table you named is never changed, and whatever leaves the last operator is your result.
AWSCloudTrail // all 24 events | where isnotempty(ErrorCode) // the 10 that failed | summarize Failures = count() by SourceIpAddress // one row per address | sort by Failures desc // biggest first
SourceIpAddress | Failures -----------------+---------- 203.0.113.50 | 7 192.0.2.20 | 3 (2 rows)
Read it as four tables in a row (you can run it yourself once step 3 has built the table). Every prefix of a query is itself a complete query, so the quickest way to debug one is to delete operators from the bottom and run it again until the table stops surprising you. Two rules before you start: everything is case-sensitive (table names, column names, operators, functions), and // starts a comment.
Walk-through: one leaked key, five detections
The lab is 24 AWS CloudTrail events from one morning, trimmed to the fields this guide needs but keeping CloudTrail's real field names and nesting. The access key of build-bot, a continuous integration (CI) user, has leaked: from 203.0.113.50 someone checks whose key it is, tries to list Identity and Access Management (IAM) users, Elastic Compute Cloud (EC2) instances and Secrets Manager secrets (and is mostly refused), then lists the Simple Storage Service (S3) buckets. Next they create a user, backup-svc, attach AdministratorAccess to it, give it an access key, and use the new identity to stop CloudTrail logging. Around that runs an ordinary morning: sign-ins, one of them without multi-factor authentication (MFA) and one failed; an administrator creating a service account; an application refused the same S3 object three times; a CI role describing instances.
The walk-through builds five detections from these events:
- 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:
AdministratorAccessattached to a user.
1. Start a Kusto engine
The Kusto emulator is Microsoft's Azure Data Explorer engine packaged as a Linux container, free and needing no Azure account. Microsoft supports it on Windows and Linux computers with an x86-64 processor and at least 2 GB of memory (4 GB recommended), not on Arm processors, and the image is several gigabytes. Setting ACCEPT_EULA=Y accepts Microsoft's licence terms for the emulator, so read them first; without it the container prints EULA Not Accepted and exits.
mkdir -p ~/kql-lab && cd ~/kql-lab docker run -d --name lab-kusto --platform linux/amd64 -e ACCEPT_EULA=Y -m 4G \ -p 8080:8080 mcr.microsoft.com/azuredataexplorer/kustainer-linux:latest
On an Apple silicon Mac the engine runs under emulation, which Microsoft does not support. This page was checked that way, on macOS with Docker Desktop's Use Rosetta for x86_64/amd64 emulation on Apple Silicon setting turned on (Settings, General), and every step ran. It was not tried without that setting. --platform linux/amd64 asks for the only build Microsoft publishes, so Docker does not warn that it differs from your machine; on an x86-64 computer it changes nothing.
The emulator has no screen of its own. It answers on port 8080 with no sign-in and no encryption, so keep it on your own machine. Microsoft's clients (Kusto.Explorer, Kusto.CLI, the software development kits) can connect to it; to stay within Python's standard library, save this small client instead:
cat > kql.py <<'EOF'
# kql.py: send one query, or one command (it starts with a dot), to the
# emulator and print the result as a table. Usage: python3 kql.py < file.kql
import json, sys, urllib.request, urllib.error
text = sys.stdin.read().strip()
url = "http://localhost:8080/v1/rest/" + ("mgmt" if text.startswith(".") else "query")
body = json.dumps({"db": "NetDefaultDB", "csl": text}).encode()
headers = {"Content-Type": "application/json", "Accept": "application/json"}
try:
table = json.load(urllib.request.urlopen(urllib.request.Request(url, body, headers)))["Tables"][0]
except urllib.error.HTTPError as err: # the engine refused: say why
sys.exit(json.load(err)["error"]["@message"])
except urllib.error.URLError as err: # nothing listening (yet)
sys.exit(f"Cannot reach the emulator: {err.reason}")
show = lambda v: "" if v is None else v if isinstance(v, str) else json.dumps(v, separators=(",", ":"))
rows = [[c["ColumnName"] for c in table["Columns"]]] + [[show(v) for v in r] for r in table["Rows"]]
width = [max(len(r[i]) for r in rows) for i in range(len(rows[0]))]
for n, row in enumerate(rows):
print((" " + " | ".join(v.ljust(w) for v, w in zip(row, width))).rstrip())
if n == 0:
print("+".join("-" * (w + 2) for w in width))
print(f"({len(rows) - 1} row{'' if len(rows) == 2 else 's'})")
EOFTo run anything on this page, save it in a file and run python3 kql.py < file.kql, or run python3 kql.py, paste, and press Ctrl-D. Check that the engine answers (if kql.py cannot reach it yet, give it a few more seconds):
.show version
BuildVersion | BuildTime | ServiceType | ProductVersion | ServiceOffering ----------------+----------------------+-------------+-------------------------------------+----------------- 1.0.9766.21061 | 2026-09-27T11:42:02Z | Engine | 2026.09.27.1134-2638-cb0a545-master | (1 row)
Your build will be newer if Microsoft has published one since. The emulator starts with one database, NetDefaultDB, and kql.py sends everything there.
No Docker, or the emulator will not start? Create a free Azure Data Explorer cluster. It needs a Microsoft account or a Microsoft Entra user identity, not an Azure subscription or a credit card, and Microsoft grants one free cluster for a year, for commercial or non-commercial use, with about 100 GB of storage. Run each KQL block on this page in its web query editor instead of through kql.py; step 2 notes the one difference.
2. Load the lab data
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}
EOFKusto stores data in typed columns. The simplest faithful load is a staging table with one column of type dynamic, which holds any JSON (JavaScript Object Notation) value, one whole event per row:
.create table RawCloudTrail (Event: dynamic)
Now push the file in. format="json" means one JSON object per line, and the mapping says where the column's value comes from: the path $ is the whole event. Microsoft meant .ingest inline for experiments like this one, not for production.
cat > load.kql <<'EOF'
.ingest inline into table RawCloudTrail with (format="json", ingestionMapping='[{"Column":"Event","Properties":{"Path":"$"}}]') <|
EOF
cat cloudtrail-lab.jsonl >> load.kql
python3 kql.py < load.kqlOn a free cluster, paste the whole of load.kql into the query editor and run it. Then look inside what you loaded instead of trusting that the command succeeded:
RawCloudTrail | summarize Rows = count(), NullEvents = countif(isnull(Event))
Rows | NullEvents ------+------------ 24 | 0 (1 row)
A load can succeed and still be empty. Run the same .ingest without ingestionMapping and the engine reports no errors and 24 rows, and all 24 are null: nothing told it which part of each line belongs in Event. A row count proves that rows arrived, not that they hold anything.
3. Give it Sentinel's shape
Sentinel stores CloudTrail in a table called AWSCloudTrail, with its own column names: eventTime arrives as TimeGenerated, sourceIPAddress as SourceIpAddress, userIdentity.arn as UserIdentityArn. Microsoft's AWSCloudTrail table reference also types three columns as string, not dynamic: RequestParameters, ResponseElements and AdditionalEventData hold JSON as plain text. Build the same thing:
.set-or-replace AWSCloudTrail <|
RawCloudTrail
| project
TimeGenerated = todatetime(Event.eventTime),
EventName = tostring(Event.eventName),
SourceIpAddress = tostring(Event.sourceIPAddress),
UserIdentityArn = tostring(Event.userIdentity.arn),
UserIdentityUserName = tostring(Event.userIdentity.userName),
ErrorCode = tostring(Event.errorCode),
RequestParameters = tostring(Event.requestParameters),
ResponseElements = tostring(Event.responseElements),
AdditionalEventData = tostring(Event.additionalEventData)That command is a query with a destination. project chooses the output columns and names them; Event.eventName reaches into the dynamic value; todatetime() and tostring() give each column its type, and tostring() on a nested object produces its JSON text, which is what Sentinel stores. .set-or-replace saves the result as a table, replacing its rows if it exists, so running it twice is harmless.
These are the nine columns this page uses. The real table has more than 50, such as UserIdentityAccountId; a query that names one missing here fails with an error naming the column, so it cannot mislead you quietly. AWSCloudTrail | getschema lists a table's columns and their types.
4. Filter rows: the trail was stopped
AWSCloudTrail
| where EventName in ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")
| project TimeGenerated, EventName, UserIdentityUserName, SourceIpAddressTimeGenerated | EventName | UserIdentityUserName | SourceIpAddress ----------------------+-------------+----------------------+----------------- 2026-09-15T10:09:03Z | StopLogging | backup-svc | 203.0.113.50 (1 row)
where keeps the rows for which its condition is true, and in tests a value against a list. These four calls stop a trail, delete it, or change what it records. One happened, from the attacker's address, made by the identity created minutes before, and the other 23 events stay out, which matters as much. == is exact and case-sensitive, =~ ignores case, and lists work the same way (in and in~). Searching inside text is where people slip:
AWSCloudTrail | summarize HasUser = countif(EventName has "User"), ContainsUser = countif(EventName contains "User")
HasUser | ContainsUser ---------+-------------- 0 | 4 (1 row)
countif() counts the rows where its condition is true. has matches a whole term: Kusto splits text into runs of letters and digits and indexes every term of three or more characters, which makes has fast. CreateUser is one term, so has "User" finds nothing, while in an Amazon Resource Name (ARN) slashes and hyphens split terms, so UserIdentityArn has "backup" does match user/backup-svc. contains matches any part of the text, ignoring case, and cannot use the index: it found both CreateUser events, ListUsers and AttachUserPolicy.
5. Reach into JSON text: a sign-in without MFA
Whether a console sign-in used MFA is recorded in additionalEventData.MFAUsed. In Sentinel's table that column is a string, and the dot only reaches into dynamic values, so AdditionalEventData.MFAUsed fails with a semantic error: a loud failure, which is the good kind. parse_json() turns the text into a dynamic value, tostring() turns the field you pull out back into text you can compare, and extend adds the result as a new column and keeps all the others:
AWSCloudTrail | where EventName == "ConsoleLogin" | extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed) | where MFAUsed == "No" | project TimeGenerated, UserIdentityUserName, SourceIpAddress, MFAUsed
TimeGenerated | UserIdentityUserName | SourceIpAddress | MFAUsed ----------------------+----------------------+-----------------+--------- 2026-09-15T09:15:47Z | bob | 198.51.100.21 | No 2026-09-15T09:20:03Z | alice | 192.0.2.77 | No (2 rows)
Two rows, and one of them is wrong. Alice's sign-in from 192.0.2.77 failed: there was no session for MFA to protect, and a rule that fires on it is noise. The outcome is in responseElements.ConsoleLogin:
AWSCloudTrail
| where EventName == "ConsoleLogin"
| extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed),
Outcome = tostring(parse_json(ResponseElements).ConsoleLogin)
| where Outcome == "Success" and MFAUsed == "No"
| project TimeGenerated, UserIdentityUserName, SourceIpAddress, Outcome, MFAUsedTimeGenerated | UserIdentityUserName | SourceIpAddress | Outcome | MFAUsed ----------------------+----------------------+-----------------+---------+--------- 2026-09-15T09:15:47Z | bob | 198.51.100.21 | Success | No (1 row)
Bob, and only Bob. The alarm AWS Security Hub checks for this (control CloudWatch.3) also requires a successful sign-in, and Microsoft's own AWS_ConsoleLogonWithoutMFA.yaml leaves failed ones out too. When a column is already dynamic, parse_json() passes the value through unchanged, so writing it costs nothing and the query works whichever type the column has.
6. Count, then threshold: a burst of refusals
A first draft of a rule for a leaked key being tried out: count AccessDenied errors per source address, and alert at five.
AWSCloudTrail | where ErrorCode == "AccessDenied" | summarize Denied = count() by SourceIpAddress | where Denied >= 5
SourceIpAddress | Denied -----------------+-------- (0 rows)
Nothing. A quiet morning, or a rule that cannot fire? Take the threshold away and look at what the data holds:
AWSCloudTrail | where isnotempty(ErrorCode) | summarize Events = count() by ErrorCode
ErrorCode | Events ------------------------------+-------- AccessDenied | 7 Client.UnauthorizedOperation | 3 (2 rows)
EC2 reports a refusal as Client.UnauthorizedOperation. The draft counted four of the attacker's seven refusals, one short of its own threshold. Other services spell a refusal differently again: the metric filter AWS Security Hub expects for unauthorized API calls (control CloudWatch.2) matches any code ending in UnauthorizedOperation or starting with AccessDenied, such as AccessDeniedException, and a refusal by a virtual private cloud (VPC) endpoint's policy is logged as VpceAccessDenied. Note isnotempty(), not isnull(): in Kusto a string cannot be null, so a missing errorCode arrives as an empty string and isnull(ErrorCode) is never true, here or in Sentinel.
A query that returns nothing and a query that cannot work return the same thing. Before you trust an empty result, remove the threshold or the filter and confirm that the rows you meant to count exist and look the way you assumed. Make that control part of testing every rule.
AWSCloudTrail | where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized" | summarize Denied = count(), Codes = make_set(ErrorCode) by SourceIpAddress | where Denied >= 5
SourceIpAddress | Denied | Codes -----------------+--------+------------------------------------------------- 203.0.113.50 | 7 | ["AccessDenied","Client.UnauthorizedOperation"] (1 row)
The attacker's address, and not the application's three refusals. Why contains and not a list: in with an exact list counts only the spellings you already know, which is how the draft failed, while the two words the codes share also catch prefixed and suffixed variants. has would not work here, because AccessDeniedException and UnauthorizedOperation are single terms that are not the terms AccessDenied or Unauthorized. The price is a scan instead of an index lookup, which is small for one table over a short window. make_set() puts the distinct codes in the result, so whoever reads the alert sees what was counted.
7. Time: ten-minute buckets
AWSCloudTrail | where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized" | summarize Denied = count() by SourceIpAddress, bin(TimeGenerated, 10m) | sort by SourceIpAddress asc, TimeGenerated asc
SourceIpAddress | TimeGenerated | Denied -----------------+----------------------+-------- 192.0.2.20 | 2026-09-15T09:30:00Z | 1 192.0.2.20 | 2026-09-15T09:40:00Z | 1 192.0.2.20 | 2026-09-15T10:00:00Z | 1 203.0.113.50 | 2026-09-15T10:00:00Z | 7 (4 rows)
bin() rounds each time down to a multiple of ten minutes, so rows fall into buckets aligned to the clock. All seven of the attacker's refusals land in the 10:00 bucket, within a minute of each other. The application's three are one per bucket across half an hour: something retrying, not someone exploring. For a fixed window, between includes both ends: where TimeGenerated between (datetime(2026-09-15 10:00) .. datetime(2026-09-15 10:10)).
8. Two events, one story: a new user given a key at once
Creating a user and immediately giving it an access key is a way to keep access after the leaked key is revoked. That is two events, so it needs a join. let gives a query a name you can use further down; each let ends with a semicolon.
let Created = AWSCloudTrail | where EventName == "CreateUser"
| project CreatedAt = TimeGenerated, CreatedBy = UserIdentityUserName, SourceIpAddress,
NewUser = tostring(parse_json(RequestParameters).userName);
let Keyed = AWSCloudTrail | where EventName == "CreateAccessKey"
| project KeyAt = TimeGenerated, NewUser = tostring(parse_json(RequestParameters).userName);
Created
| join kind=inner Keyed on NewUser
| project NewUser, CreatedAt, KeyAt, Gap = KeyAt - CreatedAt, CreatedBy, SourceIpAddressNewUser | CreatedAt | KeyAt | Gap | CreatedBy | SourceIpAddress ---------------+----------------------+----------------------+----------+-----------+----------------- backup-svc | 2026-09-15T10:06:12Z | 2026-09-15T10:06:51Z | 00:00:39 | build-bot | 203.0.113.50 svc-reporting | 2026-09-15T09:02:40Z | 2026-09-15T11:40:26Z | 02:37:46 | alice | 198.51.100.20 (2 rows)
join kind=inner pairs each row on the left with every row on the right that has the same NewUser. Both users match, and svc-reporting is alice doing her job: she created it at 09:02 and gave it a key more than two and a half hours later. The detection is the time window, a where after the join:
let Created = AWSCloudTrail | where EventName == "CreateUser"
| project CreatedAt = TimeGenerated, CreatedBy = UserIdentityUserName, SourceIpAddress,
NewUser = tostring(parse_json(RequestParameters).userName);
let Keyed = AWSCloudTrail | where EventName == "CreateAccessKey"
| project KeyAt = TimeGenerated, NewUser = tostring(parse_json(RequestParameters).userName);
Created
| join kind=inner Keyed on NewUser
| where KeyAt between (CreatedAt .. CreatedAt + 10m)
| project NewUser, CreatedAt, KeyAt, Gap = KeyAt - CreatedAt, CreatedBy, SourceIpAddressNewUser | CreatedAt | KeyAt | Gap | CreatedBy | SourceIpAddress ------------+----------------------+----------------------+----------+-----------+----------------- backup-svc | 2026-09-15T10:06:12Z | 2026-09-15T10:06:51Z | 00:00:39 | build-bot | 203.0.113.50 (1 row)
Thirty-nine seconds, by the leaked identity, from the attacker's address. Always write the kind. Leave it out and KQL uses innerunique, which keeps only one left-hand row for each key value, and Microsoft's documentation says which one is chosen at random: a user who signed in twice can quietly become a user who signed in once.
9. Make it a Sentinel analytics rule
In Sentinel a detection is a scheduled analytics rule: the query, plus how often to run it, how far back to look, when to alert, and what the alert is about. You can build one in the portal's rule wizard or deploy it as an Azure Resource Manager (ARM) template. Microsoft writes its own rules as YAML files, in the format its rule-authoring guide describes, and publishes them in the Azure-Sentinel repository's AWS analytic rules folder. That format is a good way to keep a rule under version control. Save step 6's query in the same shape; the id is a globally unique identifier (GUID) you generate once:
cat > AWS_RefusedCallBurst.yaml <<'EOF'
id: 200e45de-0a6e-4803-8137-248a3378a8dd
name: Burst of refused AWS API calls from one address
description: Identifies five or more refused AWS API calls from one source address.
severity: Medium
requiredDataConnectors:
- connectorId: AWS
dataTypes:
- AWSCloudTrail
- connectorId: AWSS3
dataTypes:
- AWSCloudTrail
queryFrequency: 10m
queryPeriod: 20m
triggerOperator: gt
triggerThreshold: 0
tactics:
- Discovery
relevantTechniques:
- T1580
- T1087.004
query: |
AWSCloudTrail
| where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized"
| summarize Denied = count(), Codes = make_set(ErrorCode) by SourceIpAddress
| where Denied >= 5
entityMappings:
- entityType: IP
fieldMappings:
- identifier: Address
columnName: SourceIpAddress
customDetails:
DeniedCalls: Denied
version: 1.0.0
kind: Scheduled
EOFqueryFrequencyandqueryPeriodare "run every" and "look back over", each from 5 minutes to 14 days. Sentinel runs the query over the lookback only, so it needs no time filter of its own, and starts each run five minutes after its scheduled time to allow for late events. A lookback twice the frequency means a burst lasting ten minutes or less falls wholly inside at least one run; the price is that a burst can be counted in two runs.triggerOperator: gtwithtriggerThreshold: 0means "alert when the query returns any rows". The trigger counts result rows, not events, so the count of five belongs in the query.requiredDataConnectorsnames both of Sentinel's AWS connectors, the older CloudTrail one (AWS) and the one that reads logs from an S3 bucket (AWSS3), because either can fillAWSCloudTrail.entityMappingstells Sentinel which column holds an address, so alerts and incidents about the same address can be grouped.customDetailsputs the count on the alert.tacticsandrelevantTechniquesmap the rule to MITRE ATT&CK, MITRE's knowledge base of adversary tactics and techniques: Discovery, with Cloud Infrastructure Discovery (T1580) and Account Discovery: Cloud Account (T1087.004). Sentinel accepts only its own list of tactic names, which the authoring guide ties to ATT&CK v16, so it lags ATT&CK's releases; Exercise 4 shows what that means for log tampering.
10. Test the rule both ways
The emulator has no scheduler, but you can count what two consecutive runs would see by writing their 20-minute windows by hand: the run at 10:00 looks back to 09:40, the run at 10:10 to 09:50.
AWSCloudTrail
| where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized"
| summarize
RunAt1000 = countif(TimeGenerated between (datetime(2026-09-15 09:40) .. datetime(2026-09-15 10:00))),
RunAt1010 = countif(TimeGenerated between (datetime(2026-09-15 09:50) .. datetime(2026-09-15 10:10)))
by SourceIpAddressSourceIpAddress | RunAt1000 | RunAt1010 -----------------+-----------+----------- 192.0.2.20 | 1 | 1 203.0.113.50 | 0 | 7 (2 rows)
The 10:10 run sees the attacker's seven refusals and raises an alert; the 10:00 run sees one of the application's and none of the attacker's, and stays silent. Keep a check like this with the rule as its test, one window that must alert and one that must not: the second is what catches a rule that fires on everything.
Hands-on exercises
The attacker gave backup-svc administrator rights (D5). Write a query that finds any attachment of a policy named AdministratorAccess, whether to a user, a group or a role, and shows who did it, to what, and when.
Show the answer
AWSCloudTrail
| where EventName in ("AttachUserPolicy", "AttachGroupPolicy", "AttachRolePolicy")
| extend Params = parse_json(RequestParameters)
| extend PolicyArn = tostring(Params.policyArn),
Target = coalesce(tostring(Params.userName), tostring(Params.groupName),
tostring(Params.roleName))
| where PolicyArn endswith "/AdministratorAccess"
| project TimeGenerated, UserIdentityUserName, EventName, Target, PolicyArnTimeGenerated | UserIdentityUserName | EventName | Target | PolicyArn ----------------------+----------------------+------------------+------------+--------------------------------------------- 2026-09-15T10:06:30Z | build-bot | AttachUserPolicy | backup-svc | arn:aws:iam::aws:policy/AdministratorAccess (1 row)
Parse the JSON once into a column, then pull fields from it. The target's name sits in a different field for each call, and coalesce() returns the first argument that is not null, or for strings not empty: the empty-string rule from step 6 working in your favour. endswith matches the AWS managed policy and any customer policy given the same name, and both are worth a look.
A colleague's version of the burst query buckets time in ten-minute steps that start at three minutes past (a rule that runs at :03, :13 and :23 splits time the same way). It returns nothing, although the seven refusals are still in the data. What happened, and how do you prove it?
AWSCloudTrail
| where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized"
| summarize Denied = count()
by SourceIpAddress, Bucket = bin_at(TimeGenerated, 10m, datetime(2026-09-15 10:03))
| where Denied >= 5SourceIpAddress | Bucket | Denied -----------------+--------+-------- (0 rows)
Show the answer
Run the control: the same query with the threshold removed.
AWSCloudTrail
| where ErrorCode contains "AccessDenied" or ErrorCode contains "Unauthorized"
| summarize Denied = count()
by SourceIpAddress, Bucket = bin_at(TimeGenerated, 10m, datetime(2026-09-15 10:03))
| sort by SourceIpAddress asc, Bucket ascSourceIpAddress | Bucket | Denied -----------------+----------------------+-------- 192.0.2.20 | 2026-09-15T09:23:00Z | 1 192.0.2.20 | 2026-09-15T09:43:00Z | 1 192.0.2.20 | 2026-09-15T09:53:00Z | 1 203.0.113.50 | 2026-09-15T09:53:00Z | 3 203.0.113.50 | 2026-09-15T10:03:00Z | 4 (5 rows)
The burst straddles a bucket boundary. Three refusals fell before 10:03:00 and four after, and neither bucket reaches five. Nothing about the attack changed, only where the boundaries fell. This is why the rule in step 9 counts over its whole lookback instead of binning inside it, and uses a lookback longer than its frequency.
Another colleague looks for the stopped trail over the last day, gets a count of zero, and concludes that nobody touched it. What went wrong, and what should they have run first?
AWSCloudTrail | where TimeGenerated > ago(1d) | where EventName == "StopLogging" | count
Count ------- 0 (1 row)
Show the answer
ago() counts back from the engine's clock, and these events happened on 15 September 2026, so the time filter removed every one of them. The control is to ask the data what time it covers:
AWSCloudTrail | summarize Earliest = min(TimeGenerated), Latest = max(TimeGenerated)
Earliest | Latest ----------------------+---------------------- 2026-09-15T08:55:12Z | 2026-09-15T11:40:26Z (1 row)
On lab data, use fixed times instead. Sentinel adds more layers of the same trap: the Log Analytics time picker shows the last 24 hours unless your query sets its own range, and a scheduled rule only ever sees its lookback. And if you sent these lab events to a real Log Analytics workspace, Azure Monitor would replace every TimeGenerated more than two days older than its arrival with the arrival time, so their dates would not survive.
Turn the trail query from step 4 into a Sentinel rule (D1). What goes in tactics and relevantTechniques, and what should the query add so that it alerts only when the change succeeded?
Show the answer
Sentinel still uses ATT&CK's pre-v19 names, so the mapping it accepts is the one in Microsoft's own AWS_LogTampering.yaml:
tactics: - DefenseEvasion relevantTechniques: - T1562.008
AWSCloudTrail
| where EventName in ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")
| where isempty(ErrorCode)
| project TimeGenerated, EventName, UserIdentityUserName, SourceIpAddressisempty(ErrorCode) keeps only calls that succeeded, so this returns step 4's single row: the StopLogging call went through. Microsoft's library puts refused attempts in a separate, low-severity rule, AWS_ClearStopChangeTrailLogs.yaml. In ATT&CK v19.2 this behaviour is T1685.002 Disable or Modify Cloud Log, under the new Defense Impairment tactic (TA0112): T1562.008 is revoked, and TA0005, which Sentinel still calls DefenseEvasion, is renamed Stealth. Neither new name is in Sentinel's tactic list yet, and Microsoft's content checks reject T1685.002 because no tactic they know contains it. Map to what your platform accepts, and expect the mapping to move when it catches up.
Common mistakes
- Reading
hasas "contains".hasmatches whole terms, andCreateUseris one term. Usecontainsfor part of a word. - Testing a string with
isnull(). Kusto strings are never null; a missing value is empty. Useisempty()andisnotempty(). - Trusting
!=to mean "has a different value".ErrorCode != "AccessDenied"also keeps every event that has no error code at all. - Counting one spelling of an outcome. A rule that counts only
AccessDeniedmisses EC2'sClient.UnauthorizedOperation, under-counts, and stays silent. - Treating Sentinel's JSON columns as objects.
RequestParameters,ResponseElementsandAdditionalEventDataare text. The dot fails loudly; searching the raw text withhasorcontainsfails quietly, because it can match the word in any field. - Leaving out
kind=on a join. The default,innerunique, keeps one left-hand row per key and drops the rest without saying so. - Filtering with
ago()before checking the data's dates. Old data, lab data and a time picker set to the last 24 hours all return nothing. Runmin(TimeGenerated)andmax(TimeGenerated)first. - Binning inside a scheduled rule. A bucket boundary can split a burst so that neither half reaches the threshold. Count over the whole lookback.
- Believing a load because it reported rows. JSON ingested without a mapping arrives as rows of nulls. Look inside before you query.
Where next
- Sigma and YARA, for writing a detection once in a vendor-neutral format and converting it for a SIEM such as Sentinel.
- Splunk SPL (Search Processing Language) and Elastic ES|QL and EQL (Elasticsearch Query Language and Event Query Language), the same five detections on the same 24 events in other query languages.
- Detection engineering for the lifecycle around a rule: testing, tuning, versioning and retiring it.
- The CloudTrail to SIEM lab, to send real CloudTrail from your own account somewhere these queries can run.
- Microsoft's common tasks with KQL for Microsoft Sentinel and the Azure-Sentinel repository, where reading Microsoft's own rules is the quickest way to learn the idioms.