Kql

KQL Skill

Write, debug, optimize, translate, and automate KQL queries across Azure data platforms.

Workflow Routing

If no specific workflow matches, default to WriteQuery.

Reference Files

Read these as needed — don't load everything upfront:

Sample Queries

The samples/ directory contains production-ready .kql files organized by service. Reference these when writing similar queries — they demonstrate the expected file format and conventions.

Output Format

Every generated query MUST use this .kql file format:

// ============================================================
// Title: <descriptive title>
// Service: <Log Analytics | Sentinel | ADX | App Insights>
// Tables: <comma-separated list of tables used>
// Description: <what this query does and when to use it>
// Parameters: <any variables the user should customize>
// Complexity: <Beginner | Intermediate | Advanced>
// ============================================================

// <the query, with inline comments for non-obvious logic>

File Naming

Use kebab-case: failed-sign-ins-by-location.kql, high-cpu-vms-last-24h.kql

Core Principles

1. Always specify the target service — KQL varies across Azure services. A query for Sentinel won't necessarily work in ADX. 2. Time-bound by default — Include TimeGenerated filters (or equivalent) to prevent full-table scans. Default to last 24 hours unless the user specifies otherwise. 3. Performance first — Filter early (where before join/summarize), use has over contains for string matching, avoid * projections on wide tables. 4. Parameterize — Use let statements for values the user will customize (time ranges, thresholds, resource names). 5. Explain the query — Add inline comments for non-trivial logic, especially mv-expand, parse, regex, and complex summarize expressions.

Service-Specific Notes

• Log Analytics: No management commands (.create, .alter). Tables like Heartbeat, Perf, Event, Syslog, AzureActivity.
• Sentinel: Extends Log Analytics with SecurityEvent, SecurityAlert, SigninLogs, ThreatIntelligenceIndicator, plus custom analytics rule functions.
• ADX: Full KQL engine — supports management commands, materialized views, continuous exports, external tables. Most powerful but queries may not be portable.
• Application Insights: Shares Log Analytics engine. Key tables: requests, dependencies, exceptions, traces, customEvents, performanceCounters.

Examples

Example 1 — Write Query:

"Write a KQL query to find failed sign-ins from outside the US in the last 7 days"

Routes to: workflows/WriteQuery.md → targets Sentinel/Log Analytics, uses SigninLogs

Example 2 — Optimize:

"This query takes forever to run, can you make it faster?" (pastes KQL)

Routes to: workflows/DebugOptimize.md

Example 3 — Translate:

"Convert this SQL query to KQL: SELECT * FROM events WHERE severity > 3 GROUP BY source"

Routes to: workflows/Translate.md

Example 4 — Tooling:

"How do I run this query from Azure CLI and export to CSV?"

Routes to: workflows/Tooling.md

---

Gotchas

• `ago()` is evaluated at query parse time, not row time — where TimeGenerated > ago(1h) and | extend Age = now() - TimeGenerated use different now() snapshots by milliseconds. Cache the value in a let now_ = now(); if comparison matters.
• `contains` is case-insensitive AND non-indexed; `has` is indexed but token-boundary only: where Message has "error" won't match errors (different token). For substring matches use contains_cs / contains knowing they full-scan.
• `summarize` without `by` returns one row, hiding all grouping bugs — if you forgot the by clause and got 1 row, that's why. Always project at least one dimension during dev.
• Cross-cluster `join` is fine; cross-workspace `join` silently truncates to the first workspace's data set if the table name collides. Use workspace("foo").Table aliases on both sides.
• `extend` is evaluated lazily — a where after it filters BEFORE the extend computes, which is fast but means columns referenced in where must already exist. Reorder: where first, then extend.
• `SigninLogs` and `AADSignInEventsBeta` are different tables with different schemas in Sentinel — queries built for one fail on the other with cryptic "column not found" errors. Check print Tables = "<expected>" against getschema.
• Sentinel analytics rules cap at 10,000 results per run silently — your hunting query that returned 50K rows interactively will alert on only 10K. Use | take 10000 explicitly to surface the limit during testing.

