TFS PowerShell Query Blink(1) Integration

Because why not really. I wanted to query TFS with PowerShell anyway so I might as well integrate it with my Blink(1) USB LED.

Power Tools

First you need to download and install Team Foundation Server Power Tools. Here is the link for the 2015 version: https://visualstudiogallery.msdn.microsoft.com/898a828a-af00-42c6-bbb2-530dc7b8f2e1.

Make sure you do a custom install because for some reason the PowerShell cmdlets are not selected in the default install.

Script

I created this script to connect to our TFS server and connect to the project I wanted to monitor. I wanted to see all defects that were not closed, back log, or resolved state.


This will take the current count from TFS, and compare it with the previous count stored in a text file. I know this is low tech but I wanted a quick solution.

Blink(1) Integration

You can do whatever you want with the basic count code. If the current defect account is lower than the previous you can send an email, update a database, update a webpage, whatever. I enabled the API on the Blink(1) and decided that I would play a color pattern if the defect count was lower, and a different color pattern if the defect count was higher.

Final Script

So then I set the PowerShell Script to run every 5 minutes using task scheduler.

powershell -file "C:\scripts\CRM_DefectCount.ps1"

If the defect count goes up I display the Fire Engine Pattern, if the defect count goes down I display the dancefloor pattern. Below is a video showing how it works.

Party Button Version 1 Amazon Dash Hack

I previously blogged about the Amazon Dash button hack here and here. I decided to step up the Dash button game a bit an create something cooler. Introducing the Party Button.

I have been messing with relays and the Arduino this weekend so I decided to incorporate all that fun into a Party Button using the Amazon Dash. When you start a party a python script is started that sends a signal to the Arduino to fire up the relay. The Relay controls an outlet that has party lights and a stereo receiver atached to it. Everything is powered on and then the Python script will pick out a random song and start it up using VLC command line.

Python Script

I used python to setup the ARP sniffer (needed to sense when the Amazon Dash Button was pressed), pick out a random song from a directory, send a signal to start the relay using the Arduino, and play the selected song using VLC.

I had to send the off command (HIGH signal or 1) to the relay at the start of the script because when you activated the Serial port using Python it would automatically reset the signal to the Relay causing it to come on. So I set a sleep command to sleep 5 seconds and then send the off signal to turn off the relay.

Since the ARP sniffer required Linux to work properly (scapy library works out of the box in Linux) a nice side benefit was the fact I could send a signal directly to the serial port using the pySerial module.

Party Button Test Rounds

So we went through a few test rounds of the Party Button. I uploaded the video to YouTube. You can see I had 2 handy wingmen helping me with the testing.

Arduino Relay Outlet

Finally started to dabble with relays and AC power. Insert the normal when you are playing with power you can die warning here. Make sure you know what you are messing with before proceeding.

My end goal is to have 8 outlets that I can turn off and on. This post will show you one outlet.

Parts List

• 4 Gang Plastic Electrical Box at Home Depot  $4
• 15 Amp Outlet at Home Depot - $1
• 8 Channel Relay
• Power cord - had one laying around
• Arduino and some male to female wires
• Misc tools - Wire cutters, screw driver, etc

Helpful Article

There are tons of articles out there on how to hook up a relay to an Arduino, however it seemed most were lacking detail. I finally stumbled on this awesome article and was able to adapt it to the 8 Channel Relay: http://www.circuitbasics.com/build-an-arduino-controlled-power-outlet/

Specifically this image. I spent a ton of time trying to find something like this.

The pic above is what I ended up with.

• Hot wire (black) from power cord is wired to common (middle terminal) on the relay.
• Connect hot wire (black) into NO terminal of Relay and connect it to hot terminal (gold) on your outlet.
• Ground wire (green) from power cord connects to Ground (green screw) on outlet
• Neutral wire (white) from power cord connects to Neutral (silver screw) on outlet
• From Digital Out 7 on Arduino to IN1 on Relay module. IN1 is the first channel
• From VCC on Relay to 5V on Arduino
• From GND on Relay to GND on Arduino

