Forum Discussion

majo01's avatar
majo01
Brass Contributor
Jul 20, 2020

KQL Performance Optimization

  Hello folks, I am building  query that basically does the following :   1-  Extend and Project fields from Table1, which contains syslogs 2- Summarize table fields mentioned in (1) 3- Join th...
Ofer_Shezaf's avatar
Ofer_Shezaf
Icon for Microsoft rankMicrosoft
Aug 09, 2020

majo01,

 

here it is:

 

let parserFunc = (T:(SyslogMessage:string))
{
  T | extend testField = SyslogMessage
};
Syslog | invoke parserFunc ()

 

the idea is to define for T the fields that want to reference in the body of the fucntion. The other "fix" is that you need to use invokde to use functions that accept tables as input.

majo01's avatar
majo01
Brass Contributor
Aug 18, 2020

Ofer_Shezaf 

Hi Ofer,

 

I found that if i save the function Under "Saved Queries" and as a function, it won't work by calling it from KQL editor. Is this expected ?

 

The code works when all put together in same KQL window

 

Working scenario:

let parserFunc = (T:(SyslogMessage:string))
{
  T | extend testField = SyslogMessage
};
Syslog | invoke parserFunc ()

Non-working scenario (required scenario):

Save the function as "testFunc":

let parserFunc = (T:(SyslogMessage:string))
{
T | extend testField = SyslogMessage
};

 

Then invoke the function from KQL:

Syslog | invoke testFunc ()

 

The result is "Body of the callable expression cannot be empty" .

The requirement is that to save the paramter function so that it gets reused and invoked by many users

 

  • Ofer_Shezaf's avatar
    Ofer_Shezaf
    Icon for Microsoft rankMicrosoft
    Aug 19, 2020

    pemontto as you do for non-parameter functions when you save them in the workspace, the fucntion has to be a valid query, not a let statement. The parameter definition and alias are part of the API/PS and not needed in a let statement. Here is an example:

     

    Set-AzContext -Subscription "...." -Name 'MyContext'
    New-AzOperationalInsightsSavedSearch `
    -ResourceGroupName "soc" `
    -WorkspaceName "..." `
    -SavedSearchId "is_private_addr_id" `
    -DisplayName "is_private_addr" `
    -Category "Advanced Functions" `
    -Query "ipv4_is_match(ipaddr, '192.168.0.0/16') or ipv4_is_match(ipaddr, '172.16.0.0/12') or ipv4_is_match(ipaddr, '10.0.0.0/8')" `
    -FunctionAlias "is_private_addr" `
    -FunctionParameter "ipaddr: string" `
    -Version 1 `
    -Force

     

  • pemontto's avatar
    pemontto
    Brass Contributor
    Aug 19, 2020

    Ofer_Shezaf just tried this with a minimal example below and I'm still seeing the same error "Body of the callable expression cannot be empty". I can confirm as expected the query doesn't show up in the query explorer if I use the functionalias and and functionparameter args, but see it's registered as when I remove it I get "Unknown function: 'is_primary'."

     

    $ResourceGroupName = "my_rg"
$WorkspaceName = "my_workspace"
$Version = 1
$DisplayName = "is_primary_fx"
$SavedSearchId = "$DisplayName"
$Category = "lookupfx"
$FunctionAlias = "is_primary"
$FunctionParameter = 'ip:string'
$query = @"
let is_primary = (ip:string) {
    iif(dynamic([
        "127.0.0.1",
        "127.0.0.2",
        "127.0.0.3"
    ]) contains ip, true, false)
};
"@

New-AzOperationalInsightsSavedSearch `
    -ResourceGroupName $ResourceGroupName `
    -WorkspaceName $WorkspaceName `
    -DisplayName $DisplayName `
    -Category $Category `
    -SavedSearchId $SavedSearchId `
    -Query $query `
    -Version $Version `
    -FunctionAlias $FunctionAlias `
    -FunctionParameter $FunctionParameter

     

    Attempted to test with:

    // Basic print, expect true
print is_primary("127.0.0.1")

// Filter a datatable
let NetworkData = datatable (Address:string )
[
"127.0.0.1",
"127.1.1.1",
"127.0.0.2",
"127.2.2.2"
];
NetworkData
| where is_primary(Address)

     

  • Ofer_Shezaf's avatar
    Ofer_Shezaf
    Icon for Microsoft rankMicrosoft
    Aug 18, 2020

    majo01 : at this time, saving and updating parameter queries needs to be done using the API, or more conveniently, PowerShell. This works, but is not visible in the query explorer.