azure - Count VMs by resource groups when multiple subscriptions

Question

I've created a PowerShell script that will export to an Excel file details about components that generated costs in Azure.

In one tab named "Details" there are exported all the components that generated costs in a specific billing period. And in another tab named "Short" there is exported count of all the VMs that generated costs filtered by subscriptions.

I tried in multiple ways but couldn't wrap my head around a new request.

How can I count all the VMs that generated costs filtered by resource groups. If you can help I would much appreciate it.

Cheers!

If (-not(Get-InstalledModule ImportExcel -ErrorAction silentlycontinue)) {
    Write-Output "Module does not exist"
  }
  Else {
    Write-Output "Module ImportExcel exists. Continuing with gathering data."
  }
  
$file=".\resources.xlsx"

# Because Get-AzBilling gives us the value as yyyyMM we need also a value for day.
# Used $billingperiodfinal to generate the correct value to be used.
$billingperiod = (Get-AzBillingPeriod)[1].Name
$day = "01"
$billingperiodfinal = $billingperiod+$day
$date=Get-Date -format "yyy-MM-dd"
$time2=(Get-Date).addmonths(-1)
$date2=Get-Date $time2 -format "yyy-MM"

$VMs = @()
$Subscriptions = Get-AzSubscription
foreach ($sub in $Subscriptions) {
  Get-AzSubscription -SubscriptionName $sub.Name | Set-AzContext
  az account set -s $sub.Name
  $VMs += (az consumption usage list -p $billingperiodfinal | ConvertFrom-Json)
}

$VMsDetails = $VMs

$VMsDetails | Export-Excel -path $file -ClearSheet -workSheetName "Detailed"

$VMsShort = $VMs `
| Where-Object {($_.product -Match "Virtual Machines")} `
| Sort-Object -Property instanceName -Descending `
| Select-Object instanceName, subscriptionName `
| Get-Unique -AsString `
| Group-Object subscriptionName | Select-Object Name,Count `
| Select-Object @{ expression={$_.Name}; label='Subscription Name' }, @{ expression={$_.Count}; label='Number of VMs' }

$VMsShort | Export-Excel -path $file -ClearSheet -workSheetName "Short"

标签： azurepowershellazure-powershell

Answer 1

I swapped az consumption usage list for Get-AzConsumptionUsageDetail because I can see the output on their help page, and I know it will take ResourceGroup as a parameter. This suggestion to iterate through your groups and add the group name as a property should work either way though:

$Subscriptions = Get-AzSubscription

# Iterate through subs
$AzConsumption = foreach ($sub in $Subscriptions) {
  Set-AzContext $sub
  $AzResourceGroups = Get-AzResourceGroup | sort ResourceGroupName

  # Iterate through each resource group
  Foreach ($rg in $AzResourceGroups) {  

    # Get the consumption usage - this can be replaced with the 'az consumption usage list' command
    $RgConsumption = Get-AzConsumptionUsageDetail -ResourceGroup $rg.ResourceGroupName -BillingPeriodName (Get-AzBillingPeriod)[1].Name 
    # Add ResourceGroup as a property to consumption report.
    $RgConsumption | Add-Member -MemberType NoteProperty -Name ResourceGroup -Value $rg.ResourceGroupName
    $RgConsumption
  }
}

$AzConsumptionShort = $AzConsumption |
  Where Product -Match "Virtual Machines" |
  Sort InstanceName -Descending | 
  Select InstanceName,SubscriptionName,ResourceGroup -Unique |
  Group subscriptionName |
  Select @{l='Subscription Name';e={$_.Name}},@{l='Number of VMs';e={$_.Count}}

# etc.