Kusto Query, How to Save Query Result and Use Later

Question

In App Insight, how can I write a KQL that save a query result into a variable, and use that variable later in a second query?

For example, find the timestamp when an incident happens:

let incidentTime = traces 
| where message = "UNIQUE IDENTIFIER"
| limit 1

Later use this timestamp in a 2nd query to find nearby traces when incident happens

traces
| where timestamp between (datetime_diff('minute', -1, incidentTime)..incidentTime)

The second query gives me an error basically saying cannot retrieve the scalar value from incidentTime.

How can I read the value from incidentTime and put it into the 2nd query?

Answer 1

you can use toscalar() and around():

• https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/toscalarfunction
• https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/around-function

for example:

let incidentTime = toscalar(
    traces 
    | where message = "UNIQUE IDENTIFIER"
    | project timestamp
    | limit 1
);
traces
| where around(timestamp, incidentTime, 1m)

similarly, if you want to do so for multiple columns:

let params = toscalar(
    traces 
    | where message = "UNIQUE IDENTIFIER"
    | project pack_array(timestamp, username)
    | limit 1
);
traces
| where around(timestamp, todatetime(params[0]), 1m)
| where username == tostring(params[1])