Combine Complex Kusto Queries

Question

I have a Python Azure Function that produces custom logging messages when the Function executes. I'm able to pull specific info from the JSON inside the log strings (thank you @yoni).

How do I correctly combine 2 or more Kusto queries which parse different logging messages into a single query?

Example:

• Each Azure Function execution produces several Python logging() messages (prepended with #######)
• I'm able to use Kusto to parse the needed info highlighted in yellow

Kusto query (spacing for readability):

let varLookback = ago(1d);

let varPath = 

union traces
| union exceptions
| where timestamp > varLookback
| where message contains "####### Will write to"

//Pulls filePath out of Python logging string
| extend parsedMessage = todynamic(trim(@"#######",substring(message, 21)))
| project operation_Id, timestamp, filePath = parsedMessage;

union traces
| union exceptions
| where timestamp > varLookback
| where message contains "####### EventGrid trigger processing an event"

//Pulls message JSON out of Python logging string
| extend parsedMessage = todynamic(trim(@"#######",substring(message, 46)))

//Parses fileName and contentLength from message JSON
| project operation_Id, timestamp, fileName = split(parsedMessage["data"]["blobUrl"], "/")[6], contentLength = parsedMessage["data"]["contentLength"]

//I've tried different join kinds here (inner, inner_unique, right_outer, etc. same erroneous results)
| join varPath on $left.operation_Id == $right.operation_Id
| order by timestamp asc

Issue:

• The combined Kusto queries shown above do not return the correct number of results.
• The results count should be 36 files, only 23 are returned
• I can't immediately see where the problem is (new to Kusto)

EDIT 1:

• After some troubleshooting, there seems to be a core discrepancy between the number of Function executions and the number of Function executions that contain the custom logging message.

• Verified count of Function executions in last 1d: 36

• (this Function moves files between two storage accounts. I verified that 36 files were moved in last 1d)

• Count of Function execution logging messages with keyword: 23

Why are some Function executions not showing custom logging?

Answer 1

a. Make sure you've chosen the right join kind (default is innerunique(doc))

• If that doesn't help, perhaps including the (obfuscated) contents of both join legs in your question would help (as the total number of records seem low enough to fit)

Unrelated, perf tips:

1. Prefer using has over contains whenever possible (doc)

2. trim() (doc) takes a regular expression as its first argument. Based on the content of your messages, see if you can choose a better operator function, that doesn't require a regular expression (e.g. parse operator or substring() function)