1

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.

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
CurlyCrank
  • 13
  • 3
  • The 2nd option (has_any) should have work for you, but only because in your example *SearchKey* equals *SMPT*. In the future please keep your example as simple as possible and provide a sample data for all involved tables & functions. – David דודו Markovitz Jan 24 '23 at 09:03

1 Answers1

0
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)))
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88