AUTOTRON 1700 - Teensy Shortcut Buttons

Wanted to cook up a shortcut button thing for common work tasks. Had some 30mm arcade buttons laying around. Man I searched and searched for a 3d printable box to house 30mm buttons in. I could not find anything, most boxes use the 24mm arcade buttons. Me updating a CAD drawing is a no go so I was able to find the top plate with 30mm holes and used it. Had a cardboard box laying around and threw some stickers on that bad boi, there ya go the AUTOTRON1700

So I used a Teensy cause it shows up as a Keyboard. I was currently using Auto Hot Key (https://www.autohotkey.com/) for keyboard macros. This allows me to use some key strokes to launch something. For example CRTL+SHIFT+1 will copy a SQL Script I use often to the clipboard so I can paste it and run it. CRTL+SHIFT+2 will launch our team wiki web site. Stuff I use often at work. 

You can use this to launch anything really. Anyway so I used Auto Hot Key to setup the macros I wanted and then programmed the Teensy to send the Keystrokes needed to launch the macro.

Works pretty well. As mentioned I wasn't able to find a printable box but I think the sweet cardboard gives it a better feel anyway. It's a little to large but plenty of room for wiring and stuff.

ESP8266 Magnetic Switch Deep Freezer Monitor

Setup an ESP8266 as a Deep Freezer Monitor using a magnetic switch. The ESP8266 will start sending me emails and text messages if the Deep Freezer has been open for more than 1 minute. I also wanted to log when the Deep Freezer was open. So a web service is called to log the data to a database.

If the Deep Freezer has been opened for over a minute I get a text and an email. Then the counter resets. If if is still open after another minute I get another text and email. In version 2 I am planning to add a temperature probe I can place in the deep freeze. Then I will take some random temp samples to monitor the temperature.

PowerShell Delete files by Modified Date

 Simple script to delete files based on modified date. 

Microsoft Teams PowerShell Bot Example

Microsoft Teams has a lot of cool features. This article will show you how to write a simple PowerShell bot that will post messages to a Teams channel.

Configure the WebHook in MS Teams

1. Open the MS Teams Channel you want to add the bot to
2. Click Connectors
   
   
   
   
   
3. Click the Configure button by the Incoming WebHook option
   
   
   
   
   
4. Give your WebHook a name...such and such Bot and you can upload an image if you want. Scroll down and click Create
5. On the create screen you will see a URL. Copy this URL and save it somewhere you will need it in your PowerShell Script.
   
   

PowerShell Script

Sample PowerShell Script Below.

The script will run a simple SELECT statement on a DB and will Send a WebRequest to the URL you setup in Teams. The Bot will post a message to the channel you configured for the Incoming WebHook above.

If you do not want to mess with the Card Format then you can just post a simple message like this.

You can update the SQL Statement to pull data and display it in whatever channel. 

AC Notification with DHT22 and ESP8266

The building installed new AC units in the building. On the new units you can actually crank up the fan and turn the unit off completely. Last summer the building would routinely heat up to over 80 degrees. We are on the west side of the building so in the afternoon it gets stuffy.

So we can control fan speed and power, but we cannot control the temperature. I wanted a solution where I could quickly determine if the AC was blowing hot or cold air. First attempt is documented here. I took a DHT22 and hooked it up to an ESP82666.

The ESP8266 takes a temperature reading every few seconds and displays the temp and humidity on a little TM1637 Display.

Here is the code

Here is a pic

Alpaca Paper Trading Bot Test

Alpaca is a cool little site that allows you to test various stock trading strategies using their free fake money account (paper account) with a powerful free API. 

Head on over to the site and sign up for a free account. You will see a link on the left side menu that takes you to the paper trading account

On the right side of the paper account you should see a link to View your API keys. You will need these keys to plug into your Python Script. They have tons of documentation on the API but I will list out a few samples below. You will see a Buy, Sell, Lookup Order, and a very basic Trade Bot.

Buy Sample

Sell Sample

Lookup order Sample

Basic Trade Bot

This is a very very very basic Trading bot for a paper money account (fake money). Please don't use this and expect big GAINS. The bot will buy shares and then hold them until a specified profit threshold or loss threshold has been hit. You can tweak the thresholds to see how the strategy works. At the bottom of the script you'll see the startBot('WDC') command. This is where you insert the stock you want to work with.

Of course this can be improved upon, this is just a sample to get the bot up and rolling quickly. The bot can be configured to work with multiple stocks, etc.

So there you have it. Run wild, make a fancy new trading strategy and reap all the GAINS.

SQL Server Read/Write Percentage and Index Usage Script

Air Quality Calculation Purple Air API and Python

Stumbled on an article about the Purple Air Air Quality API. This thing has a wealth of knowledge. Had no clue how Air Quality numbers were calculated. The API provides several different metrics for those folks that purchase the Purple Air monitoring sensors. The sensor feed data into the Purple Air network of sensors and display the data on a nice handy map: https://www.purpleair.com/map?mylocation

You can easily hit the Purple Air api when you find a sensor in your location. Just click on the sensor you want and inspect the URL. Here is an example of the sensor near Vinita Oklahoma: https://www.purpleair.com/map?opt=1/mHUMIDEX_F/a10/cC0&select=35895#12.99/36.63253/-95.14012

That Select = in the Address is what you want to key on. Now that you have the sensor ID you can just plug that in to the URL to get the JSON feed for that sensor: http://www.purpleair.com/json?show=11668

You will get back some neat looking JSON. Now all you need to do is parse it. See below for sample code. This is using Python to parse out the JSON return value and then doing some fancy math to calculate air quality index based on the value returned for PM2.5 Particulate < 2.5 microns pollutant. You can read up more about the Air Quality Index here: https://www.airnow.gov/aqi/aqi-basics/#:~:text=Think%20of%20the%20AQI%20as,300%20represents%20hazardous%20air%20quality.

You can then capture this data in a DB or display it however you want.

Copy Folder Structure Only Robocopy

Quick and easy way to copy the folder structure and not the files.



robocopy source destination /e /xf *.*

Run MSC using RunAs

At times you need to run MSC as a different user. Specifically for active directory users and computers when managing AD objects. I have to run this app as an elevated admin to make changes. Finally made a shortcut and documenting it here for the future.

C:\Windows\System32\runas.exe /user:DOMAIN\user /savecred "cmd /c start /b mmc %SystemRoot%\system32\dsa.msc"

ESP8266 DHT Temperature Sensor

My cube mate seems to think that hanging NASA Space Blankets on the glass cube block windows drastically changes the temperature in our cube. It does nothing to class the place up but does seem to offer some heat savings on hot days. The blocks get up over 100 degrees on sunny days so I am sure it helps some.

Anyway, I have a thermometer at my desk and thought it would be fun to create a real time monitor for the temperature near the space blanket to compare. I threw together a ESP8266, DHT, and a small LCD that I recently ordered and wanted to test.

I plugged the LCD up as follows:
GND - GND
VCC - VIN pin
SDA - D2
SCL - D1

I plugged the DHT data connection into D2 and ground and power.

Here is the code.


I am using the LCD library from here: https://github.com/johnrickman/LiquidCrystal_I2C. Just download the zip and then in the Arduino IDE add the zip file as a library. You will also see that I have the code needed to connect to wifi and then post the data to a web service. I was using this to get some temperature trends in the office, and may turn that back on later so I just left it in there.

I then found a bunch of local weather buzz words and made a little border using an index card. The temperature is gathered every 30 seconds. I would get a NAN reading from the sensor often so I changed it so if NAN was the read it would just display the last good value.

MQTT Python Save to SQL Server Database using OwnTracks Data

Started playing around with MQTT this week. Figured out how to save MQTT messages directly to a SQL Server database so I thought I would share how here.

Owntracks

Owntracks is an application that allows you to post your location from your phone to an MQTT broker. You can read about it here: https://owntracks.org/ and download the app. I am using Android.

Mosquitto

Mosquitto is an open source MQTT broker that has clients for Windows and Linux. I am using a Windows broker here in this example but it was easy to install this on a Raspberry Pi with the Linux version. https://mosquitto.org

The Windows install was easy, just next next finish through the setup and then make sure the Windows Service Mosquitto Broker is running

Python 

You will need to install the paho python module using pip. pip -install paho-mqtt should do the trick. Once you get in installed you can create a simple script like so. This is using pyodbc to insert data into SQL Server

Some comments. 

• This creates a subscriber to whatever mqtt message you want to post. You configure the publisher on your smartphone application.
• Notice I have a your server ip in the script. You will need to figure out what your external facing ip address is (just google what is my ip). Then you will need add a port forward to your router config for port 1883 or whatever port you are going to use.
• Create a simple table in SQL Server called mqtt and create two columns
  
• I am using the replace function here because the message payload was almost in JSON format but had some extract characters. The payload started with a b' and ended with a ' so I just remove that out.

Configure OwnTracks
As I mentioned I am using the Android App. Here are some screen shots on how I configured it.

SQL Server

Once data starts flowing into the database you can start querying it or doing whatever you want with it. Earlier I mentioned that I removed some characters off the payload so the resulting data would be in JSON format. Since we did that work here we can use the JSON_VALUE function to extract data out of the payload column.

This SELECT will give you the most recent payload captured and will return it back in some nice useable columns.

Google Maps

Then from there if you want to get real creepy you can select the lat/lon values from your db and display them on a Google Map using the JavaScript Maps API (requires an API key).

I zoomed in so you couldn't really see where I am sitting but that is my most recent position with other information displayed. Internet Type indicates I am on wifi. Pretty cool.

Chiefs Tomahawk Chop with Raspberry Pi

Wanted to cook up a quick and nerdy way to support the Chiefs this year. So I came up with a Raspberry Pi Tomahawk Chop Button using a servo and an arcade button I had laying around.

Code here.



Had to figure out how to slow the servo down a bit. You can see I went the low tech way. Essentially I am rolling through 3 chops to play with the music. I call the music first using another python script so I can return back to the code to run the chop will the song plays. Here is the song script.

See video below for the chop in action. Let's go CHIEFS!!!!!!

Python NEOPIXEL Google Traffic and Weather LED Strip

Started playing around with the Neopixel library and python on the Raspberry Pi. I usually take a run over lunch break so I wanted a way to get a quick view of the current weather conditions. I have a 60 LED WS2812 strip so I divided the strip up into 15 LED sections.

• Section 1 - Temperature. Depending on the temperature I wanted to color the 15 LEDs a specific color.
• temp > 44 and < 80 = Green
• temp > 79 and < 90 = Yellow
• temp > 90 = Red
• temp > 34 and < 45 = Yellow
• temp < 34 = Red
• Section 2 - Wind. 
• wind < 5 = green
• wind = 5 and < 16 = Yellow
• wind > 15 = Red
• Section 3 - Humidity
• Humidity < 61 = Green
• humidity > 61 and < 80 = Yellow
• humidity > 80 = Red
• Section 4 - Condition
• If it is not raining - Green
• If it is raining - Red

I am using the Open Weather Map API. You can sign up for a free account here: https://openweathermap.org/. I setup an infinite while loop that will sleep for 10 minutes between each iteration. I also set it up to only turn the LEDs on between 10AM and 2PM. The API will only be called during these times. I typically take a run around lunch time so no need to keep it on after this. 

Next steps is to add a Traffic API call. I will light the led up based upon my commute time home.

PowerShell Folder Sizes

More of just a place holder for me because I am tired of searching for this everytime I need it. The following powershell will list out all the subfolders for a given root folder with Sizes and last date modified.


The script will dump the data to CSV so you can do whatever you need from there.

Christmas Tree Timer and Switch

Didn't do the Christmas tree to music this year. I did however setup a simple timer and override switch so the tree would turn off and on automatically.

I setup a simple relay attached it to the Raspberry Pi and wrote the Python Script below. I wired up a light switch (hadn't done this before so that was fun) to the Pi with some wire nuts. The Light switch will override the timer. So if you want the tree to stay on longer just flip the switch to on.

Here are some pictures.

Look at the sweet duct tape job

The light switch was connected to GPIO pin 20 and ground. I had to use the internal pull_up_down resistor to make it work. Found a nice little function that takes in the current time, start time and end time. The function will determine if the current time is between the time range you set. Worked really well for this project.

Christmas tree would light up at 6:00AM and then turn off at 10:00AM. Then it would light up at 4:00PM and turn off at 10:00PM. It would check every 5 seconds to see if it needed to turn off or on. Then if you flip the switch it would not check for time of day and would just leave the relay in the on position.

Smart Garden v1.0 Raspberry Pi

Some fellas at the office had a crazy idea to try and grow a garden in their cube at work. After several people tried to talk them out of it, they plowed ahead and started one anyway. So why not hook up some sensors and a web cam and call it a Smart Garden?

Sensors and such

Kuman Moisture Sensor Kit 5Pc: From the reviews these have a tendency to not last very long. They are cheap and this experiment may not last long so I went for them. If they start blowing up we may invest in a nice Sensor.

DHT22: Yes I know people say these are not accurate. Calm down folks we are not reporting data for the NOAA, we are just checking the temperature and the humidity in the office. We can live with the inaccuracy.

Cheap Webcam: Whatever you have laying around. As you can see the one I found is cheap and potato quality.

Raspberry PI: if you have one laying around you are a nerd.

Readings and Data

We are taking moisture readings from two sensors, temperature and humidity readings, and a picture on the web camera every 5 minutes. I wrote two web services that are used to store the data. A python script was created to read the sensors and take a picture, the script will then call the web services to send the data over to a database, and a FTP script is called to copy the image to the web server.

I had to use FTP because I didn't take the time to figure out how to post the actual image to a web service. I may work on that eventually.

Web Site

After the data was collected a website was built to allow folks to keep tabs on stuff. I downloaded some TimeLapse software and threw together some time lapses which have turned out really cool given the cheapness of the web cam.

The Chart.js library was used to make some charts to display the recent readings. Bootstrap was used to make it mobile friendly. The Charts look squished on mobile so that may be on the do list for the future.

http://www.thejoestory.com/SmartGarden

Raspberry PI Relay Control using Python and Flask

I wanted to create a party button accessible from a web site using Python and Flask. Buckle up folks.

So somehow I got flask up and running. There should be a ton of articles on how to install it. I seem to remember it being pretty easy. Just google it already.

I created a new directory called party. Inside this directly I created a static and a template directory. I then created a file called app.py

Here is the app.py script

You can park all your images and such in the static folder and reference them using flask templates. In the template folder I created the look and feel I wanted for the web pages served up by flask.

You can see I call the template in the app.py with the return render_template command. So when you start a party you are quickly returned to the index.html page. Here is the index.html page



I wanted a cool DJ Air horn so I grabbed it and I am using VLC command line to run that. You will notice that the Flask is setup to run a couple other Python Scripts. This is my party start and party stop functions. These python scripts control the relay.

partyStart.py

partyStop.py




Pretty straight forward on the Relay control. You'll notice on the partyStart.py I wanted to call a little DJ airhorn to really kick the party off right. I am using the VLC command line interface to do that.

Here is what end result looks like

This can be expanded to do all sorts of fun stuff. Using this basic Flask/Python example you setup several different web based controls using Raspberry Pi.

ESP8266 Post to Web Service

Purchased a DHT22 recently for a Work Desk Temperature project. Wrote a web service and created some SQL stuff to capture the temperature and humidity at my desk. Here we go.

Web Service

Just a real simple web service to call the stored procedure I wrote:

See my other blog post for more details on creating a web service: http://tech.thejoestory.com/2017/01/simple-sql-server-aspnet-web-service.html

SQL stuff

Created a Table and a couple stored procedures to support this. Here is the table.
Here are the stored procs.


ESP8266 Post

From there I just needed to figure out the ESP8266 Post. I had to search around a bit but finally landed on the script below. The script will establish a WiFi connection. Then it will loop every one minute taking a temperature/humidity reading from the DHT22. Finally it will call the web service I built to store the data in SQL server.

You will need to grab the DHT and AdaFruit Libraries using your Arduino IDE. From there I created a web site using Chart.js and a couple other stored procedures I wrote to get MAX/MIN/AVG Temp and Humidity.

Find Text String in Text Files Powershell

Quick script to search a set of text files for a specific text string.



Neat