Forum Discussion

Reubz's avatar
Reubz
Copper Contributor
Aug 10, 2023

KQL - Join when comparing against multiple fields

Hey Team,   I'm currently trying to join two tables. Table1 has a list of hashes, Table2 has three separate fields with hashes in them.   I want to join the tables if any of the hashes match the ...
detection
KQL
raphaelcustodiosoares's avatar
raphaelcustodiosoares
Iron Contributor
Aug 11, 2023

Hello, I can't test it, but it might help you with something , but have three iff or use coalesce.

 

 

 

 

table1 
| join kind=inner ( 
table2 
| extend Hash1Match = iff(table1.HashColumn == table2.HashColumn1, true, false) 
| extend Hash2Match = iff(table1.HashColumn == table2.HashColumn2, true, false) 
| extend Hash3Match = iff(table1.HashColumn == table2.HashColumn3, true, false) ) on $left.HashColumn == $right.HashColumn 
| project table1.*, Hash1Match, Hash2Match, Hash3Match

 

 

coalesce mode

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/coalescefunction

table1
| join kind=inner (
    table2
    | extend AnyHashMatch = coalesce(
        iff(table1.HashColumn == table2.HashColumn1, true, false),
        iff(table1.HashColumn == table2.HashColumn2, true, false),
        iff(table1.HashColumn == table2.HashColumn3, true, false)
    )
) on $left.HashColumn == $right.HashColumn
| project Tabela1.*, AnyHashMatch

 

 

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily.

 

 

 

 

Resources