Listen to this Post
Introduction
Kusto Query Language (KQL) is a powerful tool for detection engineers working in Azure environments, enabling efficient threat hunting and log analysis. Sergio Albea, a seasoned SOC professional, shares his top 10 KQL queries to sharpen detection signals and streamline investigations. These queries are battle-tested and ready for immediate deployment.
Learning Objectives
- Learn essential KQL queries for Azure Sentinel and Microsoft Defender.
- Understand how to apply these queries for threat hunting and alert tuning.
- Gain insights into optimizing detection logic for real-world SOC workflows.
1. Detecting Suspicious PowerShell Execution
Query:
SecurityEvent
| where EventID == 4688
| where CommandLine contains "powershell"
| where CommandLine has_any ("-nop", "-exec bypass", "-enc")
| project TimeGenerated, Computer, CommandLine, Account
Explanation:
This query identifies PowerShell executions with suspicious arguments (-nop, -exec bypass, -enc), often used in malicious scripts. Filtering by `EventID 4688` (process creation) helps detect obfuscated or encoded commands.
2. Identifying Failed Logon Brute Force Attacks
Query:
SecurityEvent | where EventID == 4625 | summarize FailedAttempts = count() by Account, TargetAccount, IpAddress | where FailedAttempts > 5 | sort by FailedAttempts desc
Explanation:
This query detects brute force attempts by counting failed logins (EventID 4625). A threshold of `>5` failures per account helps identify potential attacks.
3. Hunting for Anomalous Azure AD Sign-Ins
Query:
SigninLogs | where ResultType != "0" | where LocationDetails.countryOrRegion != "US" | project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName
Explanation:
Filters Azure AD sign-ins from non-US locations and failed attempts (ResultType != 0), highlighting potential unauthorized access.
4. Detecting Data Exfiltration via Large File Transfers
Query:
OfficeActivity | where Operation == "FileDownloaded" | where FileSize > 10000000 // 10MB+ | project TimeGenerated, UserId, FileName, FileSize
Explanation:
Monitors large file downloads in Office 365, a common exfiltration tactic. Adjust the `FileSize` threshold based on organizational baselines.
5. Spotting Unusual Service Principal Activity
Query:
AADServicePrincipalSignInLogs | where AppId != "00000003-0000-0000-c000-000000000000" // Exclude Microsoft apps | summarize Count = count() by AppDisplayName, IPAddress | where Count > 10
Explanation:
Identifies excessive service principal sign-ins, which may indicate credential abuse or compromised applications.
6. Tracking Scheduled Task Persistence
Query:
SecurityEvent | where EventID == 4698 | where SubjectUserName != "SYSTEM" | project TimeGenerated, TaskName, SubjectUserName
Explanation:
Detects non-SYSTEM users creating scheduled tasks (EventID 4698), a common persistence mechanism for attackers.
7. Monitoring Registry Modifications for Persistence
Query:
SecurityEvent | where EventID == 4657 | where ObjectName contains "Run\" | project TimeGenerated, ObjectName, Account
Explanation:
Flags unauthorized changes to `Run` registry keys, often used for maintaining persistence.
8. Detecting DNS Tunneling Attempts
Query:
DnsEvents | where Name contains ".exe" or Name contains ".dll" | summarize Count = count() by Name | where Count > 3
Explanation:
Identifies DNS queries for executable files, which may indicate tunneling or malware communication.
9. Uncovering Unusual Network Connections
Query:
VMConnection | where RemotePort == 4444 or RemotePort == 8080 | summarize Connections = count() by RemoteIp, RemotePort
Explanation:
Focuses on connections to common attacker ports (4444, 8080), highlighting potential C2 traffic.
10. Identifying Azure Resource Deletion Spikes
Query:
AzureActivity | where OperationName == "Microsoft.Resources/subscriptions/resourceGroups/delete" | project TimeGenerated, Caller, ResourceGroup
Explanation:
Tracks sudden resource deletions, which could indicate insider threats or compromised accounts.
What Undercode Say
- Key Takeaway 1: KQL is indispensable for modern detection engineering, enabling rapid analysis of Azure and Windows logs.
- Key Takeaway 2: These queries address common attack vectors—persistence, lateral movement, and data exfiltration.
Analysis:
Sergio’s queries exemplify the shift toward proactive threat hunting in cloud environments. As attackers evolve, detection engineers must leverage KQL’s flexibility to stay ahead. The rise of Azure-based attacks demands continuous refinement of detection logic, blending automation with human expertise. Future advancements may integrate AI-driven anomaly detection, but foundational KQL skills will remain critical.
For deeper insights, check out the full KQL guide and the Dispatch episode featuring Sergio Albea and Alex Hurtado.
IT/Security Reporter URL:
Reported By: Anvilogic Sergio – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
