Forum Discussion

MCT

Feb 15, 2022

How to compare a array values in a column against another array from a watchlist in Kusto

I am getting results with a column named IPAddresses having values in array. I want to compare each value in this array to a list (another array from a watch list). I have been trying to make use of ...

Azure Sentinel

GaryBushey

Bronze Contributor

Feb 15, 2022

ashishrajsrivastava Your ZSWatchlist variable is a table so normally I would say to use a join but since you are using ipv4_is_in_range for your comparison, that will not work. Have you tried a union command between the ZSWatchlist and users? Then perform the comparison to weed out just those values you want. Not sure how many IP Addresses you have in the watchlist so not sure if this will be feasible or not.

ashishrajsrivastava

MCT

Feb 15, 2022

Let me try this, I do remember trying union but not sure if I did finish till the comparison.

Clive_Watson

Bronze Contributor

Feb 15, 2022

ashishrajsrivastava

I had a similar task recently, and it's still a work in progress - its simplified compared to yours to get to the main task.

//watchlist array
let ZSwatchlist = (_GetWatchlist('ipa')
    | project SearchKey 
    | summarize zlist = make_list(SearchKey));
let users = (
    // Get IP addresses for a named Table and make as an array
    AWSVPCFlow
    | where TimeGenerated > ago(30d)
    | where isnotempty(SrcAddr)
    // testing - there is a point when too many IPs fills the array, keep it small 
    | limit 1048
    | summarize IPAddresses = make_set(SrcAddr)
);
union users, ZSwatchlist
| project IPAddresses ,tostring(zlist)
| mv-apply ipscaler=IPAddresses to typeof(string) on
    (
        where not(ipv4_is_in_range(ipscaler,zlist))
    )

ashishrajsrivastava
MCT
Feb 16, 2022
Trying exactly this. Does not throw a terminal error but does not show valid results either. Trying to tweak it further.