Forum Discussion
ben_loy
Sep 13, 2022Copper Contributor
Working with watchlists and ipv4_is_in_any_range() to exclude results from query
Hello!
I am struggling with using watchlists as a blacklist.
This is my query:
let list = _GetWatchlist('blacklistedSegments')
| summarize make_list(segment);
SigninLogs
| where ipv4_is_in_any_range(IPAddress, list); //throws an error
This is my Watchlist named "blacklistedSegments" - one column named "segment":
segment |
1.2.0.0/16 |
3.4.0.0/16 |
I am trying to create a query in which sign-in logs from black listed IPs are returned.
The problem is that I get the following error :
This is probably because make_list() returns an array while the ipv4 method expects a value.
Can anyone suggest the correct KQL way of achieving the above?
Any suggestion will be highly appreciated!
Thanks in advance.
Ben
This example works for me
let list = toscalar(_GetWatchlist('...........') | summarize make_list(SearchKey)); AzureActivity | where ipv4_is_in_any_range(tostring(CallerIpAddress), list)
- Clive_WatsonBronze ContributorYou could probably, use project rather than summarize or Distinct?
let list = _GetWatchlist("....") | project SearchKey
or there is a Dynamic option, which I've not tried with a Watchlist: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ipv4-is-in-any-range-function
e.g.
ipv4_is_in_any_range("127.0.0.1", dynamic([segment])) == true- ben_loyCopper Contributor
Thanks for replying.
Unfortunately Project and Distinct throw the same error.
The docs say that the method expect a dynamic array:
and make_list() returns exactly that:
Maybe there are some subtleties I miss?
- Clive_WatsonBronze Contributor
This example works for me
let list = toscalar(_GetWatchlist('...........') | summarize make_list(SearchKey)); AzureActivity | where ipv4_is_in_any_range(tostring(CallerIpAddress), list)