A running library of deceptively simple KQL.
The KQL Library.
The queries I keep coming back to whenever a complicated problem shows up in Microsoft Sentinel, Defender XDR, or Log Analytics. Grouped by what they answer, each one shipped with the description of when to reach for it.
-
Analytics Rules
Detect Autogen Studio Agent Tool Execution Anomaly
AutoGen Studio-hosted AI agent taking code-execution or sensitive-tool actions outside its baseline set — the 'AutoJack' agent-abuse shape.
-
Analytics Rules
Detect Ci Build Egress To First Seen Domain
CI/CD build process reaching out to a domain never seen from your build fleet before — 'the build that called a stranger.'
-
Analytics Rules
Detect Diagnostic Deletion Then Tenant Activity Same Session
Enhanced log-suppression sequence detection that further requires the follow-on activity to share the same CallerIpAddress — same session, not just same identity.
-
Analytics Rules
Detect Diagnostic Deletion Then Tenant Activity Sequence
T1562.008 sequence: Azure diagnostic-setting deletion followed by any activity from the same Caller within 60 minutes.
-
Analytics Rules
Detect Dll Masquerading As Microsoft Defender
DLLs pretending to be Microsoft Defender via resource-level publisher/original-filename metadata — a Vidar Stealer TTP.
-
Analytics Rules
Detect Excel Xll Addin Spawning Shell Or Network
Excel loading an XLL add-in that then spawns a shell or beacons out — the spreadsheet-as-shell malware delivery vector.
-
Analytics Rules
Detect Nextjs Middleware Authorization Bypass
Next.js middleware authorization-bypass pattern — successful requests to authenticated routes without going through the expected auth path.
-
Analytics Rules
Detect Oauth Token Used From Novel Country
OAuth token used from a country the user has never signed in from — ToddyCat/Umbrij downstream shape where the token itself is the payload.
-
Analytics Rules
Detect Peoplesoft Process Spawning Unexpected Shell
Oracle PeopleSoft server processes (psadmin, psappsrv, java) spawning cmd, bash, whoami, curl, or net — post-exploitation shape of a PeopleSoft RCE.
-
Analytics Rules
Detect Remote Shell Command Arrival Over Wire
Shell command that arrived over the network — outbound-then-inbound-executed script pattern uncommon in legitimate remote code execution.
-
Analytics Rules
Detect Sharepoint Toolshell Rce Encoded Command
SharePoint ToolShell RCE: encoded PowerShell arriving via SharePoint worker processes. Multi-token needles (`certutil -decode`) need `contains`, not `has_any`.
-
Analytics Rules
Detect Smartconnect Session Without Signin
Microsoft SmartConnect (CVE-2026-55040) sessions lacking a corresponding sign-in event — absence-detection with windowed leftouter + countif.
-
Analytics Rules
Detect Successful Login Following Distributed Ssh Brute Force
Successful SSH login against a host that just weathered a distributed brute-force campaign. Pair with the Act-I hunt to know if the lightning hit anything.
-
Analytics Rules
Detect Telegram Tdata Session Theft File Access
Theft of Telegram's tdata session directory by an unexpected process. Uses actual DeviceFileEvents ActionTypes (FileCreated/Modified) — `FileRead` doesn't exist.
-
Analytics Rules
Detect Unsigned Dll Load Verified Signing State
Unsigned DLL loads, using IsSigned/SigningStatus rather than treating an empty SHA256 as unsigned (the field is documented as usually-populated, not always).
-
Analytics Rules
Detect Vpn Session Without Prior Authentication
PAN-OS GlobalProtect VPN sessions established without a matching auth event in the preceding 5 minutes — the shape of CVE-2026-0257. Uses windowed leftouter, not range-predicate leftanti.
-
Analytics Rules
Failed Logins
Failed Login Attempts
3+ failed sign-ins (`ResultType == 50126` — invalid username or password) for the same UPN within a 2-minute window. Written for a demo that pairs with a Logic App to auto-disable or lock the account.
-
Analytics Rules
Password Spray
Detect Low And Slow Password Spray
Low-and-slow spray pattern (Storm-0940-shaped): many unique users from the *same* IP in a day, with roughly one failed attempt per user; includes optional legacy-user-agent hints.
-
Analytics Rules
Password Spray
Detect Wide Low Volume Password Sprays
Wide, low-volume spray: from a single IP, exactly one failed attempt per user in a day, but against many different users.
-
Cost & Ingest
Billable Volume
90 Day Billable Cost Per Day Formatted
Daily billable GB and cost over 90 days, formatted as human-readable strings (`$X.XX / Day`, `XGB / Day`). Table-friendly, not chart-friendly.
-
Cost & Ingest
Billable Volume
90 Day Billable Cost Per Day
Daily billable GB and cost over 90 days with numeric `CostUSD`. The default cost-over-time view — plottable and aggregatable.
-
Cost & Ingest
Billable Volume
90 Day Billable Volume By Solution
Billable GB per day for the past 90 days, sliced by Solution, rendered as a column chart.
-
Cost & Ingest
Billable Volume
Efficiency Exercise
Teaching walkthrough of the "average daily ingest" question written four ways, from a slow `search *` to an efficient `Usage`-scoped version with cost. Read this before writing your own daily-average query.
-
Cost & Ingest
Billable Volume
GB Per Table
Ingest volume in GB for a single table (defaults to `SecurityEvent`, swap as needed).
-
Cost & Ingest
Cost By Eventid
Cost Of Eventid By Computer
Breaks the cost of a specific Event ID out by originating computer (defaults to `EventID == 4672`).
-
Cost & Ingest
Cost By Eventid
Cost Of Eventid
Estimated cost of a single Event ID over a time window, using your effective per-GB rate.
-
Cost & Ingest
Cost By Eventid
Cost Of Syslog Events By Severity
Cost of Syslog events grouped by severity level.
-
Cost & Ingest
Cost By Eventid
Eventid By Billedsize
Ingest volume in GB per Event ID from the `SecurityEvent` table.
-
Cost & Ingest
Cost By Eventid
Top 10 Eventids Windows Securityevents
Top 10 most expensive Event IDs from the `SecurityEvent` table over the last 90 days.
-
Cost & Ingest
Cost By Eventid
Top 10 Windowsevent Eventids
Top 10 most expensive Event IDs from the `WindowsEvent` table (AMA-shipped) over the last 90 days.
-
Cost & Ingest
Cost By Table
Cost Of A Table
Estimated dollar cost of a single table over a chosen window, given your effective per-GB rate.
-
Cost & Ingest
Cost By Table
Cost Of Workstations Logging Direct To Sentinel
Find workstations shipping logs directly to Sentinel and estimate what it's costing you.
-
Cost & Ingest
Cost By Table
How Loud Is A Table
Row-count-per-day graph for a given table (defaults to `Syslog`) — a quick "is this table getting louder?" check.
-
Cost & Ingest
Cost By Table
Top 10 Billable MDE Tables
Top 10 most expensive Microsoft Defender for Endpoint tables over the last 90 days.
-
Cost & Ingest
Cost By Table
Top 10 Common Security Logs By Reason With Cost Enhanced
Top `CommonSecurityLog` rows by `Reason` and `LogSeverity` (90d), ranked by event count, with an emoji cost-tier column. Filters out empty/`N/A` reasons.
-
Cost & Ingest
Cost By Table
Top 10 Common Security Logs By Severity With Cost
Top `CommonSecurityLog` groupings by `DeviceVendor`, `DeviceProduct`, and `LogSeverity` (30d), with numeric `CostUSD`.
-
Cost & Ingest
Cost By Table
Top 10 Log Sources With Cost Enhanced
Top log sources by `DataType` (30d) with an emoji cost-tier and formatted `$X.XX` string — table/dashboard friendly.
-
Cost & Ingest
Cost By Table
Top 10 Log Sources With Cost
Top log sources by `DataType` (30d) with numeric `CostUSD`. The chart-friendly default.
-
Cost & Ingest
Cost By Table
Top 10 Security Events With Cost Enhanced
Top `SecurityEvent` `EventID`s (30d) with GiB, emoji cost-tier, and formatted `$X.XX` string.
-
Cost & Ingest
Cost By Table
Top 10 Security Events With Cost
Top `SecurityEvent` `EventID`s with `Activity` (30d) and numeric `CostUSD`.
-
Cost & Ingest
Cost By Table
Top 10 Tables Exclude MDE
Top 10 most expensive log sources over 90 days, excluding MDE, via the fast `Usage` table.
-
Cost & Ingest
Ingest Trends
30 60 90 Day Common Security Log Ingest Trends
Compare `CommonSecurityLog` volume by `DeviceAction` across 30-, 60-, and 90-day periods to spot which actions are driving growth.
-
Cost & Ingest
Ingest Trends
30 60 90 Day Ingest Trends
Same three-window comparison against the `Usage` table for a workspace-wide view.
-
Cost & Ingest
Ingest Trends
Day By Day Change
Percent change in daily ingest volume vs the previous day over the last 31 days.
-
Cost & Ingest
Ingest Trends
Log Sources With Greatest Delta
Which data sources moved the most between the previous 30 days and the current 30 days — the "who suddenly got loud" query.
-
Email & Phishing
External Email Accounts Synced To Outlook Sending Attachments
Employees using Outlook on corporate machines to send email via third-party SMTP servers, with attachments — a common data-exfiltration pattern.
-
Email & Phishing
Top Blocked Malware Email Events
Top blocked malware email events. Does what it says on the tin.
-
Email & Phishing
Top Phishing Domains
Top phishing *sender domains* with severity categorized by volume.
-
Email & Phishing
Top Phishing Sender Recipient Pairs
Top phishing volume grouped by *(recipient, sender-domain)* pair — useful for spotting targeted campaigns against specific users.
-
Email & Phishing
Top Phishing Targets
Top *recipients* of phishing emails (targeted individuals) with severity by volume.
-
Email & Phishing
Whos Clicking On Junk Mail
Users who clicked links in messages that landed in the Junk folder — a strong "who needs training" signal.
-
Health Checks
Analytics Rule Health
Analytics Rules that ran successfully in the last 90 days but never produced an alert — candidates for review or tuning.
-
Health Checks
Top 10 Alerts
Top 10 alert names over 90 days with percentage of total and color-coded impact level (High / Moderate / Low).
-
Hunting
Hunt Azure Diagnostic Setting Deletions
Deletions of Azure diagnostic settings — the moment an attacker turns off logging (T1562.008). Step 1 of a two-step sequence.
-
Hunting
Hunt Blockchain Rpc C2 Dead Drop
C2 traffic hidden inside blockchain-RPC calls to public utilities (QuickNode, Alchemy) — 'the dead drop is a public utility.'
-
Hunting
Hunt C2 Beacon By Connection Window Rhythm
Low-and-slow C2 beacon hunt that counts distinct hourly time windows a process was connected in — not raw connection volume.
-
Hunting
Hunt Cav3rn Endpoint Local Log File Artifact
Endpoint-side hunt for Project CAV3RN's local file artifact (`logAzure.txt` and family) — config persistence written by the module.
-
Hunting
Hunt Cloud Metadata Ssrf Normalized Forms
Cloud instance-metadata SSRF across every string-form the attacker can write — dotted, dotless, octal, hex, IPv6, dashed hostnames, encoded slashes. Normalizes before matching.
-
Hunting
Hunt Cloud Storage Bucket Lookalike References
References to cloud storage buckets whose names are lookalikes of your real ones — homoglyphs, dashes-for-underscores, plausibly-typosquatted variants.
-
Hunting
Hunt Credential Compromise Signin Audit Alert Three Table
Three-table credential-compromise chain: joins risky sign-ins, audit follow-up, and downstream SecurityAlert on the same user.
-
Hunting
Hunt Distributed Ssh Brute Force Per Target
SSH brute-force hunt that pivots on the target host, not the source IP — catches distributed attacks that stay under per-source thresholds by using thousands of IPs.
-
Hunting
Hunt DNS Aaaa Record Covert Recovery Channel
Project CAV3RN's DNS AAAA-record recovery channel — IPv6 addresses returned in AAAA queries that decode as ASCII or structured config.
-
Hunting
Hunt Encoded Command Usage Across Fleet Time Baseline
Encoded-command executions correlated across the fleet against a rolling per-host time baseline. TimeBucket alone as the join key — deliberately no DeviceId.
-
Hunting
Hunt First Time Admin Operation User Baseline
Baselines identities that have ever executed admin operations, then alerts when an account outside that set succeeds — 'the admin who has never administered.'
-
Hunting
Hunt Inflated File Payload Evading Size Based Av Scan
File downloads anomalously large for their kind — Vidar's null-byte padding trick to slip past AV scanners that skip files above a size ceiling.
-
Hunting
Hunt Linux Process Argv0 Vs Executable Mismatch
Linux processes where argv[0] doesn't match the actual binary that was executed — a process wearing another process's name tag.
-
Hunting
Hunt Metadata IP Any Encoded Form Inspecting DNS Answer
Inspects what DNS actually resolved to (the Answer field), not what the caller wrote — catches every obfuscated string form of the metadata IPs at the resolver level.
-
Hunting
Hunt Npm Postinstall Config Modification No User Context
npm postinstall/lifecycle scripts that modified config without a corresponding interactive user command — AsyncAPI-shaped supply-chain compromise.
-
Hunting
Hunt Npm Postinstall Grandchild Network Payload
npm supply-chain worms where the payload runs two process generations down — 'sins of the grandfather' shape. Traces npm → sh -c → curl.
-
Hunting
Hunt Outlook Calendar C2 Far Future Standing Meeting
Project CAV3RN's Outlook calendar C2 — standing meetings scheduled decades in the future in fixed low-attention windows, carrying operator-agent traffic in the event body.
-
Hunting
Hunt Suspicious User Consented Oauth App Grants
'The backdoor you approved yourself' — OAuth application consents granting broad Graph permissions to unfamiliar apps. Focuses on CONSENT events, not the logins that follow.
-
Hunting
Hunt Teams Phishing Then Suspicious Login Correlation
Teams phishing messages correlated with subsequent suspicious sign-ins for the same recipient — deals with ExternalAccess not being populated by falling back to sender-domain-outside-org.
-
Hunting
Eventid Forensics
How Many Times Does This Eventid Fire From This Machine
Count of a specific Event ID from a specific machine, bucketed daily and rendered as a column chart.
-
Hunting
Eventid Forensics
Which Accounts Are Throwing This Eventid
Which accounts fire a specific Event ID and how often, per day.
-
Hunting
Eventid Forensics
Which Devices Are Throwing This Eventid
Which computers fire a specific Event ID and how often, per day.
-
Hunting
Eventid Forensics
Which Eventid Fires The Most In A Month
Noisiest Event IDs across the last month — good for spotting new noise sources.
-
Hunting
Eventid Forensics
Which Eventids Are Suddenly Acting Weird With Context
Same deviation analysis as the basic variant, joined with `Computer` and `Account` so you can see which host or user is driving the spike in one shot. Uses a 30-day recent window to reduce join noise.
-
Hunting
Eventid Forensics
Which Eventids Are Suddenly Acting Weird
Which Event IDs have recently spiked (7d) versus their 90-day baseline, sorted by deviation ratio. Basic variant — just EventID + counts.
-
Hunting
File Activity
Detect Spring Boot Heapdump Artifact On Disk
Detects Spring Boot Actuator heap-dump theft by the artifact Spring writes to disk during the request (`heapdump
-
Hunting
File Activity
File Activity Audit
Timestamped file activity (open, read, modify, delete, create) by user and device. Written for a client that needed to demonstrate this capability to an auditor.
-
Hunting
User Activity
RDP Logins Per Day Per User
RDP logins per user per day (30d) rendered as a timechart. Use for baselining "normal" login volume before hunting for anomalies.
-
Hunting
User Activity
Whats This User Doing
Unions `DeviceEvents`, `DeviceNetworkEvents`, and `DeviceFileEvents` to give a timestamped activity trace for a single user, including URLs touched. Swiss-army knife for user investigations — includes a Facebook-usage example.
-
Hunting
User Activity
Whos Logging In And When
Timestamped feed of RDP logon (4624/LogonType 10), logoff (4634), and reconnect/disconnect (4778/4779) events over 30 days.
-
Hunting
Web Tier
Hunt Spring Boot Heapdump Exfiltration Asim
Hunts Spring Boot Actuator heap-dump exfiltration in ASIM Web Session data by matching the endpoint ID as a path segment (position-independent) instead of hardcoding `/actuator/heapdump`. Recovers the real base path from traffic and ranks by response-size verdict.
-
Hunting
Web Tier
Hunt Spring Boot Heapdump Exfiltration Commonsecuritylog
Non-ASIM (`CommonSecurityLog`) variant of the heap-dump hunt. Uses correct CEF field names (`ReceivedBytes` / `SentBytes`) and carries both byte directions because CEF doesn't tell you which side is the response.
-
Identity
Who Deleted An AD User
Who deleted an AD user (`SecurityEvent` EventID `4726`) over the last 90 days.
-
Identity
Who Removed A User From Group Chat
Teams-related removal actions performed by a specific user in the last 7 days, from `OfficeActivity` / Unified Audit Log.
-
Identity
Whos Activating Roles Via PIM
PIM role activations from `AuditLogs` — useful for tracking privileged-role usage.
-
MITRE ATT&CK
Mitre Attack Tactics Observed
Events mapped against MITRE ATT&CK Tactics that have been observed in the environment, with percentage of total.
-
MITRE ATT&CK
Mitre Attack Techniques Observed
Events mapped against MITRE ATT&CK Techniques that have been observed in the environment, with percentage of total.
-
Pi-hole
Blocked Queries Over Time
Count of blocked DNS queries bucketed over time.
-
Pi-hole
DNS Query Volume
Total DNS query volume over time — the baseline "how loud is DNS" view.
-
Pi-hole
Most Queried Domains
Top 10 most-queried domains.
-
Pi-hole
New Or Rarely Seen Domains
Domains seen in the last 24h that haven't been seen recently — a classic new-domain-observed hunt.
-
Pi-hole
Pihole Usage
Billable ingest volume for the Pi-hole custom log over the last 90 days.
-
Pi-hole
Query Type Distribution
Distribution of DNS query types (A, AAAA, TXT, etc.).
-
Pi-hole
Success Vs Failure
Successful vs failed DNS query counts.
-
Pi-hole
Top Blocked Domains
Top 10 blocked domains.
-
Pi-hole
Top Clients
Top 10 clients by DNS query count.
-
Posture
Inventory Exposed Spring Boot Actuator Endpoints
Inventory of Spring Boot Actuator endpoints that answer 200 anywhere in your estate — flips the entity from "who scanned us" (infinite) to "which of my services answer" (finite and fixable). Recovers the real management base path from traffic.
-
Posture
Inventory Installed Npm Packages With Lifecycle Scripts
Inventory of npm packages installed across your fleet that carry postinstall / preinstall / install lifecycle scripts — the finite, closable population for sins-of-the-grandfather class attacks.
-
Posture
Which Devices Are Internet Facing
Identify internet-facing devices — walks through what "public" actually means for both IPv4 and IPv6 before matching.
-
Posture
Which Devices Or Software Are EOL
Devices running at least one end-of-support / end-of-life software title or version, from `DeviceTvmSoftwareInventory`.
-
Reference
Sort Function Result Comparison
Side-by-side of `sort by` vs `top` on a cost-per-EventID query, showing that both produce identical results in this case — a small worked example for anyone learning KQL sort semantics.
-
Reporting
Alert Trends
Alerts with significant increases vs the previous 90-day period, with severity categorized.
-
Reporting
Data Sources With Biggest Delta In Log Volume
Data sources with the biggest log-volume delta between comparison periods — configurable tunables at the top of the query.
-
Reporting
Report Queries
Grab-bag of reporting queries starting with MTTR against `SecurityIncident`.
No queries match that search.