Kql Detections

KQL Detection Authoring Skill

Critical Rule — Schema Verification

Detection rules fail in production when column names or table names are wrong. Microsoft updates schemas regularly (e.g. SourceProvider → IdentityEnvironment, Feb 2026; ThreatIntelligenceIndicator → ThreatIntelIndicators, Aug 2025).

Before producing ANY detection rule:

1. Identify which table(s) the detection requires
2. Look up the table in references/sentinel-tables.md or references/defender-tables.md
3. If the column you need isn't documented in the reference file, fetch the official Microsoft Learn page for that table using web_fetch — never guess column names
4. Cross-reference any function call, ActionType filter, or enumerated value against the official docs

This rule is non-negotiable. A detection with wrong column names is worse than no detection — it creates false confidence.

---

Workflow — Every Detection Follows This

Step 1 — Clarify intent
  • What behaviour is being detected?
  • Which platform: Sentinel (analytic rule) or Defender XDR (custom detection / advanced hunting)?
  • What output format: bare KQL, full YAML analytic rule, or both?
  • Time window? Threshold? Known false positives to exclude?

Step 2 — Identify data sources
  • Which tables hold the required telemetry?
  • Cross-check table availability with user's data connectors (ask if unclear)

Step 3 — Verify schema
  • Open the matching reference file
  • For any column not in the reference file → web_fetch the Microsoft Learn page
  • Confirm: table name, column names, ActionType values, expected data types

Step 4 — Build the KQL
  • Apply the performance rules in `references/kql-syntax.md`
  • Use a pattern from `references/detection-patterns.md` if one exists for this scenario
  • Add entity mappings (UserPrincipalName, IPAddress, DeviceName, etc.)
  • Project only relevant columns

Step 5 — Wrap in YAML (if Sentinel scheduled rule)
  • Use the template in `references/analytic-rule-yaml.md`
  • Add MITRE tactics + techniques (verify against attack.mitre.org)
  • Set queryFrequency, queryPeriod, severity, triggerThreshold
  • Add entityMappings — these are mandatory for SOC investigation

Step 6 — Document
  • Brief detection logic explanation
  • Expected false positives + tuning recommendations
  • Recommended response actions

---

Domain → Reference File Map

| Need | Load | |---|---| | Sentinel / Log Analytics table schemas (SigninLogs, AuditLogs, OfficeActivity, AzureActivity, etc.) | references/sentinel-tables.md | | Defender XDR advanced hunting tables (DeviceEvents, IdentityLogonEvents, EmailEvents, CloudAppEvents, etc.) | references/defender-tables.md | | KQL operators, functions, performance rules, common patterns | references/kql-syntax.md | | Pre-built detection recipes (anonymous sign-in, brute force, impossible travel, mass download, etc.) | references/detection-patterns.md | | Sentinel YAML schema, MITRE mapping, entity mappings, deployment | references/analytic-rule-yaml.md |

---

Output Formats

Format A — Bare KQL Query

Used for: Defender XDR custom detections, Sentinel hunting queries, ad-hoc investigations

// [Detection Name]
// MITRE: [Tactic] / [Technique IDs]
// Description: [one-line description]
// Data sources: [TableName(s)]

[KQL query body]

Format B — Full Sentinel Analytic Rule (YAML)

Used for: Sentinel scheduled analytic rules, content hub submissions, deployable detection

id: 
name: 
description: |
  ''
severity: 
status: Available
requiredDataConnectors:
  - connectorId: 
    dataTypes:
      - 
queryFrequency: 
queryPeriod: = queryFrequency>
triggerOperator: gt
triggerThreshold: 0
tactics:
  - 
relevantTechniques:
  - 
query: |
  [KQL query]
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: 
version: 1.0.0
kind: Scheduled

Always include both formats when the user is building for Sentinel — they need the bare query for testing and the YAML for deployment.

---

Standard Detection Output Template

Every response should follow this structure:

**Detection:** [Name]
**Platform:** Sentinel scheduled rule | Defender XDR custom detection | Hunting query
**Tables used:** [list]
**MITRE:** [Tactic] / [Technique IDs with names]

**KQL Query:**
[query in code block]

**Detection logic:**
[2–3 sentences explaining what it catches and why]

**Tuning notes:**
- False positive: [scenario] → exclude via [filter]
- Threshold rationale: [why this number]

**Recommended response:**
[SOC actions: disable account, isolate device, force MFA, etc.]

**[Optional] Full YAML for Sentinel:**
[YAML in code block]

---

Performance Rules (Apply to Every Query)

These are non-negotiable for production detections:

1. TimeGenerated filter first — Sentinel and Defender are heavily optimised for time. where TimeGenerated > ago(1d) should be the first or second line.
2. Project early — | project only the columns you need, as early as possible. Reduces data volume through the pipeline.
3. Use has over contains — has uses indexed full-word search; contains is substring scan. Faster + more accurate for words 4+ chars.
4. No leading wildcards — where Url startswith "http" is fast; where Url contains "evil.com" defeats indexing.
5. Filter before summarize/join — never summarize a 10M row dataset then filter; filter to 100K rows first.
6. Specific table over search or union * — search * is catastrophic; query specific tables.
7. Use in over or — where Country in ("CN","RU","KP") is faster and clearer than chained or.
8. Avoid tostring() in where — apply type conversions after filtering, not in the predicate.
9. materialize() for reused subqueries — when the same let is referenced twice, wrap it in materialize() for caching.
10. evaluate bag_unpack() carefully — useful but column-count-changing operations break Sentinel rule validation; project to a stable schema afterwards.

Full performance reference in references/kql-syntax.md.

---

Severity Assignment Guide

| Severity | Use when | |---|---| | High | Confirmed malicious behaviour, high-confidence indicators (e.g. successful sign-in from known threat IP, ransomware activity on disk) | | Medium | Suspicious activity needing investigation (impossible travel, anomalous OAuth grant) | | Low | Notable but often benign (single failed MFA, new sign-in location) | | Informational | Context for hunting/correlation, not standalone alerts |

If the rule has known noisy false positives → start at Medium, drop to Low after tuning.

---

MITRE ATT&CK Mapping

Every detection must include at least one tactic and one technique. Common ones for identity/cloud detections:

| Tactic | Common Techniques | |---|---| | InitialAccess | T1078 (Valid Accounts), T1566 (Phishing), T1190 (Exploit Public-Facing App) | | Persistence | T1098 (Account Manipulation), T1136 (Create Account), T1556 (Modify Auth Process) | | PrivilegeEscalation | T1078.004 (Cloud Accounts), T1484 (Domain Policy Modification) | | DefenseEvasion | T1078 (Valid Accounts), T1562 (Impair Defenses), T1070 (Indicator Removal) | | CredentialAccess | T1110 (Brute Force), T1110.003 (Password Spraying), T1621 (MFA Request Generation) | | Discovery | T1087 (Account Discovery), T1069 (Permission Groups Discovery) | | LateralMovement | T1021 (Remote Services), T1550 (Use Alternate Auth Material) | | Collection | T1530 (Cloud Storage Data), T1213 (Data from Info Repositories) | | Exfiltration | T1567 (Exfiltration to Web Service), T1041 (Exfil over C2), T1030 (Data Transfer Size Limits) | | Impact | T1485 (Data Destruction), T1486 (Data Encrypted for Impact), T1490 (Inhibit System Recovery) |

Verify technique relevance — don't map T1078 to every identity detection. Match the specific behaviour.

For full and current ATT&CK matrix: https://attack.mitre.org/

---

Platform-Specific Notes

Microsoft Sentinel (Log Analytics)

