Kql Query Authoring

KQL Query Authoring - Instructions

Purpose

This skill helps generate validated, production-ready KQL (Kusto Query Language) queries by leveraging:

• Schema validation from 331+ indexed tables
• Microsoft Learn documentation for official patterns
• Community query examples from GitHub repositories
• Best practices for performance and security

Prerequisites

Required MCP Servers:

This skill requires two MCP servers to be installed and configured in your VS Code environment:

1. KQL Search MCP Server - Provides schema validation, query examples, and table discovery

   • NPM Package: kql-search-mcp
   • Install: npm install -g kql-search-mcp
   • Features: 331+ indexed tables, schema validation, GitHub query search, query generation

2. Microsoft Docs MCP Server - Provides access to official Microsoft Learn documentation

   • GitHub Repository: MicrosoftDocs/mcp
   • Features: Official code samples, documentation fetch, Kusto language examples

Verification:

• KQL Search MCP tools should be available: mcp_kql-search_*
• Microsoft Docs MCP tools should be available: mcp_microsoft-lea_*

Without these MCP servers, this skill cannot access schema information or official documentation.

⚠️ Known Issues

search_favorite_repos Bug (as of v1.0.5)

Status: ❌ Broken - GitHub API query parsing fails with any query

Error: ERROR_TYPE_QUERY_PARSING_FATAL unable to parse query!

Affected Tool: mcp_kql-search_search_favorite_repos

Root Cause: Bug in query construction when building GitHub Code Search API request, even with valid GITHUB_TOKEN and FAVORITE_REPOS configuration.

Workarounds - Use these tools instead:

Issue Tracking: Report to noodlemctwoodle/kql-search-mcp

📑 TABLE OF CONTENTS

1. Prerequisites - Required MCP servers
2. Known Issues - Bug reports and workarounds
3. Critical Workflow Rules - Start here!
4. Query Authoring Workflow - Step-by-step process
5. Tool Reference - Available MCP tools
6. Query Patterns - By use case
7. Schema Differences - Sentinel vs XDR
8. Validation Rules - Quality checks
9. Best Practices - Performance & security

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

Before writing ANY KQL query:

1. ALWAYS validate table schema FIRST - Use mcp_kql-search_get_table_schema to verify:

   • Table exists in the environment
   • Column names are correct
   • Data types are accurate
   • Common query patterns exist

2. ALWAYS check schema differences - Column names vary by platform:

   • Microsoft Sentinel: Uses TimeGenerated for timestamp
   • Defender XDR: Uses Timestamp for timestamp
   • Other differences: See Schema Differences section

3. ALWAYS check local query library FIRST - Before writing from scratch:

   • Search queries/ and .github/skills/ for existing verified queries (these are battle-tested and pitfall-aware)
   • See the KQL Pre-Flight Checklist in copilot-instructions.md for the full priority order
   • If a suitable query exists locally, adapt it instead of building from scratch

4. ALWAYS use multiple sources - Combine for best results:

   • Schema validation (authoritative column names)
   • Microsoft Learn code samples (official patterns)
   • Community queries (real-world examples)

5. ALWAYS test queries using the correct execution tool - Follow the Tool Selection Rule in copilot-instructions.md:

   • Sentinel-native tables (SigninLogs, AuditLogs, SecurityAlert, etc.) → Data Lake
   • XDR tables (Device*, Email*, Identity*, etc.) ≤ 30d → Advanced Hunting (free); > 30d → Data Lake
   • XDR-only tables (DeviceTvm*, Exposure*, etc.) → Advanced Hunting only
   • Adapt timestamp column when switching tools (Timestamp ↔ TimeGenerated)
   • This is the MOST IMPORTANT validation step

6. ALWAYS validate syntax (if live testing unavailable) - Use mcp_kql-search_validate_kql_query as fallback

7. ALWAYS provide context - Include:

   • What the query does
   • Expected results
   • Any limitations or notes

8. ALWAYS read the complete ## Query Authoring Workflow

   • Read all the steps of the workflow
   • Understand the process and actions you need to take

📋 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., SigninLogs, 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's request mentions any of these, the queries are intended for custom detection deployment:

• "custom detection", "detection rule", "deploy as detection", "CD rule"
• "author detections for", "create detections for"
• "Defender XDR detection", "deploy to Defender"

When CD intent is detected:

