I have posted several articles in past on how to generate billing reports for different AWS services and provided scripts to get those reports in csv format. Those reports are good and handy to quickly refer in discussions. However, if you want to present a nice AWS Cloud expense report to management you would need to provide some more insights, like, services contributing to the bill and trend of expenses for the last several months.
Refer to my earlier posts to generate one of billing reports for AWS services:
- Amazon Relational Database Service Billing Report
- Amazon Elastic IP Billing Report
- Amazon Elastic Load Balancing Billing Report
- Amazon EBS Volumes Billing Report
- AWS Services Cost and Usage report
- Amazon EC2 Cost and Usage report
In this post we will write a script to automate the process of generating AWS billing report for the last 12 months and present it in a Excel file. Each worksheet will contain a month’s report and a final worksheet will present a summary report and a graph showing trend.
As mentioned in my earlier posts for AWS billing reports, I am using AWS Cost Explorer CostAndUsage APIs.
Now to start with this script, make sure you have set up your AWS credentials and AWS Powershell modules installed and imported. The credentials we will be using must have enough permission to access Cost Explorer APIs.
I am not going to discuss details of the API and how we are using it. Refer my previous posts to learn more about it.
Second, we need Excel installed on the system from where we will execute script.
With that, let’s start scripting!
First, set up credentials as shown below. This is not the right way to pass credentials(hard coding) in the script. You can use Environment variables, AWS profile etc. and use it in your script. I am hard coding it to keep this process simple.
$awsAccessKey = "AKIAMYACCESSKEYTEST"
$awsSecretKey = "awssupersecretkeysamplechangeitwithreal"
$region = "us-east-2"#Set your region based on preference
Set-AWSCredentials -AccessKey $awsAccessKey -SecretKey $awsSecretKey -StoreAs AWSProfile
Initialize-AWSDefaults -ProfileName AWSProfile -Region $regionNext, define output file and some global array variables to store powershell objects related to billing data.
$oFile = "${env:Temp}\AWSBillingDetailsForLast12Months.xlsx"
if(Test-Path $oFile){Remove-Item $oFile -Force -Confirm:$false}
New-Item -Path $oFile -ItemType File
$Global:allBillingPeriods = @()
$Global:allSvcBillingDetails = @()
$Global:costByBilledMonth=@()Now, we will generate an array of objects containing last 12 billing months, first day of the month and first of the next month. We will reverse the array to arrange data from the 12th month to latest month(previous month of current date).
0..11 | ForEach-Object{
$billMonth = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName((Get-Date).AddMonths(-($_+1)).Month))-$((Get-Date).AddMonths(-($_+1)).Year)"
$lastDay = "$((Get-Date).AddMonths(-$_).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-$_).Month)))-01"
$firstDay = "$((Get-Date).AddMonths(-($_+1)).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-($_+1)).Month)))-01"
$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)We will call functions to generate all AWS services billing data per month basis and again store those in an array of object. After that, we will call another function to generate another array of objects with total cost per month and the month in (MMM-yyyy) format. Those functions will be defined in the full script I will be providing by the end of the post.
Finally, we will be creating Excel reports based on data generated in previous steps. Each worksheet will have service based cost data for a month. The last (or the first?) worksheet displays bill summary of last 12 months and a graph representing billing trend.
Here are some screenshots of sample reports:
Here is the complete script :
$awsAccessKey = "AKIAMYACCESSKEYTEST"
$awsSecretKey = "awssupersecretkeysamplechangeitwithreal"
$region = "us-east-2"#Set your region based on preference
Set-AWSCredentials -AccessKey $awsAccessKey -SecretKey $awsSecretKey -StoreAs AWSProfile
Initialize-AWSDefaults -ProfileName AWSProfile -Region $region
$oFile = "${env:Temp}\AWSBillingDetailsForLast12Months.xlsx"
if(Test-Path $oFile){Remove-Item $oFile -Force -Confirm:$false}
New-Item -Path $oFile -ItemType File
$Global:allBillingPeriods = @()
$Global:allSvcBillingDetails = @()
$Global:costByBilledMonth=@()
#region - Function to generate aws service billing
Function Generate-AWSServicesBillingData(){
ForEach($bill in $Global:allBillingPeriods){
$costUsage = ""
$billingDetails = @()
$billingMonth = $bill.Name
$interval = New-Object Amazon.CostExplorer.Model.DateInterval
$interval.Start = $bill.FirstDay
$interval.End = $bill.LastDay
$groupInfo = New-Object Amazon.CostExplorer.Model.GroupDefinition
$groupInfo.Type = "DIMENSION"
$groupInfo.Key = "SERVICE"
$costUsage = Get-CECostAndUsage -TimePeriod $interval -Granularity DAILY -Metric BlendedCost -GroupBy $groupInfo
ForEach($c in $costUsage.ResultsByTime){
$sTime = $c.TimePeriod.Start
ForEach($grp in $c.Groups){
$srvName = $grp.Keys
$cost = $grp.Metrics["BlendedCost"].Amount
$objBill = New-Object psObject
$objBill | Add-Member Name $srvName
$objBill | Add-Member Date $sTime
$objBill | Add-Member Cost $cost
$billingDetails += $objBill
}
}
foreach($svc in ($billingDetails | Group-Object Name | ForEach-Object{$_.Group} | ForEach-Object{$_.Name} | Sort-Object | Get-Unique)){
$totalCost = 0
$billingDetails | Group-Object Name | ForEach-Object{
$_.Group | Where-Object{$_.Name -eq $svc} | ForEach-Object {
$totalCost += $_.Cost
}
}
$objSvcCost = New-Object psObject
$objSvcCost | Add-Member Name $svc
$objSvcCost | Add-Member Cost ([math]::Round($totalCost,2))
$objSvcCost | Add-Member BillingMonth $billingMonth
$Global:allSvcBillingDetails += $objSvcCost
}
}
}
#endregion
#region - Function to generate total cost array by billed month
Function Generate-CostByBilledMonth(){
$Global:allSvcBillingDetails | Group-Object BillingMonth | ForEach-Object{
$month = $_.Name
$totalCost = 0
$_.Group | Where-Object{$_.BillingMonth -eq $month} | ForEach-Object {
$totalCost += $_.Cost
}
$objMonthCost = New-Object psObject
$objMonthCost | Add-Member BillingMonth $month
$objMonthCost | Add-Member Cost ([math]::Round($totalCost,2))
$Global:costByBilledMonth += $objMonthCost
}
}
#endregion
#region - 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
}
#endregion
0..11 | ForEach-Object{
$billMonth = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName((Get-Date).AddMonths(-($_+1)).Month))-$((Get-Date).AddMonths(-($_+1)).Year)"
$lastDay = "$((Get-Date).AddMonths(-$_).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-$_).Month)))-01"
$firstDay = "$((Get-Date).AddMonths(-($_+1)).Year)-$(("{0:d2}" -f ((Get-Date).AddMonths(-($_+1)).Month)))-01"
$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)
Generate-AWSServicesBillingData
Generate-CostByBilledMonth
#region - Generate Excel Files with multile worksheets with ADO Users and Groups Data
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Add()
$tabColorIndex = 30
$Global:allSvcBillingDetails | Group-Object BillingMonth | ForEach-Object{
$month = $_.Name
$wksheet = $workbook.Sheets.Add()
$wksheet.Name =$month
$row = $col = 1
$wksheet.Cells.Item($row,$col) = "Billing Month"
$col++
$wksheet.Cells.Item($row,$col) = "Service Name"
$col++
$wksheet.Cells.Item($row,$col) = "Cost"
$_.Group | Where-Object{$_.BillingMonth -eq $month} | ForEach-Object{
$row++
$col = 1
$wksheet.Cells.Item($row,$col) = $_.BillingMonth
$col++
$wksheet.Cells.Item($row,$col) = $_.Name
$col++
$wksheet.Cells.Item($row,$col) = $_.Cost
}
Decorate-Worksheet $wksheet $col $tabColorIndex
$tabColorIndex++
}
$costWksheet = $workbook.sheets.Add()
$costWksheet.Name = "Last 12 Months Bill Summary"
$row = $col = 1
$costWksheet.Cells.Item($row,$col) = "Billing Month"
$col++
$costWksheet.Cells.Item($row,$col) = "Billed Amount"
$Global:costByBilledMonth | ForEach-Object{
$row++
$col = 1
$costWksheet.Cells.Item($row,$col) = $_.BillingMonth
$col++
$costWksheet.Cells.Item($row,$col) = $_.Cost
}
Decorate-Worksheet $costWksheet $col $tabColorIndex
$sheetData = $costWksheet.Range("A1").CurrentRegion
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$licChart = $costWksheet.Shapes.AddChart().Chart
$licChart.ChartType = $xlChart::xlColumnClustered
#xlColumnClustered
$licChart.SetSourceData($sheetData)
$licChart.HasTitle = $true
$licChart.ChartTitle.Text = "AWS Billed Amount for Last 12 Months"
$costWksheet.shapes.item("Chart 1").top = 50
$costWksheet.shapes.item("Chart 1").left = 250
$costWksheet.Tab.Color = 41;
# endregion
#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 Descktop
Start-Process $oFileLet me know if you face any issue while running this script!