2

I'm using Application Insight Workbook to design graphs over IIS logs. I want to add a parameter where users can filter on Computer. This is all well and working. This dropdown allows multiple selections and looks like this:

enter image description here

I want to include a checkbox for "All", so I do it here. enter image description here

I'm using the custom parameter like this in my query

W3CIISLog | where Computer in ({Computer})

How can I change my query to support both multiple selections and "All" from the dropdown? Is this at all possible to achieve?

smarty
  • 378
  • 3
  • 18

1 Answers1

3

We have some special github documentation with examples on how to special case all. (it doesn't look like this info has made it to the public azure docs yet)

copied from there:

one way is to use [] as the "all" value, and then write your query like this:

let selection = dynamic([{Selection}]); 
SomeQuery  
| where array_length(selection) == 0 or SomeField in (selection)

this will treat an empty selection OR the "all" selection the same (handled by the array_length check) AND will look for selected values in SomeField when anything else is selected

Other common cases use '*' as the special marker value when a parameter is required, and then test with

| where "*" in ({Selection}) or SomeField in ({Selection})

(you can also use has_any instead of in (or in~ for case insensitive in) depending on the way you are querying and what your data is, etc.

John Gardner
  • 24,225
  • 5
  • 58
  • 76
  • Thanks, @John Gardner, I'll have a look at this – smarty Sep 08 '20 at 07:22
  • When I add the "All" checkbox to the dropdown and select it, the query that uses this parameter only returns "This query could not run because some parameters are not set. Please set: Instance" when I use your first example. When I use the second example it seems to work. Will double check it :) Thanks so far – smarty Sep 21 '20 at 13:06
  • If I select "All" from the dropdown I get more results than when manually selecting all values. From my example in my question I'm querying Computer column from W3CIISLog. Any idea what this can be? – smarty Sep 21 '20 at 13:16
  • the dropdown will be limited to 1000 distinct values, where the "all" special case won't be limited at all. do you have that many distinct values? i'd have to see your exact parameter setup and exact query to be sure though. the simplest way is to generate the versions and look at the *exact* queries generated and compare them. (there's no magic here, just queries!) – John Gardner Sep 22 '20 at 22:42
  • The distinct values in this case is a list of 9 computers from the W3 IIS log. The funny thing is that when I Summarize over scStatus with and without the where clause on named Computers I get a small difference in numbers returned. If I do !In (..) for the known computers I get no result back. So I can't find what causes the differences. This is another issue though, so I will accept your solution as it solved my main question :) Thanks – smarty Oct 01 '20 at 11:40