KQL: Exclude partial string from results using Sentinel Watchlists

Question

Using a watchlist to store domains to be excluded from this query. However unable to filter out the domains on the watchlist from the results. Dealing with SMTPS. The Watchlist items do not contain the "@" symbol.

Watchlist Example:

SearchKey	SMPT
hotmail.com	hotmail.com
gmail.com	gmail.com

EmailAttachmentInfo
| join kind=inner _GetWatchlist('FriendsList') on $left.SenderFromAddress == $right.Email
//| join kind=leftanti _GetWatchlist('SMTP-exceptions') on $left.RecipientEmailAddress == $right.SearchKey
|summarize arg_max(TimeGenerated, *) by SHA256, RecipientEmailAddress 
|project SenderFromAddress, TimeGenerated, RecipientEmailAddress, FileName, SHA256, FileType, FileSize, RecipientObjectId
//| where not(RecipientEmailAddress has_any(_GetWatchlist('SMTP-exceptions')))

The comments are my attempts. These would/should work if what was needed wasn't a partial match.

Answer 1

let _GetWatchlist = (name:string)
{
    datatable(SearchKey:string, SMPT:string)
    [
        "hotmail-com" ,"hotmail.com"
       ,"gmail-com"   ,"gmail.com"
    ]
    | where name == 'SMTP-exceptions'
};
let t = datatable(RecipientEmailAddress:string)
[
   ,"eliza.doolittle@hotmail.com"
   ,"beetlejuicebeetlejuice@beetlejuice.com"
   ,"maximus@gmail.com"
   ,"pinocchio@hotmail.com"
   ,"dorothy@notkansasanymore.com"
];
t
| where not(RecipientEmailAddress has_any((_GetWatchlist('SMTP-exceptions') | project SMPT)))