Elastic ES|QL and EQL, hands-on

Two query languages, two different questions

A security team that keeps its logs in Elasticsearch cannot read every event, so it writes down the questions worth asking again and again. Was one address refused five or more times this morning? Was a new user created and then given an access key within ten minutes? Each question becomes a detection rule: a query that runs on a schedule and raises an alert when it finds a match. This page writes queries for those two detections, and three more, in two of Elastic's query languages, ES|QL and EQL.

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, the page for searching events, through the _query API (application programming interface, the way a program asks Elasticsearch for something), and as the ES|QL rule type. EQL matches events by category and, its reason to exist, by sequence: this event, then that one, sharing a value, within a time limit. It runs through the _eql/search API 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. Of the two questions above, the first is for ES|QL and the second is for EQL.

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.

How to use this guide

Intermediate here means you are comfortable in a terminal, have run containers with Docker, can read JSON, and know what a detection rule is for; Detection engineering covers that if it is new. You do not need to know Elasticsearch, Kibana, ES|QL or EQL, and you need no AWS account. The lab's events are CloudTrail records, and the setup section tells the story they record before any query reads them; Cloud logging explains what CloudTrail records in general.

Read it in order, because each part uses the one before. The next section sets out the one idea behind each language. Its first query runs only once step 4 has loaded the lab, so read it now and run it then. The setup has four steps: step 1 starts Elasticsearch and Kibana in Docker, step 2 saves two helper scripts that send queries, step 3 installs Elastic's AWS integration, which renames CloudTrail's fields, and step 4 loads the 24 lab events. Steps 5 to 8 teach ES|QL and steps 9 and 10 teach EQL. Each runs a query against the lab and shows what came back, and several show a draft that looks right and gives the wrong answer, a control query that shows why, and the fix. Steps 11 and 12 turn a query into a detection rule file and test it. The exercises ask you to write and debug queries yourself, with each answer hidden under Show the answer until you open it.

A code block labelled BASH is something to paste into your terminal. Blocks labelled ESQL and EQL are queries, not shell commands: run ./esql or ./eql, the helpers from step 2, paste the query, then press Return and Ctrl-D. The unlabelled block under a command or query shows what it should print. Each block has a copy button. Steps 2, 4 and 12 write files with a line such as cat > esql <<'EOF', which saves every line down to the one that says only EOF into a file called esql. Paste the whole block at once, the EOF line included.

Every command and query on this page was run as printed, on the versions it pins (Elasticsearch and Kibana 9.5.4, Elastic's AWS integration 7.1.4), and every output is pasted from that run, so your output should match the page. The installer in step 1 and the validator in step 12 print messages of their own before the lines shown. Two things were not run: loading step 12's rule into Kibana's Elastic Security app, and what start.sh does once the 30-day trial has ended, which step 1 describes from reading the script.

Some parts can wait for a second reading. The jq program inside step 2's eql helper and the 24 lines of lab data in step 4 are scaffolding, so paste them without studying them. The licence note at the end of step 1, and step 11's account of how the D1 rule's ATT&CK mapping changed between versions, can wait too.

If your output differs from the page, check where you ran the command. The helpers read .env from the folder they run in, so run them in elastic-start-local; step 12 leaves that folder for the validator and uses cd .. to return to it. The curl commands in steps 3 and 4 use the address and API key that source .env loaded into your shell in step 1. Then check that the load in step 4 replied {"errors":false}, that the count after it said 24, and that you installed the versions the page pins. A query that returns nothing where the page shows rows is what a field name your data lacks looks like, and the note at the end of step 4 shows one way that happens.

The one idea behind each language

Take one question from the lab for each language. How many of the calls from the address 203.0.113.50 were refused? That is a counting question, so it goes to ES|QL, which turns events into a table and the table into counts. Was a user created and then given an access key within ten minutes? That is a question about order, so it goes to EQL, which matches a pattern of events in time. Both languages read the field names Elastic gave the events when they arrived, which is the third idea below.

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, as long as the integration gave them that category (step 9 shows one it did not). 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. Step 10 builds one.

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: AdministratorAccess attached 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, which manages integrations, installs them in one call. You need neither Elastic Agent, the program that normally collects logs and sends them in, 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.name is copied from requestParameters.userName: the user an IAM call acted on. Step 10 ties events together on it.
• event.outcome is failure when there is an error code or message, and console logins take theirs from responseElements.ConsoleLogin, so the failed login counts as a failure although it has no errorCode.
• The MFA flag becomes a true/false field, and aws.cloudtrail.request_parameters becomes text in a {name=value, ...} form. It is not JSON, and several of Elastic's rules match pieces of it.
• event.category comes from a table of 91 event names. StopLogging is 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}
EOF

Elastic 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). The bulk API is the one that writes many documents in a single request.

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.

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 CSPM 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 CSPM 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 >= 5

    denied     |   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.

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, window

    denied     |   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

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

FROM logs-aws.cloudtrail-*
| WHERE aws.cloudtrail.error_code != "AccessDenied" OR aws.cloudtrail.error_code IS NULL
| STATS events = COUNT(*)

    events
---------------
17

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}

sequence by user.name with maxspan=10m
  [iam where event.action == "AttachUserPolicy"]
  [any where event.action == "StopLogging"]

sequences found: 0

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

Common mistakes

• Querying CloudTrail's own field names. Elastic stores eventName as event.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 StopLogging is not one of them, so configuration where misses it silently.
• Forgetting that null fails every comparison. != quietly drops events where the field is missing, and COUNT_DISTINCT ignores them. Use IS NULL and IS NOT NULL to say what you mean.
• Counting one denial code. EC2 reports Client.UnauthorizedOperation, not AccessDenied. 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. With sequence by, every pattern must share the same field; when the value moves from target to caller, give each pattern its own by.
• Asking EQL to compare two fields. user.name != user.target.name is rejected with Comparisons 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.