1. Read the detection-authoring skill (.github/skills/detection-authoring/SKILL.md) — specifically the Critical Rules and CD Metadata Contract sections
2. Design queries with CD constraints in mind — 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) — the Sentinel version is the source of truth; adaptation to CD format happens at deployment time via the detection-authoring skill

Step 2: Check Local Query Library

Before writing from scratch, search for existing verified queries:

1. grep_search in queries/ for the table name or keyword
2. grep_search in .github/skills/ for related investigation patterns
3. Check the Ad-Hoc Query Examples appendix in copilot-instructions.md

If a suitable query exists, adapt it (substituting entity values) and skip to Step 6. These queries encode known pitfalls and schema quirks.

Step 3: Get Table Schema (MANDATORY)

If no local query found, start here to validate table and columns exist:

mcp_kql-search_get_table_schema("<table_name>")

What this returns:

• ✅ Category (Sentinel, Defender XDR, Azure Monitor)
• ✅ Description of table purpose
• ✅ Common columns (most frequently used)
• ✅ All columns with data types
• ✅ Example queries (starting point)
• ✅ Keywords for search

Use this to:

1. Verify table exists
2. Get correct column names (avoid typos)
3. Understand data types (string, datetime, int, etc.)
4. See example query patterns

Step 4: Get Official Code Samples

Query Microsoft Learn for official patterns:

mcp_microsoft-lea_microsoft_code_sample_search(
  query: "Detailed description of what you're trying to accomplish",
  language: "kusto"
)

Best practices for search queries:

• Include table name: "EmailEvents phishing detection"
• Include scenario: "threat hunting", "user activity", "mail flow"
• Include key concepts: "spam", "failed login", "malware"

What you get:

• Official Microsoft-documented patterns
• Production-validated examples
• Links to full documentation
• Best practice implementations

Step 5: Get Community Examples

⚠️ NOTE: The search_favorite_repos tool has a known bug (see Known Issues). Use the fallback tool instead.

Search GitHub for real-world implementations:

mcp_kql-search_search_github_examples_fallback(
  table_name: "EmailEvents",
  description: "Natural language description of query goal"
)

Alternative - Search for KQL repositories:

mcp_kql-search_search_kql_repositories(
  query: "sentinel detection hunting"
)

What this provides:

• Real-world query patterns from security analysts
• Detection rules from Microsoft Sentinel community
• Advanced techniques and optimizations
• Context from surrounding documentation

Step 6: Generate Query

Combine insights from all sources:

1. Use schema for column names (authoritative source)
2. Use Microsoft Learn for patterns (official best practices)
3. Use community examples for techniques (real-world validation)

🔥 CRITICAL: Microsoft Learn Examples Use Defender XDR Syntax!

⚠️ When using Microsoft Learn code samples:

• Microsoft Learn examples use Defender XDR Advanced Hunting syntax with Timestamp
• If testing against Sentinel, you MUST convert Timestamp → TimeGenerated BEFORE testing
• Check the platform context - Sentinel vs Defender XDR - and adjust accordingly

🔥 CRITICAL: Test queries BEFORE providing them to user - not after they ask!

⚠️ CRITICAL: Variables vs Multiple Standalone Queries

When user asks for MULTIPLE queries (different analyses, different questions):

• ✅ DO: Start EACH query directly with table name (EmailEvents, SigninLogs, etc.)
• ❌ DON'T: Use shared variables (let emailData = ...) across separate queries
• Why: Each query is standalone - user runs them independently in separate windows
• Test: If you can't copy-paste a query alone and run it successfully, it's wrong

When providing ONE complex query (single cohesive analysis):

• ✅ DO: Use let variables to simplify logic and avoid repetition
• ✅ Example: let suspiciousIPs = ...; SigninLogs | where IPAddress in (suspiciousIPs)

Query structure for standalone queries:

// Description: What this query does
// Data source: Table name
// Time range: Lookback period
// Expected results: What you'll get

<TableName>
| where TimeGenerated > ago(7d)  // Sentinel
// OR
| where Timestamp > ago(7d)       // Defender XDR
| where <filters>
| project <relevant_columns>
| order by TimeGenerated desc
| take 100

Step 7: Validate and Test (MANDATORY - Do This FIRST!)

⚠️ TEST QUERIES BEFORE USER SEES THEM - NOT AFTER THEY ASK

🔴 CRITICAL PRE-TEST STEP: Platform Schema Conversion

BEFORE testing against Sentinel, you MUST:

