Azure Application Insights: How to make a nice time graph from a custom KQL query?

Question

In Azure Application Insights you can make these nice time graphs where the application automatically figures out how to split the full time period into reasonable slices for the graph. For example this response time graph:

It is also possible to make graphs from a custom KQL query, e.g. for an Application Insights dashboard.

But if I want to make a graph that shows something over time using a custom KQL query, it starts to get difficult. I can show, for example, the number of requests per day with the KQL below:

requests 
| summarize 
    Total = count()
    by Date = format_datetime(timestamp, 'yyyy-MM-dd')
| sort by Date asc
| render timechart

But this is awkward. I would like to have the system determine the slices automatically instead of having to hardcode "per day" or the like.

(This example is simplified. If I just want a graph of the request count I can use one of the built-in graphs. But if I want to use a more complicated query, I cannot use the built-in graphs.)

Can I do nice time-based graphs with a custom query?

Answer 1

I would like to have the system determine the slices automatically instead of having to hardcode "per day" or the like.

To determine the slices automatically of a custom KQL query, you can use a function called bin() or floor() available in Kusto as shown with the below syntax.

bin(value,round To)

It will automatically adjust the time slices based on the given duration (roundTo) value even for a complex query.

Usage of the above:

AppRequests
| summarize Total = count() by bin(TimeGenerated, 1h)
| sort by TimeGenerated asc
| render timechart

AppRequests
| summarize Total = count() by floor(TimeGenerated, 1h)
| sort by TimeGenerated asc
| render timechart