From sentinel
KQL expert for Microsoft Sentinel, Azure Monitor, and M365 Defender. Use proactively when the user works with any .kql file, writes or reviews KQL queries, develops analytics or detection rules, performs threat hunting, needs DCR transformation KQL, or asks to optimise, validate, or convert a KQL query. Covers query optimisation, schema validation, ASIM normalisation, SPL migration, and best practices.
How this skill is triggered — by the user, by Claude, or both
Slash command
/sentinel:kql-expertThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
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.
expected_output_analytics_rule.jsonexpected_output_query_optimization.jsonexpected_output_spl_migration.jsonreferences/ENVIRONMENTS.mdreferences/asim_schemas.mdreferences/dcr_transformation_kql.mdreferences/environments.jsonreferences/kql_best_practices.mdreferences/spl_to_kql_mapping.mdsample_input_analytics_rule.jsonsample_input_query_optimization.jsonsample_input_spl_migration.jsonscripts/kql_optimizer.pyscripts/kql_patterns.pyscripts/kql_validator.pyscripts/schema_validator.pyExpert 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.
schema_validator.pyINVOKE THIS SKILL IMMEDIATELY when any of these conditions are met:
| Condition | User Phrasing Examples |
|---|---|
.kql file extension | "Check @file.kql", "Review this .kql", "Look at @Detection.kql" |
| KQL operators in content | File contains ` |
| Sentinel/M365 Defender context | "analytics rule", "detection rule", "hunting query" |
| Trigger | Examples |
|---|---|
| KQL query writing | "Write a KQL query", "Create a detection for..." |
| Performance issues | "Query is slow", "timing out", "optimize this query" |
| Syntax problems | KQL validation fails, syntax errors |
| Best practice review | "Review for best practices", "Is this optimized?" |
| SPL migration | "Convert this Splunk query to KQL" |
| DCR transformations | "DCR transformation", "data collection rule KQL", "transform incoming logs", "filter before ingestion" |
*.kql - Always invoke for this extension| where, | extend, etc.)For complex KQL optimization challenges, apply systematic extended thinking:
When reviewing or optimizing KQL:
references/kql_best_practices.mdKQL 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.
source (not a table name)where, extend, project, project-away, project-rename, parse, print, datatable, columnifexistssummarize, join, union, topcoalesce() is not supported — use iif(isnotnull(...), ..., ...) insteadbag_remove_keys() is not supported — reconstruct the bag with pack()columnifexists (no underscore) — not column_ifexistsbase64_encodestring / base64_decodestring — not the _tostring variantsparse operator: max 10 column extractions per statementparse_cef_dictionary, geo_locationparse_json() for dynamic literals, not dynamic() syntaxTimeGenerated must be included in output for most standard tablesFor the complete supported functions allowlist and worked examples, read references/dcr_transformation_kql.md.
Located in scripts/ folder:
Validates KQL queries against table schemas. Always use this script instead of reading environments.json directly.
Features:
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.
Reusable query templates for common scenarios:
Query analysis and performance optimization:
Query validation and compliance:
Located in references/ folder:
| File | Description | Access |
|---|---|---|
environments.json | M365 and Sentinel table schemas | Scripts only - use schema_validator.py |
ENVIRONMENTS.md | Schema file documentation | Read directly |
kql_best_practices.md | Detailed optimization guide | Read directly |
spl_to_kql_mapping.md | SPL migration reference | Read directly |
asim_schemas.md | ASIM parser reference | Read directly |
dcr_transformation_kql.md | DCR transformation KQL limitations, supported operators/functions, and best practices | Read directly |
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.
// 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
// BAD - Full scan
| where CommandLine contains "powershell"
// GOOD - Uses index (3+ chars)
| where CommandLine has "powershell"
// BAD - No filters
_Im_Authentication
| where TimeGenerated > ago(1h)
// GOOD - Filters pushed to sources
_Im_Authentication(starttime=ago(1h), endtime=now(), eventresult='Failure')
// Use SearchKey for optimal joins
let allowlist = _GetWatchlist('TrustedIPs') | project SearchKey;
SigninLogs
| where TimeGenerated > ago(1d)
| where IPAddress !in (allowlist)
| Pattern | Problem | Solution |
|---|---|---|
contains for terms | Full scan | Use has |
tolower(x) == "y" | Row-by-row conversion | Use x =~ "y" |
search * / union * | Scans all tables | Explicit table names |
| No TimeGenerated filter | Full history scan | Filter first |
| No time in subqueries | Subquery scans all | Add filter to each |
sort by | take N | Full sort | Use top N by |
| Large table on left | Inefficient join | Small table left |
parse for structured strings | Fragile; breaks if schema changes | Use extract() or parse_json() |
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.
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:
| Method | Use When |
|---|---|
parse | Format is guaranteed stable AND you need all fields in sequence |
extract() | Need specific fields, format may vary, or fields may be reordered |
parse_json() | Data is JSON (extract JSON portion first if prefixed with text) |
| Metric | Excessive | Throttled |
|---|---|---|
| CPU Time | >100s | >1,000s |
| Time Span | >15 days | >90 days |
| Cross-Region | >3 | >6 |
| Query Timeout | 4 min default | 1 hour max |
| Result Limit | 500K records OR 64MB |
| Query Type | Target | Acceptable | Action if Exceeded |
|---|---|---|---|
| Detection Rules | < 5s | < 30s | Optimize filters, reduce time range |
| Dashboards | < 2s | < 5s | Pre-aggregate, reduce scope |
| Investigation Queries | < 60s | < 120s | Add time filters, sample data |
| Threat Hunting | < 120s | < 300s | Narrow scope, use summarization |
search *, union *TimeGenerated columnThe skill validates against three environments (accessed via schema_validator.py):
| Environment | Tables | Use Case |
|---|---|---|
m365 | Defender XDR tables | Advanced Hunting |
sentinel | Log Analytics tables | Microsoft Sentinel |
m365_with_sentinel | Merged (auto-created) | Cross-platform queries |
# 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}")
| Scenario | Hint | When |
|---|---|---|
| Small right table (<100KB) | hint.strategy=broadcast | Dimension lookups |
| High-cardinality (>1M) | hint.shufflekey=<key> | IP, GUID joins |
| Small dimension table | Use lookup operator | Auto-broadcast |
// 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
| Schema | Parser | Key Parameters |
|---|---|---|
| Authentication | _Im_Authentication | starttime, endtime, eventresult, username_has_any |
| Network Session | _Im_NetworkSession | starttime, endtime, srcipaddr_has_any_prefix, dstportnumber |
| DNS | _Im_Dns | starttime, endtime, responsecodename, domain_has_any |
| Process Event | _Im_ProcessEvent | starttime, endtime, commandline_has_any, hostname_has_any |
| File Event | _Im_FileEvent | starttime, endtime, filename_has_any, filepath_has_any |
| Registry Event | _Im_RegistryEvent | starttime, endtime, registrykey_has_any |
See references/asim_schemas.md for complete schema documentation.
| SPL | KQL | Notes |
|---|---|---|
eval | extend | strcat() for concat |
table | project | Select columns |
stats count by x | summarize count() by x | |
stats dc(x) | dcount(x) | Distinct count |
stats values(x) | make_set(x) | Unique values |
stats earliest(_time) | arg_min(TimeGenerated, *) | |
stats latest(_time) | arg_max(TimeGenerated, *) | |
if(a,b,c) | iff(a,b,c) | Extra 'f' |
substr(x,1,5) | substring(x,0,5) | 0-based! |
cidrmatch | ipv4_is_match() |
See references/spl_to_kql_mapping.md for complete mapping.
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
"Review this KQL query and optimize it - I'm getting timeouts"
"Validate this query against Sentinel table schemas"
"Create a Sentinel rule to detect brute force using ASIM"
"Convert this Splunk detection rule to KQL"
"Rewrite this query to use ASIM with proper filtering"
"Help tune this rule using watchlists - too many false positives"
| Plan | Ingestion | Query Cost | Best For |
|---|---|---|---|
| Analytics | Standard | Free | Security data, alerts |
| Basic | ~80% lower | Per-GB | Troubleshooting |
| Auxiliary | ~90% lower | Per-GB | Compliance, audit |
This skill supports detection across all MITRE tactics:
| Version | Changes |
|---|---|
| 2.3.0 | Added DCR Transformation KQL section and dcr_transformation_kql.md reference covering supported operators, function allowlist, and DCR-specific restrictions |
| 2.2.3 | Added Robust String Parsing section warning about fragile parse operator; recommend extract() or parse_json() |
| 2.2.2 | Enhanced proactive triggers with specific user phrasing patterns and explicit .kql file extension detection |
| 2.2.1 | Clarified environments.json should only be accessed via schema_validator.py, not read directly |
| 2.2.0 | Added Proactive Usage section with trigger patterns and file detection guidance |
| 2.1.0 | Added Extended Thinking Framework, Query Analysis Workflow, Contains Elimination Patterns, Performance Targets, Optimization Report Template |
| 2.0.0 | Added schema_validator.py with environments.json support, reorganized to scripts/ and references/ folders |
| 1.0.0 | Initial release with optimizer, validator, patterns |
Version: 2.3.0 Last Updated: March 2026
npx claudepluginhub dstreefkerk/claude-skills --plugin sentinelDevelop, optimize, and troubleshoot CrowdStrike LogScale (Humio) security detection queries using CQL syntax. Use when writing LogScale queries, building security detections, creating threat hunting rules, fixing CQL syntax errors, working with CrowdStrike EDR/Falcon security monitoring, or building behavioral rules with the correlate() function. Handles case statements, risk categorization, multi-event correlation, investigation playbooks, and actionable security outputs.
Expert guidance for Azure Sentinel: troubleshooting connectors, KQL/ASIM, Logic Apps playbooks, UEBA, multi-tenant MSSP, deployment, and cost optimization.
Query and analyze data in Azure Data Explorer (Kusto/ADX) using KQL for log analytics, telemetry, time series, and anomaly detection.