Enrich data with a custom function kql

[WiJaN]

Hi!

I'm looking to create a custom function in kql to add the subnet name to my result table, based on where the IP matches the subnet provided in a json array. I am using the ipv4_is_match function, but I run into the restriction as listed here: Kusto restriciton

A simple join worked fine, but when I have to use the function to match it gives me the following error:Semantic error: '' has the following semantic error: Unresolved reference binding: 'client_ip_s'.

The function itself works fine when I simply provide an IP address myself. Can anybody help me with this?

let IpClassify=
print fwarray = dynamic([{ "name": "PowerBi012", "endipaddress": "52.155.234.117", "startipaddress": "52.155.234.117" }])
 | mvexpand fwarray
 | evaluate bag_unpack(fwarray);
let IpRangeClassify=(ip:string)
{
//let ip =  "10.160.94.0";
 let Result = print fwarray = dynamic([
{"Name": "dsvm-subnet", "AddressPrefix": "10.160.157.0/25"},
{"Name": "adf-subnet","AddressPrefix":"10.160.94.0/25"},
{"Name": "dsvm-subnet","AddressPrefix":"10.160.93.0/25"},
{"Name": "management-subnet","AddressPrefix":"10.160.95.0/25"}])
 | mvexpand fwarray
 | evaluate bag_unpack(fwarray)
 | where ipv4_is_match(ip, AddressPrefix) == true
 | project Name;
 iif(isempty(toscalar(Result)) , toscalar('Not in known subnet'), toscalar(Result))
};
AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| project TimeGenerated, succeeded_s, client_ip_s, server_principal_name_s, database_principal_name_s, database_name_s, additional_information_s, host_name_s, statement_s, application_name_s, server_instance_name_s
| extend serverdb = strcat(server_instance_name_s, '/', database_name_s)
| summarize counts=count(), addinfo_set=makeset(additional_information_s), stmt_set=makeset(statement_s), app_set=makeset(application_name_s), host_set=makeset(host_name_s) by bin(TimeGenerated, 1d), succeeded_s, serverdb, client_ip_s
| join kind=leftouter IpClassify on $left.client_ip_s == $right.startipaddress
| extend other=IpRangeClassify(client_ip_s)
//| project name, client_ip_s//, other

[#2 · Accepted solution]

@WiJaN 

Hey, try changing your function as shown below (I also added a test-subnet option to catch the values I have)

let IpRangeClassify=(ip:string)
{
case(
ipv4_is_match(ip, "10.160.157.0/25"), "dsvm-subnet",
ipv4_is_match(ip, "10.160.94.0/25"), "adf-subnet",
ipv4_is_match(ip, "10.160.93.0/25"), "dsvm-subnet",
ipv4_is_match(ip, "10.160.157.0/25"), "management-subnet",
ipv4_is_match(ip, "23.102.166.0/25"), "test-subnet",
"Not in known subnet")
// let Result = print fwarray = dynamic([
//{"Name": "dsvm-subnet", "AddressPrefix": "10.160.157.0/25"},
//{"Name": "adf-subnet","AddressPrefix":"10.160.94.0/25"},
//{"Name": "dsvm-subnet","AddressPrefix":"10.160.93.0/25"},
//{"Name": "management-subnet","AddressPrefix":"10.160.95.0/25"}])
//| mvexpand fwarray
//| evaluate bag_unpack(fwarray)
//| where ipv4_is_match(ip, AddressPrefix) == true
//| project Name;
//iif(isempty(toscalar(Result)) , toscalar('Not in known subnet'), toscalar(Result))
};

I got these results:

[#3]

@Noa KuperbergAwesome, that works perfectly!