Friday, December 27, 2013

Powershell Parse Outlook Inbox

I try to keep my inbox pretty clean. I only keep 30 days in my inbox before archival, try to setup rules to route and filter the noise, etc. I thought it would be cool to analyze the stats on my inbox. My inbox is pretty small compared to others, however there are some cool insights to gain from just 30 days of email. This process will work for larger inboxes as well.

I wrote a Script to parse Outlook Inbox. The output is a CSV file that you can use to analyze various things about your inbox. To Parse a folder in you inbox you can do this Using a BULK INSERT Script to load the data into SQL Server I was able to start generating some graphs. Here is a graph showing the top 20 senders to my email box. I left off the names to protect the guilty.

Top Senders

 Hour of day Breakdown

Busy email hours are 8am, 9am, 4pm etc.

Here is a word cloud of all the subject lines in my inbox (some stuff redacted)

VBScript Parse

With an update to Office some of my PowerShell code stopped working so I am adding the VBScript to parse Outlook as well.

Kick It: Starting the morning off right

Every morning I kick off the same programs to get the day rolling at work. Instead of launching all these applications manually I wrote a script to launch everything I need to get the morning started. It is easy to add new programs or commands.

Monday, December 2, 2013

Aggressive Driving First Pass

As you may or may not know I have an OBD adapter the sends data from my car's computer to my phone. I import that data into a database for analysis and such. I stumbled on an interesting article the other day about a similar OBD connection that pairs up with software on an iPhone.

The thing that interested me was their Aggressive driving metric. They use measures to calculate instances of aggressive driving, so I started to wonder what my data showed as far as aggressive driving habits and such. I started a first attempt to try and figure out a way to measure aggressive driving with the following measures. 

  1. An increase of Engine RPM by X amount in a 2 second time period - this would indicate that I punched the accelerator.
  2. A count of all the instances where I was traveling over 75 MPH. Maximum speed limit on my route is 70 mph.
  3. A decrease in Speed by X amount in a 2 second time period - this would indicate that I had to hit the brake pretty hard.

After some averaging and aggregates I decided for the first attempt here to use 1500 RPMs as a gauge for measure 1. Below is a map of all the instances where I increased Engine RPM by 1500 RPM in a 2 second time interval.

Not surprising the majority of extreme accelerations occur near an on-ramp for the various highways I enter and exit on the route. Also not surprising is that the majority of extreme accelerations occur on the commute home instead of the commute to work. My fellow drivers are just as anxious to get home as I am.

Here is my current to hit against my Torque Data:

I found an issue with the data. For some reason dates before 4/01/2013 have cut off the time element of the data field. I am working to re-import that data to clean it up and then I will continue with the other 2 aspects of aggressive driving.

Tuesday, November 19, 2013

Heart Rate on Antioch Run

Data collection has commenced with the new Zephyr Hxm Bluetooth Heart Monitor. I have been collecting data now for a few weeks and have developed a database and website to display the data.

The website is using the JavaScript charting library from Example below.

The above graph charts out one of my lunch runs. I use RunKeeper to track my runs. The picture below shows the details from Run Keeper.

Wednesday, November 6, 2013

Moved Blog to

Finally got tired of the image resize drama at Tumblr and migrated to the Blogger site. I am already deep into Google with gmail, my phone, drive, etc, might as well add some more data for them to collect on me.

So all my awesome google search results are hosed since the links to my blog changed. Oh well. Sorry if that caused you some grief.

Walking through Costco

Wore the Heart Monitor while walking through Costco the other day. 
I created a database to capture all the various events I record using the heart monitor. Then I configured SSRS to display the data. I need to figure out a way to match the heart rate up with various notes. Like when walking in the frozen food section near the ice cream, does my heart rate spike up.

Friday, October 25, 2013

Heart Rate Monitor - First data

I was as excited as a tweenage girl getting her hands on the latest Fall Out Boy CD yesterday when my heart rate monitor arrived in the mail. In about 30 seconds I had my shirt off and had the chest strap and monitor on. 1.2 minutes after that I had my first heart rate readings.

I wore the monitor during our Thursday night run. We ran a little longer course and man was it chilly. Here are the results.

