'How to calculate time grain based on selected period from azure dashboard in a custom chart

While writing a kusto query to create a custom chart on my azure dashboard, I want to be able to calculate the time grain based on the period the user selected on the dashboard.

For example: last 4h => time grain 2 mins, last 24h => 10 mins

I tried the following to calculate the period because we are still unable to access it (as far as I could find on the internet).

let timeGrain = traces
| summarize min_time = min(timestamp), max_time = max(timestamp)
| extend timeWindow = max_time - min_time   // days / hrs/ min / seconds
| project timeWindow
| extend timeGrain = case(timeWindow <= 4h, "2m", 
                       timeWindow <= 12h, "5m", 
                       timeWindow <= 24h, "10m",
                       "2h")
                       | project timeGrain;

The query returns me the time grain I want to achieve but I am unable to use this variable inside of my other query.

traces
...
| summarize percentile(DurationInMs, 50) by bin(timestamp, timeGrain), CommandType
| render areachart with (ytitle = "Duration In Ms", xtitle = "Timestamp");

(I know traces isn't the best place to store data regarding duration, we are gonna change this to metrics but it's not the scope of the question)

This gives me the following error: 'summarize' operator: Failed to resolve scalar expression named 'timeGrain'

Is there a way to fix this error or is there a better way to create a dynamic time grain?

azureazure-application-insightskql


Solution 1:[1]

Obviously I do not have the same fields in my traces but you should use a timespan instead of a string to define timeGrain.

Also, to use the query result timeGrain as a variable, use toscalar (docs):

let timeGrain = toscalar(traces
| summarize min_time = min(timestamp), max_time = max(timestamp)
| extend timeWindow = max_time - min_time   // days / hrs/ min / seconds
| project timeWindow
| extend timeGrain = case(timeWindow <= 4h, 2m, 
                       timeWindow <= 12h, 5m, 
                       timeWindow <= 24h, 10m,
                       2h)
                       | project timeGrain);  
traces
| summarize count() by bin(timestamp, timeGrain)
| order by timestamp desc

this works just fine.

Solution 2:[2]

This may not be a direct answer to the question but may be useful for others who do not want to create logic to infer time grain from time range.

Use a workbook to create chart from app insights query. Add a time range parameter and refer to the parameter in query. {TimeRange:grain} would give you granularity corresponding to time range selected. Now pin the query part to dashboard and voila! Your chart is ready to use time range selected on dashboard, auto refresh parameter.

Create workbook and pin parts to dashboard: https://docs.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-overview Time range parameter: https://docs.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-time

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Peter Bons
Solution 2 zendu

Related Questions