Farley's Follies

Part 1: Splunk LED Meter - PowerShell and Splunk

2012-03-02T10:07:00.000-08:00

Sure it's been done before, but I figured why not build a USB controlled LED light strip to represent some arbitrary Splunk metric. In my case -- simply for demonstration purposes -- I wanted to gauge the number of unique visits to a particular website in the past 4 hours.

Here's what I used/purchased:

Software

Splunk 4.3 (Free)
Windows PowerShell 2.0 (Free)
Arduino (Free)
Libraries for the LED strip (Free)

Hardware

Teensy 2.0 USB Development Board ($16)
1 Meter 32 LED Light Strip ($35)

Currently I'm waiting for the hardware to show up, so I figured I would start with the PowerShell component of this project. Splunk offers a set of REST APIs capable of doing just about anything Splunk related. However, all I'm interested in is issuing a search and retrieving the results, so I'll use the search calls.

The logic of the PowerShell script is really simple: Issue a search, pause, get the SID (search ID), check the status of the search, pause, check again, and eventually retrieve the results once complete. For this project my Splunk search is returning one numeric value named "gauge" which gets set as a global variable. Later this value will be sent to the Teensy board by another PowerShell script.

Here's the script. It's pretty basic and doesn't do much (any!) error handling, but hey what's the fun in error handling.

###########################################################################
# Name: SplunkSearchGauge.ps1
# Purpose: Issues a REST API search call to Splunk and retrieves the
# result. Assumes the only value returned is "gauge" and a
# number. This should be used as a template and may require
# modification to meet your needs.
# By: Michael Farley - farley.mike@gmail.com
# Created: 02/28/2012
# Modified: 03/01/2012
###########################################################################

Add-Type -AssemblyName System.Web

###########################################################################
# Settings
###########################################################################

# Splunk hostname
$Server = "splunkserver.domain.com"
# Splunk port
$Port = "8089"

# Splunk search string. This script expects only one column and row to be
# returned name 'gauge'.
$Search = 'search index="webserver" host="hostname" earliest=-4h latest=now | dedup c_ip | stats count(c_ip) | rename count(c_ip) as gauge'

# Splunk username and password
$SplunkUser = "admin"
$SplunkPass = ConvertTo-SecureString "password" -AsPlainText -force
$Creds = New-Object -typename System.Management.Automation.PSCredential -argumentlist $SplunkUser,$SplunkPass

# Specify the XML namespace
$ns = @{ s = "http://dev.splunk.com/ns/rest"
         o = "http://a9.com/-/spec/opensearch/1.1/"
         d = "http://www.w3.org/2005/Atom"
}

# Script directory
$dir = $MyInvocation.MyCommand.Path | Split-Path

###########################################################################
# General functions
###########################################################################

function Get-Bytes($String) {
    [Byte[]]$byteArray = [System.Text.Encoding]::UTF8.GetBytes($String)
    $byteArray
}

function Get-SearchStatus($Sid) {
    $URL = "https://${Server}:${Port}/services/search/jobs/$Sid"

    $Request = [System.Net.WebRequest]::Create($URL)
    $Request.Credentials = $Creds
    $Request.Method ="GET"
    $Request.ContentType = "application/x-www-form-urlencoded"

    $Response = $Request.GetResponse()

    $Reader = New-Object System.IO.StreamReader($Response.GetResponseStream())

    [XML]$Results = $Reader.ReadToEnd()

    # Debug
    # $Results.Save("$dir\Status.xml")

    $Status = Select-Xml -Xml $Results -XPath "//s:key[@name = 'dispatchState']/text()" -Namespace $ns | Select -expand Node | Select -expand Value

    $Status
}

function Get-SearchResults($Sid) {
    $URL = "https://${Server}:${Port}/services/search/jobs/$Sid/results"

    $Request = [System.Net.WebRequest]::Create($URL)
    $Request.Credentials = $Creds
    $Request.Method ="GET"
    $Request.ContentType = "application/x-www-form-urlencoded"

    $Response = $Request.GetResponse()

    $Reader = New-Object System.IO.StreamReader($Response.GetResponseStream())

    [XML]$Results = $Reader.ReadToEnd()

    # Debug
    # $Results.Save("$dir\SearchResults.xml")

    $Results
}

###########################################################################
# Main program logic
###########################################################################

# Lets us accept self-signed certs
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}

# Step 1: Issue our request, which will be queued. We'll need to get the SID to
# retrieve the status and results once it's finished running.

$URL = "https://${Server}:${Port}/services/search/jobs"

$PostSearch = [System.Web.HttpUtility]::UrlEncode($Search)
$PostSearch = "search=" + $PostSearch

$Request = [System.Net.WebRequest]::Create($URL)
$Request.Credentials = $Creds
$Request.Method ="POST"
$Request.ContentType = "application/x-www-form-urlencoded"
$RequestStream = $Request.GetRequestStream()

$Bytes = Get-Bytes $PostSearch

$RequestStream.Write($Bytes,0,$Bytes.length)
$RequestStream.Close()

$Response = $Request.GetResponse()

$Reader = New-Object System.IO.StreamReader($Response.GetResponseStream())

[XML]$Results = $Reader.ReadToEnd()

# Debug
# $Results.Save("$dir\InitialQuery.xml")

$Sid = Select-Xml -Xml $Results -XPath "//response/sid/text()" | Select -expand Node | Select -expand Value
Write-Host "SID:" $Sid

# If the server is busy, you may need to wait longer.
# Checking the status of a job using Get-SearchStatusthat
# will failed if the job submission is still processing.

