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