You will want to make sure your connections look cleaner than the above screen shot. I went back and cleaned them up and added some electrical tape reduce the risk of a short and or shock.

Arduino Code

You can test the relay before adding the juice, to make sure your wiring is correct. This script will turn the relay on wait a little bit, then turn it off. It will cycle through like this in a loop so you can test it out.

From here you can get more advanced and trigger the relay based off other conditions such as sensor output, etc.

This is my first step towards working with Christmas light automation. I am also working on an Amazon Dash Party Button hack that I will blog about shortly.

Link Dump 1/20/2016

PowerShell steps to create a CRM user - http://crmtipoftheday.com/2015/12/22/create-non-interactive-user-like-a-boss/

XRM Powershell Modules - https://github.com/seanmcne/Microsoft.Xrm.Data.PowerShell

Minecraft Education Edition - http://www.theverge.com/2016/1/19/10788994/minecraft-education-edition-announced-microsoft

The STREIF - https://vimeo.com/142324279

Biggest Prime - http://www.engadget.com/2016/01/20/the-worlds-largest-prime-number-has-22-million-digits/

MAWS v1

Here at the office we have a never ending, pretty much daily Nerf Gun War. The battles typically start without much warning when someone fires a shot in your general direction. We have a vast assortment of Nerf Gun and ammo to choose from.

To liven things up a bit some co-workers also bought a thunder pack of smaller Knockoff Nerf Footballs from China. Throughout the day a football will enter your airspace without warning. Thus the need for MAWS

Missile Advanced Warning System

I wanted a way to know when a missile or other large object was incoming to give me time to duck or at least brace for impact. I also wanted a reason to order some PIR sensors for the Arduino. I was the lucky recipient of an Amazon gift card for Christmas so I quickly ordered a pair of sensors.

Hooking the sensors up to the Arduino was a cinch. Basically I just wanted to light up an LED when the sensor noticed movement. The sample code worked perfectly. All I had to do was add another set of variables to control the second sensor. MAWS is outfitted with two PIR sensors for double, that's right double the motion sensing power.

Testing

Inital testing indicated that there was little chance MAWS v1 would pick up the Nerf Elite Strike darts. These darts are small and travel fast (especially from the modified Nerf Guns in the office) to be detected by the 2 dollar PIR sensors I purchased.

The NERF Mega darts are also too small to be detected. The MAWS system did pick up a few shots from the Mega darts but the success rate was very low.

Larger projectiles were detected...sometimes.These include Nerf footballs and other various soft projectile type objects.

Construction

No fewer than 5 minutes were spent in MAWS construction. This is all prototype so nothing fancy was needed. I grabbed a couple cardboard boxes and a box cutter. Since this was a prototype I decided to just tape together wires with electrical tape to extend the reach of the LEDs.

I taped the LEDs to the top corner of my monitor and ran the MAWS system up to the top of my cube. I moved it back a little so the MAWS system would not pick up my movements for false positives.

Performance

The MAWS system is performing at least as well as in the initial testing. I'd say the success rate is about 2.5% just spit-balling. I'd call that a successful project (considering my past projects). I mean imagine if 1000 projectiles are being thrown my way. That is 25 projectiles that are successfully identified before impact...not bad.



It has been a good conversation piece if nothing else. Also it functions well as an advanced warning system if a visitor walks up behind me. So all and all not a bad project.


I imagine if I swing for some more pricey sensors I would have a bit more success.

Desk Cycle Bike Race

A buddy of mine at work purchased a Fit Desk Under desk Elliptical trainer. You may recall another buddy of mine (Chad Bisher) has one and uses it to illegally win FitiBit Workweek Hustle challenges. Anyway, intrigued I wanted to try it out when it arrived.