KQL Operators & Functions Reference

Table of Contents

• Tabular Operators
• Scalar Functions
• Aggregation Functions
• String Operators
• DateTime Functions
• Dynamic / JSON Functions
• Rendering

Tabular Operators

Join Kinds

Scalar Functions

Type Conversion

• tostring(), toint(), tolong(), todouble(), toreal(), todecimal()
• tobool(), todatetime(), totimespan(), toguid()
• parse_json() / todynamic() — parse JSON string to dynamic

Conditional

• iff(condition, ifTrue, ifFalse) — ternary
• iif(condition, ifTrue, ifFalse) — alias for iff
• case(cond1, val1, cond2, val2, ..., default) — multi-branch
• coalesce(a, b, c) — first non-null value
• max_of(a, b), min_of(a, b) — scalar min/max

Null Handling

• isempty() / isnotempty() — empty string or null
• isnull() / isnotnull() — null check
• coalesce() — first non-null

Aggregation Functions

String Operators

String Functions

• strcat(a, b, ...) — concatenate
• strlen(s) — length
• substring(s, start, length) — extract
• split(s, delimiter) — split to array
• replace_string(s, old, new) — replace
• replace_regex(s, pattern, rewrite) — regex replace
• extract(regex, captureGroup, s) — regex extract
• extract_all(regex, s) — all regex matches
• trim(regex, s) — trim characters
• tolower(s), toupper(s) — case conversion
• url_encode(), url_decode() — URL encoding
• base64_encode_tostring(), base64_decode_tostring() — Base64
• hash_sha256(s) — SHA-256 hash

DateTime Functions

• ago(timespan) — relative past (ago(1h), ago(7d))
• now() — current UTC time
• datetime(2024-01-15) — literal datetime
• bin(datetime, timespan) — round down to bin (bin(TimeGenerated, 1h))
• startofday(), startofweek(), startofmonth(), startofyear() — period start
• endofday(), endofweek(), endofmonth(), endofyear() — period end
• datetime_diff(unit, dt1, dt2) — difference in units
• datetime_add(unit, amount, dt) — add to datetime
• format_datetime(dt, format) — format as string
• dayofweek(dt) — day of week (timespan)
• getmonth(dt), getyear(dt) — extract parts

Timespan Literals

1d (day), 1h (hour), 1m (minute), 1s (second), 1ms, 1tick

Dynamic / JSON Functions

• parse_json(s) / todynamic(s) — parse JSON string
• bag_unpack(dynamic) — expand bag to columns (use with evaluate)
• bag_keys(dynamic) — get keys
• bag_has_key(dynamic, key) — check key exists
• bag_merge(bag1, bag2) — merge bags
• pack(k1, v1, k2, v2) — create bag
• pack_all() — all columns into one bag
• array_length(arr) — array size
• array_index_of(arr, value) — find index
• array_slice(arr, start, end) — slice
• array_concat(arr1, arr2) — concatenate
• array_sort_asc(arr), array_sort_desc(arr) — sort
• set_difference(arr1, arr2) — set operations
• set_intersect(arr1, arr2), set_union(arr1, arr2)
• treepath(dynamic) — all paths in JSON

Rendering

| render timechart          // line chart over time
| render barchart           // bar chart
| render piechart           // pie chart
| render scatterchart       // scatter plot
| render areachart          // area chart
| render stackedareachart   // stacked area
| render columnchart        // column chart
| render anomalychart       // with anomaly detection
| render ladderchart        // ladder/waterfall
| render table              // explicit table (default)

Rendering properties:

| render timechart with (title="CPU Usage", xtitle="Time", ytitle="Percent")

KQL Patterns & Anti-patterns

Table of Contents

• Performance Patterns
• Anti-patterns
• Security Hunting Patterns
• Time-series Patterns
• Data Enrichment Patterns

---

Performance Patterns

1. Filter Early, Filter Hard

// GOOD — time filter + equality first, then expensive operations
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| where AccountType == "User"
| summarize FailCount=count() by Account, Computer