1. ✅ Convert Timestamp → TimeGenerated if using Microsoft Learn examples (they use Defender XDR syntax)
2. ✅ Verify all column names match Sentinel schema (not Defender XDR schema)
3. ✅ Check the table exists in Sentinel environment

Why this matters: Microsoft Learn code samples use Timestamp (Defender XDR Advanced Hunting syntax), but Sentinel uses TimeGenerated. Testing will FAIL if you don't convert first.

Primary validation workflow (if Sentinel MCP available):

1. ✅ CONVERT SCHEMA (Timestamp → TimeGenerated) BEFORE testing
2. ✅ TEST AGAINST LIVE SENTINEL using mcp_sentinel-data_query_lake with | take 5
3. ✅ Verify query returns results (not empty)
4. ✅ Fix any syntax errors or schema mismatches
5. ✅ Re-test until query works correctly
6. ✅ Only then provide query to user with confidence

Post-test checks:

1. ✅ Include comments explaining logic
2. ✅ Add take or summarize to limit results (remove test limits if too restrictive)
3. ✅ Check datetime formatting (see time formatting section below)

🔥 CRITICAL: Always Test Queries Against Live Data

When Sentinel MCP Server is available, ALWAYS run queries to validate:

mcp_sentinel-data_query_lake(
  query: "<your_complete_kql_query>",
  workspaceId: "<workspace_id_if_multiple>"  // Optional
)

Why this is critical:

• ✅ Validates query syntax against real Sentinel environment
• ✅ Confirms columns exist and are correctly typed
• ✅ Verifies data is present in the table
• ✅ Tests aggregations and calculations work correctly
• ✅ Reveals actual data patterns and edge cases
• ✅ Provides real results to show user what to expect

Validation workflow:

1. Generate query based on schema, docs, and examples
2. Test query using mcp_sentinel-data_query_lake with | take 10 or | take 5 limit
3. Verify results are sensible and expected
4. Fix issues if query fails or returns unexpected results
5. Re-test until query works correctly
6. Provide to user with confidence it will work

Best practices for testing:

• Add | take 10 to limit results during testing (remove or adjust for user)
• Test multiple queries in parallel if providing multiple standalone queries
• Check for empty results - may indicate wrong table, time range, or filters
• Verify calculations - check that percentages, counts, and aggregations make sense
• Review actual data values - ensure field names and data types match expectations

Example testing pattern:

// Test Query: Sign-ins by user with CA status
SigninLogs
| where TimeGenerated > ago(7d)
| summarize 
    TotalSignIns = count(),
    CASuccess = countif(ConditionalAccessStatus == "success"),
    CAFailure = countif(ConditionalAccessStatus == "failure")
    by UserPrincipalName
| order by TotalSignIns desc
| take 5  // Limit for testing

If query fails:

• Check error message for column name issues
• Verify table exists in environment
• Confirm time range has data
• Review filter syntax
• Check for typos in field names

Schema-only validation (fallback):

mcp_kql-search_validate_kql_query("<your_query>")

Note: This only validates syntax and schema, not against live data. Prefer mcp_sentinel-data_query_lake when available.

Step 8: Format and Deliver Output

⚠️ CRITICAL: Output Format Based on Query Count

For SINGLE query requests:

• ✅ DO: Provide query directly in chat window
• ✅ Include brief explanation and expected results
• ✅ Show sample output if tested against Sentinel
• ❌ DON'T: Create a file for single queries

For MULTIPLE queries (3+ queries or comprehensive query collections):

• ✅ DO: Create a comprehensive markdown file
• ✅ Save to /queries directory in workspace root
• ✅ Use descriptive filename: <topic>_<platform>_queries.md
• ✅ Include all queries with full documentation
• ✅ Add examples, use cases, and tuning guidance

File naming convention:

queries/endpoint/endpoint_failed_connections.md
queries/identity/app_credential_management.md
queries/email/email_threat_detection.md

Markdown file structure for multiple queries:

# [Topic] - [Platform] Queries

**Created:** [Date]
**Platform:** Microsoft Sentinel / Defender XDR
**Data Sources:** [Tables used]
**Timeframe:** [Default lookback period]

---

## Overview

[Brief description of query collection purpose]

---

## Query 1: [Descriptive Title]

**Purpose:** [What this query detects/analyzes]

**Thresholds:**
- [Key threshold values]

