Tag: home automation

Farm Automation

Scott set up one of the ESP32’s that we use as environmental sensors to monitor the incubator. There’s an audible alarm if it gets too warm or cold, but that’s only useful if you’re pretty close to the unit. We had gotten a cheap rectangular incubator from Amazon — it’s got some quirks. The display says C, but if we’ve got eggs in a box that’s 100C? They’d be cooked. The number is F, but the letter “C” follows it … there’s supposed to be a calibration menu option, but there isn’t. Worse, though — the temperature sensor is off by a few degrees. If calibration was an option, that wouldn’t be a big deal … but the only way we’re able to get the device over 97F is by taping the temperature probe up to the top of the unit.

So we’ve got an DHT11 sensor inside of the incubator and the ESP32 sends temperature and humidity readings over MQTT to OpenHAB. There are text and audio alerts if the temperature or humidity aren’t in the “good” window. This means you can be out in the yard or away from home and still know if there’s a problem (since data is stored in a database table, we can even chart out the temperature or humidity across the entire incubation period).

We also bought a larger incubator for the chicken eggs — and there’s a new ESP32 and sensor in the larger incubator.

Home Automation and Gardening

Something like 20 years ago, I tried to grow a plumeria flower in my apartment. I had a broad-spectrum light, plenty of heat, and plenty of humidity. But getting the light turned on and off at the right times wasn’t easy (especially if I was at work all day!).

This seems like a really good use for home automation — our home automation system tracks the sunrise and sunset times for our zip code. It’s possible to essentially cron “stuff” off of these times — e.g. get the birds ten minutes before sunset. I could easily track sunrise and sunset in Honolulu then have my light turn on at sunrise (or first light) and off at sunset (or last light). Voila — “sunlight” that runs for the proper duration every day.

ESP8826 (12e) Multisensor

We’d set up a prototype multi-sensor with an environment sensing kit that Scott picked up at MicroCenter a few years ago. There’s a little LCD display … but we wanted to report readings back to our OpenHAB server. Which required a network connection. Checking out prices for network cards to add to the Uno … well, it wasn’t a cheap add-on. But we found these ESP8266 modules that support 802.11b/g/n and provide the memory/processing for small programs. At about 3$ delivered, that was exactly what we needed.

I ordered a bunch of components to make multi-sensors – pressure sensors, luminescence sensors, temperature/humidity sensors. The sensors connect into a CP2102 ESP8266. The device is powered by a couple of 18650’s in a little box — another buck. There’s some miscellaneous wiring and a little breadboard, too. The total cost for the multi-sensor is about 8.50$. We could add a vibration sensor for another 0.50$, a PIR sensor for 2$, and a UV sensor for 2.50$. That’s 13.50$ for 7 different sensors — and we don’t need seven sensors everywhere.

I kind of want to make a weather station too — add a water level sensor, a precipitation detector, and a wind speed sensor. Those are surprisingly expensive! I want to check out the process to build your own anemometer. But I’d probably buy a nice Davis Anemometer 🙂

Connecting to a WiFi network with the ESP8266 is really easy:

  • Add a library to the Arduino IDE —
    • In the Arduino IDE preferences, select File>Preferences menu.
    • In the “Additional Boards Manager URLs” field, add ‘https://arduino.esp8266.com/stable/package_esp8266com_index.json’
    • Select the Tools > Board menu and open the Boards Manager. Search for “esp8266” and install the platform.
    • From the Tools > Board menu, select the appropriate board. I ordered the CP2102 ESP8266 module, and we’re using “NodeMCU 1.0 (ESP-12E Module)” as the board.
  • Configure the WiFi network connection details in your code
  • Compile
  • Upload
  • You’ve on the network!

We’ve used an MQTT library and send sensor readings to our MQTT server.

 

openHAB – Motion Detection With Zoneminder Via SQL Triggers

