Question 215173 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Filter query based on paramater

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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:

    enter image description here

    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:

    enter image description here

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search