I will be speaking at the Power BI Fest next weekend.
The topic will be how to analyze your Azure Cost with Power BI. This gives many benefits compared with the native functionality in the Azure portal, for example speed and options to customize.
The result of this can look like the following:
All numbers on the graphic are faked so please do not get surprised by comparing different services which each other.
As with any data project getting a good foundation to build the data on, is an essential step. The following PowerShell will allow you to export the raw cost data:
# Notes
# As an alternative to generating those web requests manually with Invoke-WebRequest, the az rest command was tested first.
# However, the latter would error out with an internal server error.
# This issue was reported at: https://github.com/Azure/azure-cli/issues/18864
Param(
[Parameter(Mandatory = $True, HelpMessage = "Storage Account Subscription")]
[String]
$storageAccountSubscriptionId,
[Parameter(Mandatory = $True, HelpMessage = "Storage Account Resource Group")]
[String]
$storageAccountResourceGroup,
[Parameter(Mandatory = $True, HelpMessage = "Storage Account Name")]
[String]
$storageAccountName,
[Parameter(Mandatory = $True, HelpMessage = "Storage Account Container")]
[String]
$storageAccountContainer,
[Parameter(Mandatory = $True, HelpMessage = "Blob Root Directory")]
[String]
$storageAccountRootDirectory,
[Parameter(Mandatory = $False, HelpMessage = "Months back")]
[Int]
$monthsBackTotal = 12,
[Parameter(Mandatory = $False, HelpMessage = "Only export the subscription with the following IDs. Coma-separated list in string")]
[String]
$subscription
)
$exportTimeStamp = (Get-Date -AsUTC).ToString("yyyy-MM-ddTHH-mm-ss")
$batchSizeInMonths = 3
$subscriptions = ($subscription.Split(','))
$token = (az account get-access-token | ConvertFrom-Json).accessToken
if ($LASTEXITCODE -ne 0) {
throw "Could not get access token"
}
foreach ($batch in 1..[math]::ceiling($monthsBackTotal / $batchSizeInMonths)) {
foreach ($scopeSubscriptionId in $subscriptions) {
$exportName = "CostExport_$scopeSubscriptionId_$exportTimeStamp-batch$batch" -replace '\.', '_'
$startOffset = -[math]::min($batch * $batchSizeInMonths, $monthsBackTotal)
$endOffset = -($batch-1) * $batchSizeInMonths
$exportPeriodStart = (Get-Date -AsUTC).Date.AddMonths($startoffset).AddSeconds(1).ToString("yyyy-MM-ddTHH:mm:ss")
$exportPeriodEnd = (Get-Date -AsUTC).Date.AddMonths($endoffset).ToString("yyyy-MM-ddTHH:mm:ss")
Write-Output "Start $exportPeriodStart to $exportPeriodEnd"
$body = @"
{
"properties": {
"format": "Csv",
"deliveryInfo": {
"destination": {
"resourceId": "/subscriptions/$storageAccountSubscriptionId/resourceGroups/$storageAccountResourceGroup/providers/Microsoft.Storage/storageAccounts/$storageAccountName",
"container": "$storageAccountContainer",
"rootFolderPath": "$storageAccountRootDirectory/$exportTimeStamp"
}
},
"definition": {
"type": "ActualCost",
"timeframe": "Custom",
"timePeriod": {
"from": "$exportPeriodStart",
"to": "$exportPeriodEnd"
},
"dataSet": {
"granularity": "Daily",
"configuration": {
"columns": [
"InvoiceSectionName",
"AccountName",
"AccountOwnerId",
"SubscriptionId",
"SubscriptionName",
"ResourceGroup",
"ResourceLocation",
"Date",
"ProductName",
"MeterCategory",
"MeterSubcategory",
"MeterId",
"MeterName",
"MeterRegion",
"UnitOfMeasure",
"Quantity",
"EffectivePrice",
"CostInBillingCurrency",
"CostCenter",
"ConsumedService",
"ResourceId",
"Tags",
"OfferId",
"AdditionalInfo",
"ServiceInfo1",
"ServiceInfo2",
"ResourceName",
"ReservationId",
"ReservationName",
"UnitPrice",
"ProductOrderId",
"ProductOrderName",
"Term",
"PublisherType",
"PublisherName",
"ChargeType",
"Frequency",
"PricingModel",
"AvailabilityZone",
"BillingAccountId",
"BillingAccountName",
"BillingCurrencyCode",
"BillingPeriodStartDate",
"BillingPeriodEndDate",
"BillingProfileId",
"BillingProfileName",
"InvoiceSectionId",
"IsAzureCreditEligible",
"PartNumber",
"PayGPrice",
"PlanName",
"ServiceFamily"
]
}
}
}
}
}
"@
Write-Output "Create export $exportName"
# Create export
Invoke-WebRequest -Uri "https://management.azure.com/subscriptions/$scopeSubscriptionId/providers/Microsoft.CostManagement/exports/$exportName/?api-version=2021-01-01" `
-Method "PUT" `
-Headers @{
"authorization"="Bearer $token"
"accept"="*/*"
"accept-encoding"="gzip, deflate"
} `
-ContentType "application/json" `
-Body $body | Out-Null
Write-Output "Execute export $exportName"
# Execute export
Invoke-WebRequest -Uri "https://management.azure.com/subscriptions/$scopeSubscriptionId/providers/Microsoft.CostManagement/exports/$exportName/run?api-version=2020-06-01" `
-Method "POST" `
-Headers @{
"authorization"="Bearer $token"
"accept"="*/*"
"accept-encoding"="gzip, deflate"
} | Out-Null
$status = "InProgress";
$secondsPerIteration = 5
$executionTime = 0
while ($status -eq "InProgress") {
Start-Sleep -Seconds $secondsPerIteration
$executionTime += $secondsPerIteration
$statusraw = Invoke-WebRequest -Uri "https://management.azure.com/subscriptions/$scopeSubscriptionId/providers/Microsoft.CostManagement/exports/$exportName/runHistory?api-version=2021-10-01" `
-Method "GET" `
-Headers @{
"authorization"="Bearer $token"
"accept"="*/*"
"accept-encoding"="gzip, deflate"
} `
-ContentType "application/json"
$status = ($statusraw | ConvertFrom-Json).value.properties.status
Write-Output "Waiting for export '$exportName' to finish: current status '$status', execution time $executionTime seconds."
}
if ($status -ne "Completed") {
throw "Unexpected Status please review. It is '$status' and should have been 'Completed'"
}
}
}
It can be called with
.\Trigger-AzureCostExport.ps1 -storageAccountSubscriptionId xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx `
-storageAccountResourceGroup myRG `
-storageAccountName mySA `
-storageAccountContainer cost `
-storageAccountRootDirectory AzureCostManagement `
-subscription xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx
For more details join my session next Saturday at 11am CET.