Cyber threats are evolving at an unprecedented pace, and having a robust detection strategy is essential. In this post, I share 25 essential KQL queries that have helped me transform Microsoft Sentinel into a powerful threat detection engine. Whether you’re new to Sentinel or looking to refine your techniques, these queries—complete with context, customisation tips, and real-world insights—will help elevate your security operations.
Why These Queries Matter
Each query below is designed not only to detect specific adversary behaviours but also to integrate into a broader security strategy. By understanding the context behind these queries—such as the tactics, techniques, and procedures (TTPs) adversaries use—you can adapt them to your environment, optimise performance, and drive faster incident response.
Tip: Experiment with these queries in your lab environment and adjust timeframes or thresholds based on your network’s normal behaviour.
1. Detecting Password Spraying
Tactic: Credential Access
Why: Attackers often try a limited set of passwords across many accounts. This query helps flag brute-force attempts where a single user or IP address triggers multiple failures.
Query:
let timeframe = 1h;
SigninLogs
| where TimeGenerated > ago(timeframe)
| where ResultType == "50126" // Invalid username or password
| summarize count() by UserPrincipalName, IPAddress
| where count_ > 5
Customisation: Consider adjusting the timeframe
and threshold (currently >5 failures) to match your organisational baseline.
2. Identifying Suspicious PowerShell Execution
Tactic: Execution
Why: PowerShell is a double-edged sword—it’s essential for automation, but attackers use it to execute obfuscated commands. This query detects use of encoded commands.
Query:
SecurityEvent
| where EventID == 4688
| where NewProcessName contains "powershell.exe"
| where CommandLine contains "-enc"
Insight: Review your typical PowerShell usage to reduce false positives.
3. Flagging Anomalous Logins by Time
Tactic: Initial Access
Why: Unusual login times may suggest that an account is being accessed without proper authorisation.
Query:
let loginWindow = 1d;
SigninLogs
| where TimeGenerated > ago(loginWindow)
| summarize minTime = min(TimeGenerated), maxTime = max(TimeGenerated) by UserPrincipalName
| where minTime between (startofday(now())..12h)
| where maxTime between (12h..endofday(now()))
Diagram Suggestion: A timeline diagram can help illustrate how login times deviate from normal business hours.
4. Detecting Unusual RDP Connections
Tactic: Lateral Movement
Why: Remote Desktop Protocol (RDP) is frequently abused to move laterally within a network.
Query:
let rdpWindow = 24h;
WindowsEvent
| where EventID == 4624 and LogonType == 10 // RDP Logon
| summarize Count = count() by SourceIP, TargetUser
| where Count > 5
Customisation: Adjust the threshold based on your network’s baseline RDP usage.
5. Finding Misconfigured Cloud Resources
Tactic: Persistence
Why: Publicly accessible cloud resources can expose sensitive data. This query targets misconfigurations in storage account settings.
Query:
AzureActivity
| where ResourceProvider == "Microsoft.Storage" and OperationName == "Create or Update Storage Account"
| where Properties contains 'allowBlobPublicAccess':true
Tip: Regularly audit your cloud configuration settings to ensure compliance with best practices.
6. Identifying Clear Text Passwords in Logs
Tactic: Credential Access
Why: Logs that reveal clear text passwords pose a significant security risk.
Query:
SecurityEvent
| where EventID == 4624 and AuthenticationPackageName == "NTLM"
| where AccountName != "ANONYMOUS LOGON"
Discussion: Investigate why NTLM is in use and consider enforcing stronger authentication mechanisms.
7. Detecting Mass File Deletion
Tactic: Impact
Why: Rapid file deletion may be indicative of ransomware or a destructive attack.
Query:
let deletionWindow = 1h;
WindowsFileEvent
| where ActionType == "FileDeleted"
| summarize Count = count() by Computer, FilePath
| where Count > 100
Case Study: In one scenario, a sudden spike in file deletions prompted a rapid incident response, limiting the damage.
8. Finding Suspicious Scheduled Tasks
Tactic: Persistence
Why: Attackers often use scheduled tasks to maintain persistence after compromise.
Query:
SecurityEvent
| where EventID == 4698
| where TaskName contains "Windows\Tasks"
| where Command contains "powershell.exe"
Callout: Keep an eye on scheduled tasks that involve PowerShell, as they are a common vector for automation of malicious routines.
9. Monitoring for Suspicious DNS Queries
Tactic: Command and Control
Why: Malicious domains or dynamic DNS queries can indicate C2 (Command and Control) communications.
Query:
DnsEvents
| where QueryType == "A" or QueryType == "AAAA"
| where QueryName contains "dynamicdns"
External Resource: Cross-reference with threat intelligence feeds to update your list of suspicious domains.
10. Detecting Port Scanning
Tactic: Discovery
Why: High volumes of connection attempts to unusual ports suggest reconnaissance activity.
Query:
CommonSecurityLog
| where DestinationPort > 1024
| summarize attempts = count() by SourceIP, DestinationPort
| where attempts > 1000
Tip: Adjust the threshold based on normal network traffic patterns to reduce noise.
11. Uncovering Suspicious User Agent Strings
Tactic: Initial Access
Why: Non-standard or malicious user agents (e.g. “curl” or “wget”) can flag automated or unauthorised access attempts.
Query:
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.WEB" and Category == "AppServiceHTTPLogs"
| where HttpUserAgent contains "curl" or HttpUserAgent contains "wget"
Discussion: Use additional context, such as geolocation or time of access, to verify suspicious activity.
12. Detecting Unusual Access to Sensitive Files
Tactic: Collection
Why: Monitoring file access to folders containing sensitive information (e.g. passwords, secrets) can help pre-empt data breaches.
Query:
WindowsFileEvent
| where ActionType == "FileAccessed"
| where FilePath contains "passwords" or FilePath contains "secret"
Customisation: Integrate this query with file integrity monitoring for added context.
13. Identifying Abnormal Process Creation
Tactic: Execution
Why: Unexpected process creation, especially by non-standard applications, could indicate malware execution.
Query:
let processWindow = 1h;
SecurityEvent
| where EventID == 4688
| summarize count() by NewProcessName, SubjectUserName
| where count_ > 10
Insight: Track process creation over time to establish a baseline and detect anomalies.
14. Detecting Attempts to Disable Security Controls
Tactic: Defense Evasion
Why: Changes in security settings—such as disabling antivirus or firewall—are red flags.
Query:
SecurityEvent
| where EventID == 4728 or EventID == 4729 // User account management events
| where TargetUserName contains "service" or TargetUserName contains "admin"
Note: Review changes immediately to prevent attackers from evading detection
15. Monitoring for High Volume of Failed Logins
Tactic: Credential Access
Why: Multiple failed logins in a short span can indicate brute-force or password guessing attacks.
Query:
let loginWindow = 1h;
SigninLogs
| where TimeGenerated > ago(loginWindow)
| where ResultType != "0" // Any failure code other than success
| summarize count() by UserPrincipalName
| where count_ > 20
Customisation: Fine-tune the threshold according to the typical login failure rate in your environment.
16. Detecting Unusual Account Creation
Tactic: Persistence
Why: A surge in new account creations or role assignments may signal an attacker establishing persistence.
Query:
AzureActivity
| where OperationName == "Create User" or OperationName == "Add member to role"
| summarize count() by Caller, ResourceId
| where count_ > 5
Tip: Correlate these events with other unusual activities for a fuller picture of potential compromise.
17. Identifying Suspicious Outbound Traffic
Tactic: Command and Control
Why: Abnormal outbound connections, especially to non-standard ports, could indicate data exfiltration or botnet communications.
Query:
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "NetworkSecurityGroupEvent"
| where DestinationPort != 80 and DestinationPort != 443
| summarize count() by DestinationIP
| where count_ > 1000
Discussion: Use this query alongside threat intelligence to flag known malicious IP addresses.
18. Detecting Lateral Tool Transfer
Tactic: Lateral Movement
Why: Transfer of executable files across network shares may be part of a lateral movement strategy.
Query:
SecurityEvent
| where EventID == 5145 // A network share object was checked to see whether client can write to it
| where RelativeTargetName contains ".exe" or RelativeTargetName contains ".dll"
Insight: Identify patterns in file transfers that diverge from normal operational activity.
19. Identifying Suspicious Email Attachments
Tactic: Initial Access
Why: Malicious email attachments can be an entry point for phishing or malware attacks.
Query:
OfficeActivity
| where Operation == "MailItemsAccessed"
| where ClientInfoString contains "Outlook"
| where Subject contains "invoice" or Subject contains "payment"
Tip: Combine this query with content scanning tools for a multi-layered defence.
20. Detecting Unauthorized Access to Email
Tactic: Collection
Why: Unauthorised email access can be a precursor to data theft or espionage.
Query:
OfficeActivity
| where Operation == "MailItemsAccessed"
| where UserAgent contains "ExchangeWebServices"
| where UserId != MailboxOwnerUPN
Customisation: Adjust the query to include additional fields such as IP address or login location for deeper analysis.
21. Monitoring for Suspicious Service Creation
Tactic: Persistence
Why: Newly installed services, especially those with names similar to system processes, can indicate persistence mechanisms.
Query:
SecurityEvent
| where EventID == 7045 // A new service was installed in the system
| where ServiceName contains "svchost"
Callout: Regularly audit service creation logs to quickly identify unauthorised modifications.
22. Detecting Suspicious Registry Modifications
Tactic: Defense Evasion
Why: Attackers may modify registry keys (e.g. “Run” or “RunOnce”) to execute malicious code on startup.
Query:
SecurityEvent
| where EventID == 4657 // A registry value was modified
| where ObjectName contains "Run" or ObjectName contains "RunOnce"
Insight: Implement continuous monitoring for registry changes as part of your endpoint detection strategy.
23. Identifying Unusual Network Connections
Tactic: Command and Control
Why: Critical system alerts such as emergency-level messages in Syslog can signal abnormal network activity.
Query:
Syslog
| where Facility == "kern" and Severity == "emerg"
| where SyslogMessage contains "connection"
Discussion: Use this query alongside network monitoring tools for cross-correlation of events.
24. Detecting Attempts at Privilege Escalation
Tactic: Privilege Escalation
Why: Watch for events indicating that users or processes are attempting to gain elevated privileges.
Query:
SecurityEvent
| where EventID == 4728 // A member was added to a security-enabled global group
| where TargetUserName contains "Admin"
Customisation: Adjust the query to monitor additional high-value groups or roles.
25. Monitoring for Obfuscated Scripts
Tactic: Execution
Why: Malicious scripts often use obfuscation to hide their intent. This query targets PowerShell script block logging to detect suspicious patterns.
Query:
SecurityEvent
| where EventID == 4104 // Powershell script block logging
| where ScriptBlockText contains "Invoke-Expression"
Tip: Combine this detection with behavioural analytics to filter out benign script usage.
Conclusion
These 25 KQL queries offer a comprehensive toolkit for bolstering your Microsoft Sentinel threat detection capabilities. By understanding the underlying threat scenarios and customising each query to your environment, you can stay ahead of adversaries and improve your incident response times. Remember to continuously review and adapt these queries as your network evolves and new threats emerge.
Next Steps:
- Lab Testing: Implement these queries in a test environment and adjust thresholds based on your normal operational metrics.
- Community Engagement: Share your insights and customisations on platforms such as GitHub or security forums to contribute to the broader cybersecurity community.
- Further Reading: Explore Microsoft’s official documentation and community repositories for advanced KQL techniques and best practices.
I hope this post inspires you to harness the full potential of KQL and Microsoft Sentinel. Stay vigilant, and happy querying!