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') | proj...
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.
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
- Ofer_Shezaf
This is what I really find challenging with Splunk. Queries are absolutely unreadable. The SPL example you brought is fast to write to the initiated but does make any sense logically, making it impossible to understand if you are not a Splunk Guru.
In KQL you have to be explicit, and readable, but I don't think makes the optimization different.
Ofer_Shezaf I would definitely say this is easier to read and quicker to write (especially when you have a lot of query to go through):
| search NOT [| inputlookup LOOKUP | fields src dest dest_port app protocol url]than this:
| where SrcIP !in ((_GetWatchlist('LOOKUP') | project SrcIP)) | where Dest !in ((_GetWatchlist('LOOKUP') | project Dest)) | where DestPort !in ((_GetWatchlist('LOOKUP') | project DestPort)) | where App !in ((_GetWatchlist('LOOKUP') | project App)) | where Protocol !in ((_GetWatchlist('LOOKUP') | project Protocol)) | where Url !in ((_GetWatchlist('LOOKUP') | project Url))Even more so when you have 3 or 4 lookups to correlate and you can end up with 10+ lines of KQL just for a few lookups.
Is there a better way to store this information and correlate it in Sentinel?
