Top 5 KQL Queries Every Sentinel Admin Needs

Why KQL Mastery Matters

Kusto Query Language (KQL) is the beating heart of Microsoft Sentinel. Every alert, every hunt, every investigation runs on KQL. Yet many Sentinel deployments rely entirely on built-in rules without customisation, leaving significant detection gaps.

These five queries address real-world attack scenarios observed across CyberZonic client environments.

Query 1: Brute Force Attack Detection

This query identifies accounts that have experienced repeated failed authentication attempts followed by a successful login — a classic credential stuffing pattern.

let threshold = 10;
let timeWindow = 1h;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| summarize
    FailedAttempts = countif(ResultType != "0"),
    SuccessfulLogins = countif(ResultType == "0"),
    IPAddresses = make_set(IPAddress),
    Locations = make_set(Location)
    by UserPrincipalName, bin(TimeGenerated, timeWindow)
| where FailedAttempts >= threshold and SuccessfulLogins > 0
| project UserPrincipalName, FailedAttempts, SuccessfulLogins, IPAddresses, Locations

Why it works: Combining failure count with subsequent success catches attackers who eventually guess correctly, which a pure failure-count rule misses.

Query 2: Suspicious PowerShell Execution

Ransomware operators consistently abuse PowerShell. This query flags encoded commands and common living-off-the-land techniques.

SecurityEvent
| where EventID == 4688
| where ProcessName endswith "powershell.exe"
    or ProcessName endswith "pwsh.exe"
| where CommandLine has_any (
    "-EncodedCommand", "-enc ", "IEX", "Invoke-Expression",
    "DownloadString", "WebClient", "Net.WebClient",
    "FromBase64String", "Bypass"
)
| project TimeGenerated, Computer, Account, CommandLine, ParentProcessName
| order by TimeGenerated desc

Configuration note: Requires Windows Security Auditing with process creation events (Event ID 4688) enabled and command line logging turned on.

Query 3: Impossible Travel Detection

This query detects user logins from geographically impossible locations within a short time window — a strong indicator of account compromise.

let travelThresholdKph = 800;
SigninLogs
| where ResultType == "0"
| where isnotempty(Location)
| sort by UserPrincipalName, TimeGenerated asc
| extend
    PrevLocation = prev(Location),
    PrevTime = prev(TimeGenerated),
    PrevUser = prev(UserPrincipalName)
| where UserPrincipalName == PrevUser
| where Location != PrevLocation
| extend TimeDiffHours = datetime_diff("hour", TimeGenerated, PrevTime)
| where TimeDiffHours <= 2 and TimeDiffHours >= 0
| project UserPrincipalName, TimeGenerated, Location, PrevLocation, TimeDiffHours

Query 4: Azure AD Privilege Escalation

Attackers commonly elevate privileges by adding accounts to privileged groups. This query tracks all Global Administrator assignments.

AuditLogs
| where OperationName has "Add member to role"
| extend
    TargetUser = tostring(TargetResources[0].userPrincipalName),
    RoleAssigned = tostring(TargetResources[0].displayName),
    ModifiedBy = tostring(InitiatedBy.user.userPrincipalName)
| where RoleAssigned has_any (
    "Global Administrator", "Privileged Role Administrator",
    "Security Administrator", "Exchange Administrator"
)
| project TimeGenerated, TargetUser, RoleAssigned, ModifiedBy
| order by TimeGenerated desc

Query 5: Data Exfiltration via Large Uploads

This query identifies anomalously large outbound data transfers that may indicate exfiltration.

let threshold_mb = 100;
let threshold_bytes = threshold_mb * 1024 * 1024;
CommonSecurityLog
| where DeviceAction !has "deny"
| where tolong(SentBytes) > threshold_bytes
| summarize
    TotalSentBytes = sum(tolong(SentBytes)),
    SessionCount = count(),
    DestinationIPs = make_set(DestinationIP)
    by SourceUserName, SourceIP, bin(TimeGenerated, 1h)
| extend TotalSentMB = round(TotalSentBytes / 1048576.0, 2)
| order by TotalSentMB desc

Operationalising These Queries

Convert each query to an Analytics Rule in Sentinel with appropriate thresholds. Schedule them to run every hour and set alert severity based on your risk tolerance. Link each rule to the corresponding MITRE ATT&CK technique for coverage tracking.

Consider building an automation playbook to auto-enrich alerts with user context from Azure AD and geolocation data before routing to your ticketing system.