Exercise: Different types of KQL queries
Now, let's take what you learned about the structure and use of different types of query statements and write some queries.
Query with tabular expression statements
Tabular expression statements are fundamental in KQL as they allow us to filter and manipulate tabular data to return desired results.
Let's go through an example. Select the relevant tab for your environment.
Azure Data Explorer offers a help cluster with different types of data preloaded. You can access this cluster using the Azure Data Explorer web UI.
The following steps demonstrate how to build a query by applying operators to a starting tabular dataset. Each query is composed of tabular expression statements, some of which contain operators. Operators take a tabular input, perform an operation, and produce a new tabular output.
Start with a tabular dataset.
StormEvents
Output: The complete tabular dataset from the
StormEvents
table.Apply a filter using the
where
operator to select specific events, such as Flood events. Thewhere
operator filters the tabular dataset and preserves the tabular structure.StormEvents | where State == "FLORIDA"
Output: A tabular dataset of
StormEvents
records in the state of FLORIDA.Use another operator to further manipulate the tabular output.
StormEvents | where State == "FLORIDA" | sort by InjuriesDirect desc
Output: A tabular dataset of
StormEvents
records in FLORIDA sorted in descending order based on theInjuriesDirect
column.
Introduce a variable with a let statement
Let statements allow us to define variables in KQL queries, making them more readable and modular.
Let's go through an example. Select the relevant tab for your environment.
In the following query, state
and injuryThreshold
are variables that can be assigned values according to your specific requirements. These variables are then used within the query to filter the StormEvents
table based on the defined criteria.
let state = "TEXAS";
let injuryThreshold = 10;
StormEvents
| where State == state and InjuriesDirect + InjuriesIndirect > injuryThreshold