Same Detection, Two Languages: Writing My Splunk Detections in KQL

---

Everything I’ve written in this series so far has been SPL, Splunk’s search language, because that’s what I used at my SOC job. But an MDR provider doesn’t get to pick the client’s SIEM. One client runs Splunk, the next one runs Microsoft Sentinel, and a third wants you hunting in Defender for Endpoint. Sentinel, Defender’s Advanced Hunting, and Azure Data Explorer all speak the same language: KQL, the Kusto Query Language.

I’d read KQL before but never written a real detection in it. So I took the eight detections I wrote for this lab and rewrote every one in KQL. Then I ran both versions against the same attack data to check that they actually agree, not just that they look similar.

Where this fits. Third post in the Splunk series. Post 1 onboarded a Windows server, post 2 cut its license cost, this one writes the detections twice, and post 4 attacks the box to see what they catch.

Running KQL Without an Azure Subscription

I didn’t want to pay for a Sentinel workspace to learn a query language. Microsoft publishes a free local emulator of the Azure Data Explorer engine, the same Kusto engine that Sentinel runs on, as a Docker image called Kustainer:

docker network create kql-net
docker run -d --name kustainer --network kql-net -e ACCEPT_EULA=Y -m 4g \
  mcr.microsoft.com/azuredataexplorer/kustainer-linux:latest

It has no web UI, just a REST endpoint, so I wrote a 30-line Python script that sends a query and prints the result as a table.

The first thing that broke: my usual -p 8080:8080 did nothing. The host also runs an OpenStack lab, and that setup disables Docker’s default bridge network ("bridge": "none" and "iptables": false in daemon.json), so published ports never get wired up. Putting the container on its own user-defined network and talking to its IP directly worked fine.

Getting the Same Data Into Both

A fair comparison needs the exact same events on both sides. I wrote a loader that pulls the raw Windows XML events out of Splunk’s REST API and loads them into Kusto, shaped like the tables they’d land in on a real Sentinel workspace:

• SecurityEvent: the Security log, with Sentinel’s flattened columns (EventID, Account, SubjectUserName, NewProcessName, CommandLine, IpAddress, LogonType…).
• Event: everything else (Sysmon, PowerShell, System), with the event’s details left as an XML string in an EventData column. That’s how Sentinel’s Windows agent stores non-Security logs, and it’s why Sentinel users end up writing parsers.

The Parser: Same Job, Different Tool

In post 1 I wrote a Splunk transform that turns every <Data Name='X'>value</Data> into a field named X with one regex. KQL does the same job by actually parsing the XML:

.create-or-alter function with (folder = "Parsers") Sysmon() {
    Event
    | where EventLog == "Microsoft-Windows-Sysmon/Operational"
    | extend ed = parse_xml(EventData)
    | mv-apply d = ed.EventData.Data on (
        summarize bag = make_bag(bag_pack(tostring(d["@Name"]), tostring(d["#text"])))
      )
    | evaluate bag_unpack(bag)
    | project-away ed, EventData
}

Line by line: parse_xml turns the XML string into a nested object, mv-apply loops over each <Data> element, make_bag collects the name/value pairs into one dynamic object, and bag_unpack spreads that object out into real columns. After that, Sysmon | where EventID == 1 gives me Image, CommandLine, ParentImage, and the rest, just like the Splunk side.

It’s more verbose than the regex, but it’s also more correct: a regex can trip over an escaped quote inside a value, and a real XML parser can’t. Saving it as a function is the KQL equivalent of Splunk’s props/transforms. Every detection calls Sysmon and never has to think about XML again.

As a sanity check, I reran post 2’s big finding in KQL:

PowerShellEvent
| where EventID == 4104
| summarize events = count(), bytes = sum(strlen(ScriptBlockText))
    by generated = ScriptBlockText has "__cmdletization_"

generated | events | bytes
----------+--------+--------
True      | 254    | 3180354
False     | 114    | 11457

Same answer as Splunk: nearly all the PowerShell volume was PowerShell’s own generated code.

The Translation Cheat Sheet

Most of SPL maps onto KQL one-to-one once you see the pattern. SPL starts with a search and pipes it through commands. KQL starts with a table and pipes it through operators.

What you want	SPL	KQL
Pick the data	index=win_sysmon EventCode=1	Sysmon \| where EventID == 1
Filter	where count >= 10	where attempts >= 10
Add a field	eval x=...	extend x = ...
Choose columns	table a b c	project a, b, c
Aggregate	stats count dc(x) values(y) by z	summarize count(), dcount(x), make_set(y) by z
Time buckets	bin _time span=5m	bin(TimeGenerated, 5m) inside summarize ... by
Wildcard match	Image="*\\schtasks.exe"	Image endswith @"\schtasks.exe"
Substring	CommandLine="*comsvcs*"	CommandLine has "comsvcs" or contains
Regex	\| regex CommandLine="..."	\| where CommandLine matches regex @"..."
Multi-value	mvappend(...)	pack_array(...) + mv-apply

Side by Side

Here are three of the eight. The full set is in the lab repo.

D1: Encoded PowerShell (T1059.001)

Attackers Base64-encode PowerShell commands so the command line doesn’t show what they’re doing. PowerShell accepts any abbreviation of -EncodedCommand (-e, -en, -enc…), so a detection that only looks for -enc misses -e.

index=win_sysmon EventCode=1 (Image="*\\powershell.exe" OR Image="*\\pwsh.exe") CommandLine="* -e*"
| regex CommandLine="(?i)\s-e(n|nc|nco|ncod|ncode|ncoded|ncodedc|ncodedco|ncodedcom|ncodedcomm|ncodedcomma|ncodedcomman|ncodedcommand)?\s+[A-Za-z0-9+/=]{20,}"
| table _time Computer User ParentImage CommandLine

Sysmon
| where EventID == 1
| where Image has_any (@"\powershell.exe", @"\pwsh.exe")
| where CommandLine matches regex @"(?i)\s-e(n|nc|nco|ncod|ncode|ncoded|ncodedc|ncodedco|ncodedcom|ncodedcomm|ncodedcomma|ncodedcomman|ncodedcommand)?\s+[A-Za-z0-9+/=]{20,}"
| project TimeGenerated, Computer, User, ParentImage, CommandLine

The @"..." in KQL is a verbatim string: backslashes are literal. In SPL I have to write \\ inside a quoted search term to mean one backslash. Copy a path from one language to the other without adjusting and it silently matches nothing.

D7: Discovery Burst (T1087, T1082, T1016, T1033)

One whoami means nothing. Four different recon tools launched by the same parent process within five minutes looks like someone getting their bearings after landing on a box.

index=win_sysmon EventCode=1 (Image="*\\whoami.exe" OR Image="*\\net.exe" OR ... OR Image="*\\hostname.exe")
| bin _time span=5m
| stats dc(Image) as distinct_tools values(Image) as tools
    by _time Computer ParentProcessGuid ParentImage User
| where distinct_tools >= 4

let recon = dynamic(["whoami.exe","net.exe","net1.exe","ipconfig.exe","systeminfo.exe","nltest.exe",
                     "quser.exe","arp.exe","netstat.exe","tasklist.exe","hostname.exe"]);
Sysmon
| where EventID == 1
| extend exe = tolower(tostring(split(Image, @"\")[-1]))
| where exe in (recon)
| summarize distinct_tools = dcount(exe), tools = make_set(exe)
    by bin(TimeGenerated, 5m), Computer, ParentProcessGuid, ParentImage, User
| where distinct_tools >= 4

I think the KQL version reads better. A let statement holds the tool list in one place instead of eleven OR clauses, and pulling the file name out with split(...)[-1] makes the comparison exact instead of a wildcard. The tolower matters too: KQL’s in is case-sensitive, so "WHOAMI.EXE" in ("whoami.exe") is false.

D8: Password Guessing (T1110.001)

index=win_security EventCode=4625
| bin _time span=10m
| stats count values(TargetUserName) as users by _time Computer IpAddress LogonType
| where count >= 10

SecurityEvent
| where EventID == 4625
| summarize attempts = count(), users = make_set(TargetUserName)
    by bin(TimeGenerated, 10m), Computer, IpAddress, LogonType
| where attempts >= 10

Nearly identical. The Security log is where the two languages feel most alike, because Sentinel already flattens those fields into columns.

Where the Two Languages Disagree

Translating wasn’t the hard part. Getting the same answer was. These are the differences that actually changed results:

1. Case sensitivity. SPL’s field=value is case-insensitive. KQL’s == and in are case-sensitive; =~ and in~ are the case-insensitive versions. (has, contains, and endswith are case-insensitive by default, which is why most of my detections use them.) I checked instead of trusting my memory:

    print eq = @"C:\Windows\System32\cmd.exe" == @"C:\windows\system32\cmd.exe",
          eq_tilde = @"C:\Windows\System32\cmd.exe" =~ @"C:\windows\system32\cmd.exe"
    // eq = False, eq_tilde = True
   

   Windows paths come in every casing (C:\Windows\System32 vs C:\Windows\system32), so a KQL == on a path is a detection that quietly misses things.

2. has is not contains. KQL’s has matches whole terms using the index, so it’s fast. contains matches any substring and is slow. CommandLine has "comsvcs" works because comsvcs is its own term in comsvcs.dll. has "comsvc" does not. I tested all three: has "comsvcs" is true, has "comsvc" is false, contains "comsvc" is true. SPL wildcards (*comsvc*) behave like contains.
3. Escaping. Covered above. Verbatim @"..." strings in KQL are the fix.
4. Time field names. _time vs TimeGenerated. Trivial, but it’s in every query.
5. Nulls vs. missing fields. In Splunk, mvappend(if(..., "x", null())) just skips the nulls. KQL’s pack_array keeps every element, so the D2 translation needs an extra mv-apply ... where isnotempty(i) step to throw away the indicators that didn’t match.

Did They Actually Agree?

I ran all eight detections in both languages against the same events from post 4’s attack run:

Detection	SPL hits	KQL hits	Match
D1 Encoded PowerShell	17	17	✓
D2 Suspicious script block	34	34	✓ (after fix)
D3 Defender tampering	6	6	✓ (after fix)
D4 Local account created	1	1	✓
D5 Scheduled task	2	2	✓
D6 LSASS dump	1	1	✓
D7 Discovery burst	2	2	✓
D8 Password guessing	1	1	✓

Two of them didn’t match on my first try, and both failures were the exact has-vs-contains trap from the section above. D2 came back 30 in KQL against 34 in SPL, because has "Reflection.Assembly" skipped events where the script text was Reflection.AssemblyName (the term is AssemblyName, so the term Assembly isn’t present). D3 came back 5 against 6, because has "Disable" never matched the term DisableRealtimeMonitoring. Switching those clauses to contains, which is a true substring match like Splunk’s *...*, brought both to exact parity. I only caught them because I was comparing counts; each would have looked perfectly healthy on its own.

Honest Caveats

• The emulator isn’t Sentinel. It’s the same query engine, but there’s no Sentinel scheduling, no incidents, and no built-in parsers. I built tables shaped like Sentinel’s; a real workspace using the newer Azure Monitor Agent might put Sysmon somewhere else, and a client with Defender for Endpoint would have DeviceProcessEvents instead, with different column names (FileName, ProcessCommandLine, InitiatingProcessFileName).
• These are lab detections. Eight rules, tested on one server. The point was learning the language by forcing myself to get identical answers, not building a production rule set.

What I Took Away

KQL ended up feeling like SPL with stricter types. The biggest change in habit is starting from a table and thinking about which columns exist, instead of starting from a keyword search. The biggest risk when translating is case sensitivity, because it fails silently: the query runs, returns nothing, and looks exactly like “no attack happened.”