KQL Cheat Sheet

kql-cheat-sheet.md

Get AKS pods logs

let startTimestamp = ago(1h);
KubePodInventory
| where TimeGenerated > startTimestamp
| project ContainerID, PodName=Name
| distinct ContainerID, PodName
| join
(
    ContainerLog
    | where TimeGenerated > startTimestamp
)
on ContainerID
// at this point before the next pipe, columns from both tables are available to be "projected". Due to both 
// tables having a "Name" column, we assign an alias as PodName to one column which we actually want
| project TimeGenerated, PodName, LogEntry, LogEntrySource
| order by TimeGenerated desc

Search within custom dimensions

traces
| where timestamp > ago(5d)
| where cloud_RoleName == "foo-service"
| where customDimensions["X-B3-TraceId"] == "foobar"

Mimic group by

let window = 5m;
let eventTime = todatetime('2021-12-22T15:13:38.544Z');
traces
| union exceptions
| where timestamp between ((eventTime - window) .. (eventTime + window))
| order by timestamp desc
| summarize entries = make_list(pack_all()) by cloud_RoleName

Search within a time window

let window = 1m;
let eventTime = todatetime("2021-12-22T15:13:00");
traces
| union exceptions
| where timestamp between ((eventTime - window) .. (eventTime + window))
| order by timestamp desc

Describe table schema

traces 
| getschema 

Find timerange covered by dataset

exceptions
| where timestamp > ago(365d) 
| summarize min(timestamp), max(timestamp)

Only failed requests

requests
| success == false and resultCode != 200

Search multiple tables

traces 
| union exceptions 
| where ... 

Which cloud role emitted a specific message?

traces
| search "foobar"
| distinct cloud_RoleName

Find earliest and latest occurances

traces
| where cloud_RoleName == "foo-service"
| where message has "bar"
| summarize min(timestamp), max(timestamp)

Get all included categories

AzureDiagnostics
| distinct Category

Search all column in the table for a value

search "value" 

List of all tables where the search has matches

search "value 
| distinct $table

Limit search to specific tables

search in (Table1, Table2) "value"

Limit search to specific column

search Description:"value"