Using watchlists to whitelist or tune rules

[Nexxic]

I'm currently experimenting with tuning rules based on content in watchlists. For example, I'm trying to tune the Explicit MFA Deny to not trigger if an IP from the SigninLogs table is in a watchlist which contains our Proxy IP's.

I was hoping it would be as simple as using the following query (Watchlist IP contains the Proxy IP's in a column named IP):

let Proxy = _GetWatchlist('IP');
SigninLogs
| where ResultType == 500121
| where Status has "MFA Denied; user declined the authentication"
| extend AccountCustomEntity = AlternateSignInName
| extend IPCustomEntity = IPAddress
| extend URLCustomEntity = ClientAppUsed
| where IPCustomEntity !in (Proxy)

But the last row doesn't seem to do anything at all...

So far I've been getting so far as getting the Proxy IP's in a seperate column by using the query:

let Proxy = _GetWatchlist('IP');
SigninLogs
| join Proxy on $left.IPAddress == $right.IP

And then I would like to compare, and exclude any rows where IP and IPAddress match. Any ideas on how I can accomplish this? Is there a more efficient way of doing this without using Join?

Thanks!

[#2 · Accepted solution]

@Nexxic You are very close.  Take a look at the join types in the join command page: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuremonitor

and look at leftantisemi (Returns all the records from the left side that don't have matches from the right) and rightantisemi (Returns all the records from the right side that don't have matches from the left.)

I include both since there is a comment on this page: For best performance, if one table is always smaller than the other, use it as the left (piped) side of the join.   So you probably want to use your Proxy table on the left and do a rightantisemi join.

let Proxy = _GetWatchlist('IP');
Proxy
| join SigninLogs kind=rightantisemi on $left.IP == $right.IPAddress