Get the Count of Azure Resources using PowerShell Runbook & Send it to an Email.
Tags: azure, powershell, kql, automation
Published: 2023-05-25 12:05:52

## Background Knowledge 🤩

Before going into the solution, Let's understand a bit about the [Kusto Query Language](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/) as this is going to be used to fetch the count across subscriptions🙂. Kusto query language is the main key this is used to extract the count from azure portal. Following query is used to give the count of all resources in Azure. You can call it the punch line of this article 😎. Further more You verify this query in an Azure service called Azure [Resource Graph Explorer](https://learn.microsoft.com/en-us/azure/governance/resource-graph/) 

```kusto
summarize Count=count() by type
```

## Steps to Configure the Email Server 🔥



1. 🚀 Get SMTP email service.

2. 🚀 Add email credentials in Azure Portal > Resources > Credentials tab of side bar.

3. 🚀 Import modules names Az_ResourceGraph and all the modules that are it's dependency.


## Steps to [Create](https://learn.microsoft.com/en-us/azure/automation/manage-runbooks) Runbook 🔥

1.🚀 Create an automation account.

2.🚀 Create a PowerShell workflow type runbook.

3.🚀 Add the following script in the workflow runbook file.

```powershell
#------------------- LOGIN TO AZURE -------------------#
try
   {
        "Logging in to Azure..."
        Connect-AzAccount -Identity
   }
   catch 
   {
        Write-Error -Message $_.Exception
        throw $_.Exception
   }
```

4.🚀 Add Email Creds in the runbook 

```powershell
#------------------- CONFIGURE EMAIL SERVER -------------------#

$Username ="username"
$Password = ConvertTo-SecureString "PasswordString" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential $Username, $Password
$SMTPServer = "smtp.sendgrid.net"
$EmailFrom = "youremail.com"  

[string[]]$EmailTo = "youremail.com"
$Subject = "Attached - Resource Count"


```

5.🚀 Create variables to store the count in.

```powershell
#------------------- CALCULATING COUNT -------------------#
$WebCount = 0
$DbCount = 0
$VmCount = 0
```

6.🚀 Create an array of subscriptions to loop through and get the count of resource thought out the tenant that might contain more than 1 subscription

```powershell
$ArrayOfSubscriptions  = "Subscription1 ID","Subscription2 ID" , "Subscription3 ID","Subscription4 ID","Subscription5 ID"
```

7.🚀 Start foreach loop

```powershell
# START OF LOOP
foreach ($element in $ArrayOfSubscriptions) {
echo $element
Set-AzContext -Subscription $element

class AzResourceGraphException : Exception {
	[string] $additionalData

	AzResourceGraphException($Message, $additionalData) : base($Message) {
		$this.additionalData = $additionalData
			}
		}
```


8.🚀 Place the following code in a for loop to loop though all the subscription, If there is only one subscription no need to add loop.


- 📌 Websites📌


```powershell
#------------------- WEBSITES -------------------#
try {
	$resourceGraphQuery = "Resources | where type =~ 'Microsoft.Web/sites' | summarize count()" 

	$WebCount = Search-AzGraph -Subscription $element -Query $resourceGraphQuery -ErrorVariable grapherror -ErrorAction SilentlyContinue 

	if ($null -ne $grapherror.Length) {

	$errorJSON = $grapherror.ErrorDetails.Message | ConvertFrom-Json

	throw [AzResourceGraphException]::new($errorJSON.error.details.code, $errorJSON.error.details.message)

			}
		}
catch [AzResourceGraphException] {
			Write-Host "An error on KQL query"
			Write-Host $_.Exception.message
			Write-Host $_.Exception.additionalData
		}
catch {
	Write-Host "An error occurred in the script"
	Write-Host $_.Exception.message
		}      
```


-📌 Databases📌


```powershell
#------------------- DATABASE -------------------#

try {
     $resourceGraphQuery = "Resources | where type =~ 'Microsoft.Sql/Servers/databases' | summarize count()" 

     $DbCount = Search-AzGraph -Subscription $element -Query $resourceGraphQuery -ErrorVariable grapherror -ErrorAction SilentlyContinue 

     if ($null -ne $grapherror.Length) {

     $errorJSON = $grapherror.ErrorDetails.Message | ConvertFrom-Json

		throw [AzResourceGraphException]::new($errorJSON.error.details.code, $errorJSON.error.details.message)

			}
		}
		catch [AzResourceGraphException] {
			Write-Host "An error on KQL query"
			Write-Host $_.Exception.message
			Write-Host $_.Exception.additionalData
		}
		catch {
			Write-Host "An error occurred in the script"
			Write-Host $_.Exception.message
		}
```


-📌 Virtual Machines📌


```powershell
#------------------- VIRTUAL MACHINES -------------------#
try {
	$resourceGraphQuery = "Resources | where type =~ 'Microsoft.Compute/virtualMachines' | summarize count()" 

	$VmCount = Search-AzGraph -Subscription $element -Query $resourceGraphQuery -ErrorVariable grapherror -ErrorAction SilentlyContinue 

	if ($null -ne $grapherror.Length) {

	$errorJSON = $grapherror.ErrorDetails.Message | ConvertFrom-Json

	throw [AzResourceGraphException]::new($errorJSON.error.details.code, $errorJSON.error.details.message)

		}
	}
catch [AzResourceGraphException] {
	Write-Host "An error on KQL query"
	Write-Host $_.Exception.message
	Write-Host $_.Exception.additionalData
}
catch {
	Write-Host "An error occurred in the script"
	Write-Host $_.Exception.message
}
# END OF LOOP
```

9.🚀 Store the values in variables

```powershell
# ECHO RESULT IS DISPLAYED IN OUTPUT WINDOW OF YOUR RUNBOOK

echo "Web Count" $WebCount.count_
echo "Db Count" $DbCount.count_
echo "VM Count" $VmCount.count_
```

10.🚀 Sum Counts
```powershell
# COMBINE THE COUNTS FOR ALL SUBSCRIPTION TO GET THE COUNT FOR TENANT
$WebsiteCount = $WebsiteCount + $WebCount.count_
$DatabaseCount = $DatabaseCount + $DbCount.count_
$VirtualMachineCount = $VirtualMachineCount + $VmCount.count_
```

11.🚀 Last step is to send the email.

```powershell
#------------------- SENDING EMAIL -------------------#

$Body = "Count of Azure Resources = "
Send-MailMessage -smtpServer $SMTPServer -Credential $credential -Usessl -Port 587  -from $EmailFrom -to $EmailTo -subject $Subject -Body "Count of Tenant Resources:  <br/><br/><br/>Count of Azure Websites : $WebsiteCount <br/><br/><br/> Count of Azure Databases : $DatabaseCount <br/><br/><br/> Count of Azure VirtualMachines : $VirtualMachineCount " -BodyAsHtml
```

## The Complete Solution😍
❤️For complete solution refer to the following [gist](https://gist.github.com/kinzakanwal/9e6613864df5c15ac045a59caf748ad0). 

Thank You so much for reading, stay tunned for the future posts.😍❤️