Analyzing Azure Cost with Power BI

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: Example of Power BI Cost Reporting

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.

Previous Post Next Post