Navigation
CATEGORY: How To

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.

Effective Nightly Backups with crontab

Cron is a way of scheduling tasks on linux based machines; most often used to run nightly, weekly or monthly batch processes to move data or restart services. Crontab is the tool on ubuntu to interact with the cron daemon and really simplifies the process. To start, we need to start editing our crontab file. If this is your first time using crontab, it should prompt you that it’s creating a new file and ask you for an editor. I typically use nano on Ubuntu and vi/vim on other distros. To do this, run the following command.

user@host> crontab -e

The format of the tasks is as follows. Keep in mind that for any of the time properties, an asterisk (*) may be used to signify “all.”

minute hour day_of_month month day_of_week command

Our command is pretty straight forward. We want to run a shell script and store the output in a log file (for debugging). The path is not specific to crontab or cron jobs in general and may be changed to suit your needs. I generally keep my work in /var to align with the general use of the server (/var/www/*)

0 23 * * * /var/scripts/nightly.sh > /var/scripts/cron.log

In /var, create your scripts directory and start editing nightly.sh. We’ll break the file into three sections: MySQL backups, File backups, and Service restarts.

#!/bin/sh
echo "Making backups of MySQL databases"
mysqldump -u USERNAME -pPASSWORD DATABASE_NAME | gzip > "/var/backups/SITE_NAME.sql.gz"

echo "Making file backups of the websites"
tar -czf /var/backups/SITE_NAME.files.tar.gz /var/www/SITE_NAME

echo "Restarting services"
sudo service MySQL restart
sudo service apache2 restart

echo "Finished!"

Keep in mind that the specifics of each line are relative to your setup. In my case, I keep all my backups in /var and all my sites organized by SITE_NAME in /var/www.

To test this script, all you need to do is manually run it.

user@host> ./nightly.sh

If all is well, you’ll be set! Happy cronning!

Adding a New User to MySQL

Whenever you boot up a service like MySQL which has a root user, it’s best practice to create users for your specific needs that are limited by security and scope. In the case of running a WordPress or similar MySQL based application, it’s best to limit the user to a specific database.

We begin by loading the MySQL CLI.

mysql -u root -p

We then create the new user. If you haven’t created the database yet, now would be an excellent time.

CREATE USER 'wordpress_admin'@'localhost' IDENTIFIED BY 'someCOMPLEXpassword';

Note that we want to limit the user’s scope to just localhost such that even if we were to open the server’s ports, the user would not be able to logon remotely.

We then grant privileges to the user to access our specific database.

GRANT ALL PRIVILEGES ON wordpress_database.* TO 'wordpress_admin'@'localhost';

If you ever need to remove the user, you may do so with the following DROP command.

DROP USER 'wordpress_admin'@'localhost';

Enable URL Rewriting on Apache

After installing apache, you can enable mod_rewrite by running the following commands.

sudo a2enmod rewrite
sudo service apache2 restart

You will probably want to use the .htaccess file to configure your mod_rewrite. In this case, you should make sure to allow overrides by modifying your site’s configuration file. If you haven’t already, I suggest creating/copying the default config in /etc/apache2/sites-enabled. To enable the .htaccess file, add the following.


    Options Indexes FollowSymLinks MultiViews
    AllowOverride All
    Order allow,deny
    allow from all

Pay attention to the Directory element and the path that follows. For me, this path is different and for you it may be different as well. And as usual, restart the apache service.

sudo service apache2 restart
There are no more results.