```kql
// Query code with comments

Expected Results:

• Column descriptions
• What to look for

Indicators of [Attack/Issue]:

• [Key patterns to identify]

Example Output: [Sample results if available]

Query 2: [Next Query]

...

Tuning and Customization

[How to adjust queries for environment]

Alert Rule Recommendations

[How to convert to analytics rules]

Investigation Workflow

[What to do with results]

**When to create query files:**
- User requests 3+ different queries
- User asks for "comprehensive" or "complete" query set
- User requests query collection for specific use case (threat hunting, investigation, monitoring)
- Queries are related and form a cohesive analysis workflow

**When to use chat output:**
- User requests 1-2 simple queries
- Quick ad-hoc query for immediate use
- User testing or learning KQL syntax
- Follow-up query to previous investigation

### Custom Detection Query Files (CD-Aware Output)

When the user's intent is custom detection deployment (detected in Step 1), the output file format has additional requirements:

**Per-query cd-metadata block:** Each query MUST include a `<!-- cd-metadata -->` HTML comment block with structured YAML fields. This block is consumed by the **detection-authoring** skill when deploying queries as custom detection rules.

The full schema is defined in `.github/skills/detection-authoring/SKILL.md` under [CD Metadata Contract]. Place the block after `Severity` / `MITRE` / `Tuning Notes` and before the KQL code block:

```markdown
### Query N: [Title]

**Purpose:** [What this detects]
**Severity:** High
**MITRE:** T1053.005

**Tuning Notes:**
- [Environment-specific guidance]

<!-- cd-metadata
cd_ready: true
schedule: "1H"
category: "Persistence"
title: "Suspicious Scheduled Task on {{DeviceName}}"
impactedAssets:
  - type: device
    identifier: DeviceName
recommendedActions: "Investigate the task XML and decode any encoded payloads."
adaptation_notes: "Remove let blocks, add mandatory columns"
-->

```kql
// Query code (Sentinel format with let variables, 7d lookback)

**cd-metadata field requirements:**

| Field | Required | Values |
|-------|----------|--------|
| `cd_ready` | Always | `true` or `false` — explicitly declare CD suitability |
| `schedule` | If cd_ready | `"0"` (NRT), `"1H"`, `"3H"`, `"12H"`, `"24H"` |
| `category` | If cd_ready | API category value (e.g., `Persistence`, `LateralMovement`, `Discovery`) |
| `title` | Optional | Dynamic title with `{{ColumnName}}` placeholders. **Max 3 unique `{{Column}}` refs across title + description combined** (Graph API limit; see detection-authoring skill Pitfall 14) |
| `impactedAssets` | If cd_ready | Array of `type` + `identifier` pairs |
| `recommendedActions` | Optional | Triage guidance string |
| `adaptation_notes` | Optional | What needs to change for CD format |

**For queries NOT suitable for CD** (baseline queries, statistical aggregations):
```markdown
<!-- cd-metadata
cd_ready: false
adaptation_notes: "Statistical baseline — requires bare summarize, not CD-compatible"
-->

Summary table: When creating a CD-aware query file, the Implementation Priority table at the end should include a CD column:

| # | Query | Severity | MITRE | CD | Priority |
|---|-------|----------|-------|----|----------|
| 1 | ... | Medium | T1069.001 | ✅ 1H | 🟠 High |
| 6 | Baseline | Info | N/A | ❌ | ⬜ Tuning |

File location:

• Directory: /queries in workspace root
• Git: Already excluded in .gitignore (may contain environment-specific info)
• Purpose: Reusable query collections for common scenarios

Tool Reference

mcp_kql-search_get_table_schema

Purpose: Get comprehensive table schema with columns, types, and examples

When to use:

• Starting any new query
• Verifying column names
• Understanding data structure
• Finding example queries

Input:

{
  "table_name": "EmailEvents"
}

Returns:

• Category and source
• Description
• Common columns (most used)
• All columns with types
• Example queries
• Keywords

mcp_microsoft-lea_microsoft_code_sample_search

Purpose: Search official Microsoft Learn documentation for code samples

When to use:

• Need official Microsoft patterns
• Want production-validated examples
• Looking for best practices
• Need links to documentation

Input:

{
  "query": "EmailEvents KQL query Defender for Office mail flow statistics spam threats",
  "language": "kusto"
}

Returns:

• Code snippets with descriptions
• Links to Microsoft Learn pages
• Official examples
• Context about usage

Pro tip: Include language: "kusto" parameter to filter for KQL samples only

mcp_kql-search_search_github_examples_fallback ✅ (RECOMMENDED)

Purpose: Search all public GitHub repos for KQL query examples by table name

When to use:

• Need real-world query examples for a specific table
• search_favorite_repos is broken (see Known Issues)
• Looking for detection rules and community patterns
• Want unvalidated examples from the community

Input:

{
  "table_name": "SecurityIncident",
  "description": "summarize by status classification severity"
}

Returns:

• Query examples from GitHub repositories
• Repository and file path context
• Raw KQL code (unvalidated - verify before use)

mcp_kql-search_search_favorite_repos ❌ (BROKEN)

Status: ❌ Known bug as of v1.0.5 - do not use

Error: ERROR_TYPE_QUERY_PARSING_FATAL unable to parse query!

Use instead: mcp_kql-search_search_github_examples_fallback or mcp_microsoft-lea_microsoft_code_sample_search

mcp_kql-search_search_kql_repositories ✅

Purpose: Find GitHub repositories containing KQL queries

When to use:

• Looking for well-maintained KQL query collections
• Want to discover new query sources
• Finding repos sorted by stars

Input:

{
  "query": "sentinel detection hunting"
}

Returns:

• Repository list sorted by stars
• Repository descriptions
• Links to explore further

mcp_sentinel-data_query_lake

Purpose: Execute KQL queries against live Microsoft Sentinel workspace for validation and testing

When to use:

• ALWAYS when generating queries - validate against real data
• Testing query syntax and logic
• Verifying columns exist and are correctly typed
• Confirming data is present in tables
• Validating aggregations and calculations
• Spot-checking query results before providing to user

Input:

{
  "query": "SigninLogs | where TimeGenerated > ago(7d) | summarize count() by UserPrincipalName | take 10",
  "workspaceId": "optional-workspace-guid-if-multiple"
}

Returns:

• Query results in structured format
• Column names and data types
• Actual data rows
• Query statistics (execution time, resource usage)
• Error messages if query fails

Best practices:

• Add | take 10 or | take 5 during testing to limit results
• Test multiple queries in parallel when providing multiple standalone queries
• Check for empty results (may indicate wrong table/time range/filters)
• Verify calculations and aggregations are correct
• Review actual data values to ensure fields match expectations

Example usage:

// Test query before providing to user
mcp_sentinel-data_query_lake(
  query: "SigninLogs | where TimeGenerated > ago(7d) | summarize TotalSignIns = count(), UniqueApps = dcount(AppDisplayName) by UserPrincipalName | order by TotalSignIns desc | take 5"
)

Critical notes:

• This executes against LIVE production data - queries affect workspace resources
• Always use appropriate time ranges and result limits
• Failed queries return error messages to help debug issues
• Prefer this over schema-only validation when available

mcp_sentinel-data_search_tables

Purpose: Discover relevant Sentinel tables using natural language queries

When to use:

• User request is ambiguous about which table to use
• Need to find tables for specific scenarios
• Exploring available data sources
• Confirming table availability in workspace

Input:

{
  "query": "sign-in authentication Entra ID user activity",
  "workspaceId": "optional-workspace-guid-if-multiple"
}

Returns:

• Relevant table names
• Table schemas
• Descriptions of table contents
• Availability in workspace

Use case: "I need to query user sign-in data" → Tool suggests SigninLogs, AADNonInteractiveUserSignInLogs

Search tips:

• Use natural language
• Include table names
• Mention key concepts
• Be specific about goals

mcp_kql-search_validate_kql_query

Purpose: Validate KQL query syntax and schema

When to use:

• Before executing queries
• After generating complex queries
• To catch common mistakes
• To verify column names

Input:

{
  "query": "EmailEvents | where Timestamp > ago(7d) | summarize count() by ThreatTypes"
}

Returns:

• Syntax validation results
• Schema validation (table/column names)
• Warnings and errors
• Suggestions for fixes

mcp_kql-search_find_column

Purpose: Find which tables contain a specific column

When to use:

• Know column name, not table
• Looking for similar data across tables
• Exploring schema relationships

Input:

{
  "column_name": "ThreatTypes"
}

Returns:

• List of tables with that column
• Column details for each table
• Data types

mcp_kql-search_generate_kql_query

Purpose: Auto-generate validated KQL query from natural language

When to use:

• Quick query generation
• Starting point for complex queries
• Learning KQL patterns
• Schema-validated output needed

Input:

{
  "description": "show failed sign-ins from the last 24 hours",
  "table_name": "SigninLogs",
  "time_range": "24h",
  "filters": {"ResultType": "Failed"},
  "columns": ["TimeGenerated", "UserPrincipalName", "IPAddress", "Location"],
  "limit": 100
}

Returns:

• Fully validated query
• Schema validation results
• Documentation links
• Explanations

Note: All table names and columns are verified against 331+ table index

Common Query Patterns

1. Basic Filtering and Projection

Use case: Get specific records matching criteria

// Get failed sign-ins for specific user
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@domain.com"
| where ResultType != "0"  // 0 = success
| project TimeGenerated, IPAddress, Location, ResultDescription
| order by TimeGenerated desc
| take 100

2. Aggregation and Statistics

Use case: Count, summarize, or group data

// Count emails by threat type
EmailEvents
| where TimeGenerated > ago(7d)
| summarize Count = count() by ThreatTypes
| order by Count desc

3. Time Series Analysis

Use case: Trend over time

// Daily sign-in volume
SigninLogs
| where TimeGenerated > ago(30d)
| summarize Count = count() by bin(TimeGenerated, 1d)
| order by TimeGenerated asc
| render timechart

4. Multiple Conditions

Use case: Complex filtering logic

// High-risk sign-ins
SigninLogs
| where TimeGenerated > ago(7d)
| where RiskLevelDuringSignIn in ("high", "medium")
| where RiskState == "atRisk"
| where ConditionalAccessStatus != "success"
| project TimeGenerated, UserPrincipalName, IPAddress, Location, RiskLevelDuringSignIn
| order by TimeGenerated desc

5. Joins Across Tables

Use case: Correlate data from multiple sources

// Email threats with user info
EmailEvents
| where TimeGenerated > ago(7d)
| where ThreatTypes has_any ("Phish", "Malware")
| join kind=inner (
    IdentityInfo
    | distinct AccountUpn, Department, JobTitle
) on $left.RecipientEmailAddress == $right.AccountUpn
| project TimeGenerated, RecipientEmailAddress, Department, JobTitle, ThreatTypes, Subject

6. JSON Parsing

Use case: Extract data from dynamic/JSON columns

// Parse authentication details
SigninLogs
| where TimeGenerated > ago(7d)
| extend AuthDetails = parse_json(AuthenticationDetails)
| mv-expand AuthDetails
| extend AuthMethod = tostring(AuthDetails.authenticationMethod)
| project TimeGenerated, UserPrincipalName, AuthMethod

7. Statistical Analysis

Use case: Percentiles, averages, distributions

// Sign-in duration statistics
SigninLogs
| where TimeGenerated > ago(7d)
| where isnotempty(ProcessingTimeInMilliseconds)
| summarize 
    AvgTime = avg(ProcessingTimeInMilliseconds),
    P50 = percentile(ProcessingTimeInMilliseconds, 50),
    P95 = percentile(ProcessingTimeInMilliseconds, 95),
    P99 = percentile(ProcessingTimeInMilliseconds, 99)

8. Dynamic Type Casting

Use case: Converting dynamic/JSON types to strings for manipulation

⚠️ CRITICAL: Always convert dynamic types to string BEFORE using string functions

❌ Wrong - Will cause "expected string expression" error:

EmailEvents
| where isnotempty(ThreatTypes)
| extend ThreatType = split(ThreatTypes, ",")
| mv-expand ThreatType
| extend ThreatType = trim(" ", ThreatType)  // ERROR: ThreatType is still dynamic

✅ Correct - Convert to string first:

EmailEvents
| where isnotempty(ThreatTypes)
| extend ThreatType = split(ThreatTypes, ",")
| mv-expand ThreatType
| extend ThreatType = tostring(ThreatType)  // Convert to string first
| extend ThreatType = trim(@"\s", ThreatType)  // Now string functions work

Common scenarios requiring type conversion:

// After mv-expand
| mv-expand AuthDetails
| extend AuthMethod = tostring(AuthDetails.authenticationMethod)  // Convert before use

// After parse_json
| extend Details = parse_json(AdditionalFields)
| extend EventType = tostring(Details.EventType)  // Convert before string operations

// After split operations
| extend Parts = split(UserPrincipalName, "@")
| extend Domain = tostring(Parts[1])  // Convert array element to string

Rule of thumb: If you get "expected string expression" error, add tostring() before the problematic operation.

CRITICAL: Schema Differences

