Microsoft Sentinel KQL Reference

Overview

KQL (Kusto Query Language) is the query language for Microsoft Sentinel, Azure Monitor, and Azure Data Explorer.

Quick Start

Access KQL Interface

  • Azure Portal → Sentinel → Logs

  • Log Analytics Workspace → Logs

First Query

SecurityEvent
| where TimeGenerated > ago(1h)
| count

Basic Syntax

TableName
| where Condition
| summarize Count=count() by Field
| order by Count desc
| take 100
KQL uses pipes (|) to chain operators. Each operator transforms the data from the previous step.

Common Tables

Table Description Key Fields

SecurityEvent

Windows Security Events

EventID, Account, Computer, Activity

Syslog

Linux syslog

Facility, SeverityLevel, ProcessName

SigninLogs

Azure AD sign-ins

UserPrincipalName, ResultType, Location

AuditLogs

Azure AD audit

OperationName, Result, TargetResources

CommonSecurityLog

CEF format logs

DeviceVendor, DeviceProduct, Activity

AzureActivity

Azure subscription activity

OperationName, Caller, ResourceGroup

DeviceEvents

Defender for Endpoint

ActionType, DeviceName, InitiatingProcessFileName

OfficeActivity

Microsoft 365 activity

Operation, UserId, ClientIP

ThreatIntelligenceIndicator

Threat intelligence

ThreatType, Confidence, ExpirationDateTime

SecurityAlert

Security alerts

AlertName, Severity, Entities

Time Filters

Time Functions

Function Description Example

ago(duration)

Relative time from now

ago(1h), ago(7d), ago(30m)

now()

Current time

where TimeGenerated < now()

datetime(string)

Specific datetime

datetime(2026-02-13 09:00)

between(start .. end)

Time range

between(ago(24h) .. ago(12h))

startofday(time)

Start of day

startofday(now())

endofday(time)

End of day

endofday(now())

Duration Literals

30m   = 30 minutes
1h    = 1 hour
24h   = 24 hours
7d    = 7 days
30d   = 30 days

Operators

Filtering (where)

// Equality
| where EventID == 4625

// Not equal
| where EventID != 4624

// Contains (case-insensitive)
| where Account contains "admin"

// Contains (case-sensitive)
| where Account contains_cs "Admin"

// Starts with
| where Account startswith "svc_"

// Ends with
| where Account endswith "_admin"

// Regex match
| where Account matches regex @"^admin\d+"

// In set
| where EventID in (4624, 4625, 4648)

// Not in set
| where EventID !in (4624, 4625)

// Between (inclusive)
| where EventID between (4624 .. 4648)

// Null check
| where isnotempty(Account)
| where isempty(Account)

Aggregation (summarize)

// Count
| summarize count() by Account

// Count distinct
| summarize dcount(Account) by Computer

// Sum
| summarize TotalBytes = sum(BytesReceived)

// Average
| summarize AvgDuration = avg(Duration)

// Min/Max
| summarize MinTime = min(TimeGenerated), MaxTime = max(TimeGenerated)

// Multiple aggregations
| summarize
    Count = count(),
    UniqueUsers = dcount(Account),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated)
  by Computer

// Time binning
| summarize count() by bin(TimeGenerated, 1h)

Projection (project)

// Select specific columns
| project TimeGenerated, Account, Computer, EventID

// Rename columns
| project Time = TimeGenerated, User = Account

// Computed columns
| project
    Account,
    EventID,
    IsSuccess = EventID == 4624

// Remove columns
| project-away RawData, MG

// Reorder columns
| project-reorder TimeGenerated, Account, Computer

Sorting and Limiting

// Sort descending
| order by TimeGenerated desc

// Sort ascending
| sort by Count asc

// Take first N rows
| take 100

// Top N by value
| top 10 by Count desc

Joining Data

// Inner join
SecurityEvent
| where EventID == 4624
| join kind=inner (
    SecurityEvent
    | where EventID == 4625
  ) on Account

// Left outer join
let FailedLogins = SecurityEvent | where EventID == 4625;
SecurityEvent
| where EventID == 4624
| join kind=leftouter FailedLogins on Account

// Anti-join (rows in left not in right)
let Admins = datatable(Account:string) ["admin", "administrator"];
SecurityEvent
| join kind=leftanti Admins on Account

String Functions

// Extract with regex
| extend Domain = extract(@"([^\\]+)\\", 1, Account)

// Split string
| extend Parts = split(Account, "\\")
| extend Domain = Parts[0], User = Parts[1]

// Concatenate
| extend FullName = strcat(FirstName, " ", LastName)

// Replace
| extend CleanAccount = replace_string(Account, "@domain.com", "")

// Substring
| extend ShortName = substring(Account, 0, 10)

// Case conversion
| extend LowerAccount = tolower(Account)
| extend UpperAccount = toupper(Account)

// Trim
| extend Trimmed = trim(@"\s", Account)

Parsing

// Parse structured data
| parse EventData with * "TargetUserName\">" TargetUser "</Data>" *

