Kql Query Authoring

KQL Query Authoring - Instructions

Purpose

Generate validated, production-ready KQL queries by combining schema validation (331+ indexed tables), Microsoft Learn documentation, community examples, and performance best practices.

Prerequisites

Required MCP Servers:

1. KQL Search MCP Server — Schema validation, query examples, table discovery

   • Install: npm install -g kql-search-mcp (npm)

2. Microsoft Docs MCP Server — Official Microsoft Learn documentation and code samples

   • GitHub: MicrosoftDocs/mcp

Verification: Tools should be available as mcp_kql-search_* and mcp_microsoft-lea_*.

⚠️ Known Issues

search_favorite_repos Bug (v1.0.5)

❌ Broken — ERROR_TYPE_QUERY_PARSING_FATAL. Use mcp_kql-search_search_github_examples_fallback instead.

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

1. Validate table schema FIRST — mcp_kql-search_get_table_schema to verify table exists, column names, and data types.

2. Check platform schema — Sentinel uses TimeGenerated; Defender XDR uses Timestamp. Microsoft Learn examples default to XDR syntax — always convert before testing on Sentinel.

3. Check local query library FIRST — Use the discovery manifest (.github/manifests/discovery-manifest.yaml) for domain/MITRE lookups and grep_search for table-name/keyword lookups. See the KQL Pre-Flight Checklist in copilot-instructions.md for the full priority order.

4. Query file structure: NO placeholder TOC — When creating a new query file, do NOT add a ## Quick Reference — Query Index heading or placeholder. scripts/generate_tocs.py creates the heading and table itself. Pre-creating it confuses the strip-and-reinsert logic and produces duplicated content. See ## Creating Query Files below for full file structure rules.

5. Use multiple sources — Schema (authoritative column names) + Microsoft Learn (official patterns) + community queries (real-world examples).

6. Test using the correct execution tool — Follow the Tool Selection Rule in copilot-instructions.md:

   • Sentinel-native tables → Data Lake or AH
   • XDR tables ≤ 30d → Advanced Hunting (free); > 30d → Data Lake
   • XDR-only tables (DeviceTvm*, Exposure*) → Advanced Hunting only
   • Adapt timestamp column when switching tools

7. Test queries before presenting to user — Run with | take 5 via live execution. Use mcp_kql-search_validate_kql_query as fallback if live testing unavailable.

8. Provide context — Explain what the query does, expected results, and any limitations.

9. Read the complete workflow below before starting.

📋 Inherited rules: This skill inherits the KQL Pre-Flight Checklist, Tool Selection Rule (Data Lake vs Advanced Hunting), and Known Table Pitfalls from copilot-instructions.md. Those rules are authoritative — do not contradict them here.

Query Authoring Workflow

Step 1: Understand User Requirements

Extract key information:

• Table(s) needed: Which data source? (e.g., EntraIdSignInEvents, EmailEvents, SecurityAlert)
• Time range: How far back? (e.g., last 7 days, specific date range)
• Filters: What specific conditions? (e.g., user, IP, threat type)
• Output: Statistics, detailed records, time series, aggregations?
• Platform: Sentinel or Defender XDR? (affects column names)
• Deployment target: Custom detection rule? (see below)

Custom Detection Intent Detection:

If the user mentions "custom detection", "detection rule", "deploy as detection", "CD rule", "author detections for", or "deploy to Defender":

1. Read the detection-authoring skill (.github/skills/detection-authoring/SKILL.md) — Critical Rules and CD Metadata Contract sections
2. Design queries with CD constraints — row-level output, mandatory columns (TimeGenerated, DeviceName, ReportId), no bare summarize
3. Include cd-metadata blocks in the output file (see Step 8)
4. Still write queries in Sentinel format (with let variables, 7d lookback) — adaptation to CD format happens at deployment time via the detection-authoring skill

Step 2: Check Local Query Library

Search for existing verified queries before writing from scratch. Use two complementary methods:

1. Manifest lookup (domain/MITRE): Read .github/manifests/discovery-manifest.yaml and match by domain tag (e.g., identity, endpoint, email) or MITRE technique ID (e.g., T1078, T1566). Best when you know the security domain or ATT&CK technique.
2. Targeted grep_search (table/keyword): grep_search for the specific table name (e.g., CloudAppEvents, OfficeActivity) or operation keyword (e.g., New-InboxRule, SecretGet) scoped to queries/** and .github/skills/**. The manifest lacks table-name and keyword fields — grep fills this gap.
3. Check the Ad-Hoc Query Examples appendix in copilot-instructions.md

When to use which: Domain/technique known → manifest first. Table name/operation known → grep first. Both can be used together — manifest for breadth, grep for precision.

If a suitable query is found, adapt it and skip to Step 6. These queries encode known pitfalls and schema quirks.

Step 3: Get Table Schema (MANDATORY)

mcp_kql-search_get_table_schema("<table_name>")

Returns: category, description, all columns with data types, and example queries. Use this to verify column names and understand data types.

Step 4: Get Official Code Samples

mcp_microsoft-lea_microsoft_code_sample_search(
  query: "<table_name> <scenario description>",
  language: "kusto"
)

Include table name + scenario in the query (e.g., "EmailEvents phishing detection").

Step 5: Get Community Examples

mcp_kql-search_search_github_examples_fallback(
  table_name: "<table_name>",
  description: "<goal description>"
)

Also available: mcp_kql-search_search_kql_repositories to find KQL-focused repos.

Step 6: Generate Query

Combine insights: schema for column names, Learn for patterns, community for techniques.

Standalone queries rule: When generating MULTIPLE separate queries, each must start directly with the table name — never use shared let variables across separate queries (they run independently). Use let variables only within a single complex query.

Step 7: Validate and Test (MANDATORY)

Test queries against live data before presenting to the user.

1. Convert Timestamp → TimeGenerated if adapting MS Learn examples for Sentinel
2. Test via mcp_sentinel-data_query_lake or RunAdvancedHuntingQuery with | take 5
3. Verify results are sensible — check for empty results (wrong table/time/filters)
4. Fix schema mismatches or syntax errors, re-test
5. Remove test limits, present to user

Common errors:

Fallback validation: mcp_kql-search_validate_kql_query("<query>") — syntax/schema check only, no live data.

Step 8: Format and Deliver Output

Single query: Provide directly in chat with brief explanation and expected results.

Multiple queries (3+): Create a markdown file in queries/<subfolder>/ with the standardized metadata header. This header is mandatory — build_manifest.py parses it to index the file for discovery by threat-pulse and other skills.

File naming: queries/<subfolder>/<topic>.md — e.g., queries/email/email_threat_detection.md

Required metadata header template (first 10 lines of every query file):

# <Descriptive Title>

**Created:** YYYY-MM-DD  
**Platform:** Microsoft Sentinel | Microsoft Defender XDR | Both  
**Tables:** <comma-separated exact KQL table names>  
**Keywords:** <comma-separated searchable terms — attack techniques, scenarios, field names>  
**MITRE:** <comma-separated technique IDs, e.g., T1098.001, T1136.003, TA0008>  
**Domains:** <comma-separated domain tags from the valid set below>  
**Timeframe:** Last N days (configurable)  

Valid domain tags: incidents, identity, spn, endpoint, email, admin, cloud, exposure

After creating a new query file: Run python .github/manifests/build_manifest.py to regenerate the discovery manifest, then run python scripts/generate_tocs.py to auto-generate the Quick Reference TOC. The validator will flag any missing required fields.

Subfolder selection: Place files in the subfolder matching the primary data source: identity/, endpoint/, email/, network/, cloud/.

Include per-query documentation with Purpose, Thresholds, Expected Results, and Tuning guidance.

Heading format for TOC compatibility: The generate_tocs.py script auto-generates a Quick Reference TOC by scanning ### and ## Query headings that have a KQL code block within 40 lines. To ensure clean TOC output:

• ✅ DO use ### Query N: <Title> or ## Query N: <Title> for query headings — the number prefix ensures proper TOC ordering
• ✅ DO add a ## heading (e.g., ## Queries, ## Part A:, ## Hunts) immediately before the first ### Query N: if the file has preamble content (Overview, Table Selection, etc.). The TOC generator uses a --- → ## heading pair as its insertion anchor — without it, the script inserts the TOC at the bottom of the file.
• ✅ DO start non-query section headings with a non-query keyword (e.g., ### Deployment, ### Tuning, ### References) — these are automatically filtered out by the TOC generator
• ❌ DO NOT add a ## Quick Reference — Query Index heading or placeholder yourself — the script creates the heading and table. Pre-existing placeholders cause duplicated content and a broken file structure. (This is also enforced as Critical Rule #4 above.)
• ❌ DO NOT use ### headings for non-query content that contains a KQL code block within 40 lines — the TOC generator uses KQL proximity to detect query headings and will incorrectly include them

Investigation shortcuts (optional): Query files can include an **Investigation shortcuts:** bulleted list between the ## Quick Reference heading and the TOC table. These document recommended query combos for common investigation scenarios (e.g., "Delivered phishing drill-down: Q2.4 + Q7.6 + Q3.3"). Shortcuts are preserved by generate_tocs.py across re-runs. Don't add them to new files — they're a refinement added after real investigations reveal which query combos work best together.