Timestamp Column Names

⚠️ MOST COMMON ERROR: Using wrong timestamp column

Example differences:

// ✅ CORRECT for Sentinel
EmailEvents
| where TimeGenerated > ago(7d)

// ✅ CORRECT for Defender XDR
EmailEvents
| where Timestamp > ago(7d)

How to avoid errors:

1. Ask user which platform they're using
2. Check schema with mcp_kql-search_get_table_schema
3. Look at example queries in schema output
4. If query fails with "column not found", try alternate name

Time Range Formatting

⚠️ CRITICAL: Use ago() function, NOT datetime literals

✅ CORRECT - Use ago() function:

SigninLogs
| where TimeGenerated > ago(7d)   // Last 7 days
| where TimeGenerated > ago(30d)  // Last 30 days
| where TimeGenerated > ago(1h)   // Last 1 hour

❌ WRONG - Hardcoded datetime strings (will fail or give wrong results):

SigninLogs
| where TimeGenerated > "2026-01-06"  // ERROR: String comparison
| where TimeGenerated > 2026-01-06    // ERROR: Invalid syntax

For specific date ranges, use between() with datetime() function:

let start = datetime(2026-01-01);
let end = datetime(2026-01-13);
SigninLogs
| where TimeGenerated between (start .. end)

Common time range patterns:

// Relative time ranges (PREFERRED)
| where TimeGenerated > ago(7d)      // Last 7 days
| where TimeGenerated > ago(24h)     // Last 24 hours
| where TimeGenerated > ago(90d)     // Last 90 days

// Specific date ranges
let startDate = datetime(2026-01-01);
let endDate = datetime(2026-01-31);
| where TimeGenerated between (startDate .. endDate)

// Time window (last X to Y ago)
| where TimeGenerated between (ago(14d) .. ago(7d))  // Between 14 and 7 days ago

Time units for ago():

• d = days
• h = hours
• m = minutes
• s = seconds

Why ago() is preferred:

• Always relative to query execution time
• No timezone confusion
• Works across all environments
• No need to calculate dates manually

Other Common Differences

Validation Rules

Pre-Execution Checklist

Before providing any query to user:

• Schema validated: All tables exist
• Columns verified: All columns exist with correct names
• Time column correct: TimeGenerated vs Timestamp
• Time filter included: Always filter on time for performance
• Results limited: Include take or summarize to avoid huge results
• Comments added: Explain what query does
• Data types correct: String comparisons use == or =~, not =
• Syntax valid: No obvious typos or errors

Common Syntax Errors to Avoid

❌ Wrong:

// Missing time filter
SigninLogs | where UserPrincipalName == "user@domain.com"

// Wrong timestamp column
EmailEvents | where Timestamp > ago(7d)  // If on Sentinel

// No result limit
SecurityAlert | where TimeGenerated > ago(7d)  // Could return 1000s of rows

// Wrong string comparison
SigninLogs | where UserPrincipalName = "user@domain.com"  // Use == or =~

✅ Correct:

// Time filter + result limit
SigninLogs 
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@domain.com"  // =~ is case-insensitive
| take 100

// Correct timestamp for platform
EmailEvents 
| where TimeGenerated > ago(7d)  // Sentinel
| take 100

// Aggregation instead of raw records
SecurityAlert 
| where TimeGenerated > ago(7d)
| summarize Count = count() by Severity

// Case-insensitive comparison
SigninLogs 
| where UserPrincipalName =~ "user@domain.com"  // =~ for case-insensitive

Best Practices

Performance Optimization

1. Always filter on time first

   | where TimeGenerated > ago(7d)  // First filter
   | where UserPrincipalName =~ "user@domain.com"  // Then specific filters
   

2. Use take for exploration

   | take 100  // Limit results during testing
   

3. Use summarize instead of raw records

   // Better
   | summarize Count = count() by Category
   
   // Avoid for large datasets
   | project Category, Details, TimeGenerated
   

4. Project only needed columns

   | project TimeGenerated, UserPrincipalName, IPAddress  // Only what you need
   

5. Avoid wildcards in filters

   // Better
   | where UserPrincipalName has "admin"
   
   // Slower
   | where UserPrincipalName contains "admin"
   

Security and Privacy

1. Limit sensitive data exposure

   // Redact PII if needed
   | extend MaskedEmail = strcat(substring(UserPrincipalName, 0, 3), "***")
   