// Parse with regex
| extend IP = extract(@"(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})", 1, Message)

// Parse JSON
| extend ParsedData = parse_json(ExtendedProperties)
| extend AlertType = ParsedData.alertType

// Parse URL
| extend UrlParts = parse_url(RequestUrl)
| extend Host = UrlParts.Host, Path = UrlParts.Path

Authentication Queries

Failed Sign-in Analysis

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0  // Failed
| summarize
    FailureCount = count(),
    DistinctIPs = dcount(IPAddress),
    DistinctApps = dcount(AppDisplayName)
  by UserPrincipalName, ResultDescription
| order by FailureCount desc
| take 25

Brute Force Detection

// 10+ failures in 10 minutes
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != 0
| summarize
    FailureCount = count(),
    IPs = make_set(IPAddress),
    FirstAttempt = min(TimeGenerated),
    LastAttempt = max(TimeGenerated)
  by UserPrincipalName, bin(TimeGenerated, 10m)
| where FailureCount >= 10
| order by FailureCount desc

Successful After Failed

let FailedLogins = SigninLogs
    | where TimeGenerated > ago(24h)
    | where ResultType != 0
    | summarize FailCount = count() by UserPrincipalName;
let SuccessfulLogins = SigninLogs
    | where TimeGenerated > ago(24h)
    | where ResultType == 0
    | summarize SuccessCount = count() by UserPrincipalName;
FailedLogins
| join kind=inner SuccessfulLogins on UserPrincipalName
| where FailCount >= 5
| project UserPrincipalName, FailCount, SuccessCount
| order by FailCount desc

Impossible Travel

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, Location, IPAddress
| order by UserPrincipalName, TimeGenerated asc
| serialize
| extend PrevLocation = prev(Location, 1), PrevTime = prev(TimeGenerated, 1), PrevUser = prev(UserPrincipalName, 1)
| where UserPrincipalName == PrevUser
| where Location != PrevLocation
| extend TimeDiff = datetime_diff('minute', TimeGenerated, PrevTime)
| where TimeDiff < 60  // Less than 1 hour between different locations
| project TimeGenerated, UserPrincipalName, Location, PrevLocation, TimeDiff, IPAddress

Off-Hours Authentication

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend Hour = datetime_part("hour", TimeGenerated)
| extend DayOfWeek = dayofweek(TimeGenerated)
| where Hour < 6 or Hour >= 20 or DayOfWeek in (6d, 7d)  // Off-hours or weekend
| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress, Location
| order by TimeGenerated desc

MFA Status Analysis

SigninLogs
| where TimeGenerated > ago(24h)
| summarize
    TotalSignins = count(),
    MFARequired = countif(AuthenticationRequirement == "multiFactorAuthentication"),
    SingleFactor = countif(AuthenticationRequirement == "singleFactorAuthentication")
  by UserPrincipalName
| extend MFAPercentage = round(100.0 * MFARequired / TotalSignins, 1)
| order by SingleFactor desc

Guest Account Activity

SigninLogs
| where TimeGenerated > ago(24h)
| where UserType == "Guest"
| summarize
    SigninCount = count(),
    Apps = make_set(AppDisplayName),
    IPs = make_set(IPAddress)
  by UserPrincipalName
| order by SigninCount desc

Windows Security Events

Event ID Reference

Event ID Description

4624

Successful logon

4625

Failed logon

4634

Logoff

4648

Explicit credentials logon

4672

Special privileges assigned

4688

New process created

4720

User account created

4722

User account enabled

4725

User account disabled

4726

User account deleted

4732

Member added to local group

4756

Member added to universal group

4768

Kerberos TGT requested

4769

Kerberos service ticket requested

4776

NTLM authentication

5140

Network share accessed

5145

Network share object accessed

7045

Service installed

Failed Logon Reasons

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize Count = count() by Status, SubStatus
| extend Reason = case(
    SubStatus == "0xc0000064", "User does not exist",
    SubStatus == "0xc000006a", "Wrong password",
    SubStatus == "0xc0000234", "Account locked",
    SubStatus == "0xc0000072", "Account disabled",
    SubStatus == "0xc0000193", "Account expired",
    SubStatus == "0xc0000070", "Workstation restriction",
    SubStatus == "0xc000006f", "Time restriction",
    SubStatus == "0xc0000224", "Password must change",
    "Unknown"
  )
| project Reason, Count
| order by Count desc

Process Creation Analysis

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4688
| where NewProcessName has_any ("powershell", "cmd", "wscript", "cscript", "mshta", "regsvr32")
| project
    TimeGenerated,
    Computer,
    Account,
    NewProcessName,
    CommandLine
| order by TimeGenerated desc
| take 100

Suspicious PowerShell

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4688
| where NewProcessName endswith "powershell.exe"
| where CommandLine has_any (
    "-encodedcommand", "-enc ", "-e ",
    "downloadstring", "downloadfile",
    "invoke-expression", "iex ",
    "bypass", "-noprofile", "-windowstyle hidden",
    "frombase64string"
  )
