Must Learn KQL Part 19: The Join Operator

Chapter 19

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.

…

As noted in part/chapter 18, this mini-series on merging data contains two different principles. Reiterated from the last part/chapter…

Union allows you to take the data from two or more tables and display the results (all rows from all tables) together. Join, on the other hand, is intended to produce more specific results by joining rows of just two tables through matching the values of columns you specify.

There’s quite a bit more to the Join operator (and Join, in general) than I’ll cover in this part/chapter. I want to make sure to keep this focused on those things necessary to help build your first Microsoft Sentinel Analytics Rule in the final part/chapter of this series.

Join, merges the rows of two tables (left table and right table) to form a new pseudo-table by matching values of the specified column(s) from each table. Just like any other query language’s Join, the KQL Join operator supports the following Join methods along with some additional nuanced options – with innerunique Join being the default.

Joining tables and data

The syntax for the Join operator is as follows:

LeftTable
|join [JoinParameters] (RightTable) onAttributes

Use the following example in the KQL Playground (https://aka.ms/LADemo). This example joins together the SecurityEvent and Heartbeat tables on the common Computer column. It then filters all Computers by the 4688 Event ID (newly spawned process) and shows the Computer name and the installed OS and versioning.

SecurityEvent //table name
| join Heartbeat on Computer //joining SecurityEvent with Heartbeat on the common Computer column
| where EventID == "4688" //Looking for Event ID for new process
| project Computer, OSType, OSMajorVersion, Version //Displaying data from both tables

Your results should look similar to the following:

Results of Joining by the Computer Column

Here’s something fun. To change the kind (or, flavor) of Join, you simply add a kind option like so.

| join kind=inner Heartbeat on Computer

Changing the flavor of join will alter how and what data is displayed. Changing our original Join query example with the inner flavor or join will produce results like the following (note the results display difference from before)….

Try the following on your own in the KQL Playground (https://aka.ms/LADemo):

• | join kind=innerunique Heartbeat on Computer

• | join kind=leftouter Heartbeat on Computer

• | join kind=rightouter Heartbeat on Computer

• | join kind=fullouter Heartbeat on Computer

In the advanced series, Addicted to KQL, I’ll dig deeper into the other use cases for Join. If you’re champing at the bit to learn more now and happen to be a Star Wars nut, check out Jing’s KQL Tutorial on the Join operator on YouTube: KQL Tutorial Series | Joining Tables (Demo) | EP5