MySQL client in official Ruby docker image

I’ve got a Sinatra app, it uses MySQL, and it works on my laptop! It didn’t work in a docker container though and instead was hemorrhaging this terribly unhelpful message when using prepared statements:

Using unsupported buffer type: 245 (parameter: 7)

Wow was that ever useless. It took a while but I got to the bottom of it. The official Ruby docker image is built on Debian Jesse and ships with MySQL 5.5, but I’m using MySQL 5.7 (yay for JSON support!). The mysql2 gem is a native extension which links against libmysql. Turns out MySQL 5.5 client doesn’t play nice with MySQL 5.7 server.


The fix was to pull in the mysql distribution source list and install the latest libmysqlclient-dev. I put the list in a gist if you want it (Oracle makes it super cumbersome packaging the list as a deb, honestly Oracle…). Afterwards add this do your Docker file:

ADD mysql.list /etc/apt/sources.list.d/mysql.list
RUN apt-get update
RUN apt-get install -y --force-yes mysql-common libmysqlclient-dev
RUN gem install mysql2

And hooray it works! If Googling this baffling error message brought you here, don’t forget to like, comment and subscribe.



Using Amazon Lambda with API Gateway

Amazon Lambda is a “serverless compute” service from AWS that lets you run Nodejs, Python, or other code. They have extensive documentation, but I found a disconnect: I was struggling to get the API gateway to map into Lambda functions. I put together a YouTube video, but some of the highlights are below. The source code for the project is on GitHub.



The Lambda function I demo is very simple, returning a URL based on a given input. When creating it, filter for “hello-world”, and adjust the following:

Name bsdfinder
Description BSD Finder
Role lambda_basic_execution

For the others, just accept the defaults. The IAM Role can be tweaked to allow your lambda function to access S3 as well.

Your method needs to have a event and context objects. The event object is how properties are fed into your function from the API Gateway, and the context object is how execution is stopped and response values passed out.

Test it

Once it’s saved, click Actions → Configure test event. Use this test data:

  "bsd": "open"

The execution should succeed and the results look like:

  "code": 302,
  "location": ""

If you got this far, the lambda function works.

API Gateway

The API Gateway fronts requests from the internet and passes them into the Lambda function, with a workflow:

Method Request → Integration Request → Lambda → Integration Response → Method Response

Method Request Specify what properties (query string, headers) will be accepted
Integration Request Map method request properties to the lambda fucntion
Lambda Properties passed into event object, passed out with context object
Integration Response Map lambda context properties to response
Method Response Response finally gets returned to UA


I deliberately kept this document thin. Check out the YouTube video to see it all come together.

Windows CPU monitoring with Splunk

I needed to overcome the Nagios limitation of a slow polling interval for CPU utilization. Splunk provides robust tools for leveraging WMI to monitor windows performance. So you think to yourself: “Great, lets look at CPU usage per process on important servers”, and you bump right up into the catch. Windows perf counters come in two flavors: “raw” and “formatted”, and Splunk only reads the formatted ones. This is fine for most things, but for CPU usage, the data isn’t accurate on multi-core systems. This post covers how to use the raw data, and “cook” the values to match what you’re used to seeing in task manager.


CPU utilization on muti-core systems

CPU utilization is measured by % Processor Time over a set period. A mutli-threaded application makes calculating utilization on multi-core systems not as simple 0 to 100 scale. During a time slice, several different threads may have had processor time on a core, or more likely, different threads will have had different utilization on different cores. When calculating CPU usage on a Windows system, the maximum is actually 100% x # of cores.


2016-01-06 20_09_15-silo - Remote Desktop Connection

Per core CPU usage average

The formatted CPU usage per process counter doesn’t take this into effect, and the results will not be consistent with what you see in performance monitor or task manager.


Cooking the values

So what you actually have to do is take two samples of CPU utilization, determine the elapsed time between them, and calculate the difference. Thanks to a very detailed blog post by Greg Stemp at Microsoft, I worked out that the counter type is PERF_100NSEC_TIMER making the formula:

100* (CounterValue2 - CounterValue1) / (TimeValue2 - TimeValue1)

I’ll get into applying that formula in Splunk in a minute, but first, getting the data in.


Get the data in

Like I said, Splunk has great first class Windows support, and has built in support for WMI inputs. It also has support for monitoring performance counters automatically, but since they don’t support RAW values, we’re going to use WMI. So, here it is:

index = test
interval = 10
server = RABBIT
wql = Select IDProcess,Name,PercentProcessorTime,TimeStamp_Sys100NS from Win32_PerfRawData_PerfProc_Process

The file must go in wmi.conf, it’ll appear in the GUI as a remote performance monitor.

Querying the data

And at long last, show me the chart! First off, a long Splunk query that is going to run off the end of the page (don’t worry, we’ll unpack it and go over it):

earliest=-3m index=test Name!=_Total Name!=Idle | reverse | streamstats current=f last(PercentProcessorTime) as last_PercentProcessorTime last(Timestamp_Sys100NS) as last_Timestamp_Sys100NS by Name | eval cputime = 100 * (PercentProcessorTime - last_PercentProcessorTime) / (Timestamp_Sys100NS - last_Timestamp_Sys100NS) | search cputime > 0 AND cputime < 400 |  timechart span=3 avg(cputime) by Name

The pieces explained:

Name!=_Total Name!=Idle
  • _Total and Idle make the time chart look bad, and can be more easily added with an | addtotals
  • We need the results from oldest to newest, because the calculations are based on a “previous value”
streamstats current=f
last(PercentProcessorTime) as last_PercentProcessorTime
last(Timestamp_Sys100NS) as last_Timestamp_Sys100NS
by Name
  • Streamstats pulls values from the previous record forward. We don’t need the current value, so we drop it.
  • last(PercentProcessorTime) and last(Timestamp_Sys100NS) pull the previous values forward and alias them
  • by Name groups the events together, so that you’re getting the “right” previous PercentProcessorTime. Without it, you would be getting values from some previous process
eval cputime = 100 *
(PercentProcessorTime – last_PercentProcessorTime) /
(Timestamp_Sys100NS – last_Timestamp_Sys100NS)
  • This is the math. It works out the CPU usage between the slices, and the time between the slices
search cputime > 0 AND cputime < 400
  • Throw out anomalous values. * 400 is 100 x the number of cores on this box. Adjust yours accordingly.
timechart span=3 avg(cputime) by Name
  • Make it pretty!

* PercentProcessorTime increments forever, if you plot it on a chart it rises over time. When running the math on a single process, it’s fine, the next value is always higher than the last. There are issues around the transitions though. If the last value of PercentProcessorTime for ProcessA is 40000, and the first value for ProcessB is 10000, then the delta is 30000 and it throws the graphs way out of whack. The easiest thing to do is just drop those values. We’re going to pipe the values through an avg() in timechart anyway, so little gaps like that will get smoothed out.

The results

I used a little app called CpuKiller to throttle the CPUs and make the results more obvious.


2016-01-06 15_39_39-rabbit - Remote Desktop Connection

Windows performance monitor Green – CPU killer Red – VMWare


2016-01-06 15_39_53-Photos

The same data plotted in Splunk. Note that Splunkd doesn’t appear in the perfmon graph because I didn’t include that process, but the WQL being run by Splunk watches all processes.


Some of the fun things you can do with this are look at processes from a number of servers across an entire farm (for example, your web servers), or plot application response times against resource utilization. Yay for Splunk!

etcd 2.0 cluster with proxy on Windows Server 2012

I got interested in etcd while working on CoreOS for a work project. I’m no fan of Linux, but I like what etcd offers and how simple it is to setup and manage. Thankfully, the etcd team produces a Windows build with every release. I created a Chocolatey package and got it published, then used it to setup the cluster. The service is hosted by nssm, which also has a Chocolatey package and the extremely helpful nssm edit tool for making changes to the service config. Etcd has great documentation here.

Etcd is installed by running:

choco install -y etcd --params="<service parameters>"

Where service parameters is:

etcd --name "%COMPUTERNAME%" ^
--initial-advertise-peer-urls "http://%COMPUTERNAME%:2380" ^
--listen-peer-urls "http://%COMPUTERNAME%:2380" ^
--listen-client-urls "http://%COMPUTERNAME%:2379," ^
--advertise-client-urls "http://%COMPUTERNAME%:2379" ^
--discovery "<your_token_here>" ^

Or you can install it in proxy mode by running:

choco install -y etcd --params="--proxy on --listen-client-urls --discovery<your_token_here>"

Proxy mode is especially useful because it lets applications running on a machine be ignorant of the etcd cluster. Applications connect to localhost on a known port, and etcd in proxy mode manages finding and using the cluster. Even if the cluster is running on CoreOS, running etcd in proxy mode on Windows is a good way to help Windows apps leverage etcd.

Watch a demo of the whole thing here:

Splunk user agent string lookups with TA-browscap_express

I got a requirement to find out what browsers our clients are using. We run a SaaS product, and every client is, so I could use the cs_hostname field in the log. Using a 3rd party analytic tool was totally out of the question, all I had to go on were the IIS logs.

We’re already getting the IIS logs into Splunk, so with a bit of Googling I found the TA-browscap app by Dave Shpritz. It’s powered by the browscap project and it works. The problem is that the browscap file is now 18MB and searching it has become very slow. What started as an hack to cache matches in a separate file has turned into a total fork and re-write of most of the app, and has become TA-browscap_express.

There are installation instructions on the application page at, also in the GitHub repo, so I won’t rehash them here.

The Browscap file

The Browser Capabilities Project (browscap) is an effort to identify all known User Agent (UA) strings, which regretfully are a total mess. The project is active, and the data is accurate. They provide the data in a number of formats, the legacy INI file still used by PHP and ASP, and a CSV file, among others. The file is 18MB and 58,000 lines long.

The structure of the file is a name pattern for a UA string, followed by all the known properties. My UA string is:

Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Firefox/31.0

And the matching name pattern is:

Mozilla/5.0 (*Windows NT 6.1*WOW64*) Gecko* Firefox/31.0*

The example above matches FireFox 31 on Windows 7 x64. Now here is an interesting challenge:

Mozilla/5.0 (*Windows NT 6.1*) Gecko* Firefox/31.0*

This name pattern matches FireFox 31 ono Windows 7 x86. It also matches x64. If you take the first match, you’ll get the wrong information. To get an accurate lookup, you need to compare all 58,000 name patterns, and the longest one which matches is the most correct. You can imagine, this is quite a challenge.

Parsing the browscap.csv file

The TA-browscap app uses pybrowscap, which is a Python library for parsing and managing the browscap.csv file. The library returns an object with properties for all the fields in the browscap file. I didn’t want to check 58,000 name patterns every time, so I wanted the successful pattern as well. pybrowscap doesn’t provide it, and it’s actually hard to re-create because they’re using python’s internal CSV buster.

The solution was to lift the core logic from pybrowscap and re-write it myself, busting strings as CSV data instead of files. The first thing you have to do is convert the name pattern into regex, which is easy, then compare your challenge string against it. Like I described above, after that  you loop through every name pattern until you find the longest match.

Knowing what to cache

The last entry in the file is “*” which will match anything. It returns a set of properties called “Default Browser” where everything is false. The idea is you’ll always get some response, you won’t get null. I didn’t want to cache these “Generic” or “Default” browsers, because once they’re in the cache, they’ll come up for every new UA string, and the data will be junk.

How it works

The app (TA-browscap_express) caches matched UA strings in a file and searches it first. During a query it also keeps matches in memory, using the memory cache before the disk cache. It also supports blacklisting obviously bad UA strings and storing the cache file on a network share to help with distributed search.

When a UA string is passed into the app, it checks 4 things:

  1. Is it blacklisted? If yes, return default browser.
  2. Is it in the memory cache? If yes, return the entry.
  3. Is it in the browscap_lite.csv file? If yes, add to memory cache and return.
  4. Is it in browscap.csv? If yes, add to browscap_lite.csv, the memory cache and return.
  5. If totally unidentifiable, return the default browser.



The browscap_lite.csv file is the cache file which is checked before browscap.csv. It’s in the same format, and has the same fields. Matched UA strings are written to it.

The default location for the file is in the application\bin directory for the app. In a Splunk distributed environment, that’s not really a good idea. You never know what search head or indexer the app will run on, and you’ll end up rebuilding the cache over and over. The browscap_lookup.ini file lets you specify a location for the file.

The blacklist

Some UA strings are junk, and won’t ever be added to the browscap file. Others could be added, and I suggest you report any new strings to, but it may take a while, or you just don’t care. The blacklist.txt file is used to weed out garbage UA strings so that you don’t waste time trying to look them up in the browscap.csv file, only to get “*” Default Browser.


The fields

The app returns all fields available in the browscap file. Two I find especially interesting are:

ua_comment This is a combination of the browser name and version, so that Internet Explorer 11 becomes IE 11.

ua_platform This is a combination of the operating system and version, so that Windows 7 becomes Win7.


There is one additional field which I added, ua_fromcache, which returns true, false, or blacklist, depending on where the data came from.


The demo

Sorry in advance for my droning voice, but if you want to see the setup and usage in action, check out my Youtube Video.


Trusting self-signed certificates in Windows

I had a requirement for users on a computer to automatically trust that computers self-signed certificate. I’m generating and managing these certificates with PowerShell. Googling left me with two general themes:

  1. Use some 3rd party app
  2. Terrible idea! Depraved hackers will eat your brain!

I didn’t want to use a 3rd party app, and I’m not scared of hackers. Eventually I got it, so I’m sharing it with the world.

New-SelfSignedCertificate -DnsName "*" -CertStoreLocation Cert:\LocalMachine\My
$cert = Get-Item Cert:\LocalMachine\My\* | Where-Object {$_.Subject -eq "CN=*"}
Export-PfxCertificate -Cert $cert -FilePath $ENV:TEMP\cert.pfx -Password (ConvertTo-SecureString "somepassword" -AsPlainText -Force)
Import-PfxCertificate -FilePath $ENV:TEMP\cert.pfx -Password (ConvertTo-SecureString "somepassword" -AsPlainText -Force) -CertStoreLocation Cert:\LocalMachine\TrustedPeople

New-SelfSignedCertificate is the cmdlet, but it won’t let you stick it in LocalMachine\TrustedPeople. This is why you need to export it as a PFX and re-import it. When you import it, you can put it in TrustedPeople.

Jenkins on Windows 7

Keith Casey did a talk a talk on Continuous Integration with Jenkins at Sunshine PHP. The first half of the talk was devoted to getting Jenkins installed on our laptops. In a room full of Mackbook Pros, I was one of the few Windows desktops. What follows is my experience with getting it working.

Keith talked about a lot of tools which help to check code quality including

  • Lint
  • Phing
  • CodeSniffer

Install PHP and Pear

  1. Download PHP and install it to c:\php. Add c:\php to your PATH variable.
  2. Download go-pear.phar and save it to c:\php. The information for that is here.
  3. Open an Administrative command prompt
  4. CD to c:\php and run php go-pear.phar. Press enter through all the prompts. Now you have Pear.
  5. Paste the entire giant blob of code into your Administrative command prompt
REM install phpunit
pear channel-update
pear upgrade-all
pear channel-discover
pear channel-discover
pear channel-discover
pear update-channels

REM install phpqatools
pear config-set auto_discover 1
pear install

REM install phing
pear channel-discover
pear install phing/phing-alpha

REM add PSR codesniffer
cd c:\php\pear\php\CodeSniffer\Standards
git clone PSR
phpcs --config-set default_standard PSR

Pear will park .bat files in c:\php for all the modules you installed. Notice that phing is installed as phing-alpha. This is only necessary until 2.7.0 is promoted to stable.

You also need to install Git for Windows.  Use MsyGit and pick “Git from Windows command prompt”.

Lastly, edit your php.ini and enable the extension php_xsl.dll

Install Jenkins

This was pretty straight forward. It’s a Java based project, and there is a Windows installer. Make sure to run the setup.exe, not the MSI.

Once Jenkins is installed, connect to http://localhost:8080/. If the Jenkins dashboard opens, it works!

The web2project example

Keith used one of his own projects, web2project, to demonstrate Jenkins. Getting the toolchain working on windows was a challenge.

  1. Login to the web console
  2. Click Manage Jenkins –> Manage Jenkins
  3. Under Updates, click available updates
  4. Install: Git, Github, PMD, Phing, CPD. A restart of the jenkins service may be necessary
  5. Go back to the dashboard
  6. Click New Job –> Build a freestyle project called web2project
  7. Under source code management, pick git and use the path
  8. Under build pick a custom windows batch command and fill in: cd unit_tests & phing metrics
  9. Click save and build now