2. Be careful with take

   // Good for testing
   | take 10
   
   // May miss important data
   | take 1  // Too restrictive
   

3. Filter early, filter often

   | where TimeGenerated > ago(7d)  // Reduce dataset early
   | where isnotempty(UserPrincipalName)  // Remove nulls
   | where UserPrincipalName !has "service"  // Exclude service accounts
   

Code Quality

1. Always include comments

   // Get failed sign-ins for admin accounts in last 24 hours
   SigninLogs
   | where TimeGenerated > ago(1d)
   | where UserPrincipalName has "admin"
   | where ResultType != "0"
   | project TimeGenerated, UserPrincipalName, IPAddress, ResultDescription
   

2. Use meaningful variable names

   let SuspiciousIPs = dynamic(["203.0.113.42", "198.51.100.10"]);
   SigninLogs
   | where IPAddress in (SuspiciousIPs)
   

3. Format for readability

   // Good formatting
   SigninLogs
   | where TimeGenerated > ago(7d)
   | where ResultType != "0"
   | summarize 
       FailedCount = count(),
       UniqueIPs = dcount(IPAddress)
       by UserPrincipalName
   | order by FailedCount desc
   

4. Break complex queries into steps

   let FailedSignins = SigninLogs
       | where TimeGenerated > ago(7d)
       | where ResultType != "0";
   
   let HighRiskUsers = FailedSignins
       | summarize FailCount = count() by UserPrincipalName
       | where FailCount > 10;
   
   HighRiskUsers
   | join kind=inner (FailedSignins) on UserPrincipalName
   | project TimeGenerated, UserPrincipalName, IPAddress, Location
   

5. ⚠️ CRITICAL: Use table names directly when providing multiple standalone queries

   ❌ WRONG - Shared variables don't work across separate query executions:

   // This pattern FAILS when user runs queries separately
   let emailData = EmailEvents | where Timestamp > ago(7d);
   
   // Query 1
   emailData  
   | summarize count() by EmailDirection
   
   // Query 2 - ERROR: 'emailData' is undefined in separate query window
   emailData  
   | summarize count() by ThreatTypes
   

   ✅ CORRECT - Each query references table directly (standalone/independent):

   // Query 1: Mail flow by direction
   EmailEvents
   | where Timestamp > ago(7d)
   | summarize count() by EmailDirection
   
   // Query 2: Threats summary (completely independent)
   EmailEvents
   | where Timestamp > ago(7d)
   | summarize count() by ThreatTypes
   

   ✅ CORRECT - Variables WITHIN a single complex query:

   // Single unified query - variables work here
   let emailData = EmailEvents | where Timestamp > ago(7d);
   let threatEmails = emailData | where isnotempty(ThreatTypes);
   
   threatEmails
   | summarize 
       TotalThreats = count(),
       BlockedCount = countif(DeliveryAction == "Blocked")
       by ThreatTypes
   

   Decision Matrix:

   User Request	Pattern to Use	Reason
   "Show me 5 different mail flow analyses"	❌ NO variables, ✅ Each query starts with EmailEvents	User runs each independently
   "Analyze mail flow with multiple dimensions"	❌ NO variables, ✅ Each query starts with EmailEvents	Separate questions = separate queries
   "Create a comprehensive mail flow report"	✅ ONE query with variables	Single cohesive analysis
   "Build a complex threat hunting query"	✅ ONE query with variables	Logical flow within single execution

   Golden Rule: If you're generating multiple queries separated by blank lines/headers, ALWAYS start each with the table name directly. Variables are ONLY for internal use within a single query.

Query Output Formats

1. Provide Query with Context

Always include:

## Query Name: [Descriptive Title]

**Purpose:** [What this query does]

**Data Source:** [Table name]

**Time Range:** [Lookback period]

**Platform:** [Sentinel / Defender XDR]

**Expected Results:** [What you'll get]

```kql
// [Query with comments]

Key Columns:

• ColumnName: Description
• ColumnName: Description

Notes:

• Any caveats or limitations
• Performance considerations
• Customization suggestions

---

### 2. Provide Multiple Variations

**Example:**

```markdown
### Option 1: Summary Statistics
```kql
EmailEvents
| where TimeGenerated > ago(7d)
| summarize Count = count() by ThreatTypes
| order by Count desc

Option 2: Detailed Records

EmailEvents
| where TimeGenerated > ago(7d)
| where isnotempty(ThreatTypes)

…(truncated)