Azure dashboard is not getting updated with new time range when pinned from workbook

Question

I want to visualize some data in map and thereby I used workbook. I have not set the time range in the query since the time range should be configurable in the dashboard. After pinning the particular tile to the dashboard, the map would not get updated when the time range is changed. When I change the time range in the workbook it worked as expected though.

Please find the Kusto query that I tried in the workbook:

let mainTable = union customEvents
    | extend name =replace("\n", "", name)
    | where iif('*' in ("*"), 1 == 1, name in ("*"))
    | where true;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = client_CountryOrRegion
    | extend dimension = iif(isempty(dimension), "<undefined>", dimension)
    | summarize hll = hll(itemId) by tostring(dimension)
    | extend Events = dcount_hll(hll)
    | order by Events desc
    | serialize rank = row_number()
    | extend dimension = iff(rank > 5, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension)
    | project ['Country or region'] = dimension, Counts = dcount_hll(merged);
cohortedTable

Your input on this is really helpful. Thanks in advance

Answer 1

In order for the query to update when the dashboard time range updates, the query in the workbook needs to use a time range parameter:

https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-overview#dashboard-time-ranges

Pinned workbook query parts will respect the dashboard's time range if the pinned item is configured to use a Time Range parameter. The dashboard's time range value will be used as the time range parameter's value, and any change of the dashboard time range will cause the pinned item to update. If a pinned part is using the dashboard's time range, you will see the subtitle of the pinned part update to show the dashboard's time range whenever the time range changes.

Additionally, pinned workbook parts using a time range parameter will auto refresh at a rate determined by the dashboard's time range. The last time the query ran will appear in the subtitle of the pinned part.

If a pinned step has an explicitly set time range (does not use a time range parameter), that time range will always be used for the dashboard, regardless of the dashboard's settings. The subtitle of the pinned part will not show the dashboard's time range, and the query will not auto-refresh on the dashboard. The subtitle will show the last time the query executed.

You'll need to update your workbook to have a time range parameter, then update that query step to either use that time range parameter in the query text, like

let mainTable = union customEvents
    | where timestamp {TimeRange}  // reference the time range parameter in the query text
    | extend name =replace("\n", "", name)
    | where iif('*' in ("*"), 1 == 1, name in ("*"))
    | where true;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = client_CountryOrRegion
    | extend dimension = iif(isempty(dimension), "<undefined>", dimension)
    | summarize hll = hll(itemId) by tostring(dimension)
    | extend Events = dcount_hll(hll)
    | order by Events desc
    | serialize rank = row_number()
    | extend dimension = iff(rank > 5, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension)
    | project ['Country or region'] = dimension, Counts = dcount_hll(merged);
cohortedTable

or by choosing the time range parameter from the time range dropdown in the UX when editing the query (for logs based queries, if this is a query to the ADX data source where time range isn't a field in the ux, you have to use the time range parameter in the query text)

If you pin query step that uses a query like this (referencing a time range parameter) to a dashboard, then the dashboard knows how to "inject" the dashboard's time range into the query. (Without a time range parameter, there are various (naïve) ways we could try to inject a time range, but depending on exactly what the query does, there's a chance the query works but is not correct)

See the time range parameter link above for details on the ways you can use time range parameters, and the various syntax available, there are ways to get things like the start, end, duration, bucketing, etc, for a time range in the parameter syntax.