// BAD — summarize all, then filter
SecurityEvent
| summarize FailCount=count() by Account, Computer, EventID
| where EventID == 4625

2. Use has Over contains

has uses the term index (O(1) lookup). contains does a substring scan (O(n)).

// GOOD — 10x faster for whole-word matches
Syslog | where SyslogMessage has "error"

// BAD — substring scan, no index
Syslog | where SyslogMessage contains "error"

Use contains only when you need true substring matching (e.g., "err" matching "error").

3. Materialize Shared Subqueries

// GOOD — computed once, reused twice
let activeUsers = materialize(
    SigninLogs
    | where TimeGenerated > ago(1d)
    | distinct UserPrincipalName
);
let userCount = activeUsers | count;
let topUsers = activeUsers
| join kind=inner SigninLogs on UserPrincipalName
| summarize count() by UserPrincipalName
| top 10 by count_;

4. Efficient Joins

// GOOD — smaller table on the right, specific join kind
LargeTable
| where TimeGenerated > ago(1h)
| join kind=leftsemi SmallLookup on CommonKey

// BAD — no kind specified (defaults to innerunique, which deduplicates left), large table on right
SmallLookup
| join LargeTable on CommonKey

5. Use in for Multiple Values

// GOOD
SecurityEvent | where EventID in (4624, 4625, 4634, 4648)

// BAD
SecurityEvent | where EventID == 4624 or EventID == 4625 or EventID == 4634 or EventID == 4648

6. Limit Columns Early

// GOOD — project before expensive operations
SecurityEvent
| where TimeGenerated > ago(1d)
| project TimeGenerated, Account, Computer, EventID
| summarize count() by Account

// BAD — carries all 50+ columns through the pipeline
SecurityEvent
| where TimeGenerated > ago(1d)
| summarize count() by Account

---

Anti-patterns

1. Missing Time Filter

// BAD — scans entire retention (90 days+)
SecurityEvent | where EventID == 4625 | count

// GOOD
SecurityEvent | where TimeGenerated > ago(24h) | where EventID == 4625 | count

2. Unnecessary Case Conversion

// BAD — tolower is expensive and unnecessary
SigninLogs | where tolower(UserPrincipalName) == "user@company.com"

// GOOD — =~ is case-insensitive
SigninLogs | where UserPrincipalName =~ "user@company.com"

3. search * for Known Tables

// BAD — searches every table in the workspace
search "malware"

// GOOD — target specific tables
union SecurityEvent, SecurityAlert
| where * has "malware"

4. distinct + count Instead of dcount

// BAD — materializes all distinct values, then counts
SigninLogs | distinct UserPrincipalName | count

// GOOD — approximate count, much faster
SigninLogs | summarize dcount(UserPrincipalName)

5. Regex When Simpler Operators Work

// BAD — regex is expensive
Syslog | where SyslogMessage matches regex "^Failed"

// GOOD — startswith uses the index
Syslog | where SyslogMessage startswith "Failed"

6. Joining Without Filtering First

// BAD — joins full tables
SigninLogs | join AuditLogs on CorrelationId

// GOOD — filter both sides first
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != 0
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(1h)
    | where OperationName has "password"
) on CorrelationId

---

Security Hunting Patterns

Anomaly Detection — Rare Events

// Find processes that ran on only 1 machine (potential lateral movement)
let timeRange = 7d;
DeviceProcessEvents
| where Timestamp > ago(timeRange)
| summarize MachineCount=dcount(DeviceName) by FileName
| where MachineCount == 1
| join kind=inner (
    DeviceProcessEvents | where Timestamp > ago(timeRange)
) on FileName
| project Timestamp, DeviceName, FileName, ProcessCommandLine

Baseline Deviation

// Alert when login count exceeds 3x the 7-day average
let baseline = SigninLogs
| where TimeGenerated between (ago(8d) .. ago(1d))
| summarize AvgLogins=avg(count_) by UserPrincipalName
    | summarize AvgLogins=count() by UserPrincipalName;
let recent = SigninLogs
| where TimeGenerated > ago(1d)
| summarize RecentLogins=count() by UserPrincipalName;
recent
| join kind=inner baseline on UserPrincipalName
| where RecentLogins > AvgLogins * 3
| project UserPrincipalName, RecentLogins, AvgLogins, Ratio=round(RecentLogins * 1.0 / AvgLogins, 2)

IOC Matching

// Match network events against threat intelligence
let iocs = ThreatIntelligenceIndicator
| where Active == true
| where ExpirationDateTime > now()
| where isnotempty(NetworkIP)
| distinct NetworkIP;
CommonSecurityLog
| where TimeGenerated > ago(1d)
| where DestinationIP in (iocs) or SourceIP in (iocs)
| project TimeGenerated, SourceIP, DestinationIP, DeviceAction, Activity

Impossible Travel

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, Location=tostring(LocationDetails.city),
          Lat=todouble(LocationDetails.geoCoordinates.latitude),
          Lon=todouble(LocationDetails.geoCoordinates.longitude)
| sort by UserPrincipalName, TimeGenerated asc
| serialize
| extend PrevTime=prev(TimeGenerated), PrevLat=prev(Lat), PrevLon=prev(Lon), PrevUser=prev(UserPrincipalName)
| where UserPrincipalName == PrevUser
| extend TimeDiffHours = datetime_diff('hour', TimeGenerated, PrevTime)
| extend DistanceKm = geo_distance_2points(Lon, Lat, PrevLon, PrevLat) / 1000
| where TimeDiffHours > 0
| extend SpeedKmH = DistanceKm / TimeDiffHours
| where SpeedKmH > 1000  // faster than commercial flight
| project TimeGenerated, UserPrincipalName, Location, DistanceKm=round(DistanceKm, 0), TimeDiffHours, SpeedKmH=round(SpeedKmH, 0)

---

Time-series Patterns

Binned Aggregation

Perf
| where TimeGenerated > ago(24h)
| where CounterName == "% Processor Time"
| summarize AvgCPU=avg(CounterValue) by bin(TimeGenerated, 15m), Computer
| render timechart

Rolling Window

let data = SecurityEvent
| where TimeGenerated > ago(7d)
| summarize EventCount=count() by bin(TimeGenerated, 1h);
data
| order by TimeGenerated asc
| serialize
| extend Rolling4h = row_window_session(TimeGenerated, 4h, 4h)

Anomaly Detection with series_decompose_anomalies

let startTime = ago(14d);
let endTime = now();
SecurityEvent
| where TimeGenerated between (startTime .. endTime)
| where EventID == 4625
| make-series FailedLogins=count() on TimeGenerated from startTime to endTime step 1h
| extend (anomalies, score, baseline) = series_decompose_anomalies(FailedLogins, 1.5, -1, 'linefit')
| render anomalychart with (anomalycolumns=anomalies)

---

Data Enrichment Patterns

GeoIP Enrichment

SigninLogs
| where TimeGenerated > ago(1d)
| extend City = tostring(LocationDetails.city),
         State = tostring(LocationDetails.state),
         Country = tostring(LocationDetails.countryOrRegion),
         Lat = todouble(LocationDetails.geoCoordinates.latitude),
         Lon = todouble(LocationDetails.geoCoordinates.longitude)

JSON Parsing

AzureActivity
| where TimeGenerated > ago(1d)
| extend Props = parse_json(Properties)
| extend StatusCode = tostring(Props.statusCode),
         Resource = tostring(Props.resource)

External Data (ADX only)

let ipRanges = externaldata(CIDR:string, Owner:string, Description:string)
[@"https://raw.githubusercontent.com/org/repo/main/ip-ranges.csv"]
with (format="csv", ignoreFirstRecord=true);
CommonSecurityLog
| where TimeGenerated > ago(1h)
| evaluate ipv4_lookup(ipRanges, SourceIP, CIDR)

Azure Service Tables Reference

Table of Contents