We had used ZoneMinder filters to run a script which turned a “motion detected” switch on and off in openHAB. We had turned that off in favor of an openHAB/ZoneMinder binding; but the binding polled ZoneMinder for motion events, and this added significant load to our system. We tried re-enabling the filters we’d used previously, but they didn’t work. There are a lot of caveats around using filters (tl;dr: filtering can be delayed by several minutes, which renders ‘now’ filters ineffective) and more recent versions of ZoneMinder don’t have a number of alarm frames until after the event (which means filtering on alarm frames > 1 only detects motion after the fact). All of this means that the filters which worked pretty well a year or two ago no longer work reliably. Architecturally, the ZoneMinder filter process seemed ill suited for our needs. Actions that are not time sensitive, like file cleanup or roll-up reporting, could be done through a filter. But it’s not a good solution for identifying the FexEx guy in the driveway.

ZoneMinder uses a database to maintain system and alert data — I use MariaDB 10.3.18-1. MySQL introduced TRIGGER back in version 5. A trigger is essentially a bit of SQL automatically executed by the database when operations occur within a table — table activity triggers execution. When ZoneMinder first detects motion, an event is recorded in the database. When motion is no longer detected, the motion event is updated with event info (number of frames, event duration). Since both inserting a motion event and updating the event when motion ends are events within tables, a trigger can execute some SQL code almost immediately without much impact to system load.

The only problem is that SQL code does not, normally, POST data to a URI. Creating a trigger which can execute external binaries requires creating a UDF (user-defined function). I am using lib_mysqludf_sys which creates sys_get, sys_set, sys_exec, and sys_eval functions. The sys_get and sys_set functions are used for setting/getting environment variables. The sys_exec function returns the return code from execution, whereas sys_eval returns the output from execution.

Adding SYS UDF’s To MariaDB:

After cloning the lib_mysqludf_sys repo locally, edit Makefile to set LIBDIR to the appropriate directory for the MariaDB installation (/usr/lib64/mariadb/plugin/ in my case). I also needed to modify the compilation line to:

gcc -fPIC -Wall -I/usr/include/mysql/server -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

** 01 August 2020 update — I had to include an additional folder to build the latest version of this program on Fedora 31.

Run install.sh to install and register the user-defined functions in the MariaDB server. Because the output of command execution is unnecessary, the sys_exec is sufficient. Before registering a trigger, use the CLI SQL to verify sys_exec is working:

MariaDB [zm]> SELECT sys_exec('cat /etc/fedora-release');
+-------------------------------------+
| sys_exec('cat /etc/fedora-release') |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.012 sec)

Creating the SQL Trigger:

To create a trigger for motion events, there needs to be a mapping between the monitorID used in ZoneMinder. You see the monitorID in the URL when you view a feed — “mid” in the GET query string:

Or use a SQL client to obtain a list of monitors from the ZoneMinder database:

MariaDB [zmdb]> select Id, Name from Monitors;
+----+-----------------------------------+
| Id | Name                              |
+----+-----------------------------------+
| 15 | IPCam01 - Area 123                |
| 16 | IPCam02 - Area 234                |
| 17 | IPCam03 - Area 345                |
| 18 | IPCam04 - Area 456                |
| 19 | IPCam05 - Area 567                |
+----+-----------------------------------+

Once you can correlate monitor ID values to OpenHAB items, update the IF/THEN section of the trigger. Update the strOpenHABHost variable to your server URL. There are two useful SQL commands commented out (– ) below. SHOW TRIGGERS does exactly that – it lists triggers that are registered in the database. DROP TRIGGER is used to remove the trigger. If you are using HTTPS to communicate with OpenHAB, you may need to add “–insecure” to the curl command to ignore certificate errors (or use –cacert to to establish a trust chain).

The sys_exec function in this trigger uses curl to post an item stage change to the OpenHAB REST API. Camera items are on when motion is detected.

To create the TriggerMotionOnNewEvent trigger, paste the following into your SQL client:

-- SHOW TRIGGERS
-- DROP TRIGGER zm.TriggerMotionOnNewEvent;
DELIMITER @@

CREATE TRIGGER TriggerMotionOnNewEvent
AFTER INSERT ON `Events`
FOR EACH ROW
BEGIN

DECLARE strCommand CHAR(255);
DECLARE strCameraName CHAR(64);
DECLARE iCameraID INT(10);
DECLARE iResult INT(10);
-- variables for local openHAB REST API hostname and port
DECLARE strOpenHABHost CHAR(64);
SET strOpenHABHost='http://openhabhost.example.com:8080';


