How to Join Tables in KQL | Microsoft Defender Advanced Hunting (Part 1)

Summary

The video introduces the basics of joining tables with Kusto Query Language (KQL) in Microsoft Defender Advanced Hunting. Defender data is organized into tables covering areas such as apps, email, endpoints, and identities. Joining tables lets analysts combine related records and uncover connections that may not be apparent when viewing each table alone.

How a join works

• A join requires a shared key column in both tables. In the demonstration, both tables have an ID column, which is used as the key.
• The join compares key values and combines columns from matching rows into a result row.
• If a row in the left table matches multiple rows in the right table, it appears multiple times in the result—once for each match.
• KQL has different join types, or join flavors, that affect the result. The video focuses on the fundamental concept and notes that other flavors will be covered in Part 2.

Demonstration in Advanced Hunting

1. In Microsoft Defender, open Investigation & response, select Hunting, and then choose Advanced hunting. The schema shows the available tables for querying.
2. Create two query-defined sample tables called left table and right table, each with ID and Value columns. These are demonstration tables, not tables in the Defender schema.
3. Run a query on each table to view its rows.
4. Join the tables on ID to combine rows with matching IDs.
5. The example uses KQL’s default join behavior, which deduplicates the left table on the join key. The results show each left-side key matched with one or more corresponding right-side rows.

The video assumes viewers have a basic understanding of KQL and presents table joins as a way to gain additional insights from Microsoft Defender data.

Speakers and sources

• Speaker: Narrator (Microsoft Security video)
• Sources and learning resources mentioned:
  • KQL tutorial: aka.ms/ahkql
  • KQL join reference: aka.ms/KQLjoin
  • Advanced Hunting learning resource: aka.ms/HuntwithAH