If you have just joined a SOC team or started studying for SC-200, KQL (Kusto Query Language) is the first skill you need to develop. It powers every search, alert, and investigation in Microsoft Sentinel. The good news: KQL is one of the most readable query languages ever designed. Within a few hours of practice, you will be writing useful queries from scratch.

This guide starts from absolute zero — no prior KQL knowledge assumed. By the end you will be running real queries against Sentinel tables and understanding what they mean.

1. What Is KQL and Why Learn It?

KQL (Kusto Query Language) is a read-only query language developed by Microsoft. It is used in:

• Microsoft Sentinel — for alert queries, hunting, and investigation
• Azure Monitor / Log Analytics — for infrastructure and application monitoring
• Microsoft Defender — Advanced Hunting uses KQL directly
• Azure Data Explorer — for large-scale data analytics

Learning KQL in Sentinel means your skills transfer directly to all Microsoft security tools. It is arguably the highest-ROI skill investment for anyone working in a Microsoft-centric security environment.

2. Anatomy of a KQL Query

Every KQL query starts with a table name, then uses the pipe character (|) to chain operators together. Think of it like a conveyor belt: data flows in from the table and is processed by each operator in sequence.

// This is a complete, working KQL query:
SecurityEvent                    // 1. Start with the table
| where TimeGenerated > ago(1h)  // 2. Filter: only the last hour
| where EventID == 4625          // 3. Filter: only failed logon events
| project Account, Computer      // 4. Keep only these two columns
| take 10                        // 5. Return first 10 rows

Reading this query aloud helps: “From SecurityEvent, take events from the last hour, keep only failed logons, show me Account and Computer, and return 10 rows.”

3. Filtering Data

The where operator is how you filter rows. You can use comparison operators, logical operators, and string operators.

// Equal to
| where EventID == 4625

// Not equal to
| where ResultType != 0

// Greater than / less than
| where FailedAttempts > 5

// AND — both conditions must be true
| where EventID == 4625 and AccountType == "User"

// OR — either condition can be true
| where EventID == 4624 or EventID == 4625

// NOT
| where not(EventID == 4624)

// List membership — is value in a list?
| where EventID in (4624, 4625, 4648, 4672)

// NOT in list
| where EventID !in (4799, 4985)

4. Shaping Results

After filtering, you control which columns appear and what they are called.

// project — keep only specified columns (and rename them)
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| project
    Time       = TimeGenerated,
    User       = Account,
    Machine    = Computer,
    SourceIP   = IpAddress,
    LogonType

// extend — add a new calculated column (keeps all existing columns)
SigninLogs
| where TimeGenerated > ago(24h)
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend IsGuest = (UserType == "Guest")

// order by — sort results
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| order by TimeGenerated desc    // desc = newest first

// take — limit number of rows returned
| take 50                        // Return at most 50 rows

5. Counting and Grouping

The summarize operator aggregates data — counting, summing, averaging — and groups results by one or more columns. This is where detection becomes powerful.

// Count total events
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize TotalFailures = count()

// Count per account — how many failed logons per user?
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize FailCount = count() by Account

// Multiple grouping columns
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize FailCount = count() by Account, Computer
| where FailCount > 5            // Only show accounts with more than 5 failures
| order by FailCount desc        // Most failures first

// Count distinct values (dcount = distinct count)
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize UniqueIPs = dcount(IPAddress) by UserPrincipalName
| order by UniqueIPs desc

6. Working with Strings

// has — keyword appears in the field (fastest)
| where CommandLine has "powershell"

// contains — substring match (slower)
| where OperationName contains "delete"

// startswith — field starts with this value
| where Process startswith "cmd"

// Combining: find suspicious command lines
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4688
| where CommandLine has "powershell"
   and CommandLine has "-EncodedCommand"
| project TimeGenerated, Account, Computer, CommandLine

// tostring() — convert a value to text
| extend Country = tostring(LocationDetails.countryOrRegion)

// strcat() — combine strings into one
| extend Description = strcat(Account, " logged in from ", IpAddress)

7. Time Filters

In Sentinel, almost every query starts with a time filter. Always filter by TimeGenerated first — it is the primary partition key and enables Sentinel to scan only the relevant time range.

// Last 1 hour
| where TimeGenerated > ago(1h)

// Last 24 hours
| where TimeGenerated > ago(24h)

// Last 7 days
| where TimeGenerated > ago(7d)

// Specific date range
| where TimeGenerated between (datetime(2026-05-01) .. datetime(2026-05-09))

// Yesterday only
| where TimeGenerated >= startofday(ago(1d))
   and TimeGenerated < startofday(now())

// Understanding ago() — it means "X time before now"
// ago(1h)  = 1 hour ago
// ago(24h) = 24 hours ago (same as ago(1d))
// ago(7d)  = 7 days ago
// ago(30d) = 30 days ago

8. Your First 10 Real Sentinel Queries

Now put it all together. Run each of these in your Sentinel workspace:

// Query 1: See the most recent 20 security alerts
SecurityAlert
| where TimeGenerated > ago(24h)
| project TimeGenerated, AlertName, AlertSeverity, Entities
| order by TimeGenerated desc
| take 20

// Query 2: Count failed sign-ins by user (last 24h)
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| summarize FailedSignIns = count() by UserPrincipalName
| order by FailedSignIns desc

// Query 3: Sign-ins from outside your country
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| where Country != "India"     // Change to your expected country
| summarize Count = count() by UserPrincipalName, Country, IPAddress

// Query 4: Most common Windows Event IDs in last 24h
SecurityEvent
| where TimeGenerated > ago(24h)
| summarize Count = count() by EventID
| order by Count desc | take 20

// Query 5: Accounts with most failed logon attempts
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize Failures = count() by Account
| order by Failures desc | take 10

// Query 6: Recent Azure resource deletions
AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue has "delete"
| where ActivityStatusValue == "Success"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, Resource

// Query 7: Users with admin role assignments
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Add member to role"
| where Result == "success"
| extend Actor  = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Target = tostring(TargetResources[0].userPrincipalName)
| project TimeGenerated, Actor, Target

// Query 8: Password reset events
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName has "password"
| project TimeGenerated, OperationName, Result,
          Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

// Query 9: Devices not seen in the last 7 days
Heartbeat
| summarize LastSeen = max(TimeGenerated) by Computer
| where LastSeen < ago(7d)
| order by LastSeen asc

// Query 10: Guest user sign-ins
SigninLogs
| where TimeGenerated > ago(7d)
| where UserType == "Guest"
| where ResultType == 0
| summarize SigninCount = count() by UserPrincipalName, IPAddress,
            Country = tostring(LocationDetails.countryOrRegion)
| order by SigninCount desc