Mar 16 2021 06:14 AM
Mar 16 2021 06:14 AM
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?
Mar 16 2021 12:33 PM
Mar 17 2021 02:26 AM
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?
Mar 17 2021 05:45 AM
Mar 17 2021 01:48 PM
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.
Mar 18 2021 05:12 AM
@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]
| 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?
Mar 18 2021 06:07 AM - edited Mar 18 2021 06:08 AM
| search NOT [| inputlookup LOOKUP | fields src dest dest_port app
Splunk is parsing all the fields or columns you currently have available and matching them to the lookup, it then compares the value of that field and when it finds a match removes the result from our search.
I suppose a less verbose but similar approach in KQL land would be:
| where (SrcIP or User or Dest) !in ((_GetWatchlist('WL_Global') | project user, src, dest
Currently, the above doesn't work and you do have to individually split out your where filtering:
I can see both sides, but it's far more verbose in KQL making readability more of a struggle.