Forum Discussion

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

majo01

Brass Contributor

Jul 21, 2020

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

Ofer_Shezaf

Microsoft

Jul 28, 2020

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.

majo01
Brass Contributor
Aug 04, 2020
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
};
- Ofer_Shezaf
  Microsoft
  Aug 05, 2020
  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.
  - majo01
    Brass Contributor
    Aug 06, 2020
    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