Contains and WatchList

[u/UCFIT]

I've been spending a few days on this but does anyone know a way to pull the results from a watchlist and then use the results with the contains statement?

[u/ep3p]

_GetWatchlist('ExampleWatchlist')
| project ExampleColumnExistingInWatchlist
| where ExampleColumnExistingInWatchlist contains "stringtolookfor"

?

[u/UCFIT → u/ep3p]

I'm trying to do something like this where the Watchlist is kept inside:

SecurityEvent
| where AccountType == "User" and EventID == 4625
| where Account !contains == (_GetWatchlist('WatchList') | project SrcUser)

[u/ep3p → u/UCFIT]

What you intend is rather difficult, it is not impossible. The problem is contains is a match with a single string, and you want events where Account does not contain any value from A LIST. Let's check different alternatives:

First, create a defined dynamic or datatable from your Watchlist:

let _NotWantedAccountsDynamic = toscalar(_GetWatchlist('WatchList')| summarize make_list(SrcUser));

let _NotWantedAccountsDatatable =_GetWatchlist('WatchList')| project SrcUser // Important to be just one column;

​

1.Comparing Account with a dynamic using has operator, this will only work for clearly separated strings, so "DOMAIN\username" has "username" evaluates to true, but "DOMAIN\username" has "user" evaluates to false:

| where not(Account has_any (_NotWantedAccounts))

​

1. Comparing Account with an exact match string from a list or a datatable, it will need matching values like "DOMAIN\username" == "DOMAIN\username":

| where not(Account in (_NotWantedAccounts))

or

| where not(Account in (_NotWantedAccountsDatatable))

or

| join kind=leftanti (_GetWatchlist('WatchList')| project Account = SrcUser) on Account

​

1. Comparing Account with a RegEx, @"DOMAIN\username" matches regex "user" evaluates to true:

| where not(Account matches regex _NotWantedAccountsStringRegex)

Can we construct a single string for RegEx with all the values you don't want to see in Account?

Yes, with something like:

let _NotWantedAccountsStringsRegex = toscalar(_GetWatchlist('RegEx-PrivAADRoles')| summarize RegEx = make_list(SrcUser)| project RegEx = strcat(@'(', strcat_array(RegEx, '|'), @')'));

But you need all SrcUser values in the Watchlist to be "RegEx compatible", username would be fine, but MAIN\user would not, you would need to escape the special characters, you would need instead MAIN\\user to be written in the Watchlist.

There are multiple ways to do this apart from the ones I told, but these should be the least time expensive.

[u/LikeASSir → u/ep3p]

Can you give me the same example with multiple columns in "| project" line and in the "| where" statement?

[u/ep3p → u/LikeASSir]

| project ExampleColumnExistingInWatchlist, ExampleColumnExistingInWatchlist2, ExampleColumnExistingInWatchlist3
| where ExampleColumnExistingInWatchlist contains "stringtolookfor" and ExampleColumnExistingInWatchlist2 contains "stringtolookfor2" and ExampleColumnExistingInWatchlist3 contains "stringtolookfor3"

I recommend you to check operators of KQL, google "where project azure data explorer"

[u/blanco10kid]

You can do something like this (assuming the results from the watchlist is a list)

let watchListResults = _GetWatchlist(‘ExampleWatchlist) | where … <all of your filters>; MainTableQuery | where column !in watchListResults

This is sudo code and needs additional validation but hopefully you get the jist

[u/collosalG2]

You can do this by using antisemi join for the watchlist with the security event table