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 hash in table1.

 

E.G on $left.hashes == $right.hash1 or $left.hashes == $right.hash2 or $left.hashes == $right.hash3

 

Currently I know this is impossible due to the limitations on the "on" operator and I'll have to create 3 separate use cases for each hash, but is there any way around this to have a single use case?

detection
KQL

1 Reply

  • 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