Forum Discussion
Searching by more than one field when using a watch list
Hi there,
I'm trying to filter by multiple fields in a watchlist. Something similar to the below, but with the fields user, src and dest.
| where SrcIP !in ((_GetWatchlist('WL_Global') | project user, src, dest | project-rename SrcIP=src))
I've tried the below, but this comes back with an error:
| where * !in ((_GetWatchlist('WL_Global') | project user, src, dest | project-rename SrcIP=src))
Is there a way of doing this without repeating the entire line for each separate field?
Many thanks
- So you will need a where statement regardless to filter on each column. If you want to filter multiple columns then you will need to have 3 where statements.
If you want to shorten the statement. You could save 3 queries as functions and just call the alias which will filter to 3 columns.
Then it would be
|where user in WL_Global_user |where src in WL_Global_src |where dest in WL_Global_dest
I don't recommend doing a wildcard (*) as it may be kind of slow. Is there a reason you are doing a wildcard. If the data you are doing the where statement is not normalized, it will be a very long calculation in my opinion.
Thoughts
Normalize that source data
No wildcards if that is possible.Thanks for the response TeachJing. The data is normalised already - it's more just to see if there was a more efficient way of doing things instead of having 3 where statements.
In splunk I would do something like this:
| search NOT [| inputlookup WL_Global | fields user src dest ]Splunk would translate that into optimised code that would then run on the indexers. I was wondering if there was an equivalent in Sentinel? Or is the best way with 3 where statements loading up the watchlist each time?
- Javier-Soriano
Microsoft
tagging CliveWatson, JeremyTan and Ofer_Shezaf in case they know of a better way and to document the way Splunk does it
