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.
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.
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:
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
[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]