In the first part of our article series, we tried to understand the Azure Resource Graph service. Now with executing sample queries, let's try to detail and implement them. For example, let's suppose that we have a scenario like this: an organization with up to 500~ Azure Subscriptions that one person manages. And this person has to list all of the storage accounts.
We mentioned plenty of different ways to complete the scenario above before. If you wish, you can access this information from Azure Portal or use the Get-AzStorageAccount cmdlet on PowerShell by visiting every subscription. Of course, there is an easier way, and it is using Azure Resource Graph Query. Let's try this to get information in a more accessible way.
Listing Storage Accounts
resources
| where type =~ "Microsoft.storage/storageaccounts"
With the example code we wrote above, we requested the storageAccounts on the resources table that has the same type. We used the where operator for filtering, and it returns the storageAccounts that have the same type we requested. It is important to note that we can only list storage accounts that we have Reader permission. Let's increase the number of our examples.
Listing Public IP Addresses
resources
| where type =~ "microsoft.network/publicipaddresses"
| project name, properties.ipAddress
Let's continue with different examples. Now we request listing all the public IP addressses in subscriptions. To do this, using the KQL query above will be quite sufficient. We need to enlarge this query. This time we will use summarize operator to create a Resource Map.
resources
| where type =~ "microsoft.network/publicipaddresses"
| project name, properties.ipAddress, location
| summarize count() by location
As you can see the result above we easily listed all the public IP addresses. Let's implement another example without slowing down.
Sorting by Virtual Machine's size and location
resources
| where type =~ 'microsoft.compute/virtualMachines'
| summarize count() by tostring(properties.hardwareProfile.vmSize), location
If we take a look at the query code above, you can notice that we sorted virtual machines by their vmSize parameter by using summarize and count function again. The result should look like this:
Listing servers that has Hybrid Use Benefit License
Azure Hybrid Benefit is a license that enables the usage of Windows Server, SQL Server licenses in your Enterprise environment. If you are wondering which Windows Servers have this license, you can implement the code below to see them.
resources
| where type =~ 'microsoft.compute/virtualMachines'
| extend imageOffer=tostring(properties.storageProfile.imageReference.offer)
| extend licenseType=properties.licenseType
| where imageOffer == 'WindowsServer' and licenseType=='Windows_Server'
| project name, imageOffer, licenseType
Let's improve our example a bit more. Now we will use the query we have and represent it as Bar Chart.
resources
| where type =~ 'microsoft.compute/virtualMachines'
| extend imageOffer=tostring(properties.storageProfile.imageReference.offer)
| extend licenseType=iif(tostring(properties.licenseType) == "Windows_Server", "HUB[WindowsServer]", "WindowsServer")
| where imageOffer == 'WindowsServer'
| project name, imageOffer, licenseType
| summarize count() by licenseType
In our next article, we will create queries with Azure Defender for Cloud examples.