Export Azure Subscription Usage to CSV with NEW Billing API and PowerShell


The Azure Billing REST API was recently made available as a Public Preview, and there’s been a ton of interest from customers in leveraging this API to collect and track usage of cloud services in their Microsoft Azure subscriptions.

UPDATE: Version 0.9.4 and later of the Azure PowerShell module now include new Azure Resource Manager cmdlets to query Azure usage data directly: Get-UsageAggregates and Get-UsageMetrics.

  • I’ve included an updated script below that leverages these new cmdlets. 
  • I’ve also retained the original script that called the Billing REST API directly at the end of this article, because it serves as a useful example of how to call REST APIs when capabilities are needed that aren’t yet exposed directly via PowerShell.

Read this article ...

In this article, I’ll provide a simple script that leverages Azure PowerShell to call this API and export usage data from your Azure subscription to a CSV file for further analysis …

Export Azure Usage via PowerShell using Get-UsageAggregates

Before running this script, be sure to download and install the latest version of the Azure PowerShell module – this module will be needed to provide the Get-UsageAggregates cmdlet used within this script to pull Azure usage data.

# Set date range for exported usage data

$reportedStartTime = "2015-05-01"

$reportedEndTime = "2015-06-28"

# Authenticate to Azure

Add-AzureAccount

# Switch to Azure Resource Manager mode

Switch-AzureMode -Name AzureResourceManager

# Select an Azure Subscription for which to report usage data

$subscriptionId =
    (Get-AzureSubscription |
     Out-GridView `
        -Title "Select an Azure Subscription ..." `
        -PassThru).SubscriptionId

Select-AzureSubscription -SubscriptionId $subscriptionId

# Set path to exported CSV file

$filename = ".\usageData-${subscriptionId}-${reportedStartTime}-${reportedEndTime}.csv"

# Set usage parameters

$granularity = "Daily" # Can be Hourly or Daily

$showDetails = $true

# Export Usage to CSV

$appendFile = $false

$continuationToken = ""

Do {

    $usageData = Get-UsageAggregates `
        -ReportedStartTime $reportedStartTime `
        -ReportedEndTime $reportedEndTime `
        -AggregationGranularity $granularity `
        -ShowDetails:$showDetails `
        -ContinuationToken $continuationToken

    $usageData.UsageAggregations.Properties |
        Select-Object `
            UsageStartTime, `
            UsageEndTime, `
            @{n='SubscriptionId';e={$subscriptionId}}, `
            MeterCategory, `
            MeterId, `
            MeterName, `
            MeterSubCategory, `
            MeterRegion, `
            Unit, `
            Quantity, `
            @{n='Project';e={$_.InfoFields.Project}}, `
            InstanceData |
        Export-Csv `
            -Append:$appendFile `
            -NoTypeInformation:$true `
            -Path $filename

    if ($usageData.NextLink) {

        $continuationToken = `
            [System.Web.HttpUtility]::`
            UrlDecode($usageData.NextLink.Split("=")[-1])

    } else {

        $continuationToken = ""

    }

    $appendFile = $true

} until (!$continuationToken)

Export Azure Usage via PowerShell and Billing REST API

Before running this script, be sure to download and install the latest version of the Azure PowerShell module – this module will be needed for the initial portion of the script, as well as to provide the Azure AD Authentication Library (ADAL) modules that are used within this script to authenticate to the REST API.

# Set date range for exported usage data

$reportedStartTime = "2015-05-01"

$reportedEndTime = "2015-06-28"

# Authenticate to Azure

Add-AzureAccount

# Select an Azure Subscription for which to report usage data

$subscriptionId =
    (Get-AzureSubscription |
     Out-GridView `
        -Title "Select an Azure Subscription ..." `
        -PassThru).SubscriptionId

# Set path to exported CSV file

$filename = ".\usageData-${subscriptionId}-${reportedStartTime}-${reportedEndTime}.csv"

# Set Azure AD Tenant for selected Azure Subscription

$adTenant =
    (Get-AzureSubscription `
        -SubscriptionId $subscriptionId).TenantId

# Set parameter values for Azure AD auth to REST API

$clientId = "1950a258-227b-4e31-a9cf-717495945fc2" # Well-known client ID for Azure PowerShell

$redirectUri = "urn:ietf:wg:oauth:2.0:oob" # Redirect URI for Azure PowerShell

$resourceAppIdURI = "https://management.core.windows.net/" # Resource URI for REST API

$authority = "https://login.windows.net/$adTenant" # Azure AD Tenant Authority

# Load ADAL Assemblies

$adal = "${env:ProgramFiles(x86)}\
Microsoft SDKs\
Azure\PowerShell\
ServiceManagement\
Azure\Services\
Microsoft.IdentityModel.
Clients.ActiveDirectory.dll"

$adalforms = "${env:ProgramFiles(x86)}\
Microsoft SDKs\
Azure\PowerShell\
ServiceManagement\
Azure\Services\
Microsoft.IdentityModel.
Clients.ActiveDirectory.WindowsForms.dll"

Add-Type -Path $adal

Add-Type -Path $adalforms

# Create Authentication Context tied to Azure AD Tenant

$authContext = New-Object
"Microsoft.IdentityModel.Clients.
ActiveDirectory.AuthenticationContext"
    -ArgumentList $authority

# Acquire Azure AD token

$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

# Create Authorization Header

$authHeader = $authResult.CreateAuthorizationHeader()

# Set REST API parameters

$apiVersion = "2015-06-01-preview"

$granularity = "Daily" # Can be Hourly or Daily

$showDetails = "true"

$contentType = "application/json;charset=utf-8"

# Set HTTP request headers to include Authorization header

$requestHeader = @{"Authorization" = $authHeader}

# Set initial URI for calling Billing REST API

$uri = "https://management.azure.com/subscriptions/$subscriptionId
/providers/Microsoft.Commerce/UsageAggregates?
api-version=$apiVersion&
reportedStartTime=$reportedStartTime&
reportedEndTime=$reportedEndTime&
aggregationGranularity=$granularity&
showDetails=$showDetails"

# Call Billing API and export to CSV

$appendFile = $false

Do {

    $usageData = Invoke-RestMethod `
        -Uri $Uri `
        -Method Get `
        -Headers $requestHeader `
        -ContentType $contentType

    $usageData.value.properties |
        Select-Object `
            usageStartTime, `
            usageEndTime, `
            subscriptionId, `
            meterCategory, `
            meterId, `
            meterName, `
            meterSubCategory, `
            unit, `
            quantity, `
            @{n='meteredRegion';e={$_.infoFields.meteredRegion}}, `
            @{n='meteredService';e={$_.infoFields.meteredService}}, `
            @{n='project';e={$_.infoFields.project}}, `
            instanceData |
        Export-Csv `
            -Append:$appendFile `
            -NoTypeInformation:$true `
            -Path $filename

    $uri = $usageData.nextLink

    $appendFile = $true

} until (!$uri)

Skip to main content