Writing Detection Queries

Ten thousand failed logins scroll past and your job is to find the one pattern that means someone is spraying passwords across your domain. Reading rows is hopeless. The query is the tool: you tell the SIEM to count, group, and threshold until the noise collapses into a short list of suspects you can actually investigate.

Every SIEM has its own dialect. Splunk uses SPL, Elastic uses KQL and its aggregations, Sentinel uses KQL too, and Sigma is the vendor-neutral format you write a rule in once and convert to any of them. Learn the thinking and the syntax follows.

Start with the question, not the syntax

A detection query answers a specific question about attacker behavior. "Which source IPs failed to log in against many different accounts in a short window?" is password spraying. Write that question in plain words first, then build the search around it.

In Splunk, spraying against a Windows domain looks like this:

$index=wineventlog EventCode=4625 | stats dc(TargetUserName) as users_tried count as attempts by src_ip | where users_tried > 10

The dc() function counts distinct values, which is the whole trick. A brute force is one account many times, so you would threshold on attempts. A spray is many accounts a few times each, so you threshold on distinct users. Same log, different aggregation, different attack.

Aggregate, do not eyeball

stats (Splunk) and summarize (KQL) are where detection lives. They collapse thousands of events into a table you can reason about. Add a time bucket so the count means something.

The first row is a classic spray: 47 accounts, barely more than one try each, textbook low-and-slow to dodge lockout. The second is different, 23 accounts but 140 attempts, worth a second look for a hybrid or a misconfigured service account hammering a stale credential.

The same intent in KQL and Sigma

Sentinel and Elastic run KQL. The password-spray logic reads almost the same:

$SecurityEvent | where EventID == 4625 | summarize users_tried = dcount(TargetUserName), attempts = count() by IpAddress | where users_tried > 10

Sigma is where you write the rule once so it is portable. A Sigma rule is a YAML file describing the logsource and the detection condition, and a converter turns it into SPL, KQL, or an Elastic query. That is how threat-intel teams share detections across shops that run different SIEMs.