# Kql Detections

> >

- **Type:** Skill
- **Install:** `agentstack add skill-rudraverma-claude-kql-skills-claude-kql-skills`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [rudraverma](https://agentstack.voostack.com/s/rudraverma)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [rudraverma](https://github.com/rudraverma)
- **Source:** https://github.com/rudraverma/Claude-KQL-Skills

## Install

```sh
agentstack add skill-rudraverma-claude-kql-skills-claude-kql-skills
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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

```kql
// [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

```yaml
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"

```kql
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](https://github.com/rudraverma)
- **Source:** [rudraverma/Claude-KQL-Skills](https://github.com/rudraverma/Claude-KQL-Skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-rudraverma-claude-kql-skills-claude-kql-skills
- Seller: https://agentstack.voostack.com/s/rudraverma
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.