Write-Host "Waiting 5 seconds to continue..."
Start-Sleep -s 5

# Step 2: Check the status of the job.
# If it's complete we'll need to get the results

# Values we are checking against from XML file:
# QUEUED
# PARSING
# RUNNING
# PAUSED
# FINALIZING
# FAILED
# DONE

do {

    $Status = Get-SearchStatus $Sid

    switch ($Status) {
        "QUEUED" {
            Write-Host "Status: QUEUED"
            Start-Sleep -s 3
        }
        "PARSING" {
            Write-Host "Status: PARSING"
            Start-Sleep -s 3
        }
        "RUNNING" {
            Write-Host "Status: RUNNING"
            Start-Sleep -s 3
        }
        "PAUSED" {
            Write-Host "Status: PAUSED"
            Start-Sleep -s 3
        }
        "FINALIZING" {
            Write-Host "Status: FINALIZING"
            Start-Sleep -s 3
        }
        "FAILED" {
            Write-Host "Status: FAILED"
            $Status = "EXIT"
        }
        "DONE" {
            Write-Host "Status: DONE"
            [XML]$SearchResults = Get-SearchResults $Sid
            $Global:Gauge = Select-Xml -Xml $SearchResults -XPath "//results/result/field/value/text/text()" | Select -expand Node | Select -expand Value
            $Status = "EXIT"
        }
    }

} until ($Status -eq "EXIT" -or $Status -eq "DONE")

Write-Host "Status:" $Status
Write-Host "Gauge:" $Global:Gauge

Converting Shapfiles to GeoJSON

2011-12-12T13:58:00.000-08:00

For a recent project I needed county boundaries in GeoJSON format, which I did not have. I did, however, have the shapefiles. Luckily for me the ogr2org command easily handles this conversion:

ogr2ogr -f "GeoJSON" c:\california.json "c:\co51_d00.shp" co51_d00

Note: ogr2ogr, at least the Windows version, is bundled with Quantum GIS.

Google Fusion Tables and KML Polygons

2011-10-03T18:53:00.000-07:00

Google Fusion Tables (Fusion) makes it easy to visualize relatively simple data on a Google Map, assuming some portion of your data is or can be geocoded.

Recently, I needed to visualize California county level data on a Google Map. My requirements were pretty simple:

Outline the counties
Fill county polygons with a color representative of the data
Provide an informational pop-up when a county is clicked

Since the requirements were pretty simple I decided to give Fusion a shot. Fusion allows you to import a Google Docs Spreadsheet as a datasource, so that's the route I took.

In order to outline the California counties I turned to Fusion's ability to process KML. Using the KML polygon I was able to provide standard polygon coordinates (derived from Census data), which ended up being my "location" source for my data.

Fusion offers several options for formatting polygons on the map. In my case I used a number in my import file to color the counties/polygons creating a semi choropleth map.

Finally, Fusion allows you to format your informational popups using a combination of data from your datasource and HTML.

If interested, I've created a sample Google Docs spreadsheet that contains polygons for California's 58 counties. Note: The county_name in the sample_data sheet is being used to lookup the kml_polygon in the ca_counties sheet.

Visualizing County Level Data

2011-09-28T10:12:00.000-07:00

A few weeks ago I picked up a copy of Visual This at the bookstore. Overall I thought the book was great and was particularly interested in an example that used FIPS codes to create a choropleth map using a public domain map from WikiMedia.

The hardest part was creating an appropriate dataset. The map itself is an SVG image, which is nothing more than an XML formatted text file. Each county has an associated id which just happens to be its FIPS code. So if you're able to match up a piece of your data, in my case the zipcode, to a FIPS code you're almost there.

I'm pretty happy with the results. I ended up using Inkscape to tweak the SVG file before sharing it -- not represented here.

Update: Here's a link to a tutorial from the book's author. Wish I would have found it first!

Parsing Files with Python

2011-09-21T20:21:00.000-07:00

Recently, I've had to work with a lot of data stored in various text files: comma and tab delimited, fixed width, etc... So I decided, based on a little research, Python would be my tool of choice to parse and manipulate these files prior to inserting them into an Oracle database.

My most recent task: extract several columns from a fixed width file with over 22 million lines, remove leading zeros from two of the columns (making them a proper number) and write the results to a new CSV file.

Here's what I came up with -- it's quick and dirty, but works.

Python code:

import re

i = open("zipcty10")
o = open("10.csv","w")

line = i.readline()

while line:
print >>o,line[0:5] + "," + re.sub(r"^[0]*","",line[15:19].strip()) + "," + re.sub(r"^[0]*","",line[19:23].strip()) + "," + line[23:25] + "," + line[25:28] + "," + line[28:53].strip()

line = i.readline()

i.close()
o.close()

2011 Chicago Marathon

2011-05-10T09:57:00.000-07:00

I've been contemplating running the Chicago Marathon for a while, and today I finally decided to do it! The marathon is Sunday, October 9, 2011.

I've decided to register/run under a charity/non-profit entry for the United Parent Support for Down Syndrome (UPS for DownS for short). This mean I need to raise $950 that will go to the Katie MacDonald Literacy Project.

This give me about 5 months to train and raise the needed $950. So if you are feeling charitable, please make a donation using my Active.com fundraising page. 100% of your donation goes to the Katie MacDonald Literacy Project.

My goal is to keep this site up to date with my training and fundraising progress, so please sign up for email updates.

Thanks for your support.

Mike