-- Translate ZoneMinder IP camera ID with openHAB item name
SET iCameraID = NEW.monitorID;
IF(iCameraID = 10) THEN
SET strCameraName='IPCam05_Alarm';
ELSEIF(iCameraID = 11) THEN
SET strCameraName='IPCam03_Alarm';
ELSEIF(iCameraID = 12) THEN
SET strCameraName='IPCam04_Alarm';
ELSEIF(iCameraID = 13) THEN
SET strCameraName='IPCam01_Alarm';
ELSEIF(iCameraID = 14) THEN
SET strCameraName='IPCam02_Alarm';
END IF;

SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "ON" "',strOpenHABHost,'/rest/items/',strCameraName,'/state"');
SET iResult = sys_exec(strCommand);
END;
@@
DELIMITER ;

There is a second trigger to clear the motion event — set the camera item to off when there is no longer motion detected. ZoneMinder updates event records to record and EndTime for the event. This trigger executes any time an Event item is updated, but there is an IF statement that verifies that the EndTime is not null to avoid clearing the motion event too soon.

To create the ClearMotionOnEventEnd trigger, paste the following into your SQL client (at some point, the Events table EndTime column was renamed to match the DateTime column format — so it is now called EndDateTime … I’ve updated the trigger with the new column name; but, if your motion events do not clear, try using “describe Events” to see what the column name for the event end time is):

-- SHOW TRIGGERS
-- DROP TRIGGER zm.ClearMotionOnEventEnd;
DELIMITER @@

CREATE TRIGGER ClearMotionOnEventEnd
AFTER UPDATE ON `Events`
FOR EACH ROW
BEGIN

DECLARE strCommand CHAR(255);
DECLARE iResult int(10);
DECLARE strCameraName CHAR(25);
DECLARE iCameraID int(5);
-- variables for local openHAB REST API hostname and port
DECLARE strOpenHABHost CHAR(64);
SET strOpenHABHost='http://openhabhost.example.com:8080';

-- Translate ZoneMinder IP camera ID with openHAB item name
SET iCameraID = NEW.monitorID;
IF iCameraID = 10 THEN
SET strCameraName='IPCam05_Alarm';
ELSEIF iCameraID = 11 THEN
SET strCameraName='IPCam03_Alarm';
ELSEIF iCameraID = 12 THEN
SET strCameraName='IPCam04_Alarm';
ELSEIF iCameraID = 13 THEN
SET strCameraName='IPCam01_Alarm';
ELSEIF iCameraID = 14 THEN
SET strCameraName='IPCam02_Alarm';
END IF;

IF NEW.EndDateTime IS NOT NULL THEN
SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "OFF" "',strOpenHABHost,'/rest/items/',strCameraName,'/state"');
SET iResult = sys_exec(strCommand);
END IF;

END;
@@
DELIMITER ;

Now when new motion detection events are inserted into the Events database table, the openHAB item corresponding to the camera will be turned on. When the event record is updated with an end timestamp, the openHAB item corresponding to the camera will be turned off.

Our implementation executes a second external command. Getting notified of motion when we’re home is great — pull up ZoneMinder, see the FedEx truck. But we don’t publish most of our infrastructure to the Internet — watching the video feed from ZoneMinder means VPN’ing into the network. I put together a quick shell script to pull the 25th image from the motion event (we retain a few seconds prior to motion being detected, and the number of frames recorded per second will vary … so there is trial-and-error involved in identifying an early-in-the-event frame that includes the triggering object). The sleep ensures enough time has elapsed for the motion images to be committed to disk.

#!/bin/bash
# parameter 1 is camera ID
# parameter 2 is camera name
# parameter 3 is event ID
sleep 5
strDate=$(date +%F)
strFile='/mnt/data/zoneminder/events/'$1'/'$strDate'/'$3'/00025-capture.jpg'
echo $strFile

echo "Image for event ID $2 on $strDate is attached to this message" | mailx -r "zoneminder@example.com" -s "$2 Motion Event" -a $strFile Us@example.com

TriggerMotionOnNewEvent includes the following two lines to trigger execution of the shell script when motion is detected.

SET strCommand=CONCAT('/path/to/shell/scripts/sendZoneminderEventImage.sh ',iCameraID,' "',strCameraName,'" ',NEW.Id,"&");
SET iResult=sys_exec(strCommand);

In doing so, we have an e-mail on our phones with a JPG from the motion event — I can quickly see the difference between a cat and a cat-burgler prowling around the patio when we’re away from home.

Security Theater – Alexa Edition

Amazon announced a new privacy feature where you can ask an Alexa device to delete the day’s recordings. Not like “at 23:59:59, delete everything from today” and not “delete everything for the past 24 hours” but delete everything from 00:00:00 to right now when I’m asking you to delete it. Curious how this works in a discovery scenario. How deleted is deleted? And what happens when the next hot-tub murder scenario Alexa records is immediately followed by “hey, delete my recordings for the day”?

I expect this is in response to the poor reception news of human audio reviewers engendered. Can’t say I was shocked to hear they have humans reviewing recordings … I’ve got the same basic thought about Amazon employees/contractors listening to my recordings as I relayed to employees who were concerned that we were reading their e-mail back when I actively maintained the e-mail system. (1) They’re not that bored and (2) I’m not that interesting. I expect there’s an algorithm that flags specific scenarios for review — hopefully every time the thing wakes up and hears “cancel” because that wasn’t the wake word it just heard, probably some percentage of instances where the response is “i don’t understand that”, some other flags, and some small percentage by a pseudo-random selection.

Amazon is probably paying these reviewers a pittance, but they’re still paying them something. And Amazon isn’t paying for someone to be entertained by my daughter singing to the speaker. Are there people posting links to funny and embarrassing recordings? Sure. I also knew people who worked in a call center that contracted out to credit card companies for customer support — people who got busted for extortion because they’d read through six months of account statements after every call. Find something that might be embarrassing/suspicious & call the dude (i.e. poor sap who had rung up for assistance with his account) and demand money not to tell his wife about the affair. Or his gambling. Or what he spends at S&M clubs. Of all of my data that’s out there, smacking into the wall and yelling “bugger” as I check the temp while running out the door just doesn’t rate.
That being said, I’d just as soon not have a company retain audio recordings every time I check the time or weather. But let’s be honest — who is really going to incorporate “oh, delete today’s recordings” into their night-time routine? Once or twice, whatever. Every single day? Not gonna happen. Which is, I expect, the point. Amazon can tout this option to give you control. But they know there’s no way people would opt in to have their recordings retained. And there’s probably a significant number of people who would go through the effort of setting up retention that would automatically purge recordings after 24 hours. But this sounds like a privacy feature but is too much of a pain to use. We’ll check to see if we can purge the daily recordings via an API call, and if not we’ll have a speaker in the house play a MP3 file each night. But that’s not normal user kind of stuff … so Amazon will lose a few days worth of recordings for people who check it out, all recordings for a few uber-techs or super-security-conscious folks. A statistically significant number? Probably not. Security theater.
Worst part, though … you cannot just delete the recordings by voice. Oh, no! You’ve got to enable the function. Because it would be awful if some friend was screwing around with my device and deleted today’s recordings!? I mean, I get not wanting pranksters/kids/pets to order merchandise — which is why you can add an ordering pin for your account . But if there were some API bug which allowed any random Internet user to delete my recordings (not retrieve, not listen to … just delete), I wouldn’t care. The small subset of “every random Internet user” that actually gets within voice range of my house!?! Not exactly somewhere worthy of high security.
Amazon’s self-serving “keeping your recordings extra safe” policy means logging into the Alexa website, going to settings, scrolling down to “Alexa Privacy” (granted a fairly obvious selection), being popped over to another page which you could have hit directly if only you’d known this is where it would send you, going to “Review Voice History” (not a fairly obvious selection) and enabling voice-sourced deletion. This is, conveniently, the same place no one ever went to blow away recordings before voice deletion was an option.

Corporate Expense Reduction: Energy

One of the things we’ve done with our home automation is tracking energy usage – partially because we want to size out a solar installation and the net metering in Ohio is awesome unless you produce more electricity in a rolling 12 month period than you use. So the installation has to be really close to your actual usage. But also because electricity costs money. Similar approaches may be beneficial to corporations. I’m using our 11 cent per kWh rate as an example. Actual rates depend on location and usage.

Does a company want to devote resources to “office automation” like we have home automation? Coupling motion detectors with smart outlets {or even just office schedules – if the last person is off shift at 7PM, dropping some device power at 8 should have no impact} to turn off power might save a lot in standby draw.

Even without home automation, companies can gather usage data to allow resources to be devoted to their biggest energy draws. The first step is identifying the big draws. We use Aeon Labs zwave clamp on home energy meters, but there are stand-alone energy meters. I’ve seen DIY Arduino based ones (https://olimex.wordpress.com/2015/09/29/energy-monitoring-with-arduino-and-current-clamp-sensor), or high end Fluke devices with clamps do the same thing (@5k+ for the Fluke … that’s a bit of an investment, but if someone’s got an energy metering capable fluke for other work ‘stuff’ … they may just need the 10$ clamps). Whatever equipment – clamp it on one circuit in a panel for a few days. Get a number, move it to the next circuit. Eventually you’ve got daily usage numbers for different circuits and just need to look at what is on those circuits to narrow down potential saving points.

We found obvious stuff – HVAC uses a lot of power. If a company leases a building with outdated equipment, use firm numbers in lease negotiation. The HVAC draws x kWh per year which costs us y $. A middle-road new system should draw z kWh which means we’re spending some concrete dollar figure per year because this system is so old. The same information can be used to cost-justify upgrades/replacements for company-owned buildings. Measure usage on lighting circuits. An office with old ballasts and florescent bulbs – what they are costing to run tells you if switching to LED {and there *are* LED T4/8 tubes that don’t require fixture replacements} makes any sense.

But we also found things I would never have even considered if I made a list of all of our non-trivial electrical draws. 20% of our annual electrical usage is the septic aerator (it literally uses more energy than the geothermal HVAC system in a year). We can get it down to 11% of our projected usage by cycling the thing on during even hours and off during odd (or on/off in twelve hour chunks, or 4 on / 4 off / 4 on / 4 off / 4 on / 4 off … new aerators have scheduling and do this themselves). Now that septic aerator savings is only like 250$ a year. Not a huge amount of money, but it’s 250$ I would never have realized we were spending otherwise.

From an IT perspective – if a server supports wake-on-lan … does a backup server and tape library need to be running 24×7? If someone kicks off a restore, can it be powered up (adds a minute, but saves power whenever restores aren’t running) and can it be programmatically powered on maybe half an hour before its backup jobs are scheduled to kick. Then power back down when no jobs report as running or scheduled for x hours. As a company, we mandate that all computers be left powered on so patches can be deployed overnight. What if the nightly patch check-in then powered the computer down (either because there are no patches or after installation in lieu of a reboot)?

Or a printer — there is no need for the printer to be in standby mode for the 15 hours a day no one is around to print. Or the weekends when no one is around. Or company holidays. One of the fairly large Xerox printers we have draws a continuous 11 Watts in sleep mode uses 71 kWh each year between 17:30 and 07:30 M-F and all day Saturday and Sunday. Maybe 72 kWh if you add company holidays. That’s not quite eight dollars a year in savings (and power consumption won’t be 0 if the device can be woken remotely) – but saving 6$ per printer in a company with 2000 printers is 12,000$ each year. Some of the older printers don’t even have a lower power sleep mode and draw 95 Watts in standby mode – 620 Watts per year when no one is around, and just under 70$ in electricity. Even better – HP offers an auto-off / auto-on on activity feature that allows energy to be saved during working hours.

Are there intangible benefits to energy saving initiatives? Get into the automation side of energy savings, would some tech magazine profiling the effort (free publicity, and tech magazines are a good place to advertise a company offering network services)? Can companies form partnerships with geothermal / solar / wind / whatever manufacturers to get cheap installations + publicity? Sadly, in some markets that may not play well (what, you don’t want to burn coal!?!) … but it might not be seen as a negative if it approached as a “save money, do right by stockholders AND customers” message instead of a “green, save the planet, global warming is bad” message.

Zoneminder Setup

I just installed ZoneMinder tonight. I don’t know if I missed a section in the documentation or something’s just missing — there’s doc for getting the build onto your box (or building your own from source). I didn’t want to install the package and all of its dependencies from their repo, so I manually installed the prereqs from the standard Fedora repositories. Installed the zoneminder rpm and switched from the ‘installation guide’ to the ‘user guide’. Which starts out on a web site. Umm … what web site? There’s definitely something missing here. Their package drops a config file in /etc/httpd/conf.d … *but* it presupposes that it is a dedicated server (which, in fairness, is the recommended configuration).

I edited the zoneminder.conf file and threw the whole thing in a VirtualHost tag, added an SSL cert for the hostname I’m using, and restarted Apache. OK, that’s better … I get *something*. Unfortunately ‘something’ is a massive MySQL error.

Some searching (rpm -ql zoneminder, then search through the files the package installed for something that looks good) yielded a config file at /etc/zm/zm.conf. Went in there, defined a database, user, and password. Created said database & user in mysql & assigned rights. Umm, stranger database error that leads me to believe the database hasn’t been initialized. Oops. LMGTFY. Found a SQL file at /usr/share/zoneminder/db/zm_create.sql and imported *that* … wasn’t sure if it was as simple as changing the zm database name in the SQL file to what I wanted or not, so I just used their default database name. Went back into MySQL and assigned the user I’d created earlier rights to the zm database and dropped *my* database. (Note, if you don’t use PHP date ‘stuff’, you may need to define the time zone in your php.ini file … I’d already set a default for other purposes).

Refresh and voila, I’ve got a system. You’ve got to click ‘start’ at the top for it to, well, start pulling in video feeds. I set up a VERY basic link to our WansView camera — ffmpg type, source is the full videostream.cgi URL, and remote method is “RTP/Unicast”. I can now see the video from our camera via the web site. WooHoo!

Now we need to get the motion detection set up & play with all of the options 🙂

Home Automation Lagering

We are about to make mead (we got near 30 pounds of local honey!). In researching mead-making, different yeasts have different alcohol tolerances … so you make a dry mead by using a yeast with an alcohol tolerance at or above the level your starting gravity would yield if it were fully fermented. A sweeter mead means you have a yeast whose tolerance is lower than that value … the greater the difference, the sweeter the mead. We are going to make a dry mead with Lalvin 71b-1122, a just slightly sweet mead by adding a little more honey but still using Lalvin 71b-1122, and a sweeter mead using Lalvin D-47.

71b-1122 has a very broad temperature range (59-86 F – and how cool is it that Google returns a yeast profile summary if you search for “71b-1122 temperature range”). D-47 is more particular — a published range of 59-68 F, but reading through homebrew sites has us wanting to stay around 63 degrees. Our sub-grade level is cool, but not that cool. Especially as fermentation warms up the fluid.

Scott is developing a home automation controlled fermentation “chamber”. The beer refrigerator is now plugged into a smart outlet. One of the Arduino kits we got has a temperature sensor. We can have a temperature probe monitoring the must and cycle the refrigerator’s power to keep it within a degree or two of our target.

Reverse Proxying WebSockets to An MQTT Server

If you are trying to reverse proxy OpenHab – that’s over here. This post is about maintaining your own private MQTT server and making it accessible through a reverse proxy.

We want to be able to update our presence automatically (without publishing our location information to the Internet). Scott found a program called OwnTracks that uses an MQTT server – and there’s an MQTT binding from OpenHab that should be able to read in the updates.

We didn’t want to publish our home automation server to the Internet, but we do want to send updates from the cellular data network when we leave home. To accomplish this, I set up a reverse proxy on our Apache server.

The first step is to get an MQTT server up and working — we Installed a mosquitto package from Fedora’s dnf repository

Once it is installed, create a directory for the persistence file & chown the folder to mosquitto uid

Generate a bunch of certs using the ot-tools (git clone https://github.com/owntracks/tools.git). I edited the generate-CA.sh file in the ot-tools/tools/TLS folder prior to running the script. It will more or less work as-is, but modifying the organisation names makes a cert with your name on it. Not that anyone will notice. Or care 🙂 Modifying the IPLIST and HOSTLIST, on the other hand, will get you a cert that actually matches your hostname — which isn’t a problem for something that doesn’t verify host name information, but saves trouble if you get your hostnames to match up.
IPLIST & HOSTLIST
CA_ORG and CA_DN

Then use generate-CA.sh to generate a CA cert & a server cert. Copy these files into /etc/mosquitto/

Edit the config (/etc/mosquitto/mosquitto.conf) – LMGTFY to find settings you want. Specify a location for the persistence file, password file, and add in the websockets listeners (& ssl certs for the secure one)
persistence_file /var/lib/mosquitto/mosquitto.db

password_file /etc/mosquitto/passwd

listener 9001
protocol websockets

listener 9002
protocol websockets
cafile /etc/mosquitto/ca.crt
certfile /etc/mosquitto/mosquittohost.rushworth.us.crt
keyfile /etc/mosquitto/mosquittohost.rushworth.us.key

Add some users
/usr/bin/mosquitto_passwd /etc/mosquitto/passwd WhateverUID

Start mosquitto
mosquitto -c /etc/mosquitto/mosquitto.conf

Monitor mosquitto for the owntracks ‘stuff’
mosquitto_sub -h mosquittohost.rushworth.us -p 1883 -v -t ‘owntracks/#’ -u WhateverUID -P PWDHereToo

Setting up the reverse proxy
The big sticking point I had was that the Apache WebSockets reverse proxy has a problem (https://bz.apache.org/bugzilla/show_bug.cgi?id=55320) which is marked as closed. Fedora has 2.4.23, so I expected it was sorted. However using tshark to capture the traffic showed that the relayed traffic is still being send as clear.

Downloaded the exact same rev from Apache’s web site and checked the mod_proxy_wstunnel.c file for the changes in the bug report and found they were indeed committed. In spite of the fact I *had* 2.4.23, I decided to build it and see if the mod_proxy_wstunnel.so was different.

Make sure you have all the devel libraries (openssl-devel for me … run the config line and it’ll tell you whatever else you need)

Get apr and apr-util from Apache & store to ./srclib then gunzip & untar them. Rename the version-specific folders to just apr and apr-util

Once you have everything, configure and make
./configure –prefix=/usr/local/apache –with-included-apr –enable-alias=shared –enable-authz_host=shared –enable-authz_user=shared –enable-deflate=shared –enable-negotiation=shared –enable-proxy=shared –enable-ssl=shared –enable-reqtimeout=shared –enable-status=shared –enable-auth_basic=shared –enable-dir=shared –enable-authn_file=shared –enable-autoindex=shared –enable-env=shared –enable-php5=shared –enable-authz_default=shared –enable-cgi=shared –enable-setenvif=shared –enable-authz_groupfile=shared –enable-mime=shared –enable-proxy_http=shared –enable-proxy_wstunnel=shared

Rename your mod_proxy_wstunnel.so to something like mod_proxy_wstunnel.so.bak and the grab mod_proxy_wstunnel.so that just got built.

Grab the CA public key & the server public and private keys that were generated earlier & place them whereever you store your SSL certs on your Apache server

Create a new site config for this reverse proxy – SSL doesn’t do host headers so you need a unique port. Clear text you can use a host header. Don’t forget to add listen’s to your httpd.conf and ssl.conf files!

ProxyRequests Off
<VirtualHost #.#.#.#:##>
ServerName mosquitto.rushworth.us
ServerAlias mosquitto
DocumentRoot “/var/www/vhtml/mosquitto”

SetEnv force-proxy-request-1.0 1
SetEnv proxy-nokeepalive 1
SetEnv proxy-initial-not-pooled
SetEnv proxy-initial-not-pooled 1

ProxyPreserveHost On
ProxyTimeOut    1800

ProxyPass               /       ws://mosquittohost.rushworth.us:9001/
ProxyPassReverse        /       ws://mosquittohost.rushworth.us:9001/
</VirtualHost>

<VirtualHost #.#.#.#:##>
ServerName mosquitto.rushworth.us
ServerAlias mosquitto
DocumentRoot “/var/www/vhtml/mosquitto”

SetEnv force-proxy-request-1.0 1
SetEnv proxy-nokeepalive 1
SetEnv proxy-initial-not-pooled
SetEnv proxy-initial-not-pooled 1

ProxyPreserveHost On
ProxyTimeOut    1800

SSLEngine On
SSLProxyEngine On
SSLProxyCheckPeerCN off
SSLProxyCheckPeerName off
SSLCertificateFile /etc/httpd/conf/ssl/mosquittohost.rushworth.us.crt        # These are the public and private key components
SSLCertificateKeyFile /etc/httpd/conf/ssl/mosquittohost.rushworth.us.key        #     generated from generate-CA.sh earlier.
SSLCertificateChainFile /etc/httpd/conf/ssl/ca.crt                # This is the public key of the CA generated by generate-CA.sh

ProxyPass               /       wss://mosquittohost.rushworth.us:9002/
ProxyPassReverse        /       wss://mosquittohost.rushworth.us:9002/
</VirtualHost>

Reload apache. Create a DNS hostname internally and externally to direct the hostname to your reverse proxy server.

Configure the client — generate a key for yourself & merge it into a p12 file (make sure your ca cert files are still in the directory – if you *moved* them into /etc/mosquitto … copy them back:
sh generate-CA.sh client lisa
openssl pkcs12 -export -in lisa.crt -inkey lisa.key -name “Lisa’s key” -out lisa.p12
You’ll need to supply a password for the p12 file.

Put the ca.crt (*public* key) file and your p12 file somewhere on your phone (or Google Drive).

Client config – Install Owntracks from Play Store
Preferences – Connection
Mode:    Private MQTT
Host:    hostname & port used in your **SSL** config. Select use WebSockets
Identification:    uid & password created above. Device ID is used as part of the MQTT path (i.e. my lisa device is /owntracks/userid/lisa). Tracker ID is within the data itself
Security:    Use TLS, CA certificate is the ca.crt created above. Client cert is the p12 file – you’ll need to enter the same password used to create the file

If it isn’t working, turn off TLS & change the port to your clear text port. This will allow you to isolate an SSL-specific problem or a more general service issue. Once you know everything is working, you can drop the clear text reverse proxy component.

Voila – reverse proxied WebSockets over to Mosquitto for OwnTracks.

Using BC And Command Substitution In OpenHAB’s Exec Binding

My husband has been setting up OpenHAB to control our home automation. Our dimmers are very direct – there’s a z-Wave binding that you set to 100 if you want it at 100%, set it to 18 if you want it at 18%, and so on. We have a handful of Zigbee bulbs, though, which are not so direct. We are controlling these bulbs through a Wink hub by running a curl command with the exec binding.

The OpenHAB exec binding runs a shell with a command string passed in from the -c parameter. Thus far, I have not found anything that runs within a shell not work in the exec binding. This includes command substitution {I personally use the backtick format instead of the $(command) format, but I expect the later to be equally functional}.

What is command substitution (without having to read the Open Group Base Specifications linked above)? If you run

kill `pidof java`

the shell takes the component within the backticks, evaluates it, and then takes the standard output and places that into the command. When “pidof java” returns “938 984 1038”, the command above becomes “kill 938 984 1038”.

We want to set the value to the OpenHab value (0-100) scaled to the Wink value (0-255 for GE Link bulbs) using command substitution with bc (an arbitrary precision calculator language). To evaluate a mathematical expression, echo the expression text and pipe it to bc. To set a bulb to 75% of its maximum brightness, our post data is “nodeId=a&attrId=aprontest -u -m9 -t2 -v`echo 2.55*75/1|bc`”.

Notice the divide by 1 at the end — that’s to turn a decimal value into an integer. If you use just 2.55*75, you post a value of 191.25 which throws an error. In bc’s language, / returns the quotient — this isn’t *rounding* but rather truncating the decimal portion( i.e. bc 9.99999/1 = 9).

We configure the OpenHAB item to take the selected value (the %2$s below), scale the value with bc, and insert the result into the curl command. We use a similar technique to read the data from Wink and present the scaled value through OpenHAB.

The item entry in our sitemap.items file:

Dimmer  DS_Pantry_Bulb_Level                                            “Bulb (Pantry Downstairs) [%d]”                                 <slider>        (gZigbeeBulb,gDS_Pantry,gLight)                                                                                 { exec=”<[/bin/sh@@-c@@echo `/usr/bin/curl \”http://wink.hub.address/set_dev_value.php\” -s -d \”nodeId=a&attrId=aprontest -l -m9;\”|grep Level|grep -oP \”\\d+\\D+\\K\\d+\\D+\\K\\d+\”` /2.55|bc:3600000:] >[*:/bin/sh@@-c@@/usr/bin/curl \”http://wink.hub.address/set_dev_value.php\” -s -d \”nodeId=a&attrId=aprontest -u -m9 -t2 -v`echo 2.55*%2$s/1|bc`;\”]”}