• Log Analytics / Azure Monitor
• Microsoft Sentinel
• Application Insights
• Azure Data Explorer (ADX)

---

Log Analytics / Azure Monitor

Infrastructure & Compute

Azure Platform

Containers

Networking

Custom & Agent

---

Microsoft Sentinel

Sentinel extends Log Analytics — all tables above are available, plus these security-specific tables:

Identity & Access

Security Events

Threat Intelligence

Microsoft 365 Defender

Network Security

---

Application Insights

Key Columns (common across App Insights tables)

• timestamp — event time (NOT TimeGenerated)
• operation_Id — request correlation ID
• operation_ParentId — parent operation
• cloud_RoleName — service/app name
• cloud_RoleInstance — instance identifier
• client_IP — client IP address
• customDimensions — dynamic bag of custom properties

---

Azure Data Explorer (ADX)

ADX uses custom schemas — tables are defined per database. No predefined table names.

System Tables (available in every ADX database)

Common Patterns for ADX

• IoT/telemetry data often uses: Timestamp, DeviceId, Value, Metric
• Time-series tables commonly have: Timestamp (not TimeGenerated)
• Ingestion tracking: .show ingestion failures, .show operations

SQL to KQL Translation Reference

Concept Mapping

Translation Examples

Basic SELECT + WHERE + ORDER

SELECT EventID, Account, Computer, TimeGenerated
FROM SecurityEvent
WHERE EventID = 4625 AND TimeGenerated > '2024-01-01'
ORDER BY TimeGenerated DESC
LIMIT 100;

SecurityEvent
| where TimeGenerated > datetime(2024-01-01)
| where EventID == 4625
| project EventID, Account, Computer, TimeGenerated
| sort by TimeGenerated desc
| take 100

GROUP BY with HAVING

SELECT Account, COUNT(*) as LoginCount
FROM SecurityEvent
WHERE EventID = 4624
GROUP BY Account
HAVING COUNT(*) > 100
ORDER BY LoginCount DESC;

SecurityEvent
| where EventID == 4624
| summarize LoginCount=count() by Account
| where LoginCount > 100
| sort by LoginCount desc

JOIN

SELECT a.UserPrincipalName, a.ResultType, b.OperationName
FROM SigninLogs a
INNER JOIN AuditLogs b ON a.CorrelationId = b.CorrelationId
WHERE a.TimeGenerated > DATEADD(hour, -1, GETUTCDATE());

SigninLogs
| where TimeGenerated > ago(1h)
| join kind=inner (AuditLogs | where TimeGenerated > ago(1h)) on CorrelationId
| project UserPrincipalName, ResultType, OperationName

CTE / WITH

WITH FailedLogins AS (
    SELECT Account, COUNT(*) as Failures
    FROM SecurityEvent
    WHERE EventID = 4625
    GROUP BY Account
),
SuccessLogins AS (
    SELECT Account, COUNT(*) as Successes
    FROM SecurityEvent
    WHERE EventID = 4624
    GROUP BY Account
)
SELECT f.Account, f.Failures, s.Successes
FROM FailedLogins f
JOIN SuccessLogins s ON f.Account = s.Account;

let FailedLogins = SecurityEvent
| where EventID == 4625
| summarize Failures=count() by Account;
let SuccessLogins = SecurityEvent
| where EventID == 4624
| summarize Successes=count() by Account;
FailedLogins
| join kind=inner SuccessLogins on Account
| project Account, Failures, Successes

Subquery with EXISTS

SELECT DISTINCT Account
FROM SecurityEvent s1
WHERE EventID = 4625
AND EXISTS (
    SELECT 1 FROM SecurityEvent s2
    WHERE s2.Account = s1.Account AND s2.EventID = 4624
);

SecurityEvent
| where EventID == 4625
| distinct Account
| join kind=leftsemi (
    SecurityEvent | where EventID == 4624 | distinct Account
) on Account

Key Mindset Shifts

