Category: Home Automation

OwnTracks WebSockets MQTT SSL Error

A few weeks ago, we stopped getting location updates from OwnTracks on our phones. Checking the status, I see an error indicating that the connection failed because my certificate does not have a SAN. Which … true, it does not. I knew some consortium agreed that all certs should have SAN values (and RFCs had been updated to reflect this new direction). Evidently version 2.2.2 of OwnTracks has added SAN verification. I reissued the certificate from my CA and added a SAN. I had to put the cert on both my MQTT websockets reverse proxy and the mosquitto server; but, once both were using the new cert, OwnTracks connected and cleared through the queued updates.

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 CalDav Personal Binding – Item Name Filtering

We use the CalDav Personal binding to select items from our Exchange calendar to populate date/time OpenHAB Items — when does Anya have her next gymnastics class, when is the next Trustee meeting, etc. When we had first set this up, we were using manually created appointments. The appointments were assigned unique categories so the binding could determine which appointment should be used to update the Item. I’ve since started creating calendar items based on published calendars (so far a Google calendar and a SchoolPointe calendar), but the Python module for interacting with Exchange cannot assign a category to the appointments it creates.

The binding allows you to filter on the appointment subject (‘name’) using a regex. The binding documentation says to use name-filter:’\<Some Filter\>’ which … well, doesn’t work. We tried omitting the back-slashes in case they were meant to be escape characters. We tried omitting the greater and less-than symbols in case those were meant in the way I often use them, to designate <the part you replace>. Still doesn’t work. We tried using forward-slashes instead of backslashes because that’s the normal regular expression syntax. Nope. We tried adding a ‘starts with’ ^, trailing .* to ensure it would match anything that started with what we wanted. Nope. We’d alternately match all of the appointments or none.

Consulting the source, there is a very restrictive character set available in your name filter regex. The binding uses Java’s Matcher with a regex to extract your regex from the item configuration. You need to have filter-name: then you may have a single quote (‘? means 0 or 1 of ‘). This is Followed by one or more characters from the class which is all upper and lower case letters A-Z, a full stop, an asterisk, a plus sign, a minus sign, a space, and a pipe bar. Then you may have another single quote. The bit with one or more characters from the restricted class is extracted — this is how the binding gets your regex from the item config.

private static final String REGEX_FILTER_NAME ="filter-name:'?([A-Za-z\\.\\*\\+\\- \\|]+)'?";

Using unsupported characters in your filter-name regex alternately match all appointments or none. Using filter-name:’\<test>\’ (as the documentation literally instructs me to do) doesn’t return a match with anything as + requires one or more matches from the character set. I have zero such characters after the opening single quote. Similarly filter-name:’^Beginning of string.*’ doesn’t return a match. It appears that, in cases where the name filter is null … all items are matched. Explains why we were getting the same appointment’s details posted into each item.

On the other extreme — a filter like filter-name:’Pick up dry-cleaning at 1 Main Street’ will truncate your regular expression at the number character. The extracted matched group is Pick up dry-cleaning at … which won’t match anything unless you actually have an appointment titled “Pick up dry-cleaning at ” with a trailing space. I’ve seen posts on the OpenHAB forum where individuals have non-English words in their match … filter-name:’Trip to Askøy’ which, again, match nothing since the actual regex used by the binding is Trip to Ask  The same thing happens when looking for character classes (i.e. I don’t know if this will be capitalized, so I want to match [Tt]est).

The solution, since a question mark isn’t an option, is to use a plus or splat to replace any character that isn’t supported by the binding. Using a plus ensures there’s something where you expect the character to occur, although the * is a broader match (we use “Township: Event Name”, but I don’t need the colon to successfully match my item. “Township Event Name” would match. I could even use a different delimiter as “Township, Event Name” would also match). Where you are unsure of the case, you need to use a pipebar (e.g. filter-name:’Test|test’)

The Items that are populated with the start time and event name for the next Township meeting look like this:

DateTime Calendar_Upcoming_Township "Upcoming Township meeting (start) [%1$tA, %1$tB %1$te, %1$tY at %1$tl:%1$tM%1$tp]" <calendar> (gCalendar) {caldavPersonal="calendar:ourcalendar type:UPCOMING eventNr:1 value:START filter-name:'Township. .*'"}
String Calendar_Upcoming_Township_Title "Upcoming Township meeting [%s]" <calendar> (gCalendar) {caldavPersonal="calendar:ourcalendar type:UPCOMING eventNr:1 value:NAME filter-name:'Township. .*'"}

And the calendar events titled “Township: Trustee Regular Meeting” or “Township: Craft Fair” are all identified by the filter.

