Azure Resource Graph KQL - filter virtual machines having tags with given prefixes

Question

We have a lot of virtual machines that we create in azure. Each virtual machine has tags. Keys for some of these tags are dynamically constructed. Such keys have a fixed prefix and a dynamic suffix. We wanted a way to filter all virtual machines which have 2 or more keys with given prefixes.

Consider the following Virtualmachines:

• VM1: {name: "vm-1", tags: {"bingo-1":1, "tringo-2":1, "key1": "value1"}}
• VM1: {name: "vm-1", tags: {"tringo-2":1, "key2": "value2"}}
• VM1: {name: "vm-1", tags: {"bingo-1":1, "key3": "value3"}}

Expected Output: vm-1 as that is the only VM which contains both the tag-key-prefixes.

Attempt: I got this working but this does not seem to be optimal as it calls bag_keys function twice.

resources
 | where type =~ 'Microsoft.Compute/virtualMachines'
 | where resourceGroup =~ 'test-resource-group'
 | where bag_keys(tags) hasprefix "bingo-"
 | where bag_keys(tags) hasprefix "tringo-"
 | project name

This works as expected but at the cost of invoking bag_keys twice. Is there a more idiomatic and optimal way to achieve the same without calling bag_keys twice?

Answer 1

Instead of using two where operators, firstly I included mv-expand operator to join tags into one record and then used an and to check and filter the tags of both prefixes to optimize your code.

resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where resourceGroup =~ '<resourcegroup>'
| mvexpand tags
| extend tagKey = tostring(bag_keys(tags)[0])
| extend tagValue = tostring(tags[tagKey])
| where tagKey hasprefix "creat"  and tagKey hasprefix "cr"
| project name, tags, tagKey, tagValue

Update:

As given in comments, You can use extend operator. It creates a new column that contains all the keys in the tags object in this way.

extend tagKeys = bag_keys(tags) 
| where tagKeys hasprefix "bingo-" and tagKeys hasprefix "tringo-"

Refer resource graph explorer query article by @John Kilmister for more related query samples.