1. Pipes, not nesting — KQL flows top-to-bottom through pipes. Where SQL nests subqueries, KQL uses let statements or inline parenthesized expressions. 2. Table first — In SQL, FROM comes after SELECT. In KQL, the table name starts the query and data flows through operators. 3. *No SELECT ; use project — Always explicitly project the columns you need for readability and performance. 4. Time is a first-class citizen — `ago()`, `bin()`, timespan literals, and `TimeGenerated` are central to KQL in a way that datetime handling in SQL isn't. 5. Aggregation syntax** — summarize AggFunc() by GroupCol replaces SELECT AggFunc() ... GROUP BY GroupCol.

DebugOptimize Workflow

Steps

1. Read the query — Understand what it's trying to do before changing anything. Identify the target service.

2. Check for common issues — Read references/patterns.md for the anti-patterns checklist. Look for:

• Missing time filters (full table scan)
• contains where has would work (10x slower)
• * in project (unnecessary column transfer)
• Late filtering (WHERE after JOIN or SUMMARIZE)
• Unnecessary tolower()/toupper() — KQL string operators are case-insensitive by default
• search * instead of querying specific tables
• Cartesian joins (missing kind= specification)
• distinct + count instead of dcount()

3. Identify the bottleneck — Is it:

• Data volume: needs tighter time/column filters
• Join explosion: wrong join kind or missing key
• Serialization: unnecessary serialize or prev()/next() on large datasets
• Regex overuse: replace matches regex with has/startswith/endswith where possible

4. Apply fixes — Make the minimum changes needed. Show before/after with inline comments explaining each change.

5. Suggest query hints if applicable:

• hint.strategy=shuffle for large joins
• hint.num_partitions=N for parallel execution
• hint.materialized=true for reused subqueries
• materialize() to avoid recomputation of shared expressions

Performance Rules of Thumb

Tooling Workflow

Automate KQL queries via Azure CLI, REST API, SDKs, and alerting integrations.

Azure CLI

Log Analytics Query

# Run a query against a Log Analytics workspace
az monitor log-analytics query \
  --workspace <workspace-id> \
  --analytics-query "SecurityEvent | where TimeGenerated > ago(1h) | count" \
  --output table

# From a .kql file
az monitor log-analytics query \
  --workspace <workspace-id> \
  --analytics-query "$(cat my-query.kql)" \
  --output json > results.json

# With time range
az monitor log-analytics query \
  --workspace <workspace-id> \
  --analytics-query "SecurityEvent | count" \
  --timespan P7D \
  --output table

Application Insights Query

az monitor app-insights query \
  --app <app-id> \
  --analytics-query "requests | where timestamp > ago(1h) | summarize count() by resultCode"

ADX Query

# Using the Kusto CLI (az kusto is for cluster management, not queries)
# For queries, use the REST API or SDK
az kusto query \
  --cluster-name <cluster> \
  --database-name <db> \
  --query "TableName | take 10"

REST API

Log Analytics

POST https://api.loganalytics.io/v1/workspaces/{workspace-id}/query
Authorization: Bearer {token}
Content-Type: application/json

{
  "query": "SecurityEvent | where TimeGenerated > ago(1h) | count",
  "timespan": "PT1H"
}

ADX

POST https://{cluster}.{region}.kusto.windows.net/v1/rest/query
Authorization: Bearer {token}
Content-Type: application/json

{
  "db": "database-name",
  "csl": "TableName | take 10"
}

Python SDK

from azure.monitor.query import LogsQueryClient
from azure.identity import DefaultAzureCredential
from datetime import timedelta

credential = DefaultAzureCredential()
client = LogsQueryClient(credential)

response = client.query_workspace(
    workspace_id="<workspace-id>",
    query="SecurityEvent | where TimeGenerated > ago(1h) | count",
    timespan=timedelta(hours=1)
)

for table in response.tables:
    for row in table.rows:
        print(row)

Query Validation

Before running in production, validate queries:

1. Syntax check — Run with | take 0 appended to verify parsing without returning data 2. Row estimate — Run with | count first to gauge result size 3. Time-bound — Always include a time filter; without one, queries scan the full retention period 4. Column check — Use | getschema to verify expected columns exist in the table

