Kql Expert

KQL Expert - Microsoft Sentinel & Azure Monitor Query Specialist

Expert guidance for Kusto Query Language (KQL) covering query optimization, schema validation against M365/Sentinel tables, analytics rule development, ASIM normalization, threat hunting, and SPL migration.

Capabilities

• Schema Validation: Validate queries against M365 Defender and Sentinel table schemas via schema_validator.py
• Query Optimization: Analyze and optimize queries following filter-early principles and term indexing
• Analytics Rules: Develop scheduled/NRT rules with entity mapping, MITRE ATT&CK tags, watchlist integration
• ASIM Normalization: Source-agnostic detection using unifying parsers with filtering parameters
• SPL Migration: Convert Splunk queries to KQL with proper command mapping
• Threat Hunting: Create hypothesis-driven hunting queries with anomaly detection
• False Positive Tuning: Reduce alert fatigue via watchlists and automation rules
• Cost Optimization: Table plan selection, DCR transformations, commitment tiers

Proactive Usage

INVOKE THIS SKILL IMMEDIATELY when any of these conditions are met:

Primary Triggers (Invoke First)

Secondary Triggers

File Patterns

• *.kql - Always invoke for this extension
• Analytics rule ARM templates containing KQL
• Sentinel workbook queries
• Any file with KQL pipe operators (| where, | extend, etc.)

Extended Thinking Framework

For complex KQL optimization challenges, apply systematic extended thinking:

When to Use Extended Thinking

• Complex Multi-Filter Optimization: Queries with 5+ where clauses requiring selectivity analysis
• Performance Regression Analysis: Understanding why optimized queries sometimes perform worse
• Cross-Table Join Optimization: Complex scenarios involving multiple data sources
• Detection Logic Preservation: Ensuring optimizations don't break detection effectiveness

Thinking Process

1. Problem Understanding: Current performance issue, constraints, available techniques
2. Hypothesis Formation: Filter selectivity predictions, string operation optimizations
3. Testing Strategy: Measure performance differences, validate optimization
4. Solution Synthesis: Best combination of optimizations, trade-offs
5. Validation: Verify performance targets met, detection effectiveness maintained

Query Analysis Workflow

When reviewing or optimizing KQL:

1. Read Best Practices: Reference references/kql_best_practices.md
2. Apply Extended Thinking: For complex queries, reason through optimization approaches
3. Validate Syntax: Use schema validator for syntax checking
4. Performance Baseline: Test current query execution time
5. Deep Analysis: Consider multiple optimization approaches and trade-offs
6. Identify Optimizations: Apply string operator improvements
7. Test Variants: Create and test optimized versions
8. Compare Results: Document performance improvements
9. Validate Assumptions: Verify theoretical expectations match reality
10. Recommend Implementation: Provide final optimized query with rationale

DCR Transformation KQL

KQL used in Data Collection Rule (DCR) transformations has significant restrictions compared to standard Log Analytics KQL. When the user is working on DCR transformations, always read references/dcr_transformation_kql.md for the authoritative limitations before writing or reviewing any transformation query.

Key DCR Restrictions (Summary)

• Transformations apply per-record — only single-row-in / single-row-out operators are supported
• Input stream is referenced as source (not a table name)
• Supported tabular operators only: where, extend, project, project-away, project-rename, parse, print, datatable, columnifexists
• Unsupported: summarize, join, union, top
• coalesce() is not supported — use iif(isnotnull(...), ..., ...) instead
• bag_remove_keys() is not supported — reconstruct the bag with pack()
• columnifexists (no underscore) — not column_ifexists
• base64_encodestring / base64_decodestring — not the _tostring variants
• parse operator: max 10 column extractions per statement
• DCR-only functions: parse_cef_dictionary, geo_location
• Use parse_json() for dynamic literals, not dynamic() syntax
• TimeGenerated must be included in output for most standard tables

For the complete supported functions allowlist and worked examples, read references/dcr_transformation_kql.md.

Scripts

Located in scripts/ folder:

schema_validator.py

Validates KQL queries against table schemas. Always use this script instead of reading environments.json directly.

Features:

• Table existence validation (M365, Sentinel, merged environments)
• Column type checking
• Magic function support (FileProfile, DeviceFromIP)
• Watchlist validation
• Similar name suggestions for typos

from scripts.schema_validator import KQLSchemaValidator, format_schema_validation_result

validator = KQLSchemaValidator()  # Loads environments.json internally
result = validator.validate_query(query, environment='sentinel')
print(format_schema_validation_result(result))

Do NOT read environments.json directly - it's a large schema file meant for programmatic access only.

kql_patterns.py

Reusable query templates for common scenarios:

