Using regular expression in Azure Resource Graph queries
Tags: azure, governance, kql
Published: 2022-03-13 20:49:56

Using a KQL query in Azure Resource Graph allows operators to quickly retrieve data from deployed Azure resources. With a single KQL query, you can list all VM currently deployed in all subscriptions of a tenant. 
We already saw KQL in action to retrieve data from Azure Monitor, you can do the same to list your resource. 
From the portal, you can access Azure Resource Graph, by this URL [https://portal.azure.com/#blade/HubsExtension/ArgQueryBlade](https://portal.azure.com/#blade/HubsExtension/ArgQueryBlade) (you will need a valid Azure Account) and you can start working on a query. 
For example, if you want to get the data from all your VM in your Azure tenant you can type this

```
resources
| where type == 'microsoft.compute/virtualmachines'
``` 

You can query also the name of the VM by using “==” (case sensitive) or “=~” (case insensitive) or contains. 

```
resources
| where type == 'microsoft.compute/virtualmachines' 
| where name contains "vm"
``` 

This will give you the list of all VM with a name containing the two letters 'vm’.

But what if you want to list all VMs with a particular name pattern? Let says you need to list all VM with a name like this vmX-sqlY where X is a number between 1 and 5 and Y a number between 3 and 6. 
You can try all the possibilities, but it can be a long and almost impossible query, or you can use a regex like this.

```
resources
| where type == 'microsoft.compute/virtualmachines' 
| where name matches regex @’vm[1-5]-sql[3-6]’
```

In the same way, you can a regular expression with a string pattern. If you want to list backend VM (vmX-appY) and SQL VM (vmX-sqlY), where X and Y are numbers between 1 and 10 you can use this

```
resources
| where type == 'microsoft.compute/virtualmachines' 
| where name matches regex @’vm[1-10]-((app|sql)|$)[1-10]’
``` 

Imagine now that you want to list VMs based on the appurtenance of their IP address in a given range. For example, you want to list VM with a private IP between 172.17.0.0 and 172.17.255.255.  

You can use a regex for that. First, you will need to build this regex. For that, you could use this website [https://www.analyticsmarket.com/freetools/ipregex](https://www.analyticsmarket.com/freetools/ipregex). For the giving range the regular expression look like: 
^172\.17\.([1-9]?\d|[12]\d\d)\.([1-9]?\d|[12]\d\d)$ 

Which translate to matches regex @'172\.17\.([1-9]?\d|[12]\d\d)\.([1-9]?\d|[12]\d\d)$'
In KQL.

The second thing is to link the VM with its private IP. The private IP doesn't belong to the VM object in Azure, it belongs to the virtual network interface. So, we will need to join the two resources, the VM and the virtual nic in the query. 

```
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true'
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | mv-expand ipconfig=properties.ipConfigurations
    | project nicId = id,  privateIP = ipconfig.properties.privateIPAddress 
) on nicId
``` 

And we can add our regex clause. 

```
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true'
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | mv-expand ipconfig=properties.ipConfigurations
    | project nicId = id,  privateIP = ipconfig.properties.privateIPAddress 
) on nicId
| where privateIP matches regex @'172\.17\.([1-9]?\d|[12]\d\d)\.([1-9]?\d|[12]\d\d)$'
``` 

As you can see, the maches regex clause is KQL can expand your possibilities to explore your resource with Azure Resource Graph by going beyond standard string operations.