Library

KQL Library

Investigation KQL for Microsoft Sentinel & Defender — copy, run, tune to your tenant.

The twin to the PowerShell Library: the queries I actually reach for during an investigation, grouped by what you're hunting. Each one says what it finds, when to run it, and the gotcha that bites. Filter by category or search by keyword.

# Seed set — column names and result codes vary by tenant and workspace. Treat these as starting points: verify the schema and tune the thresholds before you trust an alert built on one.

All Identity & sign-ins Email & collab Endpoint Persistence Cloud & exfil Intune

Many failures, then a success (spray → hit)

Identity

Finds: Accounts hit by a burst of failed sign-ins followed by at least one success — the shape of a successful password spray or brute force.
When: Daily triage; first move after any spray alert.
Gotcha: Service accounts spray themselves with stale creds — allowlist them, or you'll chase ghosts.

SigninLogs
| where TimeGenerated > ago(24h)
| summarize Failures = countif(ResultType != 0),
            Successes = countif(ResultType == 0),
            IPs = dcount(IPAddress)
    by UserPrincipalName
| where Failures >= 10 and Successes >= 1
| sort by Failures desc

Successful legacy-auth sign-ins (MFA bypass)

Identity

Finds: Logins over legacy protocols that don't support modern auth — the classic way attackers dodge MFA.
When: After turning on a Conditional Access legacy-auth block, to hunt the stragglers still getting through.
Gotcha: Exchange ActiveSync can be legitimate on older mobile mail clients — confirm the device before you disable.

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where ClientAppUsed in ("Other clients","IMAP4","POP3","SMTP","MAPI","Exchange ActiveSync")
| summarize count() by UserPrincipalName, ClientAppUsed, AppDisplayName
| sort by count_ desc

One account, two countries, one hour

Identity

Finds: A single user with successful sign-ins from two or more countries inside a short window — a cheap impossible-travel proxy.
When: Account-compromise triage; pairs well with a token-theft investigation.
Gotcha: VPNs and cloud egress skew geo-IP. Treat as a lead, never a verdict — corroborate with device and app.

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| summarize Countries = dcount(Location), List = make_set(Location), IPs = make_set(IPAddress)
    by UserPrincipalName, bin(TimeGenerated, 1h)
| where Countries >= 2

Inbox rule that hides or forwards mail

Email & collab

Finds: New or changed inbox rules that delete, move, or forward — the single most reliable fingerprint of a BEC takeover.
When: The moment a mailbox is suspected compromised; also as a standing scheduled rule.
Gotcha: Power users build legitimate filing rules constantly — the tell is forward/redirect to an external address or delete, not the rule itself.

CloudAppEvents
| where TimeGenerated > ago(7d)
| where ActionType in ("New-InboxRule","Set-InboxRule")
| where tostring(RawEventData.Parameters) has_any
        ("DeleteMessage","ForwardTo","RedirectTo","ForwardAsAttachmentTo","MoveToFolder")
| project TimeGenerated, AccountDisplayName = tostring(RawEventData.UserId),
          ActionType, Rule = tostring(RawEventData.Parameters)

Sudden mass external send

Email & collab

Finds: A sender fanning out to an unusual number of external recipients in an hour — internal phishing from a taken-over account.
When: Right after a suspicious sign-in on a mailbox; correlate the two timelines.
Gotcha: Newsletters and app/service mailboxes trip this constantly — allowlist known senders.

EmailEvents
| where TimeGenerated > ago(24h)
| where EmailDirection == "Outbound"
| summarize Recipients = dcount(RecipientEmailAddress), Messages = count()
    by SenderFromAddress, bin(TimeGenerated, 1h)
| where Recipients >= 50
| sort by Recipients desc

Encoded / hidden PowerShell

Endpoint

Finds: PowerShell launched with an encoded command or hidden window — a staple of loaders and hands-on-keyboard attackers.
When: Endpoint-alert triage; broad hunt across a suspected host.
Gotcha: Legit management tooling uses -nop and hidden windows too — weight on -enc and pivot on the initiating process.

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where FileName in~ ("powershell.exe","pwsh.exe")
| where ProcessCommandLine has_any ("-enc","-EncodedCommand","FromBase64String","-w hidden","-windowstyle hidden")
| project TimeGenerated, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName
| sort by TimeGenerated desc

Office app spawning a shell

Endpoint

