This is the second part in a series of three about the Azure EA portal Billing API and what you can do with it. The first post showed you the basics of analysing the data using Excel and PowerPivot. However, it assumed that you already had the data locally. Part 2 will show you how to download it via the Billing API.
What I’m going to show you
When the billing API was new in June 2014 I wrote a C# console program that downloaded the data and imported it into a SQL database. But when planning for this blog post, I realized that it would be too much C# code and stuff most people in need of the Billing Data don’t care about. Therefor I wrote a new solution using Powershell which is something even a CFO can make a stretch to use if desperate for the data.
You don’t need any extra cmdlets downloaded, like the Azure cmdlets. You can run this with just the basic powershell that comes with Windows nowadays.
Don’t be scared that the low level details when I describe the API themselves. if you are a non-programmer, just quickly browse through that section or skip it completly. If you don’t care about the details at all and just want to download the data, read the next section and then skip to the bottom and download the script.
Getting access to the Billing API
The Billing API is only available for those who have Azure on their Microsoft Enterprise Agreement. When you enroll Azure to your EA you get an Enrollment Number. It’s the enrollment that will be invoiced for the total consumption.
In order to use the Billing API you need to find your Enrollment Number and you do that by logging into https://ea.azure.com which is the EA portal (which is different from the other Azure portals IT folks use). In the upper right corner you’ll find the Enrollment Number, which is a 8-digit number. Note – access to the EA portal is given to one person within your organization during enrollment and who then has access to it is a matter of delegation. You don’t have access to it just because you are the Service Administrator of a subscription. (Part 3 touches this subject.)
The second part you need to use the Billing API is an Access Key needed for authentication when actually using the APIs. If you go into the Manage Access area on the start page you will find it at bottom. To create a key, press Generate and copy the key to the clipboard. Currently the key is only valid for 6 months and no, there is no way to make that longer or automatically generete a new key. You just have to set a reminder and press Generate again.
The Billing APIs
The Billing API is really just a url that you can invoke in two (three) different ways. The first way gives you a list of what months you have available to get data from and the second way downloads usage data for a specified month.The third way is a short summary of usage data for a specific month, but that information is pretty useless, so I will skip that here.
API – what months are available
The bare syntax for the API call to list all the months you have available is the below HTTP GET request (low level, yes, but that is to show how simple it is). The return information is a JSON string with available months and to help you off, the urls to download them.
GET https://ea.azure.com/rest/<enrollmentnbr>/usage-reports HTTP/1.1
api-version: 1.0
authorization: bearer <loooong-key>
{“AvailableMonths”:[
{“Month”:”2015-05″,
“LinkToDownloadSummaryReport”:”rest/<enrollmentnbr>/usage-report?month=2015-05&type=summary”,
“LinkToDownloadDetailReport”:”rest/<enrollmentnbr>/usage-report?month=2015-05&type=detail”},
etc
],”ContractVersion”:”1.0″,”ObjectType”:”Usage”}
API – getting data for a month
As you can see below, you just add the month and type query parameters on the url to download usage data for a specific month. The format for the month variable is YYYY-MM and you have to set the type to “detail”.
GET https://ea.azure.com/rest/<enrollmentnbr>/usage-report?month=2015-05&type=detail HTTP/1.1
api-version: 1.0
authorization: bearer <loooong-key>
The return value this time isn’t JSON but in a CSV format. However, the first two rows contains rubbish which have to be removed to make Excel happy. The third row contains the column header values. Values are enclosed in double quotes and separated by a comma. Date is in the MM/DD/YYYY format and decimal values have a decimal point. That means if you like the most of the people in the world don’t live in the US, we have to convert this to get Excel to like the data from start.
Writing a Powershell script to download the data
The Powershell script that I wrote is actually just 70 lines, including comments. With it you can select to download a specific month or you can let it download all months available. Regardless of which, the script will output the data into one single CSV file with the Date and Decimal columns converted to your regional settings.
Parameters you can pass on the command line when running the scripts are EnrollmentNbr, Key and optionally desired Month. The two first is a must to make the script reusable and the third is so you can just download the current month and save some time. Remember that the month must be in the YYYY-MM format
The actual invocation of the API that downloads a month of data is a function so that it can be invoked in a loop while downloading multiple months or standalone when a month is specified on the command line. The parameters to the function is the variable part of the url and the powershell variable to hold the merged data downloaded over multiple months.
Powershell makes it quite easy to invoke http urls and download data via the System.Net.WebClient object. Two request header variables are required. The first is the api-version and the second is the authentication key, which is the key you generated in the EA portal, prepended with “bearer “.
Since the data is returned with two rubbish rows before the real data starts, I find the position of AccountOwnerId, which is the first column header value, and remove all junk in the string before it. Powershells ConvertFrom-CSV cmdlet is really handy here since it does all the heavy lifting in parsing the CSV data into an object. If you are a Powershell fan, you could party on the $data variable, since it contains and object model in an two dimensional array. Here, I just concatenate it with any previous downloaded data if we download multiple months.
Getting all the months is just a matter of invoking the API without month and type query parameters and use the data.
Since it’s JSON data in return we use the ConvertFrom-Json Powershell cmdlet to convert it into and and object we can loop through. The Months in the JSON data are returned in most-recent-first fashion which means we have to loop through the array starting from the end in order to get ascending sort order for all line items in the final CSV file we create.
After going through the above script code, we have all data we need in the $csvAll variable. However, before saving it to a file, we need to fix the regional settings of the Date and Decimal columns.
There are without a doubt many different solutions to do this, but I reverted to a brute force loop through all the rows in the array. It was quite fast. To get the Date correct, I first parse the date (which understands the US date format) and the use the .ToString(“d”) method which will format the date according to the ShortDate format you have on the machine that runs the script. For the Decimal values, I simply cast the to a float data type which does the trick of shifting to a comma if that is what you use for decimal point in your region.
The last part is to save the file to disk where I give it a name starting with the Enrollment Number and ending with the current date in the YYYYMMDD format so you can keep track of from when the data is. Again, saving it as a CSV file is all done via magic by Powershell who has a Export-Csv cmdlet.
Just do it!
If you didn’t understand any of this because it was too technical but you have access to the EA portal and can get the Enrollment Number and Key, you can just grab the script, save it somewhere on your laptop, open a Powershell Command Prompt (Blue on white) and type
.\download_billingdata.ps1 <EnrollmentNbr> <Key>
where you have to subsitute the values in <> for what you get in the EA portal. Then double click on the CSV file and start analysing!
Running this script with only the EnrollmentNbr and the (very long) key as parameters will download all of your Azure consumption details into a single CSV file. Using PowerPivot as I decsribed in the first part of this series-of-three blog posts, you can quickly analyse your Azure Consumption and see the trend to make forecasts.To download 12 months of my test data with some 45000 rows, it took 60 seconds.
The Billing Data is waiting for you out there!
Download script here http://data.redbaronofazure.com/public/download_billingdata.ps1
Billing API documentation from the EA Portal –
http://data.redbaronofazure.com/public/BillingandUsageAPI_20150205.pdf