Note: Scott submitted a PR to change the regex used to extract your filter-name regex. Once this change gets merged, you’ll be able to use character sets (e.g. [T|t]est), numbers, and ‘special’ characters excluding the single quote. Including the single quotes around the filter will be required.

Scraping Calendar Events

We’ve learned the value of engaging with local government — with few people involved in local proceedings, it’s pretty easy for a generally unpopular proposal to seem reasonable. And then we’re all stuck with the generally unpopular regulation. It is a pain, however, to keep manually adding the next Trustee meeting. And there’s no way I’m checking the website daily to find out about any emergency meetings.

Now I’m pulling the events from their Google calendar and creating new meeting items in my Exchange calendar:

  1. Register the app with Google to use the API
  2. Install exchangelib
  3. Copy config.sample to config.py and add personal information
  4. Create a ca.crt file with the CA signing key for your Exchange server (or remove the custom adapter if your server cert is signed by a public key)
  5. Run getCalendarEvents.py and follow the URL to authorize access to your calendar

I’ve tweaked the script to grab events from the school district’s calendar in SchoolPointe too. Now we know when there’s a school board meeting or dress-up day.

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.

Using ZoneMinder v1.32.3 With OpenHAB2

I documented a temporary fix to return ZM_PATH_ZMS and ZM_OPT_FRAME_SERVER through the ./api/configs/view/<KEYNAME>.json API so ZoneMinder 1.31.45 worked with the OpenHAB2 binding. Upon upgrading ZoneMinder to 1.32.3, the binding was no longer able to communicate with our ZoneMinder server.

In the OpenHAB2 log, errors indicated malformed JSON was received.

Caused by: com.google.gson.stream.MalformedJsonException: Use JsonReader.setLenient(true) to accept malformed JSON at line 1 column 7 path $
at com.google.gson.stream.JsonReader.syntaxError(JsonReader.java:1568) ~[?:?]
at com.google.gson.stream.JsonReader.checkLenient(JsonReader.java:1409) ~[?:?]
at com.google.gson.stream.JsonReader.doPeek(JsonReader.java:542) ~[?:?]
at com.google.gson.stream.JsonReader.peek(JsonReader.java:425) ~[?:?]
at com.google.gson.JsonParser.parse(JsonParser.java:60) ~[?:?]
at com.google.gson.JsonParser.parse(JsonParser.java:45) ~[?:?]
at name.eskildsen.zoneminder.jetty.JettyConnectionInfo.fetchDataAsJson(JettyConnectionInfo.java:352) ~[?:?]

Using a web browser to access <ZoneMinderURL>/zm/api/configs/view/ZM_PATH_ZMS.json, malformed JSON is returned.

Conf files are not updated when new packages are installed – an conf.rpmnew is created instead. The changes from the new config (zoneminder.conf.rpmnew) file need to be merged into the existing config file (zoneminder.conf). In our zm.conf file, I added:

ZM_DB_SSL_CA_CERT=
ZM_DB_SSL_CLIENT_KEY=
ZM_DB_SSL_CLIENT_CERT=

Reloading the page in my browser confirmed that the JSON response is valid.

When the ZoneMinder binding started, it successfully attached to our monitors and detected a motion alarm.

This does not negate the need for the original fix — config.php still needs to have the strcmp I added. When ZoneMinder is upgraded, /usr/bin/zmupdate.pl is run (I needed to run “/usr/bin/zmupdate.pl -f” to stop zmc from existing with return code 255), the values I added to the ZoneMinder Config table are removed — they need to be re-added.

 

Quick OpenHAB2 Apt Install In Docker Ubuntu Container

# Set up docker image — exposes OpenHAB web on your port 8080
docker run -p 8080:8080 -dit –name UbuntuOH2 ubuntu:latest

# Shell into the container
docker exec -it UbuntuOH2 /bin/bash

# From within the container, run:
apt update
apt install sudo
apt install vim
apt install wget
apt install gnupg
apt install apt-transport-https

# Repo for Zulu Java
echo ‘deb http://repos.azulsystems.com/debian stable main’ > /etc/apt/sources.list.d/zulu.list

# Repo for OpenHAB2 stable build
wget -qO – ‘https://bintray.com/user/downloadSubjectPublicKey?username=openhab’ | apt-key add –
apt-key adv –keyserver hkp://keyserver.ubuntu.com:80 –recv-keys 0xB1998361219BD9C9
echo ‘deb https://dl.bintray.com/openhab/apt-repo2 stable main’ | tee /etc/apt/sources.list.d/openhab2.list

apt-get update
apt-get install zulu-8
apt-get install openhab2
apt-get install openhab2-addons

/etc/init.d/openhab2 start

# OpenHAB will be accessible on your IP at 8080. E.g. http://10.10.10.123:8080.
# docker start/stop UbuntuOH2