Forum Discussion

ashishrajsrivastava's avatar
ashishrajsrivastava
Copper Contributor
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 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))
)

 

Azure Sentinel
  • GaryBushey's avatar
    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's avatar
      ashishrajsrivastava
      Copper Contributor
      Feb 15, 2022
      Let me try this, I do remember trying union but not sure if I did finish till the comparison.
      • Clive_Watson's avatar
        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))
    )

         

Resources