Time: ~2 hours · Difficulty: Intermediate · You need: Docker, curl, jq and about 5 GB of free disk, plus Python 3.12 for the last step. Everything runs on your own machine at no cost, with no cloud account.
How this page was checked: every command and query below was run as printed, and every output pasted from that run, on Elasticsearch and Kibana 9.5.4 installed by start-local 0.14.0, with Elastic's AWS integration 7.1.4, on an Apple silicon Mac: first on the 30-day trial licence start-local turns on, then again with its licence setting changed to Basic, with identical results. The rule file in step 12 passed Elastic's own validator (elastic/detection-rules at commit 2613eb7). Not run: loading that rule into Kibana's Elastic Security app, and what start.sh does once the trial has expired, which is described from reading the script.
Two query languages, two different questions
Elasticsearch stores events as JSON (JavaScript Object Notation) documents in named collections called indices, and Kibana is the web interface in front of it. Elastic Security, the SIEM (security information and event management) application in Kibana, runs detection rules against those indices on a schedule. Its rules can be written in several languages, and two of them are this page's subject:
- ES|QL starts from an index and passes a table of events through a chain of commands that filter, compute, count and sort. It runs in Kibana's Discover, through the
_queryAPI (application programming interface), and as the ES|QL rule type. - EQL matches events by category and, its reason to exist, sequences: this event, then that one, sharing a value, within a time limit. It runs through the
_eql/searchAPI and as the Event correlation rule type.
The distinction that matters: ES|QL answers "which events, and how many", and EQL answers "did this happen, and then that". A question with then in it belongs to EQL; a question with how many belongs to ES|QL.
A third language appears in Kibana's search bar and in many of Elastic's rules: KQL, the Kibana Query Language, which only filters. It is unrelated to the Kusto Query Language that Microsoft also abbreviates KQL, which has its own guide.
On this page
The one idea behind each language
ES|QL is a chain of tables. A query starts with a source command, usually FROM and the indices to read (here the pattern logs-aws.cloudtrail-*), which produces a table: one row per event, one column per field. Each | (a pipe) hands the table to the next command, which returns a new table. Read a query top to bottom as a list of steps; text after // is a comment. Once the lab is loaded (step 4), this runs for real with the helper from step 2:
FROM logs-aws.cloudtrail-* // 24 rows, one per event | WHERE source.ip == "203.0.113.50" // keep the 13 from one address | STATS calls = COUNT(*) BY event.outcome // a new table: one row per outcome | SORT calls DESC
calls | event.outcome ---------------+--------------- 7 |failure 6 |success
Two rows, two columns, biggest first: after STATS the table no longer holds events at all, only the counts you asked for.
EQL is a pattern over events in time. A query names an event category, then where, then a condition: iam where event.action == "CreateUser" matches user-creation events. A sequence lists several such patterns in square brackets, in the order they must happen, tied together by a shared value (by) and a time limit (with maxspan). EQL returns matching events, or matching sequences with their events inside, rather than a table.
Both query the field names your ingest pipeline wrote, not the raw log's. CloudTrail calls the API operation eventName. Elastic's AWS (Amazon Web Services) integration renames it event.action, following ECS (Elastic Common Schema), and every Elastic rule is written against the renamed fields. Step 3 installs that translation, because a mismatch there is the quietest way for a detection to fail.
Set up the lab
The lab data is the 24-event story shared by the guides in this series. A CI (continuous integration) user's access key, build-bot, has leaked. From 203.0.113.50 someone checks whose key it is, tries to enumerate IAM (Identity and Access Management), EC2 (Amazon Elastic Compute Cloud) and Secrets Manager and is mostly refused, and lists the S3 (Amazon Simple Storage Service) buckets. Then they create a user, backup-svc, make it an administrator, give it an access key and use that new identity to stop CloudTrail logging. Around the attack sits ordinary activity: alice, an administrator, signs in with MFA (multi-factor authentication) and later gives a service user a key, bob signs in without MFA, someone fails to sign in as alice, and an application is refused the same S3 object three times.
The walk-through builds five detections from it:
- 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 Elasticsearch and Kibana
Elastic's start-local script runs both in Docker on your machine. Pin the version so your output matches this page. The installer prints its own progress messages first; the last line should be the version:
curl -fsSL https://elastic.co/start-local | sh -s -- -v 9.5.4 cd elastic-start-local source .env curl -s "$ES_LOCAL_URL" -H "Authorization: ApiKey $ES_LOCAL_API_KEY" | jq -r .version.number
9.5.4
Piping a script into sh runs whatever it contains, so read it first. This one creates the elastic-start-local folder with a Docker Compose file, a .env settings file and three scripts, start.sh, stop.sh and uninstall.sh. It starts Elasticsearch on localhost:9200 and Kibana on localhost:5601, reachable only from your own machine, with passwords on and encryption off, so it is for testing only. On Apple silicon it pulls Elastic's -arm64 images. It generates a password for the elastic user and an API key, prints both at the end and keeps them in .env, which source .env loads into your shell; sign in to Kibana with that user and password. Keep Kibana: step 3 uses it. When you are done, ./uninstall.sh removes the containers, their data and the files it created, and asks separately about the images.
Licence: a setting in the Compose file starts Elasticsearch on a 30-day trial of Elastic's paid features, with no sign-up. The first time you run ./start.sh after the 30 days, it switches the cluster to the free Basic licence by calling the licence API with acknowledge=true, which accepts that change on your behalf. Nothing here needs the trial: on Basic, ES|QL and EQL are both available, and this page gave the same results on both.
2. Two helper scripts for sending queries
Both languages travel to Elasticsearch inside a small JSON document. Rather than escaping quotes by hand for every query, save two short scripts in this folder. jq builds the JSON; the jq guide explains it if it is new to you.
cat > esql <<'EOF'
#!/bin/sh
. ./.env
jq -Rs '{query: .}' |
curl -s "$ES_LOCAL_URL/_query?format=txt" --data-binary @- \
-H "Authorization: ApiKey $ES_LOCAL_API_KEY" -H 'Content-Type: application/json'
EOF
cat > eql <<'EOF'
#!/bin/sh
. ./.env
jq -Rs '{query: ., size: 20, fields: ["@timestamp", "event.action", "user.name", "user.target.name", "source.ip"]}' |
curl -s "$ES_LOCAL_URL/logs-aws.cloudtrail-*/_eql/search" --data-binary @- \
-H "Authorization: ApiKey $ES_LOCAL_API_KEY" -H 'Content-Type: application/json' |
jq -r 'def row: .fields | [."@timestamp", ."event.action", ."user.name",
."user.target.name", ."source.ip"] | map(.[0] // "-") | join(" ");
if .error then .error.root_cause[0].reason
elif .hits.sequences then "sequences found: \(.hits.total.value)",
(.hits.sequences[] | "\(.join_keys)", (.events[] | " " + row))
else "events found: \(.hits.total.value)", (.hits.events[]? | row) end'
EOF
chmod +x esql eql./esql prints an ES|QL result as a text table. ./eql runs EQL against the lab data and prints one line per event (time, action, user, target user, source address, with - where the event has no value); a sequence prints its shared value in brackets, then its events. Both read the query on standard input. Every query below is shown on its own: run ./esql or ./eql, paste the query, press Return and then Ctrl-D, or save it in a file and run ./esql < file. The ES|QL blocks also paste unchanged into Kibana's Discover.
3. Install Elastic's AWS integration
Raw CloudTrail says eventName, sourceIPAddress and errorCode. Elastic's AWS integration renames them to ECS names such as event.action, source.ip and aws.cloudtrail.error_code, and derives new fields, in an ingest pipeline: a list of processors that runs on each document as it arrives. The integration is a package whose assets include that pipeline, templates that declare each field's type (its mapping: keyword for exact text, ip, date, boolean) and dashboards. Kibana's Fleet API installs them in one call. You need neither Elastic Agent nor an AWS account, because you send the lab file yourself. Version 7.1.4 is the newest that Elastic's package registry offers for 9.5.4:
curl -s -o /dev/null -w '%{http_code}\n' -X POST "http://localhost:5601/api/fleet/epm/packages/aws/7.1.4" \
-H "Authorization: ApiKey $ES_LOCAL_API_KEY" -H 'kbn-xsrf: true'
curl -s "$ES_LOCAL_URL/_ingest/pipeline/logs-aws.cloudtrail-7.1.4" -H "Authorization: ApiKey $ES_LOCAL_API_KEY" |
jq -c 'to_entries[] | {pipeline: .key, processors: (.value.processors | length)}'200
{"pipeline":"logs-aws.cloudtrail-7.1.4","processors":137}That is the package's 133 CloudTrail processors plus four that Fleet adds so you can attach your own. The pipeline runs on anything written to a data stream (a series of indices Elasticsearch manages as one) whose name matches logs-aws.cloudtrail-*. What it does that this page relies on:
user.target.nameis copied fromrequestParameters.userName: the user an IAM call acted on. Step 10 ties events together on it.event.outcomeisfailurewhen there is an error code or message, and console logins take theirs fromresponseElements.ConsoleLogin, so the failed login counts as a failure although it has noerrorCode.- The MFA flag becomes a true/false field, and
aws.cloudtrail.request_parametersbecomes text in a{name=value, ...}form. It is not JSON, and several of Elastic's rules match pieces of it. event.categorycomes from a table of 91 event names.StopLoggingis not one of them, and step 9 shows why that matters.
4. Load the lab data
Create the data file. The block is long: copy all of it, down to the closing EOF.
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}
EOFElastic Agent would send each record in an envelope, and the integration relies on it: the record as text in a field called message, which the pipeline parses, and the data stream's type, dataset and namespace in data_stream. jq builds that and pairs each document with the bulk API's action line, {"create":{}} ("add this document"; data streams accept no other).
jq -c '{create: {}}, {message: tojson, data_stream: {type: "logs", dataset: "aws.cloudtrail", namespace: "default"}}' cloudtrail-lab.jsonl |
curl -s -w '\n' "$ES_LOCAL_URL/logs-aws.cloudtrail-default/_bulk?refresh=true&filter_path=errors,items.*.error" \
--data-binary @- -H "Authorization: ApiKey $ES_LOCAL_API_KEY" -H 'Content-Type: application/x-ndjson'{"errors":false}Then count what arrived, with your first ES|QL query:
FROM logs-aws.cloudtrail-* | STATS events = COUNT(*)
events --------------- 24
filter_path trims the bulk reply to what matters, so read it: _bulk answers with a success status even when every document in it was rejected, and only "errors":true tells you.
Field names are a contract between the pipeline and every rule. Leave data_stream out of that envelope and all 24 events still load without an error, but data_stream.dataset stays empty, and every Elastic rule that filters on it, the D1 rule in step 11 included, matches nothing. A rule that names a field nothing fills does not fail. It returns nothing, and looks exactly like a quiet day.
ES|QL walk-through
5. Filter: trail tampering (D1)
WHERE keeps the rows for which a condition is true, and KEEP chooses the columns to show. == tests equality (a single = is a syntax error), strings go in double quotes, IN tests against a list, and comparisons are case-sensitive, so "stoplogging" matches nothing.
FROM logs-aws.cloudtrail-*
| WHERE event.action IN ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")
| KEEP @timestamp, event.action, user.name, source.ip@timestamp | event.action | user.name | source.ip ------------------------+---------------+---------------+--------------- 2026-09-15T10:09:03.000Z|StopLogging |backup-svc |203.0.113.50
One event, at 10:09:03, by the backup-svc user the attacker had created three minutes earlier. The other 23 events are the negative case: none of them touch the trail.
6. Nested fields and true/false values: console login without MFA (D4)
CloudTrail nests the flag as additionalEventData.MFAUsed: "No"; the pipeline turns it into the boolean aws.cloudtrail.console_login.additional_eventdata.mfa_used. The dots are part of the field's name, so you type it whole. Booleans are written true and false, without quotes.
FROM logs-aws.cloudtrail-*
| WHERE event.action == "ConsoleLogin"
AND aws.cloudtrail.console_login.additional_eventdata.mfa_used == false
| KEEP @timestamp, user.name, source.ip, event.outcome
| SORT @timestamp@timestamp | user.name | source.ip | event.outcome ------------------------+---------------+---------------+--------------- 2026-09-15T09:15:47.000Z|bob |198.51.100.21 |success 2026-09-15T09:20:03.000Z|alice |192.0.2.77 |failure
Two rows, and the second is wrong: someone at 192.0.2.77 failed to sign in as alice. MFA was never reached, so CloudTrail records it as not used. Require success as well:
FROM logs-aws.cloudtrail-*
| WHERE event.action == "ConsoleLogin"
AND aws.cloudtrail.console_login.additional_eventdata.mfa_used == false
AND event.outcome == "success"
| KEEP @timestamp, user.name, source.ip@timestamp | user.name | source.ip ------------------------+---------------+--------------- 2026-09-15T09:15:47.000Z|bob |198.51.100.21
bob, from 198.51.100.21, and nobody else: alice's sign-in with MFA and the failed attempt both stay out. AWS's own Security Hub control for this requires success too, and so does Elastic's rule, AWS IAM User Console Login Without MFA; both also check that the caller is an IAM user, and Elastic's is a New terms rule, which alerts when a user does this for the first time in seven days.
7. Count and threshold: the denied-call burst (D2)
The obvious rule: count AccessDenied errors per source address and alert at five or more. STATS ... BY groups the rows that share a value and computes something for each group, here COUNT(*), the number of rows. A WHERE after STATS filters the groups rather than the events:
FROM logs-aws.cloudtrail-* | WHERE aws.cloudtrail.error_code == "AccessDenied" | STATS denied = COUNT(*) BY source.ip | WHERE denied >= 5
denied | source.ip ---------------+---------------
Column headings and no rows. That is either a quiet morning or a rule that cannot fire, and from here they look identical. Run the control: drop the filter and the threshold, and ask which error codes exist at all.
FROM logs-aws.cloudtrail-* | STATS events = COUNT(*) BY aws.cloudtrail.error_code | SORT events DESC
events | aws.cloudtrail.error_code ---------------+---------------------------- 14 |null 7 |AccessDenied 3 |Client.UnauthorizedOperation
null means "this event has no value here": the 14 calls that succeeded have no error code, and STATS gives them a group of their own. The third line is the one that matters. EC2 does not say AccessDenied when it refuses a call; it says Client.UnauthorizedOperation. Other services use AccessDeniedException or a bare UnauthorizedOperation, which is why AWS's own Security Hub control for unauthorized API calls matches any code that starts AccessDenied or ends UnauthorizedOperation. The attacker's seven refusals were four of one code and three of the other, so the rule counted four. Count every denial code:
FROM logs-aws.cloudtrail-*
| WHERE aws.cloudtrail.error_code IN ("AccessDenied", "AccessDeniedException",
"Client.UnauthorizedOperation", "UnauthorizedOperation")
| STATS denied = COUNT(*) BY source.ip
| WHERE denied >= 5denied | source.ip ---------------+--------------- 7 |203.0.113.50
Seven for 203.0.113.50, while the reports application at 192.0.2.20 stays below the threshold with three: the positive fires and the negative stays silent.
A query that returns nothing and a query that cannot work return the same thing. Before you trust an empty result, remove the threshold and look at the values that actually exist. One STATS ... BY aws.cloudtrail.error_code exposes the missing code before the rule is ever written.
8. Time windows
A burst is a count inside a time window. BUCKET(@timestamp, 10 minutes) rounds each timestamp down to the start of its ten-minute slot, so grouping by it counts per slot:
FROM logs-aws.cloudtrail-*
| WHERE aws.cloudtrail.error_code IN ("AccessDenied", "AccessDeniedException",
"Client.UnauthorizedOperation", "UnauthorizedOperation")
| STATS denied = COUNT(*) BY source.ip, window = BUCKET(@timestamp, 10 minutes)
| SORT source.ip, windowdenied | source.ip | window ---------------+---------------+------------------------ 1 |192.0.2.20 |2026-09-15T09:30:00.000Z 1 |192.0.2.20 |2026-09-15T09:40:00.000Z 1 |192.0.2.20 |2026-09-15T10:00:00.000Z 7 |203.0.113.50 |2026-09-15T10:00:00.000Z
All seven of the attacker's denials fall in the 10:00 slot; the reports application's three are spread over three slots, which is what a misconfigured application retrying looks like. The slots are aligned to the clock, not to the first event, so a burst from 10:08 to 10:12 is split across two slots and can slip under a threshold. When this becomes a rule, its schedule and look-back matter as much as the query.
EQL walk-through
9. Event queries, and the category trap (D1)
An EQL query is category where condition. The category is compared with the event's event.category field, and any means "whatever the category". Lists use lowercase in:
any where event.action in ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")events found: 1 2026-09-15T10:09:03.000Z StopLogging backup-svc - 203.0.113.50
The same event as step 5. Trail changes sound like configuration, so it is tempting to narrow the category:
configuration where event.action in ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")events found: 0
No error, and nothing found. Ask ES|QL which category the event really has:
FROM logs-aws.cloudtrail-* | WHERE event.action == "StopLogging" | KEEP event.action, event.category
event.action |event.category ---------------+--------------- StopLogging |null
None. The integration fills event.category from its table of 91 event names: DeleteTrail is in it, as configuration, but StopLogging, UpdateTrail and PutEventSelectors are not. A configuration where rule would catch one of the four trail-tampering actions and miss the other three without a word. Use any where when the condition already names the actions, and check which categories exist before you rely on one. EQL's == is case-sensitive too; its : ignores case and accepts * wildcards, so event.action : "create*" matches both CreateUser and CreateAccessKey.
10. Sequences: a new user given a key within ten minutes (D3)
This is what EQL exists for. The question has a then in it: a user is created, then an access key is created for that same user, soon after. Write each event as a bracketed pattern, in order. by user.target.name says both must share that field's value, and with maxspan=10m says the whole sequence must fit inside ten minutes, counted from its first event:
sequence by user.target.name with maxspan=10m [iam where event.action == "CreateUser"] [iam where event.action == "CreateAccessKey"]
sequences found: 1 ["backup-svc"] 2026-09-15T10:06:12.000Z CreateUser build-bot backup-svc 203.0.113.50 2026-09-15T10:06:51.000Z CreateAccessKey build-bot backup-svc 203.0.113.50
Thirty-nine seconds apart, both by build-bot. The tie works because the pipeline copied requestParameters.userName into user.target.name for both calls. Now the control: remove with maxspan=10m and run it again.
sequence by user.target.name [iam where event.action == "CreateUser"] [iam where event.action == "CreateAccessKey"]
sequences found: 2 ["backup-svc"] 2026-09-15T10:06:12.000Z CreateUser build-bot backup-svc 203.0.113.50 2026-09-15T10:06:51.000Z CreateAccessKey build-bot backup-svc 203.0.113.50 ["svc-reporting"] 2026-09-15T09:02:40.000Z CreateUser alice svc-reporting 198.51.100.20 2026-09-15T11:40:26.000Z CreateAccessKey alice svc-reporting 198.51.100.20
alice created svc-reporting at 09:02 and gave it a key at 11:40, two hours and 37 minutes later: ordinary administration, and without the time limit it matches too. The limit is what separates "someone is building a backdoor right now" from "someone set up a service account this morning", and this negative case is how you prove the rule knows the difference.
From query to detection rule
11. Read one of Elastic's rules
A query answers a question once. A detection rule asks it on a schedule, by default every five minutes over the last few minutes of data, and turns each match into an alert. Elastic Security has seven rule types. Single-event detections such as D1 and D5 fit a Custom query (KQL) or Event correlation (EQL) rule; the D2 count fits an ES|QL rule, where each row the query returns becomes an alert, or a Threshold rule, which counts per field value without STATS; the D3 sequence is Event correlation; and Elastic writes D4 as a New terms rule, which alerts on a value seen for the first time. Each rule also carries a MITRE ATT&CK (Adversarial Tactics, Techniques, and Common Knowledge) mapping, which coverage reports and triage build on. In ATT&CK v19.2 the five detections map to T1685.002 Disable or Modify Cloud Log (D1), T1087.004 Account Discovery: Cloud Account and T1580 Cloud Infrastructure Discovery (D2), T1136.003 Create Account: Cloud Account and T1098.001 Additional Cloud Credentials (D3), T1078.004 Valid Accounts: Cloud Accounts (D4) and T1098.003 Additional Cloud Roles (D5).
Elastic publishes its prebuilt rules as TOML (Tom's Obvious, Minimal Language) files in the elastic/detection-rules repository. Its D1 rule, AWS CloudTrail Log Suspended, is rules/integrations/aws/defense_evasion_cloudtrail_logging_suspended.toml, a file name that still carries the pre-v19 tactic. It is a Custom query rule searching logs-aws.cloudtrail-*, the data streams you loaded in step 4. ES|QL's KQL() function runs its query, unchanged, against the lab. Triple quotes let a string contain double quotes without escaping; in 9.5.4 they must stay on one line.
FROM logs-aws.cloudtrail-*
| WHERE KQL("""data_stream.dataset: "aws.cloudtrail" and event.provider: "cloudtrail.amazonaws.com" and event.action: "StopLogging" and event.outcome: "success" """)
| KEEP @timestamp, event.action, user.name, source.ip@timestamp | event.action | user.name | source.ip ------------------------+---------------+---------------+--------------- 2026-09-15T10:09:03.000Z|StopLogging |backup-svc |203.0.113.50
Three settings in that file are worth copying. from = "now-6m" on the five-minute schedule gives each run a minute of overlap with the last, and Elastic Security does not alert twice on the same event. timestamp_override = "event.ingested" selects events by when they reached Elasticsearch, not when they happened, so a record CloudTrail delivers minutes after the call (typically about five, says AWS) still falls inside a run's window. And its ATT&CK mapping shows how mappings rot: the file still says T1562.008 under Defense Evasion, the ATT&CK v18 names, because the repository keeps v18 as its baseline for stacks up to 9.4. ATT&CK v19 split Defense Evasion into Stealth, which keeps the ID TA0005, and a new Defense Impairment tactic, TA0112, and revoked T1562.008 in favour of T1685.002; the rule package a 9.5 stack installs carries the new mapping. The file you read on GitHub and the rule your stack runs can disagree.
12. Write your own rule as code, validate it, test it both ways
Here is D3 as a rule file in the same format. Its ATT&CK IDs are the v19.2 ones, which v18 used too, so there is nothing to convert.
cat > persistence_iam_user_created_then_access_key.toml <<'EOF' [metadata] creation_date = "2026/09/27" integration = ["aws"] maturity = "development" updated_date = "2026/09/27" [rule] author = ["CSOH"] description = "A new IAM user is given an access key within 10 minutes: a way to keep access after a leaked key is revoked." false_positives = ["Jobs that create a service user and its key in one run; exclude the creating identity."] from = "now-15m" index = ["logs-aws.cloudtrail-*"] language = "eql" name = "AWS IAM User Created Then Given an Access Key" risk_score = 47 rule_id = "32586f3c-f504-4d85-aa1e-c16ccd2b0611" severity = "medium" timestamp_override = "event.ingested" type = "eql" query = ''' sequence by user.target.name with maxspan=10m [iam where event.action == "CreateUser" and event.outcome == "success"] [iam where event.action == "CreateAccessKey" and event.outcome == "success"] ''' [[rule.threat]] framework = "MITRE ATT&CK" [[rule.threat.technique]] id = "T1098" name = "Account Manipulation" reference = "https://attack.mitre.org/techniques/T1098/" [[rule.threat.technique.subtechnique]] id = "T1098.001" name = "Additional Cloud Credentials" reference = "https://attack.mitre.org/techniques/T1098/001/" [[rule.threat.technique]] id = "T1136" name = "Create Account" reference = "https://attack.mitre.org/techniques/T1136/" [[rule.threat.technique.subtechnique]] id = "T1136.003" name = "Cloud Account" reference = "https://attack.mitre.org/techniques/T1136/003/" [rule.threat.tactic] id = "TA0003" name = "Persistence" reference = "https://attack.mitre.org/tactics/TA0003/" EOF
from = "now-15m" is wider than the default because a run sees only the events inside its own window: reaching back the ten-minute span plus the five-minute schedule means every qualifying sequence falls whole inside some run. rule_id is a UUID (universally unique identifier), generated once and never reused, so the rule keeps its identity through renames. false_positives is the note an analyst reads first, and maturity = "development" says the rule is not yet trusted. Now check it with Elastic's own tooling. make deps builds a Python environment inside the clone and downloads the tool's dependencies into it; it calls python3.12 by name, so that version must be on your path. The validator prints a banner, then its verdict:
git clone --depth 1 https://github.com/elastic/detection-rules cd detection-rules make deps source env/detection-rules-build/bin/activate python -m detection_rules validate-rule ../persistence_iam_user_created_then_access_key.toml
Rule validation successful
The validator knows the integration's field list: change event.action to CloudTrail's eventName, or misspell user.target.name, and it refuses the rule with Field not recognized for iam event. It does not know your data. Change the first iam where to configuration where, the mistake from step 9, and it still prints Rule validation successful, although that rule could never fire. Put the category back; only data catches that kind of error, so go back to the lab folder and test the rule's query both ways:
cd ..
sequence by user.target.name with maxspan=10m [iam where event.action == "CreateUser" and event.outcome == "success"] [iam where event.action == "CreateAccessKey" and event.outcome == "success"]
sequences found: 1 ["backup-svc"] 2026-09-15T10:06:12.000Z CreateUser build-bot backup-svc 203.0.113.50 2026-09-15T10:06:51.000Z CreateAccessKey build-bot backup-svc 203.0.113.50
It fires on backup-svc and stays silent on svc-reporting, which only the time limit excludes. Keep a positive and a negative sample like these next to every rule, and re-run them whenever the rule, the pipeline or the integration version changes.
Hands-on exercises
Which source addresses were used by more than one identity? Answer in ES|QL by counting distinct values of aws.cloudtrail.user_identity.arn, the ARN (Amazon Resource Name) of the caller.
Show the answer
FROM logs-aws.cloudtrail-* | STATS identities = COUNT_DISTINCT(aws.cloudtrail.user_identity.arn), who = MV_SORT(VALUES(user.name)) BY source.ip | WHERE identities > 1
identities | who | source.ip ---------------+-----------------------+--------------- 2 |[backup-svc, build-bot]|203.0.113.50
Only 203.0.113.50: the leaked build-bot and the backup-svc it created. VALUES collects the distinct values in each group and MV_SORT orders them. Counting user.name instead would mislead: calls made through an assumed role have no userName in this data, and COUNT_DISTINCT skips missing values, so the two addresses those roles used would show 0 identities. Count a field every event has.
An analyst wants every event that was not an AccessDenied, writes WHERE aws.cloudtrail.error_code != "AccessDenied", and gets 3 events. There are 17. What happened, and what is the fix?
Show the answer
Comparing anything with null gives null, not true, and WHERE keeps only rows where the condition is true. The 14 successful events have no error code, so != drops them along with the 7 denials, leaving the 3 EC2 errors. Say what you mean about missing values:
FROM logs-aws.cloudtrail-* | WHERE aws.cloudtrail.error_code != "AccessDenied" OR aws.cloudtrail.error_code IS NULL | STATS events = COUNT(*)
events --------------- 17
The same trap hides in rule exclusions. AND user.name != "alice", meant to drop one administrator, also drops every event that has no user.name at all, and the rule goes blind to them without any sign.
Write D5, AdministratorAccess attached to a user, in ES|QL. A colleague's version looks for "policyArn":"arn:aws:iam::aws:policy/AdministratorAccess" inside aws.cloudtrail.request_parameters and finds nothing, although the attachment is in the data. Why?
Show the answer
FROM logs-aws.cloudtrail-*
| WHERE event.action == "AttachUserPolicy"
AND aws.cloudtrail.request_parameters LIKE "*policyArn=arn:aws:iam::aws:policy/AdministratorAccess*"
| KEEP @timestamp, user.name, user.target.name, aws.cloudtrail.request_parameters @timestamp | user.name |user.target.name| aws.cloudtrail.request_parameters
------------------------+---------------+----------------+----------------------------------------------------------------------------
2026-09-15T10:06:30.000Z|build-bot |backup-svc |{policyArn=arn:aws:iam::aws:policy/AdministratorAccess, userName=backup-svc}The colleague assumed the field holds JSON. It holds the text the integration writes, {policyArn=..., userName=...}: no quotes, and = instead of :. LIKE, where * matches any run of characters, finds it; Elastic's own D5 rule, AWS IAM AdministratorAccess Policy Attached to User, is EQL and matches the same text with stringContains. Look at one real value before you write a pattern against it. The integration also keeps a structured copy, aws.cloudtrail.flattened.request_parameters, but in 9.5.4 ES|QL cannot reach the keys inside that flattened field.
A colleague wants to catch an identity that is made an administrator and then stops CloudTrail within ten minutes, D5 followed by D1. This finds nothing. Why, and how do you fix it?
sequence by user.name with maxspan=10m [iam where event.action == "AttachUserPolicy"] [any where event.action == "StopLogging"]
sequences found: 0
Show the answer
sequence by user.name ties the events on who made each call. build-bot attached the policy to backup-svc, and backup-svc stopped the trail, so no single caller appears in both. The value that links them is the target of the first event and the caller of the second. EQL lets each pattern name its own field, with by after the brackets:
sequence with maxspan=10m [iam where event.action == "AttachUserPolicy"] by user.target.name [any where event.action == "StopLogging"] by user.name
sequences found: 1 ["backup-svc"] 2026-09-15T10:06:30.000Z AttachUserPolicy build-bot backup-svc 203.0.113.50 2026-09-15T10:09:03.000Z StopLogging backup-svc - 203.0.113.50
Two minutes and 33 seconds apart. The same shape, a change to an identity followed by that identity acting, catches privilege escalation followed by its use. For a real rule, add the AdministratorAccess test from Exercise 3 to the first pattern.
Common mistakes
- Querying CloudTrail's own field names. Elastic stores
eventNameasevent.action. ES|QL and EQL refuse a field that no searched index has mapped; a field that is mapped but never filled simply matches nothing. - Filtering EQL on a category the pipeline never assigns. The integration categorises 91 event names and
StopLoggingis not one of them, soconfiguration wheremisses it silently. - Forgetting that null fails every comparison.
!=quietly drops events where the field is missing, andCOUNT_DISTINCTignores them. UseIS NULLandIS NOT NULLto say what you mean. - Counting one denial code. EC2 reports
Client.UnauthorizedOperation, notAccessDenied. List the codes that exist before you pick a threshold. - Getting a sequence's limits wrong. Without
maxspan, events hours apart still form a sequence. Withsequence by, every pattern must share the same field; when the value moves from target to caller, give each pattern its ownby. - Asking EQL to compare two fields.
user.name != user.target.nameis rejected withComparisons against fields are not (currently) supported. ES|QL can, which is why Elastic's rule for access keys created for another user is an ES|QL rule. - Believing a clean validation. Elastic's validator catches unknown fields, not categories or values that never occur. Run every rule against data that should fire and data that should not.
Where next
- Detection engineering for the lifecycle around rules like these: testing, tuning, versioning and retiring them.
- Sigma and YARA: a Sigma processing pipeline is the same field-name contract as step 3, written for every SIEM at once.
- Splunk's SPL (Search Processing Language) and Kusto Query Language for the same five detections in other languages.
- The CloudTrail to SIEM lab for real CloudTrail to run these rules against.
- Elastic's detection-rules repository, the ES|QL reference and the EQL syntax reference. Reading Elastic's AWS rules next to the pipeline that feeds them is a quick way to learn both languages.