CODEWITHSUNDEEP
azure-data-explorerkql
Matt M
Matt M

Reputation: 21

Table Entry Comparison Against Static Array in Kusto

I have a table of multiple rows and columns which contains Sign-in events by username. Each row provides other details such as Time, IP Address, Device info, Location info, etc. I have another table which is a single row with multiple columns of IPv4 Ranges (a watchlist). These IPv4 Ranges are considered "malicious". I would like to be able to return data from the SigninLogs table and compare the IP Address element of each row against each IPv4 Range to verify if the IP Address is a member of any IP Range found in the "malicious" table. If this condition is true, I wish to perform specific action. The function to perform a "range" analysis on an IPv4 subnet is

ipv4_is_in_range(<IP Address>, <IP Range>)

I have tried a few different methods, all of which have gotten nowhere. Here is an example of a strategy I have tried:

let GetPackage = (v_IPAddress: string){
    _GetWatchlist('Bad_IP_Ranges')
    | summarize IPAddresses = make_list(pack_array(IPAddress))
    | mv-apply IPAddresses on ( 
        where ipv4_is_in_range(v_IPAddress, tostring(IPAddresses))
    )
};
SigninLogs
| union AADNonInteractiveUserSignInLogs
| extend Malicious_IP = iff(GetPackage(IPAddress), IPAddress, "null")
| project TimeGenerated, Category, ResultType, ResultDescription, Identity, Malicious_IP, AppDisplayName
| order by TimeGenerated | take 1000

This gives an error:

iff(): argument #1 must be a scalar expression

I understand that it's complaining about the GetPackage() function returning a tabular value, but I do not know how to force the GetPackage() function to return just the malicious IP Address if the where statement is true.

Upvotes: 0

Views: 765

Answers (2)

chaser_o
chaser_o

Reputation: 13

Use lookup function in scalar context:

let lookup = toscalar(GetPackage
        | summarize l=make_list(vpn_ip_cidr));
    SigninLogs
    | where TimeGenerated > ago(1d)
    | mv-apply l=lookup to typeof(string) on
        (
        where ipv4_is_match (IPAddress, l)
        )
    | project-away l

Upvotes: 0

Avnera
Avnera

Reputation: 7618

There are a couple of new methods that should be helpful here (note that they are only available if you are using EngineV3):

  1. has_any_ipv4_prefix()
  2. has_any_ipv4()

Upvotes: 0

Related Questions