Here's what I do ...
Weekly, I run a powershell script that dumps all our custom RG tags into a text file full of SQL statements. I then run this to bulk load all our tags into an Azure SQL DB.
In Power BI, this DB table is joined up with the consumption data to be able to show RG tags.
# Do this before running this script:
# Login-AzureRmAccount
$todaysDate = Get-Date -UFormat '+%Y-%m-%d'
$results = @{}
$subscriptions=Get-AzureRMSubscription
Clear-Content c:\temp\AzureRGTags_SQLInsert_$todaysDate.sql
Add-Content c:\temp\AzureRGTags_SQLInsert_$todaysDate.sql "DELETE FROM AzureResourceGroupHistoricalPLTags WHERE DateReported = '$todaysDate';"
foreach ($vsub in $subscriptions) {
Select-AzureRmSubscription $vsub.SubscriptionID
$rgs = (Get-AzureRmResourceGroup)
foreach ($rg in $rgs) {
$ResourceGroupName=$rg.ResourceGroupName
$SubscriptionId=$vsub.SubscriptionID
$DateReported=$todaysDate
$SubscriptionName=$vsub.Name
if ($rg.tags -ne $null) {
$pldepartment = $rg.tags.pldepartment
$plclient = $rg.tags.plclient
$plregion = $rg.tags.plregion
$plenvironment = $rg.tags.plenvironment
$plproduct = $rg.tags.plproduct
$pldonotinherit = $rg.tags.pldonotinherit
$plowner = $rg.tags.plowner
if ($rg.tags.pldescription -ne $null) {$pldescription = $rg.tags.pldescription.Replace("'","")} else {$pldescription = 'notag'}
} else {
$pldepartment = ''
$plclient = ''
$plregion = ''
$plenvironment = ''
$plproduct = ''
$pldonotinherit = ''
$plowner = ''
$pldescription = ''
}
Add-Content c:\temp\AzureRGTags_SQLInsert_$todaysDate.sql "INSERT INTO AzureResourceGroupHistoricalPLTags (ResourceGroupName,SubscriptionId,DateReported,SubscriptionName,pldepartment,plclient,plregion,plenvironment,plproduct,pldonotinherit,plowner,pldescription) VALUES ('$ResourceGroupName','$SubscriptionId','$DateReported','$SubscriptionName','$pldepartment','$plclient','$plregion','$plenvironment','$plproduct','$pldonotinherit','$plowner','$pldescription')"
}
}
