Get started with log queries in Azure Monitor
If you're collecting data from at least one virtual machine, you can work through this exercise in your own environment. For other scenarios, use our demo environment, which includes plenty of sample data.
If you already know how to query in Kusto Query Language, but need to quickly create useful queries based on resource types, see the saved example queries pane in the Use queries in Azure Monitor Log Analytics article.
In this tutorial, you'll learn to write log queries in Azure Monitor. The article shows you how to:
- Understand query structure.
- Sort query results.
- Filter query results.
- Specify a time range.
- Select which fields to include in the results.
- Define and use custom fields.
- Aggregate and group results.
For a tutorial on using Log Analytics in the Azure portal, see Get started with Azure Monitor Log Analytics.
For more information about log queries in Azure Monitor, see Overview of log queries in Azure Monitor.
Here's a video version of this tutorial:
Write a new query
Queries can start with either a table name or the search command. It's a good idea to start with a table name, because it defines a clear scope for the query and improves both query performance and the relevance of the results.
The Kusto query language, which is used by Azure Monitor, is case-sensitive. Language keywords are usually written in lowercase. When you use names of tables or columns in a query, be sure to use the correct case, as shown on the schema pane.
Azure Monitor organizes log data in tables, each composed of multiple columns. All tables and columns are shown on the schema pane in Log Analytics in the Analytics portal. Identify a table that you're interested in, and then take a look at a bit of data:
SecurityEvent | take 10
The preceding query returns 10 results from the SecurityEvent table, in no specific order. This is a common way to take a glance at a table and understand its structure and content. Let's examine how it's built:
- The query starts with the table name SecurityEvent, which defines the scope of the query.
- The pipe (|) character separates commands, so the output of the first command is the input of the next. You can add any number of piped elements.
- Following the pipe is the take command, which returns a specific number of arbitrary records from the table.
We could actually run the query even without adding
| take 10. The command would still be valid, but it could return up to 10,000 results.
Search queries are less structured, and they're generally better suited for finding records that include a specific value in any of their columns:
search in (SecurityEvent) "Cryptographic" | take 10
This query searches the SecurityEvent table for records that contain the phrase "Cryptographic". Of those records, 10 records will be returned and displayed. If you omit the
in (SecurityEvent) part and run only
search "Cryptographic", the search will go over all tables, which would take longer and be less efficient.
Search queries are ordinarily slower than table-based queries because they have to process more data.
Sort and top
Although take is useful for getting a few records, the results are selected and displayed in no particular order. To get an ordered view, you could sort by the preferred column:
SecurityEvent | sort by TimeGenerated desc
The preceding query could return too many results, however, and might also take some time. The query sorts the entire SecurityEvent table by the TimeGenerated column. The Analytics portal then limits the display to only 10,000 records. This approach is of course not optimal.
The best way to get only the latest 10 records is to use top, which sorts the entire table on the server side and then returns the top records:
SecurityEvent | top 10 by TimeGenerated
Descending is the default sorting order, so you would usually omit the desc argument. The output looks like this:
The where operator: filtering on a condition
Filters, as indicated by their name, filter the data by a specific condition. This is the most common way to limit query results to relevant information.
To add a filter to a query, use the where operator followed by one or more conditions. For example, the following query returns only SecurityEvent records where Level equals 8:
SecurityEvent | where Level == 8
When you write filter conditions, you can use the following expressions:
|!=, <>||Check inequality
(both expressions are identical)
|and, or||Required between conditions||
To filter by multiple conditions, you can use either of the following approaches:
Use and, as shown here:
SecurityEvent | where Level == 8 and EventID == 4672
Pipe multiple where elements, one after the other, as shown here:
SecurityEvent | where Level == 8 | where EventID == 4672
Values can have different types, so you might need to cast them to perform comparisons on the correct type. For example, the SecurityEvent Level column is of type String, so you must cast it to a numerical type, such as int or long, before you can use numerical operators on it, as shown here:
SecurityEvent | where toint(Level) >= 10
Specify a time range
Use the time picker
The time picker is displayed next to the Run button and indicates that you’re querying records from only the last 24 hours. This is the default time range applied to all queries. To get records from only the last hour, select Last hour, and then run the query again.
Add a time filter to the query
You can also define your own time range by adding a time filter to the query. It’s best to place the time filter immediately after the table name:
SecurityEvent | where TimeGenerated > ago(30m) | where toint(Level) >= 10
In the preceding time filter,
ago(30m) means "30 minutes ago," which means that this query returns records from only the last 30 minutes (expressed as, for example, 30m). Other units of time include days (for example, 2d) and seconds (for example, 10s).
Use project and extend to select and compute columns
Use project to select specific columns to include in the results:
SecurityEvent | top 10 by TimeGenerated | project TimeGenerated, Computer, Activity
The preceding example generates the following output:
You can also use project to rename columns and define new ones. The next example uses project to do the following:
- Select only the Computer and TimeGenerated original columns.
- Display the Activity column as EventDetails.
- Create a new column named EventCode. The substring() function is used to get only the first four characters from the Activity field.
SecurityEvent | top 10 by TimeGenerated | project Computer, TimeGenerated, EventDetails=Activity, EventCode=substring(Activity, 0, 4)
You can use extend to keep all original columns in the result set and define additional ones. The following query uses extend to add the EventCode column. This column might not be displayed at the end of the table results, in which case you would need to expand the details of a record to view it.
SecurityEvent | top 10 by TimeGenerated | extend EventCode=substring(Activity, 0, 4)
Use summarize to aggregate groups of rows
Use summarize to identify groups of records according to one or more columns, and apply aggregations to them. The most common use of summarize is count, which returns the number of results in each group.
The following query reviews all Perf records from the last hour, groups them by ObjectName, and counts the records in each group:
Perf | where TimeGenerated > ago(1h) | summarize count() by ObjectName
Sometimes it makes sense to define groups by multiple dimensions. Each unique combination of these values defines a separate group:
Perf | where TimeGenerated > ago(1h) | summarize count() by ObjectName, CounterName
Another common use is to perform mathematical or statistical calculations on each group. The following example calculates the average CounterValue for each computer:
Perf | where TimeGenerated > ago(1h) | summarize avg(CounterValue) by Computer
Unfortunately, the results of this query are meaningless, because we mixed together a variety of performance counters. To make the results more meaningful, calculate the average separately for each combination of CounterName and Computer:
Perf | where TimeGenerated > ago(1h) | summarize avg(CounterValue) by Computer, CounterName
Summarize by a time column
Grouping results can also be based on a time column, or another continuous value. Simply summarizing
by TimeGenerated, though, would create groups for every single millisecond over the time range, because these are unique values.
To create groups that are based on continuous values, it's best to break the range into manageable units by using bin. The following query analyzes Perf records that measure free memory (Available MBytes) on a specific computer. It calculates the average value of each 1-hour period over the last 7 days:
Perf | where TimeGenerated > ago(7d) | where Computer == "ContosoAzADDS2" | where CounterName == "Available MBytes" | summarize avg(CounterValue) by bin(TimeGenerated, 1h)
To make the output clearer, you can select to display it as a time chart, which shows the available memory over time:
- To learn more about using string data in a log query, see Work with strings in Azure Monitor log queries.
- To learn more about aggregating data in a log query, see Advanced aggregations in Azure Monitor log queries.
- To learn how to join data from multiple tables, see Joins in Azure Monitor log queries.
- Get documentation on the entire Kusto query language in the KQL language reference.