SOLVED

Enrich data with a custom function kql

%3CLINGO-SUB%20id%3D%22lingo-sub-1416119%22%20slang%3D%22en-US%22%3EEnrich%20data%20with%20a%20custom%20function%20kql%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416119%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI'm%20looking%20to%20create%20a%20custom%20function%20in%20kql%20to%20add%20the%20subnet%20name%20to%20my%20result%20table%2C%20based%20on%20where%20the%20IP%20matches%20the%20subnet%20provided%20in%20a%20json%20array.%20I%20am%20using%20the%20ipv4_is_match%20function%2C%20but%20I%20run%20into%20the%20restriction%20as%20listed%20here%3A%20%3CA%20title%3D%22Kusto%20restriciton%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Ffunctions%2Fuser-defined-functions%23restrictions%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EKusto%20restriciton%3C%2FA%3E%3C%2FP%3E%3CP%3EA%20simple%20join%20worked%20fine%2C%20but%20when%20I%20have%20to%20use%20the%20function%20to%20match%20it%20gives%20me%20the%20following%20error%3ASemantic%20error%3A%20''%20has%20the%20following%20semantic%20error%3A%20Unresolved%20reference%20binding%3A%20'client_ip_s'.%3C%2FP%3E%3CP%3EThe%20function%20itself%20works%20fine%20when%20I%20simply%20provide%20an%20IP%20address%20myself.%20Can%20anybody%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20IpClassify%3D%0Aprint%20fwarray%20%3D%20dynamic(%5B%7B%20%22name%22%3A%20%22PowerBi012%22%2C%20%22endipaddress%22%3A%20%2252.155.234.117%22%2C%20%22startipaddress%22%3A%20%2252.155.234.117%22%20%7D%5D)%0A%20%7C%20mvexpand%20fwarray%0A%20%7C%20evaluate%20bag_unpack(fwarray)%3B%0Alet%20IpRangeClassify%3D(ip%3Astring)%0A%7B%0A%2F%2Flet%20ip%20%3D%20%20%2210.160.94.0%22%3B%0A%20let%20Result%20%3D%20print%20fwarray%20%3D%20dynamic(%5B%0A%7B%22Name%22%3A%20%22dsvm-subnet%22%2C%20%22AddressPrefix%22%3A%20%2210.160.157.0%2F25%22%7D%2C%0A%7B%22Name%22%3A%20%22adf-subnet%22%2C%22AddressPrefix%22%3A%2210.160.94.0%2F25%22%7D%2C%0A%7B%22Name%22%3A%20%22dsvm-subnet%22%2C%22AddressPrefix%22%3A%2210.160.93.0%2F25%22%7D%2C%0A%7B%22Name%22%3A%20%22management-subnet%22%2C%22AddressPrefix%22%3A%2210.160.95.0%2F25%22%7D%5D)%0A%20%7C%20mvexpand%20fwarray%0A%20%7C%20evaluate%20bag_unpack(fwarray)%0A%20%7C%20where%20ipv4_is_match(ip%2C%20AddressPrefix)%20%3D%3D%20true%0A%20%7C%20project%20Name%3B%0A%20iif(isempty(toscalar(Result))%20%2C%20toscalar('Not%20in%20known%20subnet')%2C%20toscalar(Result))%0A%7D%3B%0AAzureDiagnostics%0A%7C%20where%20Category%20%3D%3D%20'SQLSecurityAuditEvents'%0A%7C%20project%20TimeGenerated%2C%20succeeded_s%2C%20client_ip_s%2C%20server_principal_name_s%2C%20database_principal_name_s%2C%20database_name_s%2C%20additional_information_s%2C%20host_name_s%2C%20statement_s%2C%20application_name_s%2C%20server_instance_name_s%0A%7C%20extend%20serverdb%20%3D%20strcat(server_instance_name_s%2C%20'%2F'%2C%20database_name_s)%0A%7C%20summarize%20counts%3Dcount()%2C%20addinfo_set%3Dmakeset(additional_information_s)%2C%20stmt_set%3Dmakeset(statement_s)%2C%20app_set%3Dmakeset(application_name_s)%2C%20host_set%3Dmakeset(host_name_s)%20by%20bin(TimeGenerated%2C%201d)%2C%20succeeded_s%2C%20serverdb%2C%20client_ip_s%0A%7C%20join%20kind%3Dleftouter%20IpClassify%20on%20%24left.client_ip_s%20%3D%3D%20%24right.startipaddress%0A%7C%20extend%20other%3DIpRangeClassify(client_ip_s)%0A%2F%2F%7C%20project%20name%2C%20client_ip_s%2F%2F%2C%20other%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1416119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1417724%22%20slang%3D%22en-US%22%3ERe%3A%20Enrich%20data%20with%20a%20custom%20function%20kql%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678893%22%20target%3D%22_blank%22%3E%40WiJaN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHey%2C%20try%20changing%20your%20function%20as%20shown%20below%20(I%20also%20added%20a%20test-subnet%20option%20to%20catch%20the%20values%20I%20have)%3C%2FP%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20IpRangeClassify%3D(ip%3Astring)%0A%7B%0Acase(%0Aipv4_is_match(ip%2C%20%2210.160.157.0%2F25%22)%2C%20%22dsvm-subnet%22%2C%0Aipv4_is_match(ip%2C%20%2210.160.94.0%2F25%22)%2C%20%22adf-subnet%22%2C%0Aipv4_is_match(ip%2C%20%2210.160.93.0%2F25%22)%2C%20%22dsvm-subnet%22%2C%0Aipv4_is_match(ip%2C%20%2210.160.157.0%2F25%22)%2C%20%22management-subnet%22%2C%0Aipv4_is_match(ip%2C%20%2223.102.166.0%2F25%22)%2C%20%22test-subnet%22%2C%0A%22Not%20in%20known%20subnet%22)%0A%2F%2F%20let%20Result%20%3D%20print%20fwarray%20%3D%20dynamic(%5B%0A%2F%2F%7B%22Name%22%3A%20%22dsvm-subnet%22%2C%20%22AddressPrefix%22%3A%20%2210.160.157.0%2F25%22%7D%2C%0A%2F%2F%7B%22Name%22%3A%20%22adf-subnet%22%2C%22AddressPrefix%22%3A%2210.160.94.0%2F25%22%7D%2C%0A%2F%2F%7B%22Name%22%3A%20%22dsvm-subnet%22%2C%22AddressPrefix%22%3A%2210.160.93.0%2F25%22%7D%2C%0A%2F%2F%7B%22Name%22%3A%20%22management-subnet%22%2C%22AddressPrefix%22%3A%2210.160.95.0%2F25%22%7D%5D)%0A%2F%2F%7C%20mvexpand%20fwarray%0A%2F%2F%7C%20evaluate%20bag_unpack(fwarray)%0A%2F%2F%7C%20where%20ipv4_is_match(ip%2C%20AddressPrefix)%20%3D%3D%20true%0A%2F%2F%7C%20project%20Name%3B%0A%2F%2Fiif(isempty(toscalar(Result))%20%2C%20toscalar('Not%20in%20known%20subnet')%2C%20toscalar(Result))%0A%7D%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20got%20these%20results%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22query_results.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194411i9E8E6216FF4BA8DE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22query_results.png%22%20alt%3D%22query_results.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419484%22%20slang%3D%22en-US%22%3ERe%3A%20Enrich%20data%20with%20a%20custom%20function%20kql%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3EAwesome%2C%20that%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
Best Response confirmed by WiJaN (New Contributor)
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:

query_results.png

@Noa KuperbergAwesome, that works perfectly!