Azure Log Analytics – meet our new query language

Azure Log Analytics has recently been enhanced to work with a new query language. The query language itself actually isn’t new at all, and has been used extensively by Application Insights for some time. Recently, the language and the platform it operates on have been integrated into Log Analytics, which allows us to introduce a wealth of new capabilities, and a new portal designed for advanced analytics.

This post reviews some of the cool new features now supported. It’s just the tip of the iceberg though, and you're invited to also review the tutorials on our language site and our Log Analytics community space. The examples shown throughout the post can also be run in our Log Analytics playground – a free demo environment you can always use, no registration needed.

Pipe-away

Queries collect data, stored in one or more tables. Check out this basic query:

Event

This is as simple as you can get, but it's still a valid query, that simply returns everything in the Event table. Grabbing every record in a table usually means way too many results though. When analyzing data, a common first step is to review just a bunch of records from a table, and plan how to zoom in on relevant data. This is easily done with “take”:

Event
| take 10

This is the general structure of queries – multiple elements separated by pipes. The output of the first element (i.e the entire Event table) is the input of the next one. In this case, the final query output will be 10 records from the Event table. After reviewing them, we can decide how to make our query more specific. Often, we will use where to filter by a specific condition, such as this:

Event
| where EventLevelName == "Error"

This query will return all records in the table, where EventLevelName equals “Error” (case sensitive).

Looks like our query still returns a lot of records though. To make sense of all that data, we can use summarize. Summarize identifies groups of records by a common value, and can also apply aggregations to each group.

This example calculates PST_time which is based on TimeGenerated, but adapted from UTC to PST time zone. The query uses the new field to filter only records created between 2017-09-17 at 4 AM and 2017-09-18 at 4 AM, PST time.

A similar operator is project. Instead of adding the calculated field to the results set, project keeps only the projected fields. In this example, the results will have only four columns:

This counts how many of SecurityEvent records were created in the last hour per computer.

The common field we matched on is Computer, so eventually we get a list of computers that each has a list of missing security updates, and the total number of security events in the last hour.

The default visualization for most queries is a table. To visualize the data graphically, add "| render barchart” at the end of the query, or select the Chart button shown above the results. The outcome can help us decide how to manage our next updates:

We can see that the most required update is 2017-09 Cumulative Update for Windows Server and that the 1st computer to handle should probably be ContosoAzADDS1.ContosoRetail.com.

Joins have many flavors - inner, outer, semi, etc. These flavors define how matching should be performed and what the output should be. To learn more on joins, review our joins tutorial.