January 14, 2021

KQL Join and the Let Statement

Kevin Hood

Engineering Manager, SquaredUp

In this article I’m going to discuss table joins and the let statement in Log Analytics. Working at SquaredUp, I build a lot of dashboards with KQL and these were the concepts that really had me scratching my head for a long time. In particular I found it tricky to put all the pieces together from documentation and other people’s blog posts. Hopefully this will help anyone else out there that still has unanswered questions on one of these topics.

If you are new to Kusto, check out our Jumpstart guide to KQL before coming back to this one. You may also find my previous article on Aggregating and Visualizing data with Kusto helpful.

Let's jump into it!

KQL join – An introduction to table join types

Much like SQL, KQL supports table joins, and there are various join types to choose from depending on your needs. If you’re like me, trying to understand the difference between the join types is a bit mind-bending. I’ve set up some data tables to try and clarify exactly what happens with each different type. These are analogous to temporary tables in MS SQL and can be created using the let keyword (more on let later in this post).

Both data tables have the same schema: An integer field (Id), a datetime field (Dt) and a string field (ComputerName). The only field for which both tables have matching values is the ComputerName field, so we’re going to use this field as the key for our join. There are only two ComputerName values that are present in both tables: Server2 and Server3.

Join Types

innerunique: This is the default join type. It randomly selects a single unique row from the left table (based on the column being joined on) and duplicates it against matching rows in the right table. This means that when the data tables are joined with an innerunique join, the query processor is going to find which key values match between the two data tables (Server2 and Server3 in this case), deduplicate rows for each key value in the LeftDataTable (there are two rows for Server2 in LeftDataTable, so it’s going to randomly pick one of them), and then return the selected record for each matching record in RightDataTable. The result looks like this:

If we look at the fields from the left table in the results, we can see that the field with Id = 2 and ComputerName = Server2 has been used twice even though there are two different Server2 rows in the left table. In contrast, the two different sets of field values from the right table are represented in the results (Id1 =11 and Id1 = 12). According to the join operator article we can’t count on it selecting the first record from the left table when it deduplicates the keys either, although in this case it has done this. However, you can force the issue by adding in a where filter to the query, so if I wanted the other Server2 record in the left table to be used, I could run this query instead:

inner: Unlike innerunique, rows from the left table are not deduplicated. Every combination of matching rows from the two tables is shown in the results. This behaviour is how the standard MS SQL inner join works. The results from the joined data tables would therefore have each row from the left table matched against each row from the right table.

leftouter/rightouter: Contains all records from the Left table for leftouter or right table for rightouter, and only matched records from the other table. This is pretty much the inner join results plus all the unmatched rows from the left or right table depending on whether leftouter or rightouter was used.

leftanti/rightanti: Shows only the records that didn’t match, from either the Left table for leftanti or Right table for rightanti.

leftsemi/rightsemi: Shows only the matching records from one table: the Left table for leftsemi or the Right table for rightsemi.

It’s also worth noting that you can also do cross-workspace joins in Kusto queries. The syntax is slightly different because the workspace needs to be specified. Here’s an example:

AzureActivity
| join workspace('KH-TempWorkspace').AzureActivity 
    on Resource
| where Resource1 == "SANDBOX-DC02"

Let statements

Assign a value to a variable

If you’ve ever worked with JavaScript then the let statement might be familiar to you. In essence, it assigns a value to a variable. In Kusto, we can use it to assign a variable in the same way. For example, you might assign today’s date to a variable you have created called today and yesterday’s date to a variable called yesterday.

You can reference those variables in subsequent queries to either make what the query is doing more readable or to re-use across the entirety of your query (such as in the example given on the Microsoft page for let, where two queries for the same timeframe are executed and the results are unioned together into one big set of results). My example below is a little more simplistic: I’m simply using them to make the query more readable.

let today = endofday(now());
let yesterday = startofday(now(-1d));


InsightsMetrics
| where Computer == "SANDBOX-DC02.dev.squaredup.net" and TimeGenerated between (yesterday .. today)
| where Name == "Heartbeat"
| count

User-defined function

Another way to use lets is as a user-defined function. You can use these as helper functions in your queries. For example, to convert dates from one format to another on the fly. I have created what is a rather forced example of a user-defined function but hopefully it demonstrates what they’re about:

