Must Learn KQL Part 17: The Let Statement

Chapter 17

This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…

The full series index (including code and queries) is located here:

https://aka.ms/MustLearnKQL

The book version (pdf) of this series is located here:

https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version

The book will be updated when each new part in this series is released.

…

Going way back to part 3 when I talked about the standard workflow, you might remember me saying…

Even though the structure can deviate, understanding a common workflow of a KQL query can have powerful results and help you develop the logic needed to build your own workflows when it’s time to create your own queries.

Rod Trent,  November 19, 2021

In this part/chapter of the Must learn KQL series, I’m going to focus on one of those deviations. As you’ll see, the Let statement can deviate from the norm because it’s generally assumed that it is positioned before the query event begins because of what it does.

So, what does the Let statement do?

The easiest way to put it is that it simply allows you to create variables. This makes sense to a lot of folks who script or program and it’s not dissimilar.

These variables are stored in memory during query execution and can be used throughout the rest of the query. It’s considered a best practice and is used for developing better performing queries and query code re-use.

Most generally the Let statement will show up at the beginning of the main query, as shown in the following slight modification of the original workflow we talked about in Part 3.

Normal location of the Let statement

Another important thing to note is that the statement must be finalized. It is a statement, after all. The Let statement ends with the semicolon (;) character. This tells the query engine that the variable has been created and needs to be stored before carrying on with the rest of the query.

The best way to understand this, is to just dig into some examples. Please use the KQL Playground (https://aka.ms/LADemo) to get hands-on for the following types of Let statements.

Creating Variables from Scratch

The first method of using the Let statement is simply to generate your own data. In the following example, I’ve created a timeOffset variable that provides a time value of 7 days, I’ve created another variable called discardEventID that sets our Event ID to 4688 which records when a new process on a computer has been spawned.

The timeOffset is used to create a time range of between 7 and 14 days in which to look at data. The discardEventID is used to show everything BUT 4688 in the results.

let timeOffset = 7d; //Setting the offset variable
let discardEventId = 4688; //assigning new process as the event ID
SecurityEvent //the table
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset) //Setting a specific time range of between 7 and 14 days
| where EventID != discardEventId //showing all events but 4688

Creating Variables from Existing Data

Another type of Let statement is one that pulls data from existing tables. Essentially, you turn the results of a query into a variable. The following example assigns one query to the login variable. The second query assigns results to the logout variable. And, then to wrap up the full results, the query then merges (joins – which I’ll cover just a bit later in this series) both variables (login and logout) to show login and logout times for all accounts.

let login = SecurityEvent //Setting the login variable based on a full query
| where TimeGenerated > ago(1h) //look at records in the last hour
| where EventID == '4624' //setting the event ID to successful login
| project Account, TargetLogonId, loginTime = TimeGenerated; //creating the full output, notice the semicolon to end the let statement
let logout = SecurityEvent //Setting the logout variable based on a full query
| where TimeGenerated > ago(1h) //look at records in the last hour
| where EventID == '4634' //setting the event ID to successful logoff
| project Account, TargetLogonId, logoutTime = TimeGenerated; //creating the full output, notice the semicolon to end the let statement
login //Accessing the login output
| join kind=leftouter logout on TargetLogonId //joining login output with logout output
| project Account, loginTime, logoutTime //Showing login and logout times for each account

Creating Variables from Microsoft Sentinel Watchlists

And, finally, Microsoft Sentinel customers should know that using the Let statement enables them to use the Watchlist feature with their Analytics Rules.

Now, I apologize for this, but the following examples cannot be used with the KQL Playground (https://aka.ms/LADemo) because the KQL Playground is not enabled for Microsoft Sentinel. As a Microsoft Sentinel customer, you can use these in your own Sentinel environment. However, notice that I have a Watchlist called FeodoTracker – you probably don’t. Also, my FeodoTracker Watchlist has a data column called DstIP (destination IP address) – you probably don’t.

However, I wanted to include these examples for those working with Watchlists. These examples represent Watchlist basics. The Let statement is used to build a variable for data that exists in the Watchlist. In the first example, I’m looking for IPs that exist (in) in the Watchlist. In the second one, I’m looking for IPs that don’t (!in) exist in the Watchlist.

And, while not part of our Let statement topic, the last two examples show how to call Watchlist data in the midst of the query instead of assigning variable. Best practice is to use the Let statement, but I’ve supplied these examples to show that it’s possible.

//Watchlist as a variable, where the requested data is in the list
let watchlist = (_GetWatchlist('FeodoTracker') | project DstIP);
Heartbeat
| where ComputerIP in (watchlist)

//Watchlist as a variable, where the requested data is not in the list
let watchlist = (_GetWatchlist('FeodoTracker') | project DstIP);
Heartbeat
| where ComputerIP !in (watchlist)


//Watchlist inline with the query, where the requested data is in the list
Heartbeat
| where ComputerIP in ( 
    (_GetWatchlist('FeodoTracker')
    | project DstIP)
)

//Watchlist inline with the query, where the requested data is not in the list
Heartbeat
| where ComputerIP !in ( 
    (_GetWatchlist('FeodoTracker')
    | project DstIP)
)

Not familiar with Microsoft Sentinel Watchlists or what to do with your Let statement for Watchlists after you create it? See: Use watchlists in Microsoft Sentinel