Azure Log Analytics Query Quick Start

This post is an Azure Log Analytics query quick start to get you up and running with queries in a few minutes. It follows on my previous post showing some of the common tasks performed in Azure Log Analytics. The official documentation can be found here.

Query Syntax

Queries in Azure Log Analytics start with a data table name followed by query operators and optionally rendering instructions. Data is piped from one operator to the next using a pipe sign.

Event
 | where Computer like "webserver" and EventID == 14
 | summarize count() by Computer
 | render barchart

Common Query Operators

Summarize : Similar to the SQL Group By statement it applies one or more aggregations with optionally one or more grouping expressions. For example to see how many events were logged per computer and when the last one was logged

Event
| summarize count(Computer), max(TimeGenerated) by Computer, EventLevelName

Distinct: Returns distinct values from one or more column(s)

Event | distinct Computer

Bin: Groups records into bins and works on numbers and dates. For example if you wanted to see how many events were logged per hour.

Event | summarize count(EventID) by bin(TimeGenerated, 1h)

Or group events by their eventid in bins of 1000.

Event | summarize count(EventID) by bin(EventID, 1000)

Or aggregate events by eventid and group on EventLevelName per day. For example to see the failed versus successful entries per day

Event | summarize count(EventID) by EventLevelName , bin(TimeGenerated, 1d)

Event | join kind=inner (SecurityEvent) on Computer

Let: Let stores a value in a variable, the values can be tabular query results or any user supplied value. The variable can then be used in queries. For example if you have to join results from two very long queries or store constants used in your queries.

let errors = Event | ... very long query ...| summarize Count = count(EventID) by Computer, bin(TimeGenerated, 1h);

let memory = Perf | ... very long query ... | summarize avg(CounterValue) by Computer, bin(TimeGenerated,1h);

errors | join kind= inner (memory) on TimeGenerated

Project: Selects the columns to include in the query result, just like SQL Select statement.

Parse: Parse text into columns, it is a long explanation link here. It is really handy to extract data into columns from free-form text like custom log files.

Event| where EventLog == "DPM Backup Events"| parse RenderedDescription with * "Backup job for datasource: " ServerName " on " *

In this example RenderedDescription looked like this:
“Backup job for datasource: SomeServerName on production server: SomeDPMServerName completed succcessfully…“.

The section between the qoutes are the “guide” string to look for, including the spaces and ServerName is new column name to extract.

Render: Renders a chart, too many options to mention here look at the documentation.

Event
| where Computer like "local" and EventID == 14
| summarize count() by Computer
| render piechart

Extend: Create calculated columns.

Event | extend RequiresAttention = iif(EventLevel == 4, "Yes", "No" )

• About
• Latest Posts

Francois Delport

I am a cloud and devops consultant, technology fan and previously a professional C# developer with a keen interest in system design and architecture. Currently I am involved in projects using Azure, the Microsoft stack and DevOps. I am based in Melbourne, Australia. Email: [email protected]

Latest posts by Francois Delport (see all)

• How To Convert A Linux Disk Or Image File To VMDK File - July 8, 2020
• Read SMART Attributes Using PowerShell And Smartmontools - February 10, 2020
• How To Install OMS Agent On Server Core - June 25, 2018