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

[Ashish Raj]

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 mv-apply but with no success, can any guide me in this.

Here is my code snippet:

let timeframe = ago(3h);
let threshold = 2;
let ZSwatchlist = (_GetWatchlist('zscaler')
| project SearchKey);
let zarray = (ZSwatchlist
| summarize zlist = make_list(SearchKey));
let users = (imAuthentication
| where TargetUserType != 'ServicePrincipal'
| where TimeGenerated > timeframe
| where EventType == 'Logon' and EventResult == 'Success'
| where isnotempty(SrcGeoCountry)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Vendors=make_set(EventVendor), Products=make_set(EventProduct), Countries = make_set(SrcGeoCountry), IPAddresses = make_set(SrcDvcIpAddr)
, NumOfCountries = dcount(SrcGeoCountry)
by TargetUserId, TargetUsername, TargetUserType);
users
| mv-apply ipscaler=toscalar(IPAddresses) to typeof(string) on(
where not(ipv4_is_in_range(IPAddresses,zarray))
)

[#2]

@Ashish Raj 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.

[#3]

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

[#4]

@Ashish Raj

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

[#5]

Trying exactly this. Does not throw a terminal error but does not show valid results either. Trying to tweak it further.