Forum Discussion

ben_loy's avatar
ben_loy
Copper Contributor
Sep 13, 2022

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

 

  • 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)

     

     

     

    • ben_loy's avatar
      ben_loy
      Copper Contributor

      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?

      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor

        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)

         

         

         

Resources