Parameterized Queries?

Occasional Visitor

Question: When writing a query, is it possible to define (and use) a variable that is exposed to the user via the interface? I'm hoping for something similar to how the time range is exposed (see below)—but with the ability to define the range and capture the user input as part of the query.





Aside, I'm relatively new to Application Insights Analytics, and really appreciating the power, flexibility, and ease-of-use of the querying language. This is a really nice service.



1 Reply

Hi Jeremy,


First of all, thank you for the kind words!


As far as your question: there are a few approaches. For the sake of an example, let's use our demo environment, and the following query:

| where resultCode !in (200)
| summarize avgDuration = avg(duration), p95Duration = percentile(duration, 95) by resultCode, bin(timestamp, 1h)
| render timechart

Approach 1: parametrize your query using the "let" statement:

let excludeResultCodes = pack_array(200, 404);
let percentileRange = 95;
let binGrain = 1h;
| where resultCode !in (excludeResultCodes)
| summarize avgDuration = avg(duration), p95Duration = percentile(duration, percentileRange) by resultCode, bin(timestamp, binGrain)
| render timechart 

These values become constants for the rest of the query, and can be reused multiple times.


Approach 2: use the "filters" UI mechanism

After you run a query, "filters" will show you all values returned for the final result set (excluding columns that are not expected to have non-unique values, for example timestamps). You can then hover on any value with your mouse, and press "+" to add a clause to your query to only show rows with this value, or "-" to exclude rows with this value from your result set.



Approach 3: Direct-link to a query:

You can generate the q=... portion of the direct link URL yourself using the following algorithm:

UrlEncode(Base64Encode(GZip(original query)))

Give it a shot using this JSFiddle example I put together. Generating these links yourself and integrating them into your own UI can allow you to provide a great drill-through experience. You can of course further refine this approach using the previous two approaches.


Hope that helps,