none
How to access the time range selected in query editor from within a query? RRS feed

  • Question

  • Hi everybody,

    is it possible to access the time range selected in the query editor from within the query?

    Background: I don't want to define the time range in the query itself, but rather want it to be selectable from the query editor. However, parts of my query are dependent on the selected timestamp, such as the bin size when summarizing binned by timestamp (e.g. when selected time range is < 1h I want bins of 1m granularity, if it's 1d I want bins of 15m granularity, etc) 

    See also my question on twitter here:
    https://twitter.com/tknerr_de/status/1093812068980084737

    I already found `bin_auto(timestamp)` which looked promising, however I still need to explicitly `set query_bin_auto_size=1m` (i.e. again hardcode the bin size in the query to a fixed value, which is exactly not what I want).

    Any ideas how to compute the bin size dynamically based selected time range in query editor UI?

    Cheers,
    Torben

    Friday, February 8, 2019 2:27 PM

All replies

  • Hello, Torben!

    The immediate answer here is that in the scenario you described above, the design was to change the query with a where timegenerated >ago() like the following:

    | where timegenerated > ago(1d) 

    Having said that, we do see where the functionality you outlined would be useful and we'd love to have you submit the idea:

    https://feedback.azure.com/forums/357324-application-insights

    I'd be happy to submit the idea on your behalf, if you prefer, but submitting it yourself makes it easier to keep tabs on updates and the like.  If you do submit the idea, please post the link here so that myself and others can vote on it!

    Thursday, February 14, 2019 12:39 AM
    Moderator
  • Torben,

    Just wanted to expand on the previous reply. 

    The Analytics UI simply issues API calls to the data platform underlying App Insights, and then renders the results in a nice manner. There are no hooks into the UI. What I would recommend is something as follows, still dependent on specifying the time range in query, but then picking the bins out according to customer logic. I’ve included this as an example that you can run on our demo environment here. Note that the query in your screenshot stays roughly the same, we simply add some additional code to derive the bin size:

    let timerange = 5d; //change this to set time query runs over and automatically select bin size
    let bin_size = (tr: timespan){
        case(
            tr <= 12h, 10m,
            tr <= 2d, 1h,
            tr <= 30d, 1d,
            7d
            )
    };
    performanceCounters
    | where timestamp > ago(timerange)
    | where name == "% Processor Time"
    | summarize ["% processor time (avg)"] = sum(value)/count() by cloud_RoleInstance, bin(timestamp, bin_size(timerange))
    | render timechart
    

    Here are a couple of screenshots, one over 5 days (1 day derived bin size), and one over 10 hours (10 minute derived bin size). The only thing that changes is the timerange variable:

    Hope that helps!

    -Evgeny

    Tuesday, February 19, 2019 7:36 PM
  • Hi kobulloc-msft,

    sorry for the late reply, for some reason I did not get any update notifications on the thread here.

    Anyway, I just created this idea on the user voice forum:
    https://feedback.azure.com/forums/357324-application-insights/suggestions/37020748-support-for-time-granularity-selected-in-azure-por

    My concrete use case is to embed Application Inisghts charts into an Azure Dashboard, choose both time range and granularity on the dashboard:
    


    While this works fine for the time range (which is not hardcoded in the query) it does not work for the time granularity (which currently is hardcoded in the query).

    Tuesday, March 5, 2019 2:40 PM
  • Hi Evgeny,

    thanks for the reply, and sorry for the delay in response.

    Your suggestion is helpful wrt to defining custom bin sizes depending on the time range, however the time range is still hardcoded in the query.

    Would it be possible to get the current (via Portal UI selected) time range into the query without hardcoding it?

    My use case is that I want to pin such an appinsights timechart on a dashboard, and then use the time range selection on the dashboard level (see my other comment I just posted above)

    Any further ideas how that could be achieved?

    Best regards,

    Torben

     

    Tuesday, March 5, 2019 2:47 PM
  • Managed to create a fixed amount of buckets query. The query looks like this:

    let numberOfBuckets = 24;
    let interval = toscalar(requests
    | summarize interval = (max(timestamp)-min(timestamp)) / numberOfBuckets
    | project floor(interval, 1m));
    requests
    | summarize count() by bin(timestamp , interval)

    Does this work for you?

    Tuesday, March 12, 2019 12:32 PM
  • Hi Assaf,

    Yes, that (in combination with Evgeny's answer above) definitely helps and provides me with a viable workaround.

    Thank you!

    Cheers,
    Torben

    P.S.: full writeup from Assaf here: https://4pp1n51ght5.com/2019/03/12/how-to-split-your-data-into-a-fixed-amount-of-buckets/



    Wednesday, March 13, 2019 1:19 PM