03. 10. 2025 Daniel Degasperi Blue Team, Log-SIEM, SEC4U

From Noisy Detections to Precision: Moving from KQL to ESQL in Elastic Security

Introduction

In modern SOC environments, detection rules are the cornerstone of identifying malicious activity. However, the effectiveness of a rule depends not only on what it looks for but also on how precisely it defines suspicious behavior. Many analysts have experienced the pain of rules that are “noisy” – generating countless false positives (FPs) that drown out true security incidents.

This article explores a real-world example: starting from a KQL-based rule that detects failed VPN logins on Fortinet devices, and then rewriting it in ES|QL (Elastic Search Query Language). The transition results in a rule that is not only more precise but also drastically reduces false positives. Moreover, this behavioral detection pattern can be adapted to other protocols such as Kerberos and NTLM for detecting password spraying or brute-force attacks.

The KQL Rule – A Noisy Starting Point

fortinet.firewall.action: ssl-login-fail 
AND NOT source.ip : (10.0.0.0/8 OR 172.16.0.0/12 OR 192.168.0.0/16 OR 127.0.0.1) 
AND source.user.name:* 
AND source.ip:*

Results aggregated by source.ip >= 1 
when unique values count of source.user.name >= 10

What it does:

Detects SSL VPN login failures.
Excludes internal IP ranges (to avoid flagging local traffic).
Requires both source.user.name and source.ip to be present.
Aggregates results per source.ip, triggering if 10+ distinct usernames fail from the same IP.

Limitations:

Binary logic – it only cares about the count of usernames, ignoring context.
No action diversity – only checks for ssl-login-fail, ignoring related events like successful logins.
High false positives – legitimate scanning or misconfigured clients can trigger alerts.

Result: the SOC could receive an alert storm – noisy, hard to triage, and often irrelevant.

The ESQL Rule – Context and Precision

from logs-fortinet_fortigate.log-*
| mv_expand event.category
| eval
    Esql.elastic_agent_domain = agent.domain,
    Esql.time_window_date_trunc = date_trunc(30 minutes, @timestamp),
    Esql.user_name_lower = to_lower(source.user.name),
    Esql.event_action_lower = to_lower(fortinet.firewall.action)
| where
    event.dataset == "fortinet_fortigate.log" and
    event.category in ("authentication", "network") and
    fortinet.firewall.subtype == "vpn" and
    Esql.event_action_lower in ("tunnel-up", "ssl-login-fail")
| stats
    Esql.user_name_lower_count_distinct = count_distinct(Esql.user_name_lower),
    Esql.user_name_lower_values = values(Esql.user_name_lower),
    Esql.event_action_lower_values = values(Esql.event_action_lower),
    Esql.event_action_lower_count_distinct = count_distinct(Esql.event_action_lower),
    Esql.source_ip_values = values(source.ip),
    Esql.source_ip_count_distinct = count_distinct(source.ip),
    Esql.source_as_organization_name_values = values(source.`as`.organization.name),
    Esql.source_geo_country_name_values = values(source.geo.country_name),
    Esql.source_geo_country_name_count_distinct = count_distinct(source.geo.country_name),
    Esql.source_as_organization_name_count_distinct = count_distinct(source.`as`.organization.name),
    Esql.timestamp_first_seen = min(@timestamp),
    Esql.timestamp_last_seen = max(@timestamp),
    Esql.event_count = count(*)
  by Esql.time_window_date_trunc, Esql.elastic_agent_domain, source.ip
| eval
    Esql.event_duration_seconds = date_diff("seconds", Esql.timestamp_first_seen, Esql.timestamp_last_seen),
    Esql.brute_force_type = case(
        Esql.user_name_lower_count_distinct >= 5 and Esql.event_action_lower_count_distinct == 2, "password_spraying",
        "other"
    )
| keep
    Esql.elastic_agent_domain,
    Esql.time_window_date_trunc,
    Esql.user_name_lower_count_distinct,
    Esql.user_name_lower_values,
    Esql.event_action_lower_count_distinct,
    Esql.event_action_lower_values,
    Esql.source_ip_values,
    Esql.source_ip_count_distinct,
    Esql.source_geo_country_name_values,
    Esql.source_geo_country_name_count_distinct,
    //Esql.source_as_organization_name_count_distinct,
    //Esql.source_as_organization_name_values,
    Esql.timestamp_first_seen,
    Esql.timestamp_last_seen,
    Esql.event_duration_seconds,
    Esql.event_count,
    Esql.brute_force_type
