Azure Subscription Billing Reports Part 1

Reporting on Azure’s billing statements for a subscription has been a fairly contentious issue essentially since the inception of the service. For such an enterprise offering, the kludgy data schema seems almost comical. Each month you are given a simple CSV file that contains three tables of data, none of which seem normalized. It’s the goal of this mini-series to develop a solution for reporting on the data in a human readable format. Specifically, as a subscription owner, I would like you know the cost per meta group (client, service, application, et cetera) over the course of time. Let’s dive in, shall we?

To begin, we’ll need to know more about the actual data coming from Azure. We’ll focus on the v2 format as it makes a whole lot more sense than its v1 counterpart. The three tables in each report are “Provisioning Status” (Subscriptions), “Statement”, and “Daily Usage”. The amount you owe is determined solely from the Statement table. The problem is that this table only breaks it down per meter, not by resource group or resource. In other words, two Web Apps within the same meter would be compiled into a single amount owed. There is no way to know which app cost what for the billing period simply by looking at the Statement table.

This is where the Daily Usage table comes into play. This table describes the daily usage of each meter by each resource. If you have a database, web app, VM, CDN, or any other Azure resource, it will show up here. The odd thing, however, is that the meter data from the Statement table is replicated again, but with more information. It would have been great if Microsoft had included a fourth table for meters so that you could simply run a vlookup to bring in any related information. For now, we’ll have to figure that connection out manually.

“It would have been great if Microsoft had included a fourth table for meters so that you could simply run a vlookup to bring in any related information.”

The overall format of the CSV file is straight-forward at least, which will make parsing it a relatively simple task. It is as follows.

Provisioning Status
[Column Headers]
[Table Data for Subscriptions]

Statement
[Column Headers]
[Table Data for Billed Meters]

Daily Usage
[Column Headers]
[Table Data for Daily Usage]

To make programmatic use of this simpler, we’re going to create in-memory representations of the CSV file. We’ll start with the obvious – a CsvFile class. This class is going to hold the entire representation of the CSV file in memory, focusing on the collection of tables (as listed above).

namespace AzureReporter.ServiceModel {
    public class CsvFile {
        private List<CsvTable> _tables = new List<CsvTable>();
        private string _filename = string.Empty;

        public CsvTable GetTable(string name)
        {
            return Tables.FirstOrDefault(m => m.Name.ToLower() == name.ToLower());
        }

        public List<CsvTable> Tables
        {
            get { return _tables; }
            set { _tables = value; }
        }

        public string Filename 
        {
            get { return _filename; }
            set { _filename = value; }
        }
    }
}

Next, we need to create the CsvTable class which provides us with the next tier of data access. The goal of the table class is to provide unified access to the tabular data in the file, regardless of the number of columns or number of data rows that exist. We’ll store one row as the Header Row with the column name data. We’ll then use a second class in a collection to store the actual data.

namespace AzureReporter.ServiceModel {
    public class CsvTable {
        private string _name = string.Empty;
        private CsvBaseRow _headerRow = null;
        private List<CsvDataRow> _data = new List<CsvDataRow>();

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        public CsvBaseRow HeaderRow
        {
            get { return _headerRow; }
            set { _headerRow = value; }
        }

        public List<CsvDataRow> Data
        {
            get { return _data; }
            set { _data = value; }
        }
    }
}

Straight forward so far, right? At the core of this functionality is the ability to parse data out of a row of CSV text. We’ll use the CsvBaseRow class to handle this with helper methods much in the same way SqlDataReader works. I’m not going to include the code for every helper method as they are all simple and straight-forward. I have, however, included the method, GetCurrenciedAmount, as it adds a bit of extra functionality.

namespace AzureReporter.ServiceModel {
    public class CsvBaseRow {
        private List<string> _data = new List<string>();

        public string Get(int index) { /* */ }

        public double GetCurrenciedAmount(int index, string currency = "USD")
        {
            var data = Get(index).Replace(currency, "").Replace(" ", "");

            switch (currency.ToLower())
            {
                case "usd":
                    var i = 0.0;
                    if (double.TryParse(data.Replace("$", ""), out i))
                    {
                        return i;
                    }
                    break;
            }

            return 0;
        }

        public int GetInt(int index) { /* */ }
        public double GetDouble(int index) { /* */ }
        public Guid GetGuid(int index) { /* */ }
        public DateTime GetDate(int index) { /* */ }

        public List<string> Data
        {
            get { return _data; }
            set { _data = value; }
        }
    }
}

Then, for the data rows, we’ll inherit this class and add some extra functionality. Basically, we’ll want to link back to the Header Row in case anyone wants to do a lookup based on column name. We won’t focus on this functionality as it’s not required, but it’s nice to add.

namespace AzureReporter.ServiceModel {
    public class CsvDataRow : CsvBaseRow {
        private CsvBaseRow _headerRow = null;

        public string Get(string name)
        {
            return Get(HeaderRow.Data.IndexOf(name));
        }

        public Guid GetGuid(string name)
        {
            return GetGuid(HeaderRow.Data.IndexOf(name));
        }

        public CsvBaseRow HeaderRow
        {
            get { return _headerRow; }
            set { _headerRow = value; }
        }
    }
}

In the next part, we’ll take a look at how we can parse the CSV files into these in-memory objects.


There are no comments.

Leave a Reply

Navigation