Render timechart on summarized rows with timespan

user_846163 0
Viewed 166

In Azure Application Insights I grouped traces entries per InvocationId to trace parallel calls of an Azure Function.

traces
| where timestamp between (todatetime('2022-06-29T21:00:00Z')..todatetime('2022-06-29T22:00:00Z'))
| where tostring(customDimensions.InvocationId) <> "" 
| summarize StartedAt=min(timestamp), FinishedAt=max(timestamp), 
            Succeeded=max(severityLevel)==1
         by operation_Id, tostring(customDimensions.InvocationId)

Based on the Kusto query above, I want to create a chart, which displays the number of parallel runs over time. While I looked into Window functions and the make_series operator, I found no solution. I want to render a timechart, which shows per minute, how many invocations are running in parallel, e.g. countif(currentMinute? between (StartedAt..FinishedAt))

How can I produce the desired chart?

azure-monitoring kql azure-application-insights
2 Answers

Update

Simplified solution

There are 2 downsides to this solution:

  1. The graph may seem misleading where there are no data points (A line stretched between 2 points hides the fact that between them the number of events is actually 0)
  2. In case there are many data points, there might be memory pressure for the chart rendering.
// Sample generation. Not part of the solution
let t = materialize(range i from 1 to 100 step 1 | project duration = 1h*rand(), StartedAt = ago(rand()*1d) | extend FinishedAt = StartedAt + duration | project-away duration);
// Solution starts here 
let p_resolution = 15m;
t
|   mv-expand       timestamp   = pack_array(StartedAt, FinishedAt) to typeof(datetime)
                   ,delta       = dynamic([1, -1])                  to typeof(int)
|   order by        timestamp asc
|   project         timestamp, running_total = row_cumsum(delta)
|   render          timechart

simplified

Fiddle

This solution continues where you left, when we already have each call's StartedAt & FinishedAt.
From there we count each StartedAt as +1 and each FinishedAt as -1.

The running total is the number of parallel executions at each point.

// Sample generation. Not part of the solution
let t = materialize(range i from 1 to 100 step 1 | project duration = 1h*rand(), StartedAt = ago(rand()*1d) | extend FinishedAt = StartedAt + duration | project-away duration);
// Solution starts here 
let p_resolution = 15m;
t
|   mv-expand       timestamp   = pack_array(StartedAt, FinishedAt) to typeof(datetime)
                   ,delta       = dynamic([1, -1])                  to typeof(int)
|   order by        timestamp asc
|   extend          running_total = row_cumsum(delta)
|   make-series     max(running_total) on timestamp step p_resolution
|   render          timechart

make-series

Fiddle

user_63364790

Based on the accepted solution, I simplified the original Kusto query, since the timestamps of StartedAt and FinishedAt can be identified with (message startswith "Executing '" or message startswith "Executed '"). The delta value can be extracted considering the message. It makes summarize and one mv-expand obsolete.

traces
| where timestamp between (todatetime('2022-06-29T21:00:00Z') .. todatetime('2022-06-29T22:00:00Z'))
| where tostring(customDimensions.InvocationId) <> ""
// Adapting the original query from the question..
| where (message startswith "Executing '" or message startswith "Executed '")
| extend delta=case(message startswith "Executing '", 1, -1)
// Using the solution from the accepted answer..
| make-series delta=sum(delta) on timestamp step 5s
| mv-expand timestamp to typeof(datetime), delta to typeof(int)
| serialize 
| project timestamp, running_total = row_cumsum(delta)
| render timechart
user_8461630
Related