| where Esql.brute_force_type != "other"

What it adds compared to KQL:

Time window control: date_trunc(30 minutes, @timestamp) groups events → avoids noise from long-term benign failures.
Data normalization: converts usernames and actions to lowercase → avoids case mismatches.
Rich context: evaluates both failed (ssl-login-fail) and successful (tunnel-up) attempts.
Classification logic: using the case evaluation we can consider several scenarions with the same query and the same rule. For example detects brute-force or password spraying when multiple distinct usernames are targeted by the same IP in a short window (as reported in the query above).
Reduced false positives: benign failed logins no longer trigger alerts.
Extended visibility: can enrich with geo-IP and ASN for external threat analysis.

Result: fewer, higher-quality alerts, allowing analysts to focus on real threats.

Reuse & Portability: Applying the Same ESQL Pattern to Kerberos and NTLM

The behavioral detection pattern used in the Fortinet rule is protocol-agnostic and can be adapted to detect password spraying or brute-force attempts in Kerberos and NTLM environments.

Why it generalizes:

Multiple distinct usernames failing from the same IP in a short time.
Repeated attempts against the same host or service.
Mix of failed and occasional successful authentications.
Suspicious enrichment signals (foreign geos, unknown ASNs).

ESQL allows expressing these behaviors explicitly, so the pattern can be reused by changing only the protocol-specific fields and thresholds.

Steps to adapt:

Replace dataset (event.dataset) with Kerberos/NTLM logs (e.g., winlog / windows_security).
Map protocol-specific fields:
- source.user.name → same
- source.ip → same
- event.action → map to failed logon types (e.g., Kerberos pre-auth fail, TGS requests, NTLM 4625 failures)
Keep time-window and aggregation logic.
Adjust thresholds to environment (e.g., >=5 usernames).
Enrich with geo, ASN, target host, SPN.
Return actionable fields (user list, first/last seen, event count, geo, target host).

Just swap in your protocol-specific fields (Kerberos pre-auth failures, NTLM 4625 events, etc.) and keep the same logic.

KQL vs ESQL – Side by Side

Aspect	KQL (original)	ESQL (enhanced)
Ease of authoring	Simple filters	More verbose but expressive
Time-window logic	Statics	Native (`date_trunc`) – precise and adaptive windows
Aggregation	Limited	Rich `stats`, `count_distinct`
Normalization	Manual/awkward	Built-in (`to_lower`, `eval`)
Context enrichment	External pipeline	Inline enrichment
False positives	High	Low
Investigator value	Minimal	High (user lists, attack type, duration)

Takeaways

KQL is good for quick searches and simple detections but tends to generate noisy alerts.
ESQL enables advanced detection logic with time-bound aggregation, normalization, classification, and enrichment.
The migration from KQL to ESQL represents a shift from raw event matching to behavioral analytics
The same ESQL detection pattern can be applied to multiple protocols (Fortinet VPN, Kerberos, NTLM), making it a powerful, reusable framework for SOCs

By adopting ESQL, SOC teams can move from drowning in false positives to working with precise, high-value alerts, strengthening detection quality and operational efficiency.

Conclusion

Transitioning from KQL to ESQL is not just a matter of syntax—it’s a shift in how we approach threat detection. Where KQL stops at filtering events, ESQL allows us to model attacker behavior with context, time windows, and classification logic. The difference is tangible: fewer false positives, richer investigative context, and detection rules that can be easily adapted across multiple protocols and attack vectors.

For SOC analysts, this means moving from reactive alert handling to proactive detection engineering. Instead of spending time dismissing noise, teams can focus on what really matters: identifying and stopping adversaries before they succeed.

The Fortinet VPN example demonstrates the immediate benefits of ESQL, but the true value lies in its portability—applying the same detection logic to Kerberos or NTLM brute force attempts shows how ESQL can unify detection strategies across different parts of the infrastructure.