ES|QL in the real world

ES|QL in the real world: from raw log to decision. This isn't a function catalog but the queries an engineer actually keeps on hand — SOC detection (beaconing, DNS exfiltration, impossible travel), statistical observability (MAD, z-score, SLO), and predictive capacity planning. The output you see IS the insight, built with recent functions like INLINESTATS, CATEGORIZE, and ST_DISTANCE that are nearly impossible to find documented in French.

21 featured snippets

• →Request-volume-weighted average latencyWEIGHTED_AVG weights each measurement by its real weight (call count), unlike a plain average that over-represents lightly used instances.
• →Parse a raw Apache access log with GROKOn-the-fly extraction of combined log fields (IP, method, path, status, bytes) with :int typing applied directly in the GROK pattern.
• →Password spraying: many accounts, few attemptsThe inverse signature of brute force: one IP tries many accounts with 1-3 attempts each to stay under lockout thresholds.
• →DNS exfiltration: numerous, lengthy subdomainsTwo markers of DNS tunneling combined: high cardinality of unique subdomains under a single domain and an abnormal average query length.
• →Account created then promoted to admin in under an hourMIN(CASE(...)) captures both milestones (creation, elevation) per target account, then DATE_DIFF measures the gap — a short gap is a marker of attacker persistence.
• →Robust anomaly threshold: median + 5×MADMEDIAN_ABSOLUTE_DEVIATION resists outliers, unlike standard deviation — the median + 5×MAD threshold yields reliable alert bounds per service.
• →30-day availability and remaining SLO error budgetAvailability measured on non-5xx responses, then positioned against a 99.9% SLO — the remaining budget as a percentage drives deployment freezes.
• →LOOKUP JOIN: match traffic against threat-intel IOCsJoining outbound network traffic with an index of indicators of compromise — only matched destinations keep the threat.* fields.
• →Test functions without an index using ROWROW builds an in-memory row: ideal for validating a DISSECT pattern, a conversion, or a CIDR_MATCH before applying it to millions of documents.
• →INLINESTATS: each request's deviation from its service's averageINLINESTATS adds the aggregate as a column without collapsing rows — each transaction is compared to its own service's average within the same query.
• →Outlier hosts via z-score (STD_DEV)STD_DEV measures CPU-load dispersion per host; the spike's z-score ((max − mean) / standard deviation) tells a truly anomalous spike apart from normally volatile load.
• →Group errors by pattern with CATEGORIZECATEGORIZE automatically clusters similar messages into patterns — thousands of error lines collapse into a handful of families, with no regex to write.
• →Logins far from HQ with ST_DISTANCEST_DISTANCE computes the distance in meters between a login's geolocation and a reference point — here the Paris headquarters — to surface geographically improbable accesses.
• →Top 3 CVSS scores per host with MV_SORT and MV_SLICEMV_SORT orders the multi-value field in descending order, MV_SLICE keeps its top three values, and MV_FIRST extracts the worst — all without exploding the rows.
• →Estimating days left before disk saturationTwo time-filtered aggregations (14 days ago vs. today) give the daily growth, then extrapolated into the number of days until 100% usage.
• →Successful login after a burst of failuresAggregations filtered by outcome: if the first success for the IP/account pair occurs after the last failure in a burst, the brute force likely succeeded.
• →Triage timeline for a compromised hostChronological multi-category reconstruction (auth, process, network, files) over an incident window, with a unified detail field via COALESCE to read the attack end to end.
• →C2 beaconing: regular connections at constant volumeAn implant that "beats" toward its command server produces many connections to the same destination with near-identical sizes: STD_DEV of bytes close to zero over a long period.
• →Kerberoasting: burst of RC4 TGS ticketsThe combo of event 4769 + encryption 0x17 (RC4) requested en masse against many distinct SPNs within minutes is the classic signature of kerberoasting.
• →Web shell: recurring POSTs to rare URIsA web shell gives itself away through repeated POSTs to a specific script, from very few IPs, almost always returning 200 — the exact opposite of a public form.
• →Split an LDAP Distinguished Name into organizational unitsSPLIT on the comma, then MV_SLICE extracts the main OU from a DN, aggregating accounts by organizational branch without a dedicated field in the directory.