The data collection is dead simple. I just threw the CSV into an excel spreadsheet. I will feed the data into a database for some more analysis but so far I am happy with the Product.

Zephyr Hxm BT -

Should be fun consuming this new data set.

Tuesday, October 22, 2013

Cold Feet

I created the Not So Mobile Temperature logger using the Arduino. Not so mobile because my 9V battery packs did not work. 

I used it to track the temperature under my desk at work. My feet are constantly cold at work in the fall/winter months. This day was a chilly day but not terribly cold.

Log Start Time: 7:36AM
Log End Time: 4:30PM

Average Temperature: 63.71
Mode: 64.17
StdDev: .085
Max: 65.93
Min: 60.65

Here is a graph using a subset of the data set.

Here is a shot of the Not So Mobile Temperature Logger.

Monday, September 16, 2013

Tootsee Roll Likability Progression

Remember that awesome song Tootsee Roll by the 69 Boyz circa 1994. Well you may remember the song but not the awesomeness of it. That song has an amazing intro, and then proceeds to get terrible at about the 84.35 second mark. The song craters at this point in such a manner you are asking yourself what just happened.

Here is a non-scientific chart showing the likability progression of the song.

Click Image for larger version.

Wednesday, September 11, 2013

Multiple ALTER Trigger Statements SQL Server

I was trying to update multiple triggers in a single job step. The solution took me a while to come up with so I thought I would throw it in here. When running mulitple dynamic ALTER TRIGGER statements you quickly discover that you lose context. By using the sp_executesql stored procedure and specifying the database name you can preserv context during each execution.

Friday, August 30, 2013

Draft this Weekend

So my Fantasy Football draft is this weekend. I have taken part in a few mock drafts and keep coming up with the same issue. After the first couple of rounds you are siting there staring at the screen wondering what player to take.

Fortunately there is not a lack of analisys. Seems like everyone has an opinion, a ranking, a projected stat calculation, etc. However flipping from site to site becomes pretty tedious.

I am in a hurry now to throw together a tool I call draft view. I am scraping the web sites for analysis and throwing them into a database. From there I can type in a player or a list of players and get all the information on a single screen.

I am not predicting a victory for my team, but at least I will not be scurrying around as much during the draft trying to figure out if I should take Hillman or Joique Bell.

Early Screen shot of the app:


Update 8/30 @ 3:55PM

Sources of info

  • ESPN Average Draft Position Chart

  • Cheat Sheet from this one site (wink)

  • ESPN FF Projected Rankings

  • ESPN Point Projections

  • Point Projections from this one site (wink)

Wednesday, August 28, 2013

First FitBit Numbers

So I have been wearing the FitBit for over a year now. I thought I would start looking at the numbers. I haven’t made time to really look at the numbers but I did come up with the Top 10 Step Count days and the Top 11 best days of sleep. I used 11 because I found out that one day I forgot to stop the counter when I woke up. FitBit thought I slept for 12 hours that day…yeah right!

Thursday, August 8, 2013

PowherShell Mass download with Credential

I needed a PowerShell script to download several images from my web server. The images were in a password protected area of my web site. At first I was receiving 401 errors due to lack of authentication. So I just had to add the Get-Credential module under the System.Net.WebClient. This prompted me for a password and boom the download worked as planned.

The $i is used to denote the number in the loop. As you can see my pictures had an interesting naming format since I just exported these out of picasa as JPG. The pics were originally shot as RAW files from my Nikon

