Getting the number of concurrent requests for each bin in Kusto / Application Insights

Question

I want to know how many requests are currently executing at any given second in Azure Application Insights.

For example, if request 1 starts at 10:00:00 and finished at 10:00:03 (timestamp of 10:00:00 and duration of 3999ms), it should be counted in the rows for 10:00:00, 10:00:01, 10:00:02 and 10:00:03

The output should be something like:

| timestamp               | count_ |
| ----------------------- | ------ |
| 0000-00-00T00:00:00.000 | 254    |
| 0000-00-00T00:00:00.001 | 345    |
| 0000-00-00T00:00:00.002 | 216    |

I thought I could maybe use the join operator against a table of seconds, but I cannot use something like on $right.timestamp >= $left.timestamp and $right.timestamp + duration < $left.timestamp, kusto replies with join: Only equality is allowed in this context..

So how can I get concurrent requests (or dependencies) by timestamp bin?

Avnera Avnera · Accepted Answer · 2020-02-26T14:25:06

Assuming that you can tell the start and end of each session, you can use the range() function to generate the applicable datetime values by the bin size when the session is active, and then use the mv-expand operator to expand the list so you can count the concurrent sessions. Here is an example:

let sampleEvents = datatable(sessionId:string,eventType:string, timestamp:datetime)
["A", "start", datetime(2020-02-29T10:32:52.1953143Z),
"A", "end", datetime(2020-02-29T10:33:08.1953143Z),
"B", "start", datetime(2020-02-29T10:32:57.1953143Z),
"B", "end", datetime(2020-02-29T10:33:10.1953143Z)];
let startEvents = sampleEvents | where eventType =="start" | project startTime = timestamp, sessionId;
startEvents
| lookup kind=leftouter (sampleEvents | where eventType =="end" | project endTime=timestamp, sessionId) on sessionId
| extend endTime = iif(isempty(endTime), now(), endTime)
| extend sessionActiveTimeInOneSecondInterval = range(startTime, endTime, 1s)
| mv-expand sessionActiveTimeInOneSecondInterval to typeof(datetime)
| summarize count() by bin(sessionActiveTimeInOneSecondInterval, 1s)

Also consider using the row_window_session() function to determine the session's start time

Getting the number of concurrent requests for each bin in Kusto / Application Insights

1 Answers