Get started with log queries in Azure Monitor

This tutorial shows you how to:

• Structure a query.
• Sort query results.
• Filter query results.
• Specify a time range.
• Include or exclude columns in query 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.

Note

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 (KQL) but need to quickly create useful queries based on resource types, see the saved example queries pane in Use queries in Azure Monitor Log Analytics.

Tutorial video

Note

This video shows an earlier version of the user interface, but the screenshots throughout this article are up to date and reflect the current UI.

Permissions required

You must have Microsoft.OperationalInsights/workspaces/query/*/read permissions to the Log Analytics workspaces you query, as provided by the Log Analytics Reader built-in role, for example.

Structure a 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. It also improves query performance and the relevance of the results.

Note

KQL, 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.

Table-based queries

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 Azure portal.

Identify a table that you're interested in, 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 common way to get a glance at a table helps you to 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 operator. We could run the query even without adding | take 10. The command would still be valid, but it could return up to 30,000 results.

Search queries

Search queries are less structured. They're best suited for finding records that include a specific value in any of the columns of a certain table.

KQL mode

This query searches the SecurityEvent table for records that contain the phrase "Cryptographic." Of those records, 10 records are returned and displayed:

search in (SecurityEvent) "Cryptographic"
| take 10

If you omit the in (SecurityEvent) part and run only search "Cryptographic", the search goes over all tables. The process would then take longer and be less efficient.

Important

Search queries are ordinarily slower than table-based queries because they have to process more data.

Simple mode

To search for records that include a specific value in any of their columns:

1. Select a table and chose SecurityEvent.

2. Open Add > Search in table.

   

3. Enter Cryptographic, then select Apply.

   

4. To only show 10 results, see Limit results in simple mode.

Important

We recommend using Filter if you know which column holds the data you're searching for. The search operator is substantially less performant than filtering, and might not function well on large volumes of data.

Limit results

KQL mode

Use the take operator to view a small sample of records by returning up to the specified number of records. For example:

SecurityEvent
| take 10

The selected results are arbitrary and displayed in no particular order. If you need to return results in a particular order, use the sort and top operators.

Simple mode

To return up to a specific number of records, you can limit the results:

1. Select Show to open the Show results window.

2. Pick one of the preset limits or enter a custom limit, then select Apply.

Sort results

This section describes the sort and top operators and their desc and asc arguments. Although take is useful for getting a few records, you can't select or sort the results in any particular order. To get an ordered view, use sort and top.

Sort

KQL mode

You can use the sort operator to sort the query results by the column you specify. However, sort doesn't limit the number of records that are returned by the query.

For example, the following query returns all available records for the SecurityEvent table, which is up to a maximum of 30,000 records, and sorts them by the TimeGenerated column.

SecurityEvent	
| sort by TimeGenerated

The preceding query could return too many results. Also, it might also take some time to return the results. The query sorts the entire SecurityEvent table by the TimeGenerated column. The Analytics portal then limits the display to only 30,000 records. This approach isn't optimal. The best way to only get the latest records is to use the top operator.

Desc and asc

Use the desc argument to sort records in descending order. Descending is the default sorting order for sort and top, so you can usually omit the desc argument.

For example, the data returned by both of the following queries is sorted by the TimeGenerated column, in descending order:

• SecurityEvent	
  | sort by TimeGenerated desc
  

• SecurityEvent	
  | sort by TimeGenerated
  

To sort in ascending order, specify asc.

Simple mode

To sort your results:

1. Open Add > Sort.

   

2. Pick a column to sort by.

3. Choose Ascending or Descending, then select Apply.

   

4. Open Add > Sort again to sort by another column.

Top

KQL mode

Use the top operator to sort the entire table on the server side and then only return the top records.

For example, the following query returns the latest 10 records:

SecurityEvent
| top 10 by TimeGenerated

The output looks like this example:

Simple mode

In simple mode, there's no direct equivalent to the top operator. Instead, you can sort a column and then limit the results.

Filter results

Filtering is the most common way to limit query results to relevant information.

KQL mode

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:

Expression	Description	Example
==	Check equality (case-sensitive)	Level == 8
=~	Check equality (case-insensitive)	EventSourceName =~ "microsoft-windows-security-auditing"
!=, <>	Check inequality (both expressions are identical)	Level != 4
and, or	Required between conditions	Level == 16 or CommandLine != ""

Simple mode

To filter results in the SecurityEvent table to only show records where the Level equals 8:

1. Open Add and choose the column Level.

   

2. In the Operator dropdown list, choose Equals. Enter the number 8 in the field below, then select Apply.

   

Filter by multiple conditions

KQL mode

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

Note

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

Simple mode

To filter by multiple conditions, you can add additional filters:

1. Open Add and choose the column EventID.

2. In the Operator dropdown list, choose Equals. Enter the number 4672 in the field below, then select Apply.

---

Specify a time range

KQL mode

You can specify a time range by using the time picker or a time filter.

Note

If you include a time range in the query, the time picker automatically changes to Set in query. If you manually change the time picker to a different value, Log Analytics applies the smaller of the two time ranges.

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 default time range is 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." This query returns records from only the last 30 minutes, which is expressed as, for example, 30m. Other units of time include days (for example, 2d) and seconds (for example, 10s).

Simple mode

The time picker is displayed next to the Run button and indicates that you're querying records from only the last 24 hours. This default time range is applied to all queries. To get records from only the last hour, select Last hour and then run the query again.

Include or exclude columns in query results

KQL mode

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)

Simple mode

You can manually select the columns you want to show in your results:

1. Open Add > Show columns.

   

2. Deselect All, then select the columns TimeGenerated, Computer, and Activity.

   

3. Select Apply.

Define and use custom fields

KQL mode

You can use extend to keep all original columns in the result set and define other ones. The following query uses extend to add the EventCode column. This column might not be displayed at the end of the table results. You would need to expand the details of a record to view it.

SecurityEvent
| top 10 by TimeGenerated
| extend EventCode=substring(Activity, 0, 4)

Note

Use the extend operator for ad hoc computations in queries. Use ingestion-time transformations or summary rules to transform or aggregate data at ingestion time for more efficient queries.

Simple mode

In simple mode, there's no direct equivalent to the extend operator.

Aggregate and group results

Aggregate groups of rows

KQL mode

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

Simple mode

To review all Perf records from the last, group them by ObjectName, and count the records in each group:

1. Open Time range and change it to Last hour.

2. Open Add > Aggregate, then make the following selection and select Apply:

   • Select column: ObjectName
   • Operator: count

Group unique combinations of values in multiple columns

KQL mode

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

Simple mode

Currently, it's not possible to define groups by multiple dimensions in simple mode.

Perform mathematical or statistical calculations

KQL mode

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 different 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

Simple mode

To calculate the average CounterValue for each computer:

1. Open Time range and change it to Last hour.

2. Open Add > Aggregate, then make the following selection and select Apply:

   • Select column: Computer
   • Operator: avg
   • Average: CounterValue

Unfortunately, the results of this query are meaningless because we mixed together different performance counters. To make the results more meaningful, you could calculate the average separately for each combination of CounterName and Computer.

However, it's currently not possible to define groups by multiple dimensions in simple mode. Switch to the KQL mode tab to see how this can be done using a Kusto query.

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 values are unique.

KQL mode

To create groups 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 == "DC01.na.contosohotels.com" 
| 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 do so, switch to Chart view, open the Chart formatting sidebar to the right and select Line for Chart type:

Simple mode

1. Select the Perf table.

2. Open Time range and change it to Last 7 days.

3. Open Add, select Computer, then check DC01.na.contosohotels.com and select Apply.

   Note

   If DC01.na.contosohotels.com doesn't show, increase the shown results from 1000 (standard) to a higher number.

4. Open Add and select CounterName, then check Available MBytes and select Apply.

5. Open Add > Aggregate and make the following selection, then select Apply.

   • Select column: TimeGenerated
   • Operator: avg
   • Average: CounterValue

6. Switch to Chart view, open the Chart formatting sidebar to the right and select Line for Chart type:

   

Frequently asked questions

This section provides answers to common questions.

Why am I seeing duplicate records in Azure Monitor Logs?

Occasionally, you might notice duplicate records in Azure Monitor Logs. This duplication is typically from one of the following two conditions:

• Components in the pipeline have retries to ensure reliable delivery at the destination. Occasionally, this capability might result in duplicates for a small percentage of telemetry items.
• If the duplicate records come from a virtual machine, you might have both the Log Analytics agent and Azure Monitor Agent installed. If you still need the Log Analytics agent installed, configure the Log Analytics workspace to no longer collect data that's also being collected by the data collection rule used by Azure Monitor Agent.

Next steps

• 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.