Matthias' Blog

Cheat Sheet - KQL

Debug ingestion failures with ADX 

.show ingestion failures 

.show ingestion failures with ( OperationId = <operationId> )

The easiest way (c) to search through exceptions

exceptions
| where cloud_RoleName == 'my-cloud-rolename' and ['details'] has `search-string` and timestamp > ago (14d) 

exceptions 
| where cloud_RoleName == 'my-cloud-rolename`
| search `my-search-string`

Find the most chatty App Insights data feeders

AppDependencies
| where _ResourceId endswith "<your-app-insights-resource>"
| summarize Count=count() by AppRoleName

Which dependency types are ingested the most into App Insights?

dependencies
| where timestamp > ago(48d)
| summarize Count=count() by ['type']

What happened a couple of seconds before? 

let window = 5s;
let eventTime = todatetime('2022-03-24T16:07:52.241Z');
exceptions
| union traces, requests, (dependencies | where type == "HTTP")
| where timestamp between ((eventTime - window) .. eventTime)
| order by timestamp desc

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 the time range covered by the 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 occurrences

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

Get all included categories

AzureDiagnostics
| distinct Category

Search all columns 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 a specific column

search Description:"value"

Filter on nested key 

myTable 
| where ["key1"]["key2"] == 'foobar'