Creating a workbook tile using KQL which utilises parameters - in this case VirtualMachines.
If I run the following with only 1 VM selected in the paramater, the results are displayed. If I select 2 or more VMs there is an error explaining the results could not be parsed, with the error output showing VM1, VM2.
Delimiter configured for the parameter is ,
How could I correct the following query to work with multiple VirtualMachine parameter selections?
InsightsMetrics
| where Origin == "vm.azm.ms"
| where Namespace == "LogicalDisk" and Name == "FreeSpacePercentage"
| extend Disk=tostring(todynamic(Tags)["vm.azm.ms/mountId"])
| extend vmName = tostring(toupper(Computer))
| extend VM_Name = tostring(split(vmName,'.')[0])
| summarize AggregatedValue = avg(Val) by VM_Name, Disk, _ResourceId
| where VM_Name in '{VirtualMachines:label}'
| top 30 by AggregatedValue asc
Thanks
FYI: For single value parameters in workbooks, the value of a parameter is just text, so you need to wrap it in quotes inside the query text
so if VirtualMachines is a single value param, you'd use it like this in a query:
...
| where machine == "{VirtualMachines}"
which then turns into
...
| where machine == "the value of the parameter"
at query time.
HOWEVER, when using multi value dropdown params, the parameter settings has options for what to use to for quote (' by default) and delimiter (,) by default, so you have to use it like this:
...
| where machine in ({VirtualMachines}) // note NO QUOTES HERE
which at query time gets resolved into
...
| where machine in ('value1', 'value2', 'value3') // note NO QUOTES HERE
for multi value params, especially if optional, the best way to do this is with a let:
let machines = dynamic([{VirtualMachines}]); // turns into a KQL array,even if param is empty
because then you can use it like
let machines = dynamic([{VirtualMachines}]); // turns into a KQL array,even if param is empty
...
| where machine in (machines) // note NO QUOTES HERE
which will be syntactically valid even if there's no machines selected
(so you could do like |where array_length(machines)==0 or machine in (machines) to allow ALL machines if nothing is selected