Exercise: Different types of KQL queries

4 minutes

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. This cluster can be accessed using the Azure Data Explorer web UI.

Azure Data Explorer help cluster

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.

Run the query
```
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. The where operator filters the tabular dataset and preserves the tabular structure.

Run the query
```
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.

Run the query
```
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.

Microsoft Sentinel and Log Analytics in Azure Monitor both use the demo environment that is accessed by searching for and selecting Logs in the Azure portal.

Log Analytics demo environment

Start with a tabular dataset.

Run the query
```
LAQueryLogs
```
Output: The complete tabular dataset from the LAQueryLogs table.
Apply a filter using the where operator to select specific events. The where operator filters the tabular dataset and preserves the tabular structure.

Run the query
```
LAQueryLogs
| where ResponseCode != 200
```
Output: A tabular dataset of LAQueryLogs records whose response code isn't 200.
Use another operator to further manipulate the tabular output.

Run the query
```
LAQueryLogs
| where ResponseCode != 200
| sort by ResponseDurationMs desc
```
Output: A tabular dataset of LAQueryLogs records whose response code isn't 200 sorted in descending order based on the ResponseDurationMs.

The Azure Resource Graph Explorer is accessed by searching for and selecting Resource Graph Explorer in the Azure portal.

Azure Resource Graph Explorer

Start with a tabular dataset.
```
resources
```
Output: The complete tabular dataset from the resources table.
Apply a filter using the where operator to select specific events. The where operator filters the tabular dataset and preserves the tabular structure.
```
resources
| where location == "eastus"
```
Output: A tabular dataset of resources in the eastus region.
Use another operator to further manipulate the tabular output.
```
resources
| where location == "eastus"
| distinct subscriptionId
```
Output: A tabular dataset of subscription IDs with resources in the eastus region.

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.

Run the query

let state = "TEXAS";
let injuryThreshold = 10;
StormEvents
| where State == state and InjuriesDirect + InjuriesIndirect > injuryThreshold

In the following query, responseCodes is a variable of type dynamic array that contains response codes. The variable is then used within the query to filter the LAQueryLogs table for logs with those response codes.

Run the query

let responseCodes=dynamic([400, 499]);
LAQueryLogs
| where ResponseCode in (responseCodes)
| sort by ResponseDurationMs desc

Continue

Query with tabular expression statements

Introduce a variable with a let statement

Feedback