| project TimeGenerated, Computer, Account, CommandLine
| order by TimeGenerated desc

New Service Installation

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 7045
| project TimeGenerated, Computer, ServiceName = tostring(EventData.ServiceName),
          ServiceFileName = tostring(EventData.ImagePath),
          ServiceAccount = tostring(EventData.AccountName)
| order by TimeGenerated desc

Network Analysis

CommonSecurityLog Analysis

CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DeviceVendor == "Palo Alto Networks" or DeviceVendor == "Fortinet"
| where Activity == "TRAFFIC"
| summarize
    Connections = count(),
    TotalBytes = sum(SentBytes + ReceivedBytes)
  by SourceIP, DestinationIP, DestinationPort
| top 25 by TotalBytes desc

DNS Analysis

DnsEvents
| where TimeGenerated > ago(24h)
| summarize QueryCount = count() by Name, ClientIP
| where QueryCount > 100
| order by QueryCount desc
| take 50

Outbound Traffic to Rare Destinations

CommonSecurityLog
| where TimeGenerated > ago(7d)
| where Activity == "TRAFFIC" and DeviceAction == "allow"
| summarize
    TotalConnections = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated)
  by DestinationIP
| where TotalConnections < 5  // Rare destinations
| order by LastSeen desc

Threat Hunting

Known Malicious IPs

let MaliciousIPs = ThreatIntelligenceIndicator
    | where TimeGenerated > ago(30d)
    | where NetworkIP != ""
    | distinct NetworkIP;
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DestinationIP in (MaliciousIPs) or SourceIP in (MaliciousIPs)
| project TimeGenerated, SourceIP, DestinationIP, DeviceVendor, Activity

Lateral Movement Detection

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID in (4624, 4648)
| where LogonType in (3, 10)  // Network and RemoteInteractive
| summarize
    TargetCount = dcount(Computer),
    Targets = make_set(Computer)
  by Account
| where TargetCount > 5
| order by TargetCount desc

Pass-the-Hash/Pass-the-Ticket

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4624
| where LogonType == 9  // NewCredentials
| where AuthenticationPackageName == "Negotiate"
| project TimeGenerated, Computer, Account, IpAddress, LogonProcessName
| order by TimeGenerated desc

Kerberoasting Detection

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4769
| where ServiceName !endswith "$"
| where TicketEncryptionType has_any ("0x17", "0x18")  // RC4
| summarize RequestCount = count() by Account, ServiceName
| where RequestCount > 5
| order by RequestCount desc

Data Exfiltration Indicators

// Large uploads
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where Activity == "TRAFFIC"
| where SentBytes > 100000000  // 100 MB
| summarize
    TotalSent = sum(SentBytes),
    Connections = count()
  by SourceIP, DestinationIP
| extend TotalSentMB = round(TotalSent / 1048576.0, 2)
| order by TotalSent desc
| take 25

Alert Management

Recent Alerts by Severity

SecurityAlert
| where TimeGenerated > ago(24h)
| summarize Count = count() by AlertSeverity, AlertName
| order by AlertSeverity asc, Count desc

Alert Timeline

SecurityAlert
| where TimeGenerated > ago(7d)
| summarize AlertCount = count() by bin(TimeGenerated, 1h), AlertSeverity
| render timechart

Unresolved High Severity

SecurityAlert
| where TimeGenerated > ago(7d)
| where AlertSeverity == "High"
| where Status != "Resolved"
| project TimeGenerated, AlertName, Entities, Tactics
| order by TimeGenerated desc

Useful Patterns

Dynamic Thresholds

// Compare current to baseline
let Baseline = SigninLogs
    | where TimeGenerated between (ago(30d) .. ago(7d))
    | summarize BaselineCount = count() by UserPrincipalName;
let Current = SigninLogs
    | where TimeGenerated > ago(24h)
    | summarize CurrentCount = count() by UserPrincipalName;
Current
| join kind=inner Baseline on UserPrincipalName
| extend Ratio = round(CurrentCount * 1.0 / BaselineCount, 2)
| where Ratio > 3  // 3x increase
| order by Ratio desc

Time Series Analysis

SigninLogs
| where TimeGenerated > ago(7d)
| summarize Count = count() by bin(TimeGenerated, 1h)
| render timechart

Entity Extraction

SecurityAlert
| where TimeGenerated > ago(24h)
| mv-expand Entity = parse_json(Entities)
| extend EntityType = Entity.Type, EntityValue = Entity.Name
| where EntityType == "ip"
| summarize AlertCount = count() by tostring(EntityValue)
| order by AlertCount desc

Performance Tips

  1. Filter early - Use where before summarize

  2. Use has instead of contains - has is faster for whole-word matching

  3. Limit time range - Always specify TimeGenerated > ago()

  4. Use take - Limit results during exploration

  5. Project columns - Select only needed columns

  6. Avoid * in join - Specify columns explicitly

Related