Microsoft Sentinel KQL Recipes: 20+ Patterns You Can Use in SOC Operations

KQL (Kusto Query Language) is the de facto must-have skill for Microsoft Sentinel. Every Sentinel query, Hunting Query, Workbook, and Analytics Rule is written in KQL. This article organizes 20+ KQL recipes SOC analysts can put to work immediately, each with an explanation.

KQL Basic Syntax

KQL uses a SQL-like pipeline syntax (table | where ... | summarize ...) optimized for log analysis and threat hunting.

Common Operators

• project: column selection
• extend: add columns (derived)
• summarize: aggregation (count, sum, avg, dcount)
• where: filter
• join: join
• union: vertical concat of tables
• parse: string parsing
• extract: regex extraction
• mv-expand: array expansion
• bin: time bucketing (bin(TimeGenerated, 1h))
• ago: past time (ago(7d))
• now: current time

Recipe 1: Failed Sign-in Detection (Brute Force)

Detect 10+ failed sign-ins from the same user + IP in the past hour.

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != 0
| summarize FailedCount = count() by UserPrincipalName, IPAddress
| where FailedCount > 10
| order by FailedCount desc

Recipe 2: Password Spray Success Detection

Detect accounts with many failures followed by a success — the classic password spray success pattern.

SigninLogs
| where TimeGenerated > ago(1h)
| summarize FailedCount = countif(ResultType != 0),
            SuccessCount = countif(ResultType == 0)
            by UserPrincipalName, IPAddress
| where FailedCount > 5 and SuccessCount > 0

Recipe 3: Impossible Travel (Sign-ins from Distant Locations in a Short Window)

Detect sign-ins from multiple geographic locations within 24 hours.

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize CountryList = make_set(Country),
            IPList = make_set(IPAddress),
            TimeList = make_list(TimeGenerated)
            by UserPrincipalName
| where array_length(CountryList) > 1

Recipe 4: Admin Role Assignment Detection

Detect assignments of privileged roles (Global Admin, Privileged Role Admin).

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add member to role"
| extend RoleName = tostring(TargetResources[0].modifiedProperties[1].newValue)
| extend AddedUser = tostring(TargetResources[0].userPrincipalName)
| where RoleName in ("Global Administrator", "Privileged Role Administrator")
| project TimeGenerated, AddedUser, RoleName,
          InitiatedBy = tostring(InitiatedBy.user.userPrincipalName)

Recipe 5: Conditional Access Block Analysis

Aggregate sign-ins blocked by Conditional Access.

SigninLogs
| where TimeGenerated > ago(7d)
| where ConditionalAccessStatus == "failure"
| extend BlockedPolicy = tostring(ConditionalAccessPolicies[0].displayName)
| summarize BlockedCount = count()
            by UserPrincipalName, BlockedPolicy, ResultDescription
| order by BlockedCount desc

Recipe 6: Defender for Cloud High Severity Alerts

SecurityAlert
| where TimeGenerated > ago(7d)
| where AlertSeverity == "High"
| extend ResourceId = tostring(parse_json(ExtendedProperties).ResourceId)
| summarize HighAlerts = count() by ResourceId, AlertName
| order by HighAlerts desc

Recipe 7: Anomalous VM RDP / SSH Access

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4624
| where LogonType in (10, 7)  // RemoteInteractive, Unlock
| summarize LoginCount = count() by Account, IpAddress, Computer
| where LoginCount > 50
| order by LoginCount desc

Recipe 8: Anomalous Storage Account Access

StorageBlobLogs
| where TimeGenerated > ago(1d)
| where StatusCode == 403
| summarize ForbiddenCount = count()
            by CallerIpAddress, AccountName, OperationName
| where ForbiddenCount > 100
| order by ForbiddenCount desc

Recipe 9: Time-Series Trend (1-Hour Granularity)

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| summarize FailedCount = count() by bin(TimeGenerated, 1h)
| render timechart

Recipe 10: Top 10 Attack Source IPs

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0
| summarize AttackCount = count(), Users = dcount(UserPrincipalName)
            by IPAddress
| where AttackCount > 100
| order by AttackCount desc
| take 10

MITRE ATT&CK Mapping

Sentinel Analytics Rules can be registered with MITRE ATT&CK Tactics and Techniques mapped to them.

Microsoft's official GitHub (Azure-Sentinel) publishes 300+ MITRE-mapped queries you can copy and paste directly.

Performance Optimization

1. Always narrow the time range (where TimeGenerated > ago(7d)) — this alone reduces processed data by orders of magnitude
2. Place where clauses early (filtering on the left side of the pipeline makes everything downstream lighter)
3. Use project to keep only the columns you need (drop unnecessary columns early)
4. Put summarize last (filter first, then aggregate)
5. union + summarize over join — sometimes faster
6. For parse, string operations are faster than regex
7. Cap results with take / top
8. Use materialize() to physicalize and reuse subqueries

Tune using the Time Range and Records Examined metrics in the Log Analytics Query Performance pane. Production Analytics Rules should target an execution time under 5 minutes.

Learning Resources

• Kusto Query Language Documentation: Official KQL documentation
• Azure-Sentinel GitHub: 300+ Detection / Hunting Queries
• Microsoft Learn 'Write your first query with Kusto Query Language' module
• KQL Cheat Sheet (official Microsoft PDF)
• Sentinel Notebooks (Jupyter-based advanced analytics)

Related Certifications

• SC-200 (Security Operations Analyst): The flagship cert for this space; KQL is tested deeply.
• AZ-104 (Administrator): Domain 5 covers Azure Monitor + KQL fundamentals.
• SC-100 (Cybersecurity Architect Expert): SOC architecture design.
• DP-700 (Fabric Data Engineer): KQL in the Fabric Real-Time Intelligence (KQL Database) context.
• MS-102 (Microsoft 365 Administrator Expert): Domain 3 covers Defender XDR + Advanced Hunting.

Frequently Asked Questions

The technical content in this article is based on the Microsoft Sentinel Documentation and the Kusto Query Language Documentation. This article is not an official Microsoft Corporation product and has no affiliation or sponsorship. Microsoft, Azure, Microsoft Sentinel, Microsoft Defender, and Microsoft Entra are trademarks of the Microsoft group of companies. MITRE ATT&CK is a registered trademark of The MITRE Corporation. Information is based on official public materials as of May 24, 2026. Always check the official pages for the latest information.