Home

Parameterized Queries?

Jeremy Caney
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.

 

 ApplicationInsights.Analytics.Timespan.png

 

 

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.

 

Jeremy

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:

requests
| 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;
requests
| 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.

snip_20180525135739.png

 

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,

-Evgeny

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies