Navigation

My Leadville Journey, Part 1

Riding more than 100 miles. At altitudes over 10,000 feet. With more than 10,000 feet of cumulative climbing. On a Mountain Bike. On my 31st birthday.

This is the Leadville 100 MTB race. And I’ll use that word “race” loosely, as for me it will be more a question of survival. There will be time checks that have to be made out on course just for the right to continue on the death march. If you cannot repair your bike, you’re out. If you accept medical aide, you’re out. Why even bother? And what exactly will go into making this a reality?

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

Two Months of Surface Book Success

It’s been roughly two months of ownership over a Microsoft Surface Book, the laptop version of the tablet that replaces laptops. Chew on that for a few minutes. Marketing issues aside, the device has been a complete success for me and has replaced my much loved Surface Pro 3 for everything but desktop Netflix watching. That said, life with the new hardware has not been without problems or unwanted surprises. The short of it is that while the hardware brilliantly fills a gap in Microsoft’s ecosystem, the software layer that supports it leaves a lot to be desired.

I’ve owned a Surface Pro 3 essentially since the launch of the device and have used it tremendously while traveling or visiting clients. The keyboard/kickstand and pen trick is definitely fantastic. A big part of this “review” is comparing the importance of factual data and more subjective points; having a kickstand and not needing a lumpy case that adds to the overall size & weight is a huge win. My mother’s iPad case is falling apart, but my SP3’s stand is still going strong. But as good as the Surface Pro 3 was, the keyboard was always lacking that professional feel. The new Surface 4 keyboard has helped, but only in an iterative way you’d expect.

“If you own or are purchasing a Surface Pro 3, the new Surface 4 keyboard is a must-buy.”

Enter the Surface Book: a device designed to fix the shortcomings of the Surface Pro while remaining squarely in the Laplet (laptop + tablet) category. In this sense is it 100% a success. Where as I would normally use the MacBook Pro as the bar for professional looking and feeling ecosystem, the Surface Book exceeds in all areas. Going back to my fiancĂ©’s MacBook now leaves me feeling as though I’m playing with a late 90’s Dell.

The number one reason for me either enjoying or hating a laptop device is the usability of the trackpad. While some are infatuated with the Lenovo hardware, I simply loathe it. My Lenovo always ghost clicks and the additional buttons simply get in the way. My Dell’s have had similar problems or have not had the accuracy and sensitivity compared to the Apple counterparts. I love the Surface Book, if for no reason other than its amazing trackpad. It is precise, sensitive, never ghost clicks and the builtin hard clicks are well proportioned.

While the hardware looks great, feels fantastic and responds quickly, the problems seem to start as soon as you hit the power button. At least, that’s what the community would have you believe. With titans like Paul Thurrott openly complaining about BSODs and general failures, you were seen as crazy for being an early adopter.

That’s exactly what I did – I bought as early as I could – and I’ve only had a single BSOD to date. There is a lot that can be said about this. My shipment was delayed until the second hardware revision, so there is a chance I’ve simply been “lucky.” I’m in the insider ring, so there is a chance I’ve had updates that main consumers may not received. And finally, it’s common that the loudest voices in a community are those who are having problems, and rightfully so. Certainly in that respect, I have not, until now, spoken anything positive (or negative) regarding my experience.

“I love the Surface Book, if for no reason other than its amazing trackpad.”

But perhaps more importantly is that, as a software engineer, I am incredibly tolerant of a device or software having issues, especially in the first iteration. The fact is that Microsoft simply cannot replicate all the use cases that a million consumers will throw at the device. When comparing or considering any device, it’s important not only to look at the factual information (how much battery life does it get?) but also the personal and emotional experience with that device. Microsoft drives that home with Windows 10 and specifically Windows Hello, a new chunk of software and hardware that authenticates users with facial recognition. And it works better than you would expect. In Apple-talk, it simply works. I’ve been in hooded sweatshirts, hats, no hates, buzzed head, bearded, and all sorts of variations and it has, without a single fault, logged me in every single time. It’s just that smooth.

Unlike others in the community, this is where my problems start. While Windows 10 is a fantastic iteration on the revolution, there are parts that feel like slapware and abandonware. For instance, when the OS updates, why is it that I need to sit through a minute long “reintroduction” to my desktop, telling me that my files are in the same place, as if that would ever be in doubt? And why has Edge still not received significant updates several months after its launch? When I open a new tab why am I still facing the age old question of whether or not my cursor will be placed in the search box? While the new control panel interface is touch friendly, I’m still stuck going through the old UX to scan paper from my Canon MX880 because the functionality (provided via right click) just isn’t available otherwise. But there are workarounds and the percentage of my life that these issues plague is incredibly low. Most of my time is spent in Chrome, Visual Studio, Office, and Adobe software. And those are wonderful and smooth and run beautifully on the Surface Book.

In the end, it simply doesn’t matter what device has the best screen, the best keyboard, the best CPU or the best battery life. What matters most is how you use it and how often it is used. For me, the Surface Book gets used everyday and used to develop software. It excels at what it does and there is no other device I’d rather have in its place.

There are no more results.