let timeframe = (days:timespan, mode:string="end")
{toscalar(iff(mode=="start",startofday(now(days)), endofday(now(days))))};

This function takes two variables, one called days which has a datatype of timespan and one called mode which has a datatype of string. The mode variable has a default value of “end” for no other purpose apart from to show that you can assign default values to variables! What the function does is take a timespan value and count back the number of days in the timespan value from today’s date to get the day we’re interested in. It then gets either the start of the day or the end of that day based on the value of the mode parameter; if mode is equal to “start” then it will get the start of the day. If it’s not equal to “start” it will get the end of the day. The toscalar() function ensures that the result being returned is a single value rather than a table containing a single value.

Create temporary table

Another use of the let statement is to create a temporary table. I’m going to add my user-defined function to a query that calculates minimum and maximum CPU utilization values from the same weekday last week. To use let in this way, all you have to do is provide a name for the variable (historicaldata in this case), write your query as normal and then put a semicolon on the end of it all. That’s very important because the error message you get when it’s missing isn’t very helpful! Note the user-defined function being used in the second line of the historicaldata query.

let timeframe = (days: timespan, mode: string="end") {
    toscalar(iff(mode == "start", startofday(now(days)), endofday(now(days))))
};

let historicaldata = InsightsMetrics
    | where TimeGenerated between (timeframe(-7d, "start")..timeframe(-7d, "end"))
    | where Namespace == "Processor" and Name == "UtilizationPercentage"
    | summarize LastWeekMax=max(Val), LastWeekMin=min(Val) by Computer, bin(TimeGenerated, 5m)
    | extend TimeWindow = format_datetime(TimeGenerated, 'HH:mm:ss');

Example: KQL join and Let statements in action

With the let table for this day last week created, I’ve created another let table for today’s CPU Utilization values. Again, I’m using my user-defined function to calculate the start and end of a day, but this time I’m passing in a timeframe of 0d to get the start and end of today.

let currentdata = InsightsMetrics
    | where TimeGenerated between (timeframe(0d, "start")..timeframe(0d, "end"))
    | where Namespace == "Processor"
    | where Name == "UtilizationPercentage"
    | summarize TodayMin = min(Val), TodayMax = max(Val) by Computer, bin(TimeGenerated, 5m)
    | extend TimeWindow = format_datetime(TimeGenerated, 'HH:mm:ss');

Finally, I’m going to join these two temporary let tables together using the default join type (innerunique) based on the TimeWindow field I created in each let statement and on the Computer field. The TimeWindow field is the datetime value from last week for the historical data and today for the currentdata, but the date has been stripped out of the datetime values. Because both let statements summarised their data by bin(TimeGenerated, 5m) we should be able to use the TimeWindow field as the join key because they should all be in distinct 5 minute intervals. The additional Computer field used as a join key is needed because there are lots of different computers in the results. If we join based on TimeWindow alone then the results may be mismatched because there are lots of different computer names in the historical and current temporary tables.

currentdata 
| join historicaldata on TimeWindow, Computer
| where Computer == 'SANDBOX-DC02.dev.squaredup.net' 
| project TimeGenerated, LastWeekMin, LastWeekMax, TodayMin, TodayMax, Computer

The result of the full query shows we have nice time series data with distinct 5-minute intervals and min and max CPU utilization values from today and from this day last week.

If we render this as a timechart in the Portal, we get a single line. We’ll need to click Don’t split underneath Split-by in the chart formatting section on the right-hand side.

When this has been done, we can choose All option under Y-Axis so that all the expected lines are drawn. That should get us what we’re looking for: a timechart of min and max values from today, together with min and max values from this day last week:

If you're building your KQL visualisations in SquaredUp, we can do a similar thing by checking the show all checkbox under the column overrides section. The result looks like this:

Looking at this chart, it’s pretty clear that this server is not under much more load than it was last week, so I guess we can relax, knowing that there’s no unexpected load on this server today.

And that’s all I’ve got on the topic of table joins and let statements. Hopefully it’s been of some help to you!

For more advanced things you can do in Kusto:

To learn all you need to know about Azure Monitor: