SOLVED

Compare a watchlist of IPs to DNS Events

%3CLINGO-SUB%20id%3D%22lingo-sub-3037481%22%20slang%3D%22en-US%22%3ECompare%20a%20watchlist%20of%20IPs%20to%20DNS%20Events%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3037481%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20compare%20IOCs%20from%20a%20watchlist%20in%20Sentinel%20to%20the%20DnsEvent%20based%20on%20a%20KQL%20query.%20But%20I%20am%20having%20trouble%20comparing%20the%20two%20sources.%20I%20thought%20about%20a%20union%20statement%2C%20but%20I%20can't%20get%20around%20that%20I%20have%20to%20compare%20specific%20columns%20in%20two%20different%20sources.%20Does%20anyone%20know%20how%20to%20compare%20results%20(ips)%20from%20a%20watchlist%20to%20DNS%20events%20using%20KQL%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3037543%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20a%20watchlist%20of%20IPs%20to%20DNS%20Events%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3037543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1215756%22%20target%3D%22_blank%22%3E%40agoodson%3C%2FA%3E%26nbsp%3Ba%20join%20would%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E_GetWatchlist('IPAddresses')%0A%7C%20join%20(DnsEvents)%20on%20%24left.IPAddress%20%3D%3D%20%24right.IPAddresses%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Microsoft

I am trying to compare IOCs from a watchlist in Sentinel to the DnsEvent based on a KQL query. But I am having trouble comparing the two sources. I thought about a union statement, but I can't get around that I have to compare specific columns in two different sources. Does anyone know how to compare results (ips) from a watchlist to DNS events using KQL? 

1 Reply
best response confirmed by agoodson (Microsoft)
Solution

@agoodson a join would work. 

 

_GetWatchlist('IPAddresses')
| join (DnsEvents) on $left.IPAddress == $right.IPAddresses