Edit

Share via


Tutorial: Learn common operators

Applies to: ✅ Microsoft FabricAzure Data ExplorerAzure MonitorMicrosoft Sentinel

Kusto Query Language (KQL) is used to write queries in Azure Data Explorer, Azure Monitor Log Analytics, Azure Sentinel, and more. This tutorial is an introduction to the essential KQL operators used to access and analyze your data.

For more specific guidance on how to query logs in Azure Monitor, see Get started with log queries.

Note

Can't find what you're looking for? This article was recently split, as follows:

In this tutorial, you'll learn how to:

The examples in this tutorial use the StormEvents table, which is publicly available in the help cluster. To explore with your own data, create your own free cluster.

The examples in this tutorial use the StormEvents table, which is publicly available in the Weather analytics sample data.

Prerequisites

To run the following queries, you need a query environment with access to the sample data. You can use one of the following:

  • A Microsoft account or Microsoft Entra user identity to sign in to the help cluster

Count rows

Begin by using the count operator to find the number of storm records in the StormEvents table.

StormEvents 
| count

Output

Count
59066

See a sample of data

To get a sense of the data, use the take operator to view a sample of records. This operator returns a specified number of arbitrary rows from the table, which can be useful for previewing the general data structure and contents.

StormEvents 
| take 5

The following table shows only 5 of the 22 returned columns. To see the full output, run the query.

StartTime EndTime EpisodeId EventId State EventType ...
2007-09-20T21:57:00Z 2007-09-20T22:05:00Z 11078 60913 FLORIDA Tornado ...
2007-12-20T07:50:00Z 2007-12-20T07:53:00Z 12554 68796 MISSISSIPPI Thunderstorm Wind ...
2007-12-30T16:00:00Z 2007-12-30T16:05:00Z 11749 64588 GEORGIA Thunderstorm Wind ...
2007-09-29T08:11:00Z 2007-09-29T08:11:00Z 11091 61032 ATLANTIC SOUTH Waterspout ...
2007-09-18T20:00:00Z 2007-09-19T18:00:00Z 11074 60904 FLORIDA Heavy Rain ...

Select a subset of columns

Use the project operator to simplify the view and select a specific subset of columns. Using project is often more efficient and easier to read than viewing all columns.

StormEvents
| take 5
| project State, EventType, DamageProperty

Output

State EventType DamageProperty
ATLANTIC SOUTH Waterspout 0
FLORIDA Heavy Rain 0
FLORIDA Tornado 6200000
GEORGIA Thunderstorm Wind 2000
MISSISSIPPI Thunderstorm Wind 20000

List unique values

It appears that there are multiple types of storms based on the results of the previous query. Use the distinct operator to list all of the unique storm types.

StormEvents 
| distinct EventType

There are 46 types of storms in the table. Here's a sample of 10 of them.

EventType
Thunderstorm Wind
Hail
Flash Flood
Drought
Winter Weather
Winter Storm
Heavy Snow
High Wind
Frost/Freeze
Flood
...

Filter by condition

The where operator filters rows of data based on certain criteria.

The following query looks for storm events in a specific State of a specific EventType.

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| project StartTime, EndTime, State, EventType, DamageProperty

There are 146 events that match these conditions. Here's a sample of 5 of them.

StartTime EndTime State EventType DamageProperty
2007-01-13T08:45:00Z 2007-01-13T10:30:00Z TEXAS Flood 0
2007-01-13T09:30:00Z 2007-01-13T21:00:00Z TEXAS Flood 0
2007-01-13T09:30:00Z 2007-01-13T21:00:00Z TEXAS Flood 0
2007-01-15T22:00:00Z 2007-01-16T22:00:00Z TEXAS Flood 20000
2007-03-12T02:30:00Z 2007-03-12T06:45:00Z TEXAS Flood 0
... ... ... ... ...

Sort results

To view the top floods in Texas that caused the most damage, use the sort operator to arrange the rows in descending order based on the DamageProperty column. The default sort order is descending. To sort in ascending order, specify asc.

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| sort by DamageProperty
| project StartTime, EndTime, State, EventType, DamageProperty

