Azure Sentinel: Understanding the Order of KQL Statements and Join Operators

Join Operators and Field Identification in KQL

Prev Question Next Question

Question

You are a SOC Analyst working at a company that is deploying Azure Sentinel.

You are in charge for performing log data analysis to search for malicious activity, display visualizations, and perform threat hunting.

To query log data, you use the Kusto Query Language (KQL)

Often a result set from a KQL statement needs to be combined or joined with another result set.

You need to understand how the order of a KQL statement impacts your expected results.

While using the join operators, how do you identify fields from each table?

Answers

A. $inner.columname and $outer.columnname

B. $1.columname and $2.columnname

C. $left.columname and $right.columnname

D. $leftouter.columname and $rightouter.columnname.

Show Answer

Explanations

Click on the arrows to vote for the correct answer

A. B. C. D.

Correct Answer: C Option C is correct.

The $left and $right preceding the field name specifies the table.

Reference:

When joining two tables in Kusto Query Language (KQL), it is important to understand how to identify fields from each table. The correct syntax to identify fields from each table depends on the type of join that is being used.

Here are the different join types and how to identify fields from each table:

Inner Join: An inner join returns only the rows that have matching values in both tables. When using an inner join, the fields from the two tables can be identified using the $inner and $outer keywords.

The $inner keyword refers to the table on the right side of the join, while the $outer keyword refers to the table on the left side of the join. To identify fields from each table in an inner join, you would use the syntax $inner.columnname and $outer.columnname.

For example:

bash
TableA | join kind=inner TableB on Column1 | project $inner.Column2, $outer.Column3

In this example, we are joining TableA with TableB using the Column1 field. The resulting table will have columns Column2 (from TableB) and Column3 (from TableA). We use the $inner and $outer keywords to specify which table each column belongs to.

Left Join: A left join returns all the rows from the left table and the matching rows from the right table. When using a left join, the fields from the two tables can be identified using the $left and $right keywords.

The $left keyword refers to the table on the left side of the join, while the $right keyword refers to the table on the right side of the join. To identify fields from each table in a left join, you would use the syntax $left.columnname and $right.columnname.

For example:

bash
TableA | join kind=leftouter TableB on Column1 | project $left.Column2, $right.Column3

In this example, we are performing a left outer join between TableA and TableB using the Column1 field. The resulting table will have all the rows from TableA and the matching rows from TableB. We use the $left and $right keywords to specify which table each column belongs to.

Right Join: A right join returns all the rows from the right table and the matching rows from the left table. When using a right join, the fields from the two tables can be identified using the $left and $right keywords, just like in a left join.

For example:

bash
TableA | join kind=rightouter TableB on Column1 | project $left.Column2, $right.Column3

In this example, we are performing a right outer join between TableA and TableB using the Column1 field. The resulting table will have all the rows from TableB and the matching rows from TableA. We use the $left and $right keywords to specify which table each column belongs to.

Full Join: A full join returns all the rows from both tables. When using a full join, the fields from the two tables can be identified using the $leftouter and $rightouter keywords.

The $leftouter keyword refers to the table on the left side of the join, while the $rightouter keyword refers to the table on the right side of the join. To identify fields from each table in a full join, you would use the syntax $leftouter.columnname and $rightouter.columnname.

For example:

bash
TableA | join kind=fullouter TableB on Column1 | project $leftouter.Column2, $rightouter.Column3

In this example, we are performing a full outer join between TableA

Prev Question Next Question