Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

KQL Performance Optimization

Brass Contributor

 

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 the summarized table with a static datatable (Table2)

 

The performance is poor, it frequently hits the 10 minutes limits. I did troubleshooting by isolation; decomposing the query into sub-queries to pinpoint the problematic part, and found it's the summarize line!

 

Acting on the same dataset: Extend/Project operations take few seconds, JOIN takes few seconds, Summarize hits the 10 mins limit

 

QUESTION1: What are the options to improve the performance of the Summarize operation? I tried hint.strategy=shuffle but it didn't change the situation.

 

QUESTION2: How to review/confirm the settings of Data Partitioning Policy and Shuffle strategy  in Sentinel ?

 

Tried both lines:

| summarize  count() by app;

| summarize hint.strategy=shuffle count() by app;
 

 

13 Replies

@majo01 

 

I suspect you have read this: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices

 

Is the summarize that's having the issue within the Join or outside the join?  If the data is Syslog, I assume its the Syslog table and you have that on the right of the join - see link above  (the datatable being the smaller table on the left?).  Are you able to reduce the data from syslog, maybe use a bin if its across many hours or days?


Can you offer a sample of the datatable and Syslog syntax?  

 

 

 

@CliveWatson 

Yes, i read it.

Summarize is outside the JOIN, and  i confirmed that it's the culprit, not the JOIN. Any ideas to improve the performance of JOIN, given that the dataset it's acting on it very huge (tens of millions of logs).

 

//PART1: Logs preparation, note that syslog1 is a function created elsewhere:

Syslog1

| where TimeGenerated > startDatetime and TimeGenerated < endDatetime
| where Activity == "TRAFFIC" or Activity == "THREAT"

| project-rename signature = ThreatName
| project-rename threat_category = ThreatCategory
| project-rename category= ThreatType, severity = SeverityLevel, action = DeviceAction, content_version = ContentVersion

| extend app = tostring(Application), day = dayofmonth(TimeGenerated), month = getmonth(TimeGenerated), year = getyear(TimeGenerated)
| extend traffic = toint(Bytes)
| extend parsed_src_IP = parse_ipv4(SourceIP)
| extend src_zone = case(parsed_src_IP between ( parse_ipv4("192.168.0.0") .. parse_ipv4("192.168.255") ),"Guest",
parsed_src_IP between ( parse_ipv4("127.0.0.0") .. parse_ipv4("127.0.0.1") ),"Corporate",
parsed_src_IP between ( parse_ipv4("10.0.0.0") .. parse_ipv4("10.255.255.255") ),"Corporate",
parsed_src_IP between ( parse_ipv4("172.16.0.0") .. parse_ipv4("172.31.255.255") ),"Corporate",
//else
"Public")
| extend parsed_dst_IP = parse_ipv4(DestinationIP)
| extend dst_zone = case(parsed_dst_IP between ( parse_ipv4("192.168.0.0") .. parse_ipv4("192.168.255") ),"Guest",
parsed_dst_IP between ( parse_ipv4("127.0.0.0") .. parse_ipv4("127.0.0.1") ),"Corporate",
parsed_dst_IP between ( parse_ipv4("10.0.0.0") .. parse_ipv4("10.255.255.255") ),"Corporate",
parsed_dst_IP between ( parse_ipv4("172.16.0.0") .. parse_ipv4("172.31.255.255") ),"Corporate",
//else
"Public")

 

// Problematic Part which is the following summarize statement

| summarize sum(traffic), count(app) by app, src_zone, dst_zone

 

// PART3:

JOIN

@majo01 :

 

I think there is little to do with "summarize" beyond shuffle. Is the performance prior to summarize reasonable? 

 

One option is to filter before parsing. If it is easy to check Activity on raw syslog, it should accelerate things considerably. I woudl first check by including the parser in the query. If it works, the parser can be omdified to be a parameter function that can be applied after initial filtering. 

@Ofer_Shezaf 

 

Thank you.

 

I tried a bit to make a parser as parameter function, something is going wrong. How can we call the tabular parameter from within the function ?

 

Below is sample:

 

//PART1: prepare the filtered dataset that's to be passed to the parser

let filteredDataSet = Syslog
| where SyslogMessage contains ",TRAFFIC,";
 
//PART2: the parameterized function with tablular parameter. The function will perform Extend/Project actions on the table / message passed to it as parameter
let parserFunc = (T:(*) )
{
T | extend testField = SyslogMessage // here it fails to call SyslogMessage, even if SyslogMessage is passed as a strandalone string
};
 
// PART3: invoke the function and pass the filtered dataset as parameter
parserFunc(filteredDataSet)
 
Note that the function seems to be able to read the passed table as a whole, but not to invoke a specific column from the table.
This func returns the filtered dataset properly:
 
let parserFunc = (T:(*) )
{
T //| extend testField = SyslogMessage
};

 

@majo01 : the function needs to be defined with parameters for the fields that you will reference; the table you pass in will require those fields but can have others.

@Ofer_Shezaf 

 

So if want to reference the SyslogMessage field of the syslog table in the parameter function, should it be like this:

 

let parserFunc = (T:(*), msg:string )
{
T | extend testField = msg
};

 

Can you provide  a quick example of how the function should be built and called ?

 

Thanks in advance

 

@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.

@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

 

ahh, this is the exact same problem as in Kusto user-defined function for common actions 

@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.

Could you share the join part of the query?
If your left table is bigger than the right table, it is recommended to use lookup operator for the performance. https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/lookupoperator

Apart from that, what happens if you try the following?
staticTable
| join (
syslogData | extend columns
) on common columns
| summarize

@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)

 

@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