Filter query based on paramater

Question

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

Answer 1

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

Answer 2

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.

Thanks to Craig for suggesting the same.

You should replace ["VM1", "VM2"] with the actual parameter name that contains the selected VMs.

Method : 1

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 ("CH1-DMAVM","SQL01","AppBE00","DC01")
| top 30 by AggregatedValue asc

Output:

Method 2:

To make the query work with multiple VirtualMachine parameter selections, Instead of using the in operator, you should use the in operator along with the dynamic function to construct the appropriate dynamic expression.

let selectedVMs = dynamic(["SQL01", "SQL10", "DC01","AppBE00"]);
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 (selectedVMs)
| top 30 by AggregatedValue asc

By using the in (selectedVMs) expression, the query will filter the results based on the VM names present in the selectedVMs array.

Output:

Answer 3

Thanks everyone for your help. I achieved this by using the following:

let machines = dynamic('{VirtualMachines:label}');
let vms = split(machines, ', ');
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~ (vms)
| top 30 by AggregatedValue asc