Finds: Word, Excel, PowerPoint, or Outlook launching a command interpreter or LOLBin — the signature of a malicious macro or exploit.
When: Phishing-response and endpoint triage. Very high signal, low volume.
Gotcha: Some add-ins legitimately shell out — check the command line before you isolate.

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe","excel.exe","powerpnt.exe","outlook.exe")
| where FileName in~ ("cmd.exe","powershell.exe","pwsh.exe","wscript.exe","cscript.exe","mshta.exe","rundll32.exe","regsvr32.exe")
| project TimeGenerated, DeviceName, InitiatingProcessFileName, FileName, ProcessCommandLine

New secret added to an app or service principal

Persistence

Finds: Credentials (a secret or certificate) added to an application or service principal — a favourite Entra persistence move after a token-theft.
When: Post-compromise hunting; standing rule for privileged tenants.
Gotcha: Legit app registrations rotate secrets — baseline who normally does this, and alert on the actor you don't expect.

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName in~ ("Add service principal credentials",
        "Update application - Certificates and secrets management",
        "Add app role assignment to service principal")
| project TimeGenerated, OperationName,
          Actor = tostring(InitiatedBy.user.userPrincipalName),
          Target = tostring(TargetResources[0].displayName)
| sort by TimeGenerated desc

Someone added to a privileged role

Persistence

Finds: Directory-role membership changes into anything admin-flavoured — privilege escalation, or an attacker granting themselves persistence.
When: Daily; and immediately during any identity incident.
Gotcha: PIM activations also log here — filter to permanent assignments, or you'll flag every legitimate JIT elevation.

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add member to role"
| extend Role = tostring(TargetResources[0].displayName)
| where Role has_any ("Admin","Administrator")
| project TimeGenerated, Role,
          Actor = tostring(InitiatedBy.user.userPrincipalName),
          Added = tostring(TargetResources[0].userPrincipalName)

OAuth app consent (illicit-consent phishing)

Cloud & exfil

Finds: Users (or admins) consenting to an application's permissions — the mechanism behind illicit-consent grant attacks that survive a password reset.
When: After any consent-phishing report; standing rule with an app-allowlist.
Gotcha: Separate admin consent from user consent, and baseline your known apps — the noise is legitimate SaaS onboarding.

AuditLogs
| where TimeGenerated > ago(14d)
| where OperationName in~ ("Consent to application","Add delegated permission grant","Add OAuth2PermissionGrant")
| project TimeGenerated, OperationName,
          Actor = tostring(InitiatedBy.user.userPrincipalName),
          App = tostring(TargetResources[0].displayName)
| sort by TimeGenerated desc

Bulk file downloads (staging exfil)

Cloud & exfil

Finds: A user pulling an unusual volume of files from SharePoint/OneDrive in a short window — data staging before exfil, or a leaver.
When: Insider and departing-employee reviews; post-compromise data-access checks.
Gotcha: The OneDrive sync client inflates download counts massively — exclude sync, or scope to browser downloads.

CloudAppEvents
| where TimeGenerated > ago(24h)
| where ActionType == "FileDownloaded"
| summarize Downloads = count(), Files = dcount(tostring(RawEventData.ObjectId))
    by Account = tostring(RawEventData.UserId), bin(TimeGenerated, 1h)
| where Downloads >= 200
| sort by Downloads desc

Intune config change out of hours

Intune

Finds: Create / delete / patch operations against Intune configuration outside business hours — the window an attacker would pick to weaken device policy quietly.
When: Once Intune logs are flowing to Sentinel (see the ingestion field note). Standing rule.
Gotcha: Automation and maintenance windows run at night too — baseline the service accounts before alerting.

IntuneAuditLogs
| where TimeGenerated > ago(7d)
| extend Hour = datetime_part("hour", TimeGenerated)
| where Hour >= 20 or Hour < 7
| where OperationName has_any ("Create","Delete","Patch")
| project TimeGenerated, OperationName, Identity, Result = tostring(ResultType)
| sort by TimeGenerated desc

Devices falling out of compliance

Intune

Finds: A rise in non-compliant devices by state and OS — posture drift, a broken policy, or tampering.
When: Daily posture check; spike investigation after a policy change.
Gotcha: Schema fields differ across tenants and OS — confirm the compliance columns in your workspace first.

IntuneDeviceComplianceOrg
| where TimeGenerated > ago(1d)
| where ComplianceState != "Compliant"
| summarize Devices = dcount(DeviceId) by ComplianceState, OS
| sort by Devices desc

No queries match that filter yet.