• Tables ingest via data connectors — confirm the connector is enabled before the rule will work
• Scheduled rules run at queryFrequency interval; queryPeriod is the lookback window
• NRT (Near-Real-Time) rules trigger within ~1 minute — limited to 20/workspace, simpler query syntax
• Custom detections in the unified Defender portal are now the recommended path for new rules (lower ingestion cost, real-time, automatic entity mapping)
• Default schema lives in references/sentinel-tables.md
• Schema reference root: https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/tables-category

Microsoft Defender XDR (Advanced Hunting)

• Advanced hunting tables (Device, Email, Identity, Cloud, Alert*) live in the Defender XDR schema
• Tables are populated only if the relevant Defender service is deployed (MDE, MDO, MDI, MDCA)
• Custom detection rules in Defender XDR support real-time response actions (isolate device, disable user, etc.)
• Schema reference root: https://learn.microsoft.com/en-us/defender-xdr/advanced-hunting-schema-tables
• Boolean values change from numeric (1/0) to textual (True/False) on 25 February 2026 — be aware in any existing query

Unified Defender Portal (Sentinel in Defender XDR)

• Tables from both platforms are available in the same query workspace
• Cross-platform joins are now possible (e.g. SigninLogs + IdentityLogonEvents) — exploit this for richer detections
• Custom detections here automatically map entities and support response actions

---

Common Pitfalls — Avoid These

1. Wrong table for the data source — SigninLogs is Entra ID sign-ins; AADNonInteractiveUserSignInLogs is non-interactive sign-ins; AADServicePrincipalSignInLogs is service principals. Choose deliberately.
2. Case-sensitivity — KQL operators like == are case-sensitive; use =~ for case-insensitive match. UserPrincipalName == "user@example.com" will miss User@Example.com.
3. ResultType confusion — 0 = success; non-zero = failure with specific error code. Different error codes mean very different things (50053 = locked out, 50126 = wrong password, 53003 = blocked by Conditional Access).
4. Boolean handling change — until Feb 2026, Defender booleans are 1/0; after, they're True/False. Future-proof: use == true not == 1.
5. Time math — ago(1d) is 24h before now; startofday(ago(1d)) is midnight yesterday. Be intentional.
6. extend vs project — extend adds a column; project replaces the column set. project-rename and project-away are also useful.
7. parse_json() on already-dynamic columns — DeviceDetail may already be dynamic; tostring(parse_json(DeviceDetail).operatingSystem) works either way, but unnecessary double-parsing is wasteful.
8. Missing entityMappings — Sentinel won't create proper incidents without them. At minimum, map Account.FullName.

---

Quick Start Examples

"Detect anonymous sign-ins"

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where AuthenticationRequirement == "singleFactorAuthentication"
| where IsInteractive == true
| extend ClientApp = tostring(parse_json(DeviceDetail).browser)
| where AppDisplayName has_any ("Microsoft Authentication Broker", "Azure Portal")
| where Identity has "Anonymous" or UserPrincipalName has "anonymous" 
       or ResultDescription has "Anonymous"
| project TimeGenerated, UserPrincipalName, IPAddress, Location, AppDisplayName, ResultDescription

The full and correct version of this detection (which uses the Entra ID Identity Protection signal) is in references/detection-patterns.md.

Reference File Table of Contents

• references/sentinel-tables.md — All major Sentinel tables with column lists, common ResultType codes, OperationName values, and direct links to Microsoft Learn
• references/defender-tables.md — All Defender XDR advanced hunting tables with column lists, common ActionType values, and direct links
• references/kql-syntax.md — Operators, scalar functions, aggregation functions, time functions, parsing functions, joins, performance optimization
• references/detection-patterns.md — 20+ pre-validated detection recipes covering identity, endpoint, email, cloud, and network scenarios
• references/analytic-rule-yaml.md — Full YAML schema, ARM template structure, entity mapping reference, MITRE tactics enum, deployment patterns

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: rudraverma
• Source: rudraverma/Claude-KQL-Skills
• License: MIT

Install and usage instructions live in the source repository linked above.