Azure KQL - Create Total Average Line on top of timechart-CodePudding

I have some data that I'm logging from our internal label print system. I want to see the duration of each request in a timechart, but would also like to have a total average line on top of the other one. Is there anyway that this can be achieved in KQL?

Notice the red line. This is what I want.

I'm currently using the following KQL:

requests
| where url has "api/FileHandover"
| project Duration = duration, Timestamp = timestamp
| summarize Average = avg(Duration) by Timestamp
| render timechart

CodePudding user response：

It's recommended not to group by timestamp, but by the requested bin, E.g., bin(timestamp, 1h)

// Data sample generation. Not part of the solution
let requests = range i from 1 to 1000 step 1 | extend url= "api/FileHandover", duration = 1d * rand() / 1m, timestamp = ago(rand() * 7d);
// Solution starts here
let raw_data = requests | where url has "api/FileHandover";
let total_avg = toscalar(raw_data | summarize avg(duration));
raw_data
| summarize Average = avg(duration) by bin(timestamp, 1h)
| extend total_avg
| render timechart

// Data sample generation. Not part of the solution
let requests = range i from 1 to 1000 step 1 | extend url= "api/FileHandover", duration = 1d * rand() / 1m, timestamp = ago(rand() * 7d);
// Solution starts here
requests
| where url has "api/FileHandover"
| as raw_data;
raw_data
| summarize avg(duration)
| as total_avg;
raw_data
| summarize Average = avg(duration) by bin(timestamp, 1h)
| extend total_avg = toscalar(total_avg)
| render timechart