Let me tell you about let, my favorite operator in the Kusto Query Language. Why my favorite?
It is extremely flexible. It lets you create constants, variables, datasets, and even reusable functions. Let me tell you, it’s very powerful.
Before I go further, let me say the samples in this post will be run inside the LogAnalytics demo site found at https://aka.ms/LADemo. This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.
If you’ve not read my introductory post in this series, I’d advise you to do so now. It describes the user interface in detail. You’ll find it at https://arcanecode.com/2022/04/11/fun-with-kql-the-kusto-query-language/.
Note that my output may not look exactly like yours when you run the sample queries for several reasons. First, Microsoft only keeps a few days of demo data, which are constantly updated, so the dates and sample data won’t match the screen shots.
Second, I’ll be using the column tool (discussed in the introductory post) to limit the output to just the columns needed to demonstrate the query. Finally, Microsoft may make changes to both the user interface and the data structures between the time I write this and when you read it.
A Quick Note on Casing
If you have been following my posts in this Fun With KQL series, you’ll have noticed that operators and functions in KQL are all in lowercase. Table and column names though are almost always in mixedcase, although when users create their own data clusters they can use whatever casing they wish. In mixed case, the first letter is capitalized, then letters of distinct words are also capitalized. Some examples are Perf, AppRequests, and AppAvailabilityResults.
As mentioned in the intro, let allows you to create variables to hold variable data, constants, and functions. To keep these distinct from other KQL items I use what is known as camel case. With camel casing, the first letter is in lower case, but any different words after it are placed in capitals. Some examples you’ll find later in this post are timeDiffInDays, usageData, and counterName.
Using camel case immediately lets me identify a name as being created from a let statement and not an operator or table / column name. The use of camel case is not a requirement, but it is a common practice among many of us who use the Kusto Query Language.
Using Let to Create a Constant Value
Using let in its most basic form is very simple. You simply use let, followed by the name of the variable you want to create. Next comes an equal sign, and the value you want to assign to it. The let must end in a semicolon (;).
You can have multiple let statements before your query, as you see in this example. Then within the main query, you use the variable name as you would any column name, table or operator.
Here, we set two variables, minCounterValue and counterName. Placing these values in constants like we do makes them easier to change between runs of the query. For example, perhaps the first time we run the query it returns too many rows, so we can easily change the minimum counter value to say 100 to reduce the number of rows we will need to work with.
This is especially useful when we use the values in multiple places in the query. Here they are used in the strcat operator. Then, they are both used in the where operator. This is a simple query, but you can see how useful it is to be able to quickly change values in one quick setting.
Allow me to point out one other thing, a reminder really, that Kusto is case sensitive. CounterName is a different object than counterName. It is this case sensitivity that allowed us to use where CounterName == counterName in our query.
Be careful when doing this, sometimes it’s too easy to miss the difference in casing when quickly looking over a query. For that reason I suggest avoiding situations such as using CounterName and counterName in the same query. I did it here so I could demonstrate the concepts.
Using Let to Create a Calculated Value
You can also use let to create a value based on a calculation. Here we are doing something very simple, and setting the startDate to twelve hours ago. This makes it very easy for us to alter the calculated value between executions of the query.
Of course you could create far more complex calculations, I kept it simple for this demo.
Also keep in mind, at the time the let statement executes, the main query has not yet executed. Thus you won’t have access to any of the columns in your query.
There’s a way around this though, through the use of functions which we’ll see in the next section.
Creating a Reusable Function with Let
If you want to create a reusable calculation, but need to work with data from your dataset, you’ll need to create a function.
We start by creating the name of the function then an equal sign: let timeDiffInSeconds = .
Next we have parenthesis that enclose the list of parameters we want to use. You can have as many parameters as you need, or none at at all. In our function we need two.
For each parameter we need to indicate the name we want to use inside the function, then a colon, and the datatype for the parameter.
Here we named the parameters date1 and date2, then the colon followed by the datatype. In this case we specified datetime for both of them: (date1: datetime, date2: datetime).
Following the list of parameters we then define the function. The function is enclosed in squiggly braces { }. The function is pretty simple, we take the date2 parameter and subtract the date1 parameter. The result is divided by one second, so the time difference will be returned in seconds: { (date2 - date1) / 1s }.
Here we could fit our function into a single line, but you can use any number of lines you need as long as it is still valid code. For example we could have done:
{
(
date2 - date1
)
/ 1s
}
To be honest this is an example that makes the code a lot harder to read, and if you ever see production code from me that looks like this it’s a clear indicator I’ve been kidnapped by space aliens and am asking for help. But here, it serves to illustrate the point that my function can spread out over multiple lines.
Using the let defined function is simple. In the project we simply create a new column name, ElapsedSeconds and assign to it the function we created in the let, here timeDiffInSeconds. As parameters we then pass in the StartTime and EndTime. This results in:
ElapsedSeconds - timeDiffInSeconds(StartTime, EndTime)
Here it is in action.
As you can see in the output, we have our ElapsedSeconds column which shows the difference in seconds between the start and end times.
Functions with Default Values
It’s also possible to supply a default value for the last parameter in your list. Before I go on, let me warn you this is an undocumented feature. The online help for the let statement makes no mention of defaults, so USE THIS AT YOUR OWN RISK. I cannot predict how Microsoft may alter this feature in the future.
I discovered this when I created the first version of the Kusto course for Pluralsight around 2018. For it I was working closely with Microsoft on the content and it was included in the samples they wanted to use. I’m not sure why it is now undocumented, they may plan to discontinue, or change its behavior. So let me say one more time, use at your own risk.
In the list of parameters after the datatype for the last parameter we use an equal sign, followed by the default value.
In this example, if we do not pass in a value for the second parameter it uses the default value supplied. Here, date2: datetime = datetime(2023-01-01) will return January first of 2023 when no date is supplied.
For this example we also altered the function to return the time difference in days instead of seconds.
Looking at the last line of the query, you can see we used ElapesedDaysSinceStartOfYear = timeDiffInDays(TimeGenerated) and only passed in one value, the TimeGenerated.
Right above that we had a line, ElapsedDays = timeDiffInDays(EndTime, StartTime). In this line we passed in two values. Instead of using the default it instead used the value in StartTime. This gave our function a lot of flexibility.
Note default values do have one issue, you can’t use dynamic values such as ago(20d). That’s why we needed to hard code the January first date.
Creating Useful Functions
Back in my post Fun With KQL – Case, I showed how to use a case statement to retrieve the name of the month based on the month number that was used.
This is a useful piece of code, so I created a function out of it. I have a file full of these useful pieces of code. Putting them in functions makes them easy to reuse.
In this function I simply create one parameter, the monthNumber. It will return the text associated with the month number passed in. For more on how the case works, refer back to my Fun With KQL – Case post.
I then added a second useful function, getNiceDate, which uses string concatenation to assemble a nice date. For more on the strcat and format_datetime operators see the See Also section of this post for links to the associated blog posts I’ve done.
In my query I used a couple of extends to call my functions then add their output to my query. I could have done away with the extends and just embedded the calls the functions right inside my project. I just thought separating it out made it a little easier to read.
As you can see in the output, I have both the MonthName and NiceDate columns with the nicely formatted data.
Using a Let to Hold a Dataset
The let operator can also hold a dataset, often referred to as a datatable in Kusto.
In the second let statement, I simply provide a name to hold the dataset, here usageData. I then supply the query needed to get the data.
In the final line I simply use the variable usageData to send the data to the output pane.
Right now this may not seem very useful, but in upcoming posts on join and union you’ll see how to use this functionality. For now just remember it as it will prove useful soon.
See Also
The following operators, functions, and/or plugins were used or mentioned in this article’s demos. You can learn more about them in some of my previous posts, linked below.
Fun With KQL – DateTime Arithmetic
Fun With KQL – Format_DateTime
Conclusion
In this post we explored the versatile let operator. We first saw how to use let to hold constant values. Next we learned how to hold variables using let.
From there we saw how to create reusable functions, in my humble opinion lets greatest power. Finally we saw how to use let to hold a dataset, also called a datatable. We’ll explore this last capability more in upcoming blog posts.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
The first course in the series is Kusto Query Language: Getting Started. The second course is Kusto Query Language: Beginning Operators. The third course, to be published soon, is Kusto Query Language: Basic Scalar Operators
I have two previous Kusto courses on Pluralsight as well. Kusto Query Language (KQL) from Scratch and Introduction to the Azure Data Migration Service, and these are two of the many courses I have on Pluralsight. All of my courses are linked on my About Me page.
If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.
Arcane Code 