Working with watchlists and ipv4_is_in_any_range() to exclude results from query

[ben_loy]

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

[#2]

You 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

[#3]

@Clive_Watson 

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?

[#4 · Accepted solution]

@ben_loy 

This example works for me

let list = toscalar(_GetWatchlist('...........')
| summarize make_list(SearchKey));
AzureActivity
| where ipv4_is_in_any_range(tostring(CallerIpAddress), list)

[#5]

This work great. Any thoughts on if I want to exclude the IP address in the watchlist from my query?

[#6]

Something like this (not tested)?

| where not(ipv4_is_in_any_range(tostring(CallerIpAddress), list))