The KQL Grimoire

Steven Lim

A collection of the most sought-after KQL queries for Microsoft Sentinel and DefenderXDR. [Updated: 27 February 2025]

URL: The KQL Grimoire

Practice-Verified KQL Commands:

1. Basic Query to Retrieve Sign-In Logs:

[kql]
SigninLogs
| where TimeGenerated > ago(7d)
| summarize Count = count() by UserPrincipalName, ResultType
| sort by Count desc
[/kql]

2. Detect Failed Sign-Ins:

[kql]
SigninLogs
| where ResultType == “50126”
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType
[/kql]

3. Hunt for Suspicious Processes:

[kql]
DeviceProcessEvents
| where InitiatingProcessFileName =~ “powershell.exe”
| where FileName in~ (“certutil.exe”, “regsvr32.exe”)
| project TimeGenerated, DeviceName, FileName, InitiatingProcessFileName
[/kql]

4. Query for Unusual Outbound Traffic:

[kql]
DeviceNetworkEvents
| where RemoteIPType == “Public”
| where ActionType == “ConnectionSuccess”
| summarize ConnectionCount = count() by DeviceName, RemoteIP
| where ConnectionCount > 100
[/kql]

5. Identify Rare Executables:

[kql]
DeviceProcessEvents
| summarize ExecutionCount = count() by FileName
| where ExecutionCount < 10
| join kind=inner (DeviceProcessEvents) on FileName
| project TimeGenerated, DeviceName, FileName, FolderPath
[/kql]

What Undercode Say:

The KQL Grimoire is an invaluable resource for cybersecurity professionals working with Microsoft Sentinel and DefenderXDR. KQL (Kusto Query Language) is a powerful tool for querying and analyzing large datasets, particularly in the context of security operations. The provided queries are just a starting point for leveraging KQL to detect and respond to threats effectively.

For instance, the query to detect failed sign-ins (ResultType == "50126") is crucial for identifying potential brute-force attacks. Similarly, hunting for suspicious processes like `certutil.exe` or `regsvr32.exe` can help uncover malicious activity masquerading as legitimate processes.

In addition to KQL, cybersecurity professionals should also be familiar with Linux and Windows commands for incident response. For example:
– Linux:
– `grep “Failed password” /var/log/auth.log` – To find failed SSH login attempts.
– `netstat -tuln` – To list all open ports and listening services.
– `ps aux | grep suspicious_process` – To identify running processes.

• Windows:
  – `Get-EventLog -LogName Security -InstanceId 4625` – To retrieve failed login attempts.
  – `netstat -ano` – To display active connections and their process IDs.
  – `tasklist /svc` – To list all running processes and services.

For further reading on KQL and its applications in cybersecurity, refer to the official Microsoft KQL documentation.

By combining KQL with traditional command-line tools, cybersecurity teams can enhance their threat detection and response capabilities, ensuring a robust defense against evolving threats.

References:

Hackers Feeds, Undercode AI