Alert Rule Integration

Create a scheduled alert rule (Log Analytics)

az monitor scheduled-query create \
  --name "High Failed Logins" \
  --resource-group <rg> \
  --scopes "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.OperationalInsights/workspaces/<ws>" \
  --condition "count > 50" \
  --condition-query "SigninLogs | where ResultType != 0 | summarize count() by bin(TimeGenerated, 5m)" \
  --evaluation-frequency 5m \
  --window-size 5m \
  --severity 2 \
  --action-groups "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Insights/actionGroups/<ag>"

Export Patterns

# JSON export
az monitor log-analytics query --workspace <id> --analytics-query "$(cat query.kql)" -o json > output.json

# CSV export (via jq)
az monitor log-analytics query --workspace <id> --analytics-query "$(cat query.kql)" -o json \
  | jq -r '.[0] | (.[0] | keys_unsorted) as $cols | $cols, (.[] | [.[$cols[]]] | @csv)' > output.csv

# PowerShell export
Invoke-AzOperationalInsightsQuery -WorkspaceId <id> -Query (Get-Content query.kql -Raw) |
  Select-Object -ExpandProperty Results |
  Export-Csv -Path output.csv -NoTypeInformation

Translate Workflow

Converts SQL or Splunk SPL queries to idiomatic KQL.

Steps

1. Identify the source language — SQL or Splunk SPL. Read the query to understand its intent.

2. Map the concepts — Read references/sql-to-kql.md for the SQL mapping table. For SPL, use the mapping below.

3. Identify the target service — Ask which Azure service this will run on. This determines available tables and functions.

4. Translate — Don't do a literal 1:1 translation. Write idiomatic KQL that achieves the same result using KQL's strengths:

• Pipe-based flow instead of nested subqueries
• let statements instead of CTEs
• summarize instead of GROUP BY
• extend instead of computed columns in SELECT
• mv-expand instead of UNNEST/LATERAL
• Native time functions (ago(), bin()) instead of date arithmetic

5. Output as .kql file with the standard header. Note the original language in the description.

SPL to KQL Quick Reference

WriteQuery Workflow

Steps

1. Identify the target service — Ask or infer: Log Analytics, Sentinel, ADX, or Application Insights? This determines available tables and syntax.

2. Identify tables — Read references/service-tables.md if unsure which tables hold the data the user needs. Pick the narrowest table that covers the requirement.

3. Check for similar samples — Look in samples/ for queries targeting the same service/domain. Reuse patterns rather than starting from scratch.

4. Write the query using these KQL best practices:

• Start with let declarations for parameters (time range, thresholds, resource filters)
• Filter with where as early as possible — push time filters and equality checks first
• Use has instead of contains for whole-token string matching (10x faster)
• Use in instead of chained or for multiple value checks
• Project only needed columns with project or project-away to reduce data transfer
• For joins, put the smaller table on the right side
• Add | take 100 or | limit 100 during development to preview results

5. Format as a .kql file following the output format in the main SKILL.md — include the comment header block with title, service, tables, description, parameters, and complexity.

6. Explain the query — After the code block, provide a brief walkthrough of what each major step does, especially for intermediate/advanced queries. Mention any gotchas or service-specific behaviors.

Common Query Patterns

Aggregation with time bins

let timeRange = 24h;
TableName
| where TimeGenerated > ago(timeRange)
| summarize Count=count() by bin(TimeGenerated, 1h), ColumnName
| render timechart

Top-N analysis

TableName
| where TimeGenerated > ago(7d)
| summarize Count=count() by ColumnName
| top 10 by Count desc

Join pattern (small right table)

let lookupTable = materialize(
    SmallTable | where Condition | project Key, Value
);
LargeTable
| where TimeGenerated > ago(1d)
| join kind=inner lookupTable on Key

Multi-value expansion

TableName
| mv-expand parse_json(JsonColumn)
| evaluate bag_unpack(JsonColumn)