Output

StartTime EndTime State EventType DamageProperty
2007-08-18T21:30:00Z 2007-08-19T23:00:00Z TEXAS Flood 5000000
2007-06-27T00:00:00Z 2007-06-27T12:00:00Z TEXAS Flood 1200000
2007-06-28T18:00:00Z 2007-06-28T23:00:00Z TEXAS Flood 1000000
2007-06-27T00:00:00Z 2007-06-27T08:00:00Z TEXAS Flood 750000
2007-06-26T20:00:00Z 2007-06-26T23:00:00Z TEXAS Flood 750000
... ... ... ... ...

Get the top n rows

The top operator returns the first n rows sorted by the specified column.

The following query returns the five Texas floods that caused the most damaged property.

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| top 5 by DamageProperty
| project StartTime, EndTime, State, EventType, DamageProperty

Output

StartTime EndTime State EventType DamageProperty
2007-08-18T21:30:00Z 2007-08-19T23:00:00Z TEXAS Flood 5000000
2007-06-27T00:00:00Z 2007-06-27T12:00:00Z TEXAS Flood 1200000
2007-06-28T18:00:00Z 2007-06-28T23:00:00Z TEXAS Flood 1000000
2007-06-27T00:00:00Z 2007-06-27T08:00:00Z TEXAS Flood 750000
2007-06-26T20:00:00Z 2007-06-26T23:00:00Z TEXAS Flood 750000

Note

The order of the operators is important. If you put top before where here, you'll get different results. This is because the data is transformed by each operator in order. To learn more, see tabular expression statements.

Create calculated columns

The project and extend operators can both create calculated columns.

Use project to specify only the columns you want to view, and use extend to append the calculated column to the end of the table.

The following query creates a calculated Duration column with the difference between the StartTime and EndTime. Since we only want to view a few select columns, using project is the better choice in this case.

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| top 5 by DamageProperty desc
| project StartTime, EndTime, Duration = EndTime - StartTime, DamageProperty

Output

StartTime EndTime Duration DamageProperty
2007-08-18T21:30:00Z 2007-08-19T23:00:00Z 1.01:30:00 5000000
2007-06-27T00:00:00Z 2007-06-27T12:00:00Z 12:00:00 1200000
2007-06-28T18:00:00Z 2007-06-28T23:00:00Z 05:00:00 1000000
2007-06-27T00:00:00Z 2007-06-27T08:00:00Z 08:00:00 750000
2007-06-26T20:00:00Z 2007-06-26T23:00:00Z 03:00:00 750000

If you take a look at the computed Duration column, you may notice that the flood that caused the most damage was also the longest flood.

Use extend to view the calculated Duration column along with all of the other columns. The Duration column is added as the last column.

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| top 5 by DamageProperty desc
| extend Duration = EndTime - StartTime

Output

StartTime EndTime ... Duration
2007-08-18T21:30:00Z 2007-08-19T23:00:00Z ... 1.01:30:00
2007-06-27T00:00:00Z 2007-06-27T12:00:00Z ... 12:00:00
2007-06-28T18:00:00Z 2007-06-28T23:00:00Z ... 05:00:00
2007-06-27T00:00:00Z 2007-06-27T08:00:00Z ... 08:00:00
2007-06-26T20:00:00Z 2007-06-26T23:00:00Z ... 03:00:00

Map values from one set to another

Static mapping is a useful technique for changing the presentation of your results. In KQL, one way to perform static mapping is by using a dynamic dictionary and accessors to map values from one set to another.

let sourceMapping = dynamic(
  {
    "Emergency Manager" : "Public",
    "Utility Company" : "Private"
  });
StormEvents
| where Source == "Emergency Manager" or Source == "Utility Company"
| project EventId, Source, FriendlyName = sourceMapping[Source]

Output

EventId Source FriendlyName
68796 Emergency Manager Public
... ... ...
72609 Utility Company Private
... ... ...

Next step

Now that you're familiar with the essentials of writing Kusto queries, go on to the next tutorial and learn how to use aggregation functions to gain deeper insight into your data.