Must Learn KQL Part 18: The Union Operator
Chapter 18
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:
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 I did with parts/chapters 13-16 of this series for the series-within-the-series for data view manipulation, this part/chapter and the next form another mini-series of sorts. The Union and Join operators are important parts of the KQL journey as they represent opportunities to combine data from tables in different ways.
Before jumping directly off into talking about the Union operator, I think it’s best to start with describing the differences between Union and Join. Knowing the differences will allow you to determine which one to use for which scenario.
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. You’ll see the differences once we get through this mini-series and you can get hands-on with the examples. I highly suggest taking the examples from this part/chapter and running them against the examples of Part 19 on the Join operator to get a proper comparison.
There’s a lot to the Union operator, so I suggest reviewing the reference page for all additional options, including things like kind=inner(common columns), outer (all columns- default), and isfuzzy. I’ll discuss Union more in the Advanced series, Addicted to KQL, but for our purposes for the Must Learn KQL journey what’s important to know are the following:
Union supports wildcard to union multiple tables (union Security*)
Union can be used to merge tables from different Log Analytics Workspaces (or clusters)
For most of your operations in the Microsoft security tools like Microsoft Sentinel for creating Analytics Rules (covered in Part 20, the last part/chapter of the Must Learn KQL series), you’ll make use of the Join operator because of its ability to hone directly into specific results. Union, though, is an important tool for hunting in Microsoft Sentinel and Advanced Hunting in Defender.
To get started with the Union operator, use the following examples in the KQL Playground (https://aka.ms/LADemo).
This following query merges the SecurityEvent and Heartbeat tables and then displays each hostname (computer) stored in both tables and how many times each computer is recorded for some sort of activity.
SecurityEvent //the table
| union Heartbeat //merging SecurityEvent table with the Heartbeat table
| summarize count() by Computer //showing all computers from both tables and how many times This next query example is the same as before but merging an additional table (SecurityAlert) to show the data from three tables instead of two.
SecurityEvent //the table
| union Heartbeat, SecurityAlert //merging SecurityEvent table with the Heartbeat table and SecurityAlert
| summarize count() by Computer //showing all computers from all tables and how many times they are referencedThe following example introduces a couple changes to the first two queries in that it merges all tables that start with ‘Sec’ (notice the wildcard character) and sorts the computers in alphabetical ascending order (the last line).
SecurityEvent //the table
| union Sec* //merging together all tables beginning with 'Sec'
| summarize count() by Computer //showing all computers from all tables and how many times they are referenced
| sort by Computer asc //displaying Computer names in ascending order