$storageDir = "C:dl"
$webclient = New-Object System.Net.WebClient
$webclient.Credentials =Get-Credential
$i = 6900
do {
$url = "http://your$i.nef.jpg"
$file = "$storageDir$i.jpg"
while ($i -le 7548)

Monday, July 29, 2013

Cardinals vs Braves since 1990

Few of my friends like to trash talk Cardinals vs Braves. Here is how the teams compare since 1990. I chose 1990 because that is around the time I started to care about MLB and its my blog, not yours.

I did run some numbers for the entire history of the two teams. Cardinals own the overall games won total by an impressive 411 games. However remember that at one point in the history of the Braves, they were called the Boston Beaneaters. Not sure how you expect to field a good team with a name like that. Cardinals also lead the overall head to head record 1060-957 (.526).

However since 1990:

  • Braves lead in overall wins by 154 games

  • Cardinals have been in the playoffs 10 times

  • Braves have been in the playoffs 16 times

  • Cardinals have been to 3 World Series and have a record of 2-1

  • Braves have been to 5 world Series and have a record of 1-4

  • Braves lead the head-to-head record 115-85

Click Image for a larger version.


So one could easily say in recent history that Braves have been better than the Cardinals. Remember though you can make the numbers say exactly what you want (and I do not like the Cardinals).

I can almost hear the Cardinal tears being cried how this is not fair, or claiming how high their attendance numbers have been, or total World Series counts or something.

The Cardinals have had a long storied history of excellence and the Braves have had an extended run of excellence over the past two decades. Hey as a Royals fan, I really have nothing to say. We had some glory years in the 80s. Fans of the Cardinals and Braves have it good. 

I guess as a Royals fan we do have one thing to say: “Safe” -Don Denkinger Game 6 10/26/1985.

Wednesday, July 10, 2013

Friday, June 14, 2013

PowerShell Mass Download

Here is an example of a PowerShell script to mass download files from a web site. This is a very simple example and would probably need to be changed based on your need. The files I wanted to grab were named 1000.jpg.  A four digit number jpg.

$storageDir = "C:somefolder"
$webclient = New-Object System.Net.WebClient
$i = 1050
do {
$url = "$i.jpg"
$file = "$storageDir$i.jpg"

while ($i -le 2000)

Good luck

Friday, May 31, 2013

Chiefs 2012 Review - First Down Play Calling

Last year was a tough year to be a Chiefs fan. Cautiously optimistic and hoping for the best this year. Here are some numbers from the 2012 debacle.

First Down Play calling

The cheifs had a total of 436 First Downs, 22nd in the league. That stat doesn’t mean a whole lot. Two possible explanations for first down counts are You stink, or you have a lot of big plays on offense. The Chiefs called a pass play 36% of the time on First Down compared to 64% rush play calls. League average pass play call on first down is 46% vs 54% for rushing play calls.

Here is the pass vs rush play calls on first down by quarter for the Chiefs and the entire NFL

1st quarter shows heavy run for the Chiefs. Trying to establish the run early at 73% of the plays run plays. Well above the 58% league percentage. The rush play call on 1st down is lopsided in every quarter for the Chiefs except the 4th quarter. At this point in the game the Chiefs have to start passing since they were typically losing.

The sample size is too small to predict the play on 1st down, but it will be interesting to see 2013 stats with a new quarterback and new head coach.

Wednesday, May 29, 2013

Chats with Byrne

Few months back I did a word cloud analysis on IM conversations with the one and only Byrne. Word Clouds are amazingly easy using R and R-Studio. 


Below is the cloud.Some notes

  • The numbers are time stamps

  • Byrne and are are in-decisive with words like probably, maybe, pretty, etc.

  • We overuse the word cool

  • Lunch is an important topic.

Friday, May 17, 2013

When is the best chance to see a HR

Between 1952 and 2012 when is the best chance to see a HR? It happens to be on a 0-0 count in the 4th inning. Historically, 53.07% of all HR balls happen on an 0-0 count.

That number, to me, is surprisingly high. 


The 4th inning holds a slight edge on most HRs in an inning with 12.623% of all HRs. In my opinion a couple explanations could be the pitcher is getting tired as the game progresses and/or the batter is having  more success in second/third at bats against the pitcher. Again the margin is slight. Interesting note is that the 9th inning is relatively low related to the other innings at 7.89%. Perhaps explained by the dominance of closing pitchers. However when a closer gives up a jack we seem to remember it since typically the situations are high leverage situations with the game on the line.


The inning with the least chance to see a HR is the 25th inning with a .0005% chance. Of course if you make it to the 25th inning to see a HR you should get some kind of prize like a free ticket to a game or a free dozen donuts or something.

Looking at 3 recent HR sluggers you can see their success on the 0-0 count. The 0-2 count is incredible. Pitchers have the upper hand on 0-2 as far as the long ball is concerned.

Perhaps the batter switches plate approach to contact, but truly an amazing drop off in HR balls on 0-2. The HR numbers fall dramatically at 0-2 count. Of course 3-0 count is the lowest HR % count since typically you take that pitch.


Monday, April 22, 2013

Same Name Game

Found this entry on a blog post about “pouring over” retro sheets to find some data. Not sure if the pouring over is thrown in there for dramatic effect or what. Honestly once you have the retro sheet loaded up in SQL Server you really do not have to pour over anything, just write a query.

I digress. Anyway the blog post wondered how many times a batter shared the same first name of the previous batter’s last name. The author of the blog stated that this has happened 130 times. After some “pouring over” I discovered the author had only reported 1 instance per game. 

I wanted to see how many actual plate appearances this same name game had happened. My data set is only 1952-2012 and I show 315 Times where  a batter had the same first name as the previous batter’s last name.

SELECT e.GAME_ID, e.YEAR_ID,p.lastname, p.firstname, p2.lastname, p2.firstname
FROM dbo.[events] e JOIN dbo.[lkup_people] p ON e.BAT_ID =
JOIN dbo.[events] e2 ON e2.seq_events = e.seq_events+1
JOIN dbo.[lkup_people] p2 ON e2.BAT_ID =
WHERE p.lastname = p2.firstname


Looking more closely at the Casey Blake/Blake DeWitt combo we see that Casey Blake reached base 48% of the time before Blake DeWitt stepped to the plate. Blake DeWitt would then reach base 38.5% of the time directly following Casey Blake. 


SELECT e.GAME_ID, e.YEAR_ID,p.lastname, p.firstname, p2.lastname, p2.firstname,e.EVENT_CD, e2.EVENT_CD
FROM dbo.[events] e JOIN dbo.[lkup_people] p ON e.BAT_ID =
JOIN dbo.[events] e2 ON e2.seq_events = e.seq_events+1
JOIN dbo.[lkup_people] p2 ON e2.BAT_ID =
WHERE p.lastname = p2.firstname

Monday, April 15, 2013

Servo Tests

Started playing with servo and the Arduino tonight. My goal is to make a web cam turret controlled by a joystick or game pad of some kind.

Here is a video of the servo with a mind of its own…as in hard-coded movement sequences.


Here is a video with the servo hooked up to the potentiometer 


My next step is to figure out how to hook up the gamepad to control the movements. I have a few different gamepads to try. Just need to make the time to test.

Thursday, April 11, 2013

Hitting bombs 0-2 with 2 outs

So started playing with the retrosheet data. Retrosheet is a non-profit organization that gathers baseball stats and presents them in consumable formats. 

Then some dude wrote this software called chadwick which allows you to parse the data and import it into a database. The article I found created a MySQL database which is ok for small stuff. The event data (every plate appearance since 1952) is 9.4 million rows. I didn’t really have the patience to learn how to optimize this using MySQL, so I quickly moved the data over to a SQL Server instance. So now I have a SQL Server database with every play from 1952-2012. 

I have run several queries against the data, created some stored procedures for Run Expectancy and such. One of the searches I am working on is who is the player that has hit the most Home runs with an 0-2 count with 2 outs. See the chart below.


Sammy “No Comprende” Sosa tops the list with 11 jacks on an 0-2 2 out count. In fact we have several who’s who of the steroid era in this list. Jose Valentin has the least amount of career bombs but hits our list at number 7. 7.32% of the time with a 0-2 2 out count he hits a home run, and a full 3.61% of his Home runs were hit on this count. Kendrys Morales has the fewest TotalABs at this count and is leading in PCT HRs on this count. His PCT will go down as he gets more ABs.

CTE used to get Initial Counts.

(select p.firstname + ' ' + p.lastname Player, COUNT(1) TotalHR,
FROM events e JOIN lkup_people p ON e.BAT_ID =
and BALLS_CT = 0
GROUP BY p.firstname + ' ' + p.lastname,
SELECT r.player, r.TotalHR, COUNT(1)
FROM events e JOIN HRCTE r on e.BAT_ID =
and BALLS_CT = 0
GROUP BY r.player, r.TotalHR

Monday, April 1, 2013

Monday, March 18, 2013

Cost of a Bedroom overnight

After hooking up a humidifier in the boys room overnight, I started to wonder what that actually cost me. Easy enough to calculate. The boys have a clock radio, a night light and the humidifier running overnight. They have not been running the ceiling fan so I did not include that in my calculations. I used 10 hours per night which is about average.

Here are the numbers for the Humidifier.


I figured 350 nights per year taking into account the nights we are not at home, probably away from home a few more nights and sure some other variables here but you get the gist. So if we run the humidifier every night that will cost me $107 per year. That is of course if we run the humidifier every night. Costs .30 cents per night.

Since we only run the humidifier when the boys are sick, I wanted so see what their room cost me overnight on a more typical night with just the night light and the radio. Figure below.


So 0.08 cents per night on a normal night. The little things add up. Surprised that the radio drew less power than the night light. The radio is playing music and displaying the time all night.

Friday, March 15, 2013

SQL Server xp_dirtree

I needed a way to parse the filenames in a directory and plug them into a RESTORE command. The xp_dirtree works really well for this.

IF OBJECT_ID('tempdb..#restoreFiles') IS NOT NULL
DROP TABLE #restoreFiles

CREATE TABLE #restoreFiles
[filename] VARCHAR(2000),
[depth] INT,
[file] INT

INSERT INTO #restoreFiles
EXECUTE xp_dirtree 'D:backups',1,1

From there I was able to set a variable based off a WHERE clause. My FULL backups are pre-pended with the word FULL so I can plug that into my RESTORE command.

DECLARE @FullBackupFile VARCHAR(2000) = (SELECT TOP 1 [filename] FROM #restoreFiles WHERE [filename] like 'FULL%' ORDER BY [filename] DESC)

FROM DISK = "D:Backups' + @FullBackupFile + '"
WITH MOVE "DataFile" TO "D:MSSQLDataData.mdf",
MOVE "LogFile" TO "D:MSSQLDatalog.ldf",
STATS = 10

Wednesday, March 13, 2013

Syntax Highlighter using Pygments and Python

I was using this web site to highlight and format my SQL code to HTML to insert into our wiki at work on Sharepoint, when all of a sudden the website stopped working. After some searching I discovered the site was using Pygments, a Syntax Highlighting Library for Python. So I decided to try and roll my own solution. This article will detail the steps I went through to make it happen.


  • Download Python 2.7, 3.3 did not work for Easy Setup which I used to install pygments.

  • Download Easy Setup also called setuptools.


  • Installed Python 2.7 with the defaults which creates a folder called C:Python27

  • Installed Easy Setup using the EXE taking the defaults

  • Installed Pygments by hitting a console window and running this from the C:Python27Scripts folder. easy_install Pygments


  • You have to configure IIS to work with Python.

  • IIS 7: I used this article for IIS 7 config:

  • IIS 6: I used this article for IIS 6 Config:

  • The IIS 6 and IIS 7 config is a little different specifically on the Executable parameters.


  • First I setup a basic HTML page with a simple form.The page had a single form with a TEXTAREA named code.

  • Second I pieced a Python file together using code snippets from the Pygments web site, StackOvervlow, and Python help files.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import cgi
# enable debugging
import cgitb

#print "Content-Type: text/plain;charset=utf-8"

from pygments import highlight
from pygments.lexers import SqlLexer
from pygments.formatters import HtmlFormatter
from pygments.styles import get_all_styles

form = cgi.FieldStorage()
code = form["code"].value

print ""
print ""
print ""
print highlight(code, SqlLexer(), HtmlFormatter(noclasses=True,style='default'))
print ""
print ""
print ""
#print HtmlFormatter().get_style_defs('.highlight')

print highlight(code,SqlLexer(),HtmlFormatter(noclasses=True,style='default'))

#print list(get_all_styles())

  • SqlLexer is hardcoded here meaning that we can only convert SQL code with this page.

  • Working on allowing for other languages

  • Pygments Formatter has several options, this code is only using the noclasses and style options.

  • There are several builtin Styles and you can create your own styles.

Here is the semi-finished product.

Next Steps

  • Allow for different types of code

  • Create a custom style (don’t like the default background color)

Friday, March 8, 2013

Tuesday, March 5, 2013

Wednesday, February 13, 2013

Work Temps

Took the ol Laser Infrared Thermometer to work last week and recorded temps of things around the desk. Far left is the Baseline temp. The Up/Down trends are between each recording. The Temp Diff column on the  far right is the difference between the 4:14PM reading on 2/5 and the 7:40AM reading on 2/6.

Friday, February 1, 2013

Basement Temperature

Startingt to look at some of this data the Arduino is producing. I am recording the temperature in the basement every minute. The chart below shows the recordings from 1/24/2013 - 2/1/2013. The Orange is the Standard Deviation line.

Looks like we had a spike starting on 1/28 @ 12:13PM and lasting to 1/29 @ 9:02PM. We had unseasonably warm temperatures on Monday 1/28 so I bet that would explain the spike.

Today is the coldest day in 2 years so I’ll be interested to see how far the temp dips today.


Tuesday, January 29, 2013

PowerShell Encrypted Password NET USE

I needed to copy a file from a UNC to a server today. In the past I have always used the NET USE command to connect to the share. The main problem with this is that I typically stored the password unencrypted in the script. Lame.

I figured there has to be a better way to do this. Turns out there is. Power Shell allows you to setup an encrypted password file, then decrypt that file on the fly at runtime. I know this is not the most secure solution, but it sure beats storing the password in plain text.

#Function used to decrypt password
function Decrypt([string]$exportfile)
$securepassword = ConvertTo-SecureString $exportfile
$helper = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$global:pass = $helper.GetNetworkCredential().Password

#Set some variables
# Secure file stores the password for the DOMAINUSER Account the file was generated using this command
# read-host -assecurestring | convertfrom-securestring | out-file C:scriptsSecureFile.txt
$username = "DOMAINUSERNAME"
$sourcePath = "\someserverx$somefile.ext"
$destpath = "x:some path"
$securefile = "C:scriptsSecureFile.txt"

#Open up the secure file and decrypt it
$exportfile = get-content $securefile
Decrypt $exportfile

#Map the Drive
#using old school NET USE command to map the drive. This will cache the credentials so the Copy-Item command will work.
net use \serverx$ $pass /USER:$username

#copy the File and delete the drive
# Clean up the NET USE command by deleting the connection.
Copy-Item $sourcePath -Destination $destPath
net use \serverx$ /DELETE

Thursday, January 24, 2013

Basement Temp

Was able to play with the Arduino last night. Setup a simple Temperature sensor to record the temperature every minute. I then had the help of a Serial Port/Arduino proxy called Gobetwino. This allowed me to record the temperature to a text file. From there I wrote a FTP script to move the text file to my web server. Then I was able to consume the data using an ASP page.

Terrible looking website but my focus was to get it working. The FTP script runs every 10 minutes. Works pretty smooth. Now I want to add the Photo Resistor to the project to tell when the light turns on and add that to the output.

Friday, January 11, 2013

2012 NFL Penalty Data

Found a guy that put all the plays for the NFL season in a CSV file for consumption. Awesome. Here is my first analysis on the data. More penalties are called on 3rd down than any other down. Perhaps more of a pressure situation. Also interesting more penalties are called in the 2nd and 4th quarters. Need to dive down a little deeper, maybe sort by time intervals.

Wednesday, January 9, 2013

JavaScript Determine which key is pressed

Needed to act based on button pressed. So here it is.

function RandomEP(e)
var evtobj=window.event? event : e //distinguish between IE's explicit event object (window.event) and Firefox's implicit.
var unicode=evtobj.charCode? evtobj.charCode : evtobj.keyCode
var actualkey=String.fromCharCode(unicode)
if (actualkey=="d")
//your code here

Then in the body tag you insert onkeypress=”RandomEP()” or whatever your function is called.

Wednesday, January 2, 2013

TSQL Make Directory

Create a directory using xp_cmdshell. Script includes steps to enable xp_cmdshell and then disabling it when finished. Take that out if you want.

--ENABLE xp_cmdshell to execute the mkdir
EXEC sp_configure 'show advanced options', 1
GO EXEC sp_configure 'xp_cmdshell', 1
--Make the directory

       @directory VARCHAR(100) = 'C:tempjoe ' + @@SERVERNAME 
SET @cmd = ' mkdir ' + @directory
EXEC xp_cmdshell @cmd, no output

--Disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 0