• Analytics rule patterns (brute force, impossible travel, suspicious execution)
• Threat hunting patterns (IoC detection, lateral movement, anomaly detection, persistence)
• ASIM templates with filtering parameters
• Join optimization patterns

kql_optimizer.py

Query analysis and performance optimization:

• Time filtering checks (missing, late placement)
• String operator analysis (contains vs has)
• Join optimization opportunities
• Aggregation anti-patterns
• ASIM parameter usage

kql_validator.py

Query validation and compliance:

• Syntax validation
• Analytics rule constraints
• Entity mapping validation
• MITRE ATT&CK framework alignment
• Cross-workspace query limits

References

Located in references/ folder:

Important: Never read environments.json directly. It's a large data file (~500KB+) designed for programmatic access via schema_validator.py. Use the Python script to validate schemas.

Key Optimization Principles

1. Filter Early (CRITICAL)

// BAD - Late filtering
SecurityEvent
| extend x = tolower(Account)
| join IdentityInfo on Account
| where TimeGenerated > ago(1h)  // Too late!

// GOOD - Time filter FIRST
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| join (IdentityInfo | where TimeGenerated > ago(1h)) on Account

2. Use Term Indexing

// BAD - Full scan
| where CommandLine contains "powershell"

// GOOD - Uses index (3+ chars)
| where CommandLine has "powershell"

3. ASIM with Filtering Parameters

// BAD - No filters
_Im_Authentication
| where TimeGenerated > ago(1h)

// GOOD - Filters pushed to sources
_Im_Authentication(starttime=ago(1h), endtime=now(), eventresult='Failure')

4. Watchlist Integration

// Use SearchKey for optimal joins
let allowlist = _GetWatchlist('TrustedIPs') | project SearchKey;
SigninLogs
| where TimeGenerated > ago(1d)
| where IPAddress !in (allowlist)

Anti-Patterns to Avoid

Contains Elimination Patterns

Expert patterns for replacing expensive contains:

• contains ".Insert(" → has "Insert" ✅
• contains "InstallProduct(" → has "InstallProduct" ✅
• contains "function(" → has "function" ✅
• contains "cmd /c" → Keep contains (complex pattern) ❌

Rule: If the contains target has a 3+ character word boundary term, extract it for has.

Robust String Parsing

The parse operator is sensitive to exact string formats and breaks silently when upstream schemas change (spacing, field order, new fields):

// FRAGILE - breaks if format changes
| parse KeyDescription with "KeyIdentifier=" KeyId ", KeyType=" KeyType ", KeyUsage=" KeyUsage

// ROBUST - extract with regex (tolerant of spacing/order changes)
| extend KeyId = extract(@"KeyIdentifier=([^,]+)", 1, KeyDescription)
| extend KeyType = extract(@"KeyType=([^,]+)", 1, KeyDescription)

// ROBUST - if the value is JSON-formatted
| extend ParsedKey = parse_json(newValue)
| extend KeyId = tostring(ParsedKey.KeyIdentifier)

When to use each approach:

Regex Engine Limitations (RE2)

extract(), extract_all(), matches regex, and parse_regex all run on Google's RE2 engine, not PCRE. Unsupported constructs:

Failure mode for analytic rules: a PCRE-style pattern usually deploys fine via ARM PUT (ARM doesn't pre-validate KQL semantics), but the Sentinel UI raises Relop semantic error: SEM0420: Regex pattern is ill-formed when the rule is opened, and scheduled execution fails silently — no incidents fire. Always validate regex grammar before deploy.

Common rewrites:

// FAILS - lookahead asserting end-of-line or '['
| extend Reason = extract(@"ERROR[:\s]+(.+?)(?=\s*$|\s*\[)", 1, msg)

// WORKS - negated character class + trim
| extend Reason = trim(@"\s+$", extract(@"ERROR[:\s]+([^\[]+)", 1, msg))

// FAILS - lookbehind for "not preceded by X"
| where Field matches regex @"(?<!Authorised)Login"

// WORKS - invert the test in KQL
| where Field matches regex @"Login" and not(Field has "AuthorisedLogin")

Verify regex grammar in a Kusto/Azure Data Explorer playground (RE2) — not in regex101 (PCRE).

Graph Semantics (make-graph / graph-match)

KQL graph semantics (make-graph → graph-match / graph-shortest-paths) apply to Microsoft Sentinel and Azure Monitor and are commonly used for lateral-movement and attack-path detection. A variable length edge (-[e*1..5]-) matches a path of repeated edges; the matched path is a sequence of edges, not a single edge.

Accessing variable-length edge properties — dot-notation is deprecated. Referencing a property of a variable-length edge with dot-notation (e.Prop) — including combined with operators or scalar functions — is deprecated by Microsoft. Use the graph functions instead:

// DEPRECATED - dot-notation on a variable-length edge
... | graph-match (a)-[chain*1..5]-(b)
      project hops = array_length(chain.FileName)

// CORRECT - map() returns a dynamic array of the expression per edge
... | graph-match (a)-[chain*1..5]-(b)
      project hops = array_length(map(chain, FileName))

Notes:

• Inside map() / all() / any(), reference the property by name only (Prop), not edge.Prop.
• map(edge, expr) returns a dynamic array (one element per edge; empty for zero-length paths). To reach the inner nodes of a variable-length edge use map(inner_nodes(edge), expr).
• Dot-notation still works for fixed/single edges and nodes (e.g. n.name, single -[e]-> edges) — the change is specific to variable-length edges.
• This applies to the graph-match and graph-shortest-paths operators. Validate in an Azure Data Explorer / Kusto playground before deploying graph-based analytic rules.

For the full graph operator set (make-graph, graph-match, graph-shortest-paths, graph-to-table, graph-mark-components), the map()/all()/any()/inner_nodes() functions, Sentinel/Azure Monitor vs preview vs ADX-only availability, and attack-path patterns, read references/graph_semantics_kql.md.

Resource Thresholds

Performance Targets

Analytics Rule Constraints

• Query max: 10,000 characters
• Entity mappings: 10 max (3 identifiers each)
• Entities per alert: 500 max
• NRT rules: 50 per workspace, 30 alerts per execution
• Multi-workspace: 20 max
• Prohibited: search *, union *
• Required: Return TimeGenerated column

Supported Environments

The skill validates against three environments (accessed via schema_validator.py):

Table Schema Validation

# Check available tables
validator = KQLSchemaValidator()
print(validator.get_available_environments())
# ['m365', 'sentinel', 'm365_with_sentinel']

# Get table schema
schema = validator.get_table_schema('sentinel', 'SecurityEvent')
print(schema.columns)  # {'TimeGenerated': 'datetime', 'EventID': 'int', ...}

# Validate query
result = validator.validate_query("""
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| project TimeGenerated, Account, IpAddress
""", environment='sentinel')

print(f"Valid: {result.is_valid}")
print(f"Tables: {result.referenced_tables}")
print(f"Unknown: {result.unknown_tables}")

Join Strategy Reference

// Broadcast for small tables
| join kind=inner hint.strategy=broadcast (SmallTable) on Key

// Shuffle for high-cardinality
| join kind=inner hint.shufflekey=IPAddress (LargeTable) on IPAddress

ASIM Parser Quick Reference

See references/asim_schemas.md for complete schema documentation.

SPL to KQL Quick Reference

See references/spl_to_kql_mapping.md for complete mapping.

Optimization Report Template

When reporting optimization results:

## Performance Analysis
| Version | Execution Time | Improvement | Key Changes |
|---------|---------------|-------------|-------------|
| Original | Xms | - | Description |
| Optimized | Yms | +Z% faster | Optimizations |

## Optimization Reasoning
[Summary of why specific optimizations were chosen]

## Recommended Query
[Optimized KQL with comments]

## Key Optimizations Applied
- List specific improvements with performance impact
- Explain why approaches were chosen over alternatives
- Document assumptions validated during testing

Common Use Cases

Query Optimization

"Review this KQL query and optimize it - I'm getting timeouts"

Schema Validation

"Validate this query against Sentinel table schemas"

Analytics Rule Creation

"Create a Sentinel rule to detect brute force using ASIM"

SPL Migration

"Convert this Splunk detection rule to KQL"

ASIM Implementation

"Rewrite this query to use ASIM with proper filtering"

False Positive Tuning

"Help tune this rule using watchlists - too many false positives"

Table Plan Selection

MITRE ATT&CK Coverage

This skill supports detection across all MITRE tactics:

• Initial Access, Execution, Persistence, Privilege Escalation
• Defense Evasion, Credential Access, Discovery, Lateral Movement
• Collection, Command and Control, Exfiltration, Impact

Limitations

• KQL is read-only (no data modification)
• Max query: 10,000 characters for analytics rules
• Case-sensitive identifiers
• No dynamic workspace references
• Basic/Auxiliary tables: single table, 30-day max, no joins
• Cross-workspace: 20 max for rules, 100 for general queries

When NOT to Use

• SQL database queries (use T-SQL)
• Azure Resource Graph (different KQL variant)
• Real-time data modification
• Dynamic workspace selection
• Non-Microsoft platforms (Splunk, Elastic)

Version History

Version: 2.4.0 Last Updated: May 2026