I immediately determined this is something I'd like to purchase. As luck would have it Chad Bisher's wife, the doc, had purchased a Desk Cycle but rarely used it and was willing to sell it to me.

I quickly installed it and got it adjusted to my work area and started pedaling. Of course in true thejoestory fashion I was not satisfied with merely riding the bike and receiving the health benefits. I quickly challenged my co-worker to a competition. A virtual bike race to various destinations in the world. We will see who can pedal the farthest, the fastest, etc.

Website

Thus the Bikerace website was born.

Our first destination was Chicago from our HQ here in Kansas City. I am not sure why or how I picked Chicago. Anyway we started tracking metrics: Miles, Time, Calories burned. Progress to the goal was displayed on the site.

I decided to use the bootstrap framework for this site. I have been using foundation a lot and wanted to change things up a bit. Bootstrap has a lot of features to offer. We will see how it goes.

One thing we noticed right away was the difference in speed calculations between the two bikes. The FitDesk seems to measure speed differently. My co-workers speed was registering 5 MPH faster than the Desk Cycle.

We both tried the other person's cycle and the results were similar. Therefore I will add a modifier to my buddy's distance. As you can see in the screenshot...I have ridden over 2 hours more than him and I am only 9 miles ahead of him to Chicago.

Anyway I will tweak the numbers and see how it goes.

Future Plans

I would like to pinpoint where we each rider is located along the route based on miles. I have not figured out how to do that on Google Maps yet. I need to figure out how to do Lat/Long math along a path given a specific bearing. Should be an interesting challenge.

Other than that we just have to come up with where we want to ride next. By the time the Tour de France rolls around we may mimic some of the legs by cranking up the resistance on the bike for the mountain climbs and such. Yes I know it is nothing like the real tour...just something fun to try.

Link Dump 1/11/2016

• The driverless taxis are coming - http://www.theverge.com/2016/1/4/10706250/gm-lyft-driverless-cars-ride-sharing-investment
• Interesting Tech Predictions - http://www.wired.com/2016/01/espn-will-finally-kill-the-cable-bundle-in-2016/
• Top Fuel Pencil Sharpener - https://www.youtube.com/watch?v=XLQUy6g40SQ
• Started working on a FoodBot - http://www.theverge.com/2016/1/6/10718282/internet-bots-messaging-slack-facebook-m
• https://medium.com/why-not/lunch-hacks-fde6099b3ad9#.ak90fo4v4

Link Dump 1/4/2016

Stumbled on this guideline last week. - http://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/ 

Cool Raspberry Pi project - http://blog.dylanjpierce.com/raspberrypi/magicmirror/tutorial/2015/12/27/build-a-magic-mirror.html

Database of all the Police Killings - http://www.theguardian.com/us-news/ng-interactive/2015/jun/01/the-counted-police-killings-us-database

Punter engages fetal position - http://deadspin.com/rams-punter-slams-cliff-avril-down-then-curls-up-into-1749872691

Cool data viz on typical day in the life of folks. Would like to explore this dataset further. http://flowingdata.com/2015/12/15/a-day-in-the-life-of-americans/

Twitter Sentiment with Python Part 2

Earlier I blogged about grabbing some tweets from Twitter and running them through a text sentiment analysis module using Python. I recently revisited the project and added a new feature.

Here is the full script. Below I will add some notes

Some Notes

So I setup the script to run every 15 minutes against the keyword Kansas City to track the sentiment of this fine Midwestern town in the Great USA. I turned the script lose and sorta forgot about it for  month or so.

Kansas City is not the most polarizing of search terms on Twitter. Out of the 5000+ executions of the script the sentiment went negative a total of 88 times (1.76%).  The top 10 negative sentiment scores were recorded at the times in the table below.

