In my previous post I talked about generating AWS Billing data for last 12 months in Excel file and also showed you how to create a chart on Excel showing cost comparison all using Powershell. To me this is a nice and quick way to generate such report and email to stake holders automatically. I am sure, you also feel the same. No one wants to spend hours generating such reports 🙂
I will continue that trend and today will share a powershell script to generate similar report for Azure. Some of the pre-requisites remain same.Such as, you still need Microsoft Excel installed on the system from where you will run the script. However, unlike AWS, you need an Azure Account and password with permissions to all your subscriptions to access billing. The user account you will use must be an Organization account or native Azure AD Account. You can not use a Microsoft account.
Next, we need Azure AD Powershell module installed and imported. I will add those in the script. It will make sure it is installed and imported.
$AadModule = Get-Module -Name "AzureAD" -ListAvailable
if ([String]::IsNullOrEmpty($AadModule)){
Install-Module -Name "AzureAD" -AllowClobber
}
$adal = Join-Path $AadModule.ModuleBase "Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
[System.Reflection.Assembly]::LoadFrom($adal) | Select-Object FullNameLet’s define credentials as discussed above. For the sake of simplicity, I am hard coding user name and password in the script. This is not at all recommended for real environment. My suggestion is to leverage Key Vault to store secrets and retrieve those in script at run time.
$AutoAccName = "***************.onmicrosoft.com" # Use an Organizational Account or a native Azure AD user account. Don't use Microsoft account.
$AutoAccPwd = "****************"
$azpassword = ConvertTo-SecureString $AutoAccPwd -AsPlainText -Force
$AzCred = New-Object System.Management.Automation.PSCredential ($AutoAccName, $azpassword)
Connect-AzAccount -Credential $AzCredWe will use Azure Cost Management REST API to retrieve billing data. As we are trying to get last 12 months data , we need to use custom time period in the query data for our POST api calls. Refer following Microsoft document to learn more about this API :
We will use a well known Microsoft Azure provided Client Id “1950a258-227b-4e31-a9cf-717495945fc2” for authentication along with supplied credentials. Refer to the full script to understand authentication process and how we are generating authorization bearer token before making API calls.
Rest of this script is quite straight forward. We will loop through each subscription( so, script will take care if you have multiple subscriptions with proper permission) and generate bill data for every month and store them in an array. Finally, we will use that data and generate Excel workbook with worksheet for each subscription and one final worksheet for summary chart.Quite easy!
Once you successfully run this script, you should see an Excel file popped up on your system as shown below :
Also, you should see an worksheet for each subscription if you have multiple :
Subscription based data
Here is the complete script. Download and save it as a .ps1 file. Run it and in few moments you have your data! Cool!
$oFileName = "AzureBillingDetailsForLast12Months.xlsx"
$oFile = "${env:Temp}\$($oFileName)"
if(Test-Path $oFile){Remove-Item $oFile -Force -Confirm:$false}
New-Item -Path $oFile -ItemType File
$clientId = "1950a258-227b-4e31-a9cf-717495945fc2" #Well known Client id provided by Microsoft Azure to access Management APIs
$resourceAppIdURI = "https://management.core.windows.net/"
$costManagementApiVersion = "2019-10-01"
$Global:allSubsTotalCostArray = @()
$Global:allBillingPeriods = @()
#Install and initialize Azure AD Module
$AadModule = Get-Module -Name "AzureAD" -ListAvailable
if ([String]::IsNullOrEmpty($AadModule)){
Install-Module -Name "AzureAD" -AllowClobber
}
$adal = Join-Path $AadModule.ModuleBase "Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
[System.Reflection.Assembly]::LoadFrom($adal) | Select-Object FullName
#Retrieve Azure AD Service Account Credentials from Key Vault
$AutoAccName = "***************.onmicrosoft.com" # Use an Organizational Account or a native Azure AD user account. Don't use Microsoft account.
$AutoAccPwd = "**************"
$azpassword = ConvertTo-SecureString $AutoAccPwd -AsPlainText -Force
$AzCred = New-Object System.Management.Automation.PSCredential ($AutoAccName, $azpassword)
Connect-AzAccount -Credential $AzCred
########
#Function to design Worksheet with some nice formatting
Function Decorate-Worksheet($wksht,$col,$tabCol){
For($c = 1; $c -le $col ; $c++){
$wksht.Cells.Item(1,$c).Font.Size = 10
$wksht.Cells.Item(1,$c).Font.Bold=$True
$wksht.Cells.Item(1,$c).Font.Name = "Arial"
$wksht.Cells.Item(1,$c).Interior.ColorIndex = 10
$wksht.Cells.Item(1,$c).Font.ColorIndex = 2
$wksht.Tab.ColorIndex = $tabCol;
}
$dataRange = $wksht.UsedRange
$dataRange.EntireColumn.AutoFit() | Out-Null
}
########
#Function to generate excel worksheet with Subscription based cost data
Function Add-ExcelWorksheetBySub($wb,$sub){
$wksheet = $wb.Sheets.Add()
$wksheet.Name =$sub
$row = $col = 1
$wksheet.Cells.Item($row,$col) = "Subscription Name"
$col++
$wksheet.Cells.Item($row,$col) = "Billing Month"
$col++
$wksheet.Cells.Item($row,$col) = "First Day"
$col++
$wksheet.Cells.Item($row,$col) = "Last Day"
$col++
$wksheet.Cells.Item($row,$col) = "Cost"
$Global:allSubsBillingArray | ForEach-Object{
$row++
$col = 1
$wksheet.Cells.Item($row,$col) = $_.SubscriptionName
$col++
$wksheet.Cells.Item($row,$col) = $_.BilledMonth
$col++
$wksheet.Cells.Item($row,$col) = $_.FirstDay
$col++
$wksheet.Cells.Item($row,$col) = $_.LastDay
$col++
$wksheet.Cells.Item($row,$col) = $_.Cost
}
Decorate-Worksheet $wksheet $col $tabColorIndex
$tabColorIndex++
}
########
#Function to generate Chart Worksheet
Function Add-ChartExcelWorksheet($wb){
$tmpWksheet = $workbook.WorkSheets.item('Sheet1')
$row = $col = 1
$tmpWksheet.Cells.Item($row,$col) = "Billing Month"
$col++
$tmpWksheet.Cells.Item($row,$col) = "Total Cost"
$row++
$Global:allSubsTotalCostArray | Group-Object BilledMonth | ForEach-Object{
$month = $_.Name
$col = 1
$tmpWksheet.Cells.Item($row,$col) = $month
$col++
$totalCost = 0
$_.Group | Where-Object{$_.BilledMonth -eq $month} | ForEach-Object{
$totalCost += $_.Cost
}
$tmpWksheet.Cells.Item($row,$col) = $totalCost
$row++
}
$sheetData = $tmpWksheet.Range("A1").CurrentRegion
$licWksheet = $workbook.sheets.Add()
$licWksheet.Name = "Bill Summary For All Subs"
$row = $col = 1
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$licChart = $licWksheet.Shapes.AddChart().Chart
$licChart.ChartType = $xlChart::xlColumnClustered
$licChart.SetSourceData($sheetData)
$licChart.HasTitle = $true
$licChart.ChartTitle.Text = "Last 12 Months Azure Bill For All Subscriptions Combined"
$licWksheet.shapes.item("Chart 1").top = 50
$licWksheet.shapes.item("Chart 1").left = 250
$licWksheet.Tab.Color = 10;
$tmpWksheet.Visible=$False
}
########
#Function to generate Azure billing data
Function Generate-AzureBillByMonth(){
#Parse through each subscription
Get-AzSubscription | ForEach-Object {
$Global:allSubsBillingArray = @()
$tenantId = $_.TenantId
$authority = "https://login.windows.net/$tenantId"
$userCredentials = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.UserPasswordCredential -ArgumentList $AutoAccName, $AutoAccPwd
$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority
$authResult = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContextIntegratedAuthExtensions]::AcquireTokenAsync($authContext, $resourceAppIdURI, $clientid, $userCredentials);
$authHeader = @{ 'Authorization' = "Bearer " + $authResult.Result.AccessToken }
$subscription = $_.Name
$subscriptionId = $_.Id
$costManagementUri = "https://management.azure.com/subscriptions/$($subscriptionId)/providers/Microsoft.CostManagement/query?api-version=$($costManagementApiVersion)"
ForEach($billingPeriod in $Global:allBillingPeriods){
Start-Sleep -Seconds 1
$billedAmount = 0.00
$billedMonth = $billingPeriod.Name
$costData = ""
$startDate = Get-Date ($billingPeriod.FirstDay) -UFormat "%Y-%m-%dT%H:%M:%S.000Z"
$endDate = Get-Date ($billingPeriod.LastDay) -UFormat "%Y-%m-%dT%H:%M:%S.000Z"
$querybody="{
`"type`": `"Usage`",
`"timeframe`": `"Custom`",
`"timePeriod`": {
`"from`": `"$startDate`",
`"to`": `"$endDate`"
},
`"dataset`": {
`"granularity`": `"Accumulated`",
`"aggregation`": {
`"totalCost`": {
`"name`": `"PreTaxCost`",
`"function`": `"Sum`"
}
}
}
}"
$costData = Invoke-RestMethod -Uri $costManagementUri -Method Post -Headers $authHeader -Body $querybody -ContentType "Application/JSON"
if(!([String]::IsNullOrEmpty($costData.properties.rows))){
$billedAmount = $costData.properties.rows[0][0]
}
else{
$billedAmount = 0.00
}
$objCost = New-Object psobject
$objCost | Add-Member SubscriptionName $subscription
$objCost | Add-Member Cost ([math]::Round($billedAmount,2))
$objCost | Add-Member BilledMonth $billedMonth
$objCost | Add-Member FirstDay $billingPeriod.FirstDay
$objCost | Add-Member LastDay $billingPeriod.LastDay
$Global:allSubsBillingArray += $objCost
$objTotalCost = New-Object psobject
$objTotalCost | Add-Member SubscriptionName $subscription
$objTotalCost | Add-Member Cost ([math]::Round($billedAmount,2))
$objTotalCost | Add-Member BilledMonth $billedMonth
$Global:allSubsTotalCostArray += $objTotalCost
}
Add-ExcelWorksheetBySub $workbook $subscription
}
Add-ChartExcelWorkSheet $workbook
}
########
# Generate an Object array of last 12 billing months and reverse the order
1..12 | ForEach-Object{
$billMonth = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName((Get-Date).AddMonths(-($_)).Month))-$((Get-Date).AddMonths(-($_)).Year)"
$firstDay = "$((Get-Date).AddMonths(-($_)).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-($_)).Month)))-01"
$lastDay = "$((Get-Date).AddMonths(-$_).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-$_).Month)))-$([DateTime]::DaysInMonth((get-date).AddMonths(-$_).Year, (get-date).AddMonths(-$_).Month))"
$objPeriod = New-Object psObject
$objPeriod | Add-Member NoteProperty Name $billMonth
$objPeriod | Add-Member NoteProperty FirstDay $firstDay
$objPeriod | Add-Member NoteProperty LastDay $lastDay
$Global:allBillingPeriods += $objPeriod
}
[array]::Reverse($Global:allBillingPeriods)
#Create new Excel file and initiate workbook
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Add()
$tabColorIndex = 30
########
#Call function to generate azure billing data
Generate-AzureBillByMonth
########
#Stop alert window to overwrite excel file
$excel.DisplayAlerts = $false
#Save excel file
$workbook.SaveAs($oFile,51)
$workbook.Saved = $true
$workbook.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
#Open Excel file on Windows Desktop
Start-Process $oFile
Post your comment if this script works for you or you face any issue! See you in next blog!