Microsoft Sentinel and Defender XDRvsSplunk
KQL vs SPL
KQL (Microsoft Sentinel and Defender) and SPL (Splunk) are both pipe-based languages for searching security logs. They work the same way. You start with the data, then filter, shape and count it one step at a time. The differences are mostly the names of things, the case rules and where the time range is set.
Side by side
| What | KQL | SPL |
|---|---|---|
| Used in | Microsoft Sentinel and Defender XDR | Splunk Enterprise, Splunk Cloud and Enterprise Security |
| A query starts with | A table, like SecurityEvent | A search, like index=wineventlog |
| Filter rows | where EventID == 4625 | EventCode=4625 in the search, or where |
| Pick columns | project | table or fields |
| Count and group | summarize count() by Account | stats count by user |
| Sort | sort by Failures, descending by default | sort -Failures, the minus means descending |
| Keep the top rows | top 10 by Failures | sort -Failures then head 10 |
| Add a column | extend | eval |
| Pull text out with regex | extract() or parse | rex |
| Time range | The time picker, or in the query with ago() | The time picker, or earliest= in the search |
| Matching text | == is case-sensitive, =~ isn’t | Search terms ignore case, where and eval don’t |
| Field names | Case-sensitive | Case-sensitive |
Same question, both languages
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize Failures = count() by TargetAccount
| top 10 by Failures
index=wineventlog EventCode=4625 earliest=-1d
| stats count AS Failures BY user
| sort -Failures
| head 10
DeviceProcessEvents
| where Timestamp > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "outlook.exe")
| where FileName =~ "powershell.exe"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
index=sysmon EventCode=1 earliest=-7d Image="*\\powershell.exe"
(ParentImage="*\\winword.exe" OR ParentImage="*\\excel.exe" OR ParentImage="*\\outlook.exe")
| table _time host User CommandLine
Index and field names in Splunk depend on how your data is set up. These use the common Windows and Sysmon add-ons.
What catches people out when switching
sortin KQL is descending unless you sayasc. In SPLsort Failuresis ascending, and you add a minus for descending.summarize count()names the columncount_in KQL. In SPLstats countnames itcount.- SPL’s
sortstops at 10,000 rows unless you writesort 0 -Failures. - Searching for
powershellin SPL matches any case. In KQL==doesn’t, so use=~orhas. - Field names are case-sensitive in both, so
eventcodewon’t findEventCode. - In both, the time picker sets the range unless the query sets its own. Check which one is in charge before trusting an empty result.
Keep going
Practise both on the same exercise
SOC Fluent launches with KQL, and SPL is coming soon. Every exercise works in each language, so you can solve the same exercise in both and compare.
Free to join. Students get 50% off at launch.