KQL basics: your first Log Analytics queries to filter, count and chart

#The goal

Someone says "check the logs", and you get a blank query window. Kusto Query Language (KQL) reads top to bottom: start with a table, then pipe it through one step at a time. Learn eight operators and you can answer most on-call questions.

Open your Log Analytics workspace in the portal and select Logs. You may need to switch to KQL mode from simple mode.

#Step 1: find out what data you have

This shows which tables hold data and how much they've taken in:

Usage
| where TimeGenerated > ago(1d)
| where IsBillable == true
| summarize GB = sum(Quantity) / 1024 by DataType
| order by GB desc

Common tables, if the related data sources are connected:

Look at a few rows and their column names:

Heartbeat
| take 10

take returns any 10 rows, in no particular order. It's only for looking at the shape of the data.

#Step 2: filter with where

Syslog
| where TimeGenerated > ago(1h)
| where SeverityLevel in ("err", "crit", "alert", "emerg")
| where Computer == "vm-app-01"

Useful comparison operators:

Prefer has to contains where it'll do the job. It uses the term index.

Tip

Always filter on TimeGenerated first. It's the largest single speed improvement you can make, and it stops you from scanning far more data than you need.

#Step 3: choose columns and sort

Syslog
| where TimeGenerated > ago(1h)
| where SeverityLevel == "err"
| project TimeGenerated, Computer, ProcessName, SyslogMessage
| order by TimeGenerated desc
| take 50

project keeps only the columns you name. To add a calculated column and keep the others, use extend:

Perf
| where TimeGenerated > ago(15m)
| where ObjectName == "Memory" and CounterName == "Available MBytes"
| extend AvailableGB = round(CounterValue / 1024, 1)
| project TimeGenerated, Computer, AvailableGB

Counter names differ between Windows and Linux, and with your collection settings. Check yours with:

Perf
| where TimeGenerated > ago(1h)
| distinct ObjectName, CounterName

#Step 4: count things with summarize

summarize is the operator you'll use most. It groups rows and calculates a result for each group.

Errors per server:

Syslog
| where TimeGenerated > ago(24h)
| where SeverityLevel == "err"
| summarize Errors = count() by Computer
| order by Errors desc

Servers that have stopped reporting:

Heartbeat
| summarize LastSeen = max(TimeGenerated) by Computer
| where LastSeen < ago(10m)
| order by LastSeen asc

Average and 95th percentile CPU:

Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize AvgCpu = avg(CounterValue), P95Cpu = percentile(CounterValue, 95) by Computer
| order by P95Cpu desc

The Heartbeat query has no TimeGenerated filter, so it uses the portal's time picker. Widen the range if a server may have been gone for days.

#Step 5: chart a trend

Use bin() to group by time, then render:

Syslog
| where TimeGenerated > ago(24h)
| where SeverityLevel == "err"
| summarize Errors = count() by bin(TimeGenerated, 15m), Computer
| render timechart

A timechart is the fastest way to answer "when did this start?", and whether it matches a deployment.

#Step 6: join two tables

Who changed something, and which resources were involved? Most of the time a single table is enough:

AzureActivity
| where TimeGenerated > ago(7d)
| where ActivityStatusValue == "Success"
| where OperationNameValue endswith "delete"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, _ResourceId
| order by TimeGenerated desc

When you need two tables, join them on a shared column. This finds servers with errors and adds their OS from Heartbeat:

Syslog
| where TimeGenerated > ago(1h) and SeverityLevel == "err"
| summarize Errors = count() by Computer
| join kind=leftouter (
    Heartbeat
    | where TimeGenerated > ago(1h)
    | summarize arg_max(TimeGenerated, OSType) by Computer
) on Computer
| project Computer, OSType, Errors

arg_max returns the row with the latest time for each computer.

#Run it from the CLI

Use the workspace GUID, called the customer ID:

WS=$(az monitor log-analytics workspace show -g rg-monitor -n law-prod --query customerId -o tsv)

az monitor log-analytics query --workspace "$WS" \
  --analytics-query "Heartbeat | summarize LastSeen = max(TimeGenerated) by Computer | take 5" \
  -o table

The command may ask to install an extension the first time you run it.

#Common mistakes

#Prevent it next time

• Save the queries you use often, and share them with your team.
• Turn the useful ones into alerts. See alerts that didn't fire for how to test them.
• If a table you expect is empty, check the agent and DCR setup.
• Next, try VM health queries and the Application Insights cheat sheet.