Join Behaviour Question

%3CLINGO-SUB%20id%3D%22lingo-sub-1555481%22%20slang%3D%22en-US%22%3EJoin%20Behaviour%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555481%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20I'm%20working%20with%20Sentinel%20and%20I've%20run%20into%20some%20join%20behaviour%20that%20either%20I%20don't%20understand%20or%20is%20broken.%20I'm%20using%20a%20join%20to%20compare%20data%20in%20an%20IoC%20watchlist%20(Anomali%20stored%20in%20ThreatIntelligenceIndicator)%20and%20some%20basic%20firewall%20logs%20in%20CommonSecurityLog.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20the%20query%20below%20I%20get%20one%20result%20per%20%22hit%22%20on%20the%20watch%20list.%20I%20also%20get%20a%20warning%20about%20this%20query%20utilising%20resources%20and%20it%20takes%20a%20while%20to%20execute.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECommonSecurityLog%0A%7C%20join%20(%20ThreatIntelligenceIndicator%20%7C%20where%20ThreatType%3D%3D%22WatchList%22%20%7C%20project-rename%20DestinationIP%20%3D%20NetworkIP%20)%20on%20DestinationIP%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20this%20query%20where%20I%20%22reverse%20the%20join%22%20I%20get%20each%20individual%20firewall%20event%2C%20which%20is%20multiple%20hits%20per%20target%20IP%20on%20the%20watchlist.%20This%20query%20is%20also%20faster!%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EThreatIntelligenceIndicator%20%7C%20where%20ThreatType%20%3D%3D%20%22WatchList%22%0A%7C%20join%20(%20CommonSecurityLog%20%7C%20project-rename%20NetworkIP%20%3D%20DestinationIP%20)%20on%20NetworkIP%0A%7C%20project%20TimeGenerated1%2C%20DeviceAction%2C%20SourceIP%2C%20NetworkIP%2C%20DestinationPort%2C%20ReceivedBytes%2C%20SentBytes%2C%20Message%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObviously%2C%20the%20second%20query%20and%20its%20multiple%20results%20are%20preferable.%20But%20is%20this%20expected%20behaviour%20and%20why%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555529%22%20slang%3D%22en-US%22%3ERe%3A%20Join%20Behaviour%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744641%22%20target%3D%22_blank%22%3E%40JKatzmandu%3C%2FA%3E%26nbsp%3BThe%20inner%20join%20you%20are%20doing%20also%20does%20a%20de-duplication%20(removing%20any%20duplicate%20rows)%20before%20trying%20to%20join%20with%20the%20table%20on%20the%20right.%26nbsp%3B%20Since%20you%20are%20not%20doing%20any%20sort%20of%20filtering%20in%20the%20first%20query%2C%20that%20alone%20will%20take%20some%20time%20since%20it%20has%20to%20look%20at%20all%20the%20rows%20in%20the%20selected%20time%20period.%26nbsp%3B%20%26nbsp%3BIt%20will%20then%20perform%20the%20inner%20join%20with%20the%20right%20table.%26nbsp%3B%20%26nbsp%3BMy%20guess%20is%20there%20is%20only%20one%20entry%20in%20the%20left%20table%2C%20after%20the%20rows%20were%20de-duplicated%2C%20that%20matches%20any%20rows%20in%20the%20right%20table%20hence%20there%20is%20only%20one%20row%20returned.%26nbsp%3B%20Take%20a%20look%20at%20the%20row%20being%20matched%20in%20the%20left%20table.%26nbsp%3B%20Does%20it%20have%20duplicate%20entries%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20switching%20the%20table%20order%20you%20are%20now%20doing%20the%20de-duplication%20on%20a%20table%20that%20does%20have%20a%20filter%20so%20that%20step%20alone%20will%20be%20faster.%26nbsp%3B%20%26nbsp%3BYou%20then%20match%20everything%20on%20the%20left%20table%20with%20the%20matching%20values%20on%20the%20right%20table.%26nbsp%3B%20%26nbsp%3BSince%20the%20right%20table%20is%20no%20longer%20being%20de-duplicated%20there%20are%20more%20matches%20(and%20I%20am%20guessing%20at%20that%20part).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555535%22%20slang%3D%22en-US%22%3ERe%3A%20Join%20Behaviour%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555535%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3BUnderstood!%20It's%20just%20me%20figuring%20out%20proper%20join%20behaviour%2C%20that's%20all%20%3AD%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555552%22%20slang%3D%22en-US%22%3ERe%3A%20Join%20Behaviour%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744641%22%20target%3D%22_blank%22%3E%40JKatzmandu%3C%2FA%3E%26nbsp%3BHere%20is%20the%20official%20documentation%20on%20that%20join%20flavor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fjoinoperator%3Fpivots%3Dazuredataexplorer%23default-join-flavor%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fjoinoperator%3Fpivots%3Dazuredataexplorer%23default-join-flavor%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561217%22%20slang%3D%22en-US%22%3ERe%3A%20Join%20Behaviour%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744641%22%20target%3D%22_blank%22%3E%40JKatzmandu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETwo%20additional%20points%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E's%20excellent%20answer%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20behavior%20you%20see%20is%20indeed%20suriprising.%20Azure%20Sentinel%20defaults%20to%20a%20join%20type%20called%20%22innerunique%22%2C%20while%20other%20systems%20usually%20default%20to%20%22inner%22.%20If%20you%20want%20the%20behavior%20you%20were%20expecting%20use%20%22join%20kind%3Dinner%22.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERegardless%20of%20the%20join%20type%2C%20it%20is%20recommended%20-%20for%20performance%20reasons%20-%20to%20use%20the%20smaller%20table%20on%20the%20left%20side%20of%20the%20join%2C%20which%20I%20assume%20your%20second%20query%20addressed.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E~%20Ofer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi everyone! I'm working with Sentinel and I've run into some join behaviour that either I don't understand or is broken. I'm using a join to compare data in an IoC watchlist (Anomali stored in ThreatIntelligenceIndicator) and some basic firewall logs in CommonSecurityLog.

 

