Script Sample - Format EA Billing Usage Csv for Tags

As a system administrator, you will need to work with Enterprise Agreement (EA) related billing data a lot. As such you will need to pull lots of reports based on this consumption and usage related csv file that you get from the EA portal i.e. at address: https://ea.azure.com. Tagging is one of the important components. But it comes as a composite JSON in one column. You will want to split this into multiple columns, with one column for each tag.

The script sample in this blog performs exactly this parsing for you. You will need to tweak the script to include the tags as per your environment.

Note: The EA portal is only available to the EA customer. If you are not an EA customer, then if you try to login, you will get the following error:Invalid User: The account provided is not a valid user of the Microsoft Azure Enterprise Portal. Please sign in with a valid account. If you believe you have received this message in error, please contact Support.

Sample Input EA Azure Billing CSV

The tags section in the CSV will look similar to below JSON format key-value pairs under the column for "Tags":

{  "ApplicationOwner": "aman@domain.com",  "BusinessUnit": "Infrastructure",  "ApplicationType": "Test",  "Department": "Infrastructure"}

Now as a requirement, you will want to split this to separate columns for the Tags for "Application Owner", "Business Unit", "ApplicationType" and "Department".

How does the script parse this JSON

The script parses the JSON by splitting it into multiple values and then saving the new object as an output CSV. Right now these tag values are hard coded into the script and needs to be tweaked as per your requirements. Look out for the names of the variables and output member names with same name as that of the Tags mentioned above. These are the ones that you will need to alter.

The script also takes into factor if the tag was added incorrectly with space in the name instead of pascal casing (i.e. name with no space between two words and first letter capital for each word).

Location of the Script

The script is located in the GitHub along with samples for Input and Output CSV files at this location: GitHub location for Format Billing CSV Script

Note: An alternative to using the billing related csv file is to leverage PowerBI and build dashboards by consuming the EA related billing APIs which are available out of the box. These are in preview at the time of the writing of this blog. Parsing tags is still an issue with these. We will cover these in one of the future blog post.