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:
The book version (pdf) of this series is located here:
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.
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
[Subscribe to the RSS feed for this blog]
[Subscribe to the Weekly Microsoft Sentinel Newsletter]
[Subscribe to the Weekly Microsoft Defender Newsletter]
[Learn KQL with the Must Learn KQL series and book]