Negative	capturedate
0.999995749	12/6/15 15:38
0.999951771	12/20/15 13:52
0.999894862	12/16/15 15:53
0.999850365	11/29/15 14:52
0.999812706	12/25/15 8:52
0.999426109	12/20/15 12:22
0.999421925	12/17/15 22:22
0.998945902	12/6/15 14:23
0.993400587	11/18/15 16:52
0.992757985	12/13/15 15:53

• Odd that I did not see any negative sentiment hits on Thanksgiving.  Black Friday did not even have a hit.
• 12/25/2015 @ 8:52AM apparently some folks did not like their Christmas gifts in Kansas City.
• 12/20/2015 from 12:07 - 21:07. This time span had the most negative sentiment hits of the entire data set. A quick search on twitter for that day did not yield any clues. I also searched the news outlets and did not see anything obvious.

After reviewing the collected data I noticed a problem with my process. I am parsing the tweets to score the sentiment, however I cannot go back to review the tweets to see what caused the sentiment. 

Therefore I launched version 2 of the script (the script listed above) and added a new table to my database. I am now capturing the actual tweets as well as storing the sentiment score. I changed the capture time to 30 minutes.

Website

During the initial script build I also wanted a way to visualize the current sentiment in a sort of heat map type color system based on the negative score. I remember searching a while for this code but now I cannot remember where I found it. Anyway basically it will set the background color of this web page based on the value of the negative score. The negative score is determined using the sentiment analyzer.

If the negative score is close to 0 then the page will be green. If the negative score is close to 1 then the page will be red. If the negative score is .5 the page will be blue. Everything in between will be a various shade of these 3 main colors. Here is a sample of a high negative sentiment, the numbers are off here because I hijacked the code to negative sentiment of 1 to show the red color.:

All of this is driven by the code below.

So there you have it. I have cranked up the script again to collect data every 30 minutes. I predict in about a month or so I will remember that the script is running and will blog the findings. One of the next steps I want to do is incorporate the values in the D3js calendar chart. However, I may have to pick a more polarizing topic otherwise the whole year will be green.

Fusion Table API Insert Data Python

Me and a buddy have been hacking at this Google Fusion API for a couple days now trying to figure it out. We finally had a break through. He sent me his sample code with the authentication piece and the SELECT statement and I started trying an INSERT. After about 10 rounds of fail finally got something to work and wanted to post it. We struggled to find a simple sample on how to do this, I am sure there is a better way, but at least its working.

Python Modules Needed

You will need to install some Python modules if you have not authenticated to the Google APIs before. Here is a list of modules I installed. 

• requests
• urllib3
• apiclient
• gspread
• PyOpenSSL
• oauth2client
• google-api-python-client (used easy_install)

Not all of these are needed for the script. I am working from a new laptop so I had install them fresh. All of these were installed using pip except the Google API Python client. For some reason pip did not install that so we had to use easy_install.

Authentication

We used the same method detailed in this earlier post using the gspread module. You will want to create the JSON file with your authentication key in it so you can authenticate to the Fusion Table API.

Make sure you grant your client email address access to edit the Fusion table. You can do this using the Fusion Table Share feature and then add the email from the JSON file you downloaded when  you built your key   XXXXXXXXXXXXXXX@developer.gserviceaccount.com

Script

I am using the USGS Earthquake JSON feed I blogged about earlier today to import Earthquake data in to a Fusion Table. Here is the script.

Basically we just setup a loop to parse the Earthquake JSON data and on each loop we execute an INSERT statement in Fusion. Again there is probably a better way than the line by line method detailed here. But again at least we can import data into Fusion. This is not going to be practical for thousands of rows but is fine for our purpose here.

Link to the Fusion Table Map:

https://www.google.com/fusiontables/embedviz?q=select+col4+from+1aXGZQoukkHHSAKSHuCakYuVbh2qSaBxN5C-APpOE&viz=MAP&h=false&lat=17.868051231860818&lng=-24.377267187500024&t=1&z=2&l=col4&y=2&tmplt=2&hml=TWO_COL_LAT_LNG