If I run the query below I get one result per "hit" on the watch list. I also get a warning about this query utilising resources and it takes a while to execute.

CommonSecurityLog
| join ( ThreatIntelligenceIndicator | where ThreatType=="WatchList" | project-rename DestinationIP = NetworkIP ) on DestinationIP

 

If I run this query where I "reverse the join" I get each individual firewall event, which is multiple hits per target IP on the watchlist. This query is also faster!

ThreatIntelligenceIndicator | where ThreatType == "WatchList"
| join ( CommonSecurityLog | project-rename NetworkIP = DestinationIP ) on NetworkIP
| project TimeGenerated1, DeviceAction, SourceIP, NetworkIP, DestinationPort, ReceivedBytes, SentBytes, Message

 

Obviously, the second query and its multiple results are preferable. But is this expected behaviour and why?

 

Thanks!

4 Replies

@JKatzmandu The inner join you are doing also does a de-duplication (removing any duplicate rows) before trying to join with the table on the right.  Since you are not doing any sort of filtering in the first query, that alone will take some time since it has to look at all the rows in the selected time period.   It will then perform the inner join with the right table.   My guess is there is only one entry in the left table, after the rows were de-duplicated, that matches any rows in the right table hence there is only one row returned.  Take a look at the row being matched in the left table.  Does it have duplicate entries?

 

By switching the table order you are now doing the de-duplication on a table that does have a filter so that step alone will be faster.   You then match everything on the left table with the matching values on the right table.   Since the right table is no longer being de-duplicated there are more matches (and I am guessing at that part).

 

Does that make sense?

@Gary Bushey Understood! It's just me figuring out proper join behaviour, that's all :D

@JKatzmandu 

 

Two additional points to @Gary Bushey's excellent answer:

 

  • The behavior you see is indeed suriprising. Azure Sentinel defaults to a join type called "innerunique", while other systems usually default to "inner". If you want the behavior you were expecting use "join kind=inner". 

 

  • Regardless of the join type, it is recommended - for performance reasons - to use the smaller table on the left side of the join, which I assume your second query addressed.

 

~ Ofer