Advice - Advance hunting query joining question

Question

I was wondering if ya'll can give me an advice on how to join two unique table together when it doesn't have similar schema. Basically the query is meant to detect email events allowed clicked which trigger an defender alert. I was gonna add DeviceEvents but I also couldn't figure it out so I did AlertId instead.:

let ClickURL = EmailEvents
| where EmailDirection == "Inbound"
| join (UrlClickEvents 
| where Workload == "Email"
| where ActionType == "ClickAllowed" or IsClickedThrough != "0") on NetworkMessageId
| project Timestamp, SenderFromAddress, AccountUpn, Url, UrlChain, UrlCount, DeliveryAction, Subject, NetworkMessageId, LatestDeliveryAction;
AlertInfo
| where DetectionSource == "EDR"
| project AlertId
| join kind = inner ClickURL on $left.AlertInfo == $right.Url //this is what I don't get??

Thank You

Answer 1

Two inner join two tables with different schema, you can try below workaround.

In this approach, first add a similar column to both tables using row_number(). Then, join the tables based on that column. The row_number() needs the table to be ordered before its use but as it should not disturb the original order of the table, add a dummy column with same value in all rows and order the table based on this column. This preserves the original order of the table. Then create similar columns in both tables using row_number() and join the tables based on this column. At the end project-away the extra columns and your tables will be joined.

Here is a sample demo of joining two tables with different schema.

let a = datatable(name:string, age:long) 
[ 
  "Alice", 32,  
  "Bob", 31,  
  "Eve", 27,  
  "Joe", 29,  
  "Chris", 45, 
  "Alex", 35,
  "Ben", 23,
  "Richard", 39,
]; 
let b = datatable(employee:string, manager:string) 
[ 
  "Bob", "Alice",  
  "Chris", "Alice",  
  "Eve", "Bob",
  "Ben", "Chris",
  "Joe", "Alice", 
  "Richard", "Bob"
]; 

// ordering first table based on a single valued column and adding row_number() column

let one= a
| extend dum1='temp'
| order by dum1
| extend key1=row_number();

// similarly do it for second table
let two = b
| extend dum2='temp'
| order by dum2
| extend key2=row_number();

// join both tables
one
| join kind = inner two on $left.key1 == $right.key2
| project-away dum1,dum2,key1,key2

Result: