Exercise: Different types of KQL queries

Completed

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.

  1. Start with a tabular dataset.

    StormEvents
    

    Output: The complete tabular dataset from the StormEvents table.

  2. Apply a filter using the where operator to select specific events, such as Flood events. The where 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.

  3. 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 the InjuriesDirect 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