Tag: OpenHAB

Exchange 2013 Calendar Events In OpenHAB (CalDAV)

We’ve wanted to get our Exchange calendar events into OpenHAB — instead of trying to create a rule to determine preschool is in session, the repeating calendar event will dictate if it is a break or school day. Move the gymnastics session to a new day, and the audio reminder moves itself. Problem is, Microsoft stopped supporting CalDAV.

Scott found DAVMail — essentially a proxy that can translate between CalDAV clients and the EWS WSDL. Installation was straight-forward (click ‘next’ a few times). Configuration — for Exchange 2013, you need to select the “EWS” Exchange protocol and use your server’s EWS WSDL URL. https://yourhost.domain.cTLD/ews/exchange.asmx … then enable a local CalDAV port.

On the ‘network’ tab, check the box to allow remote connections. You *can* put the thumbprint of the IIS web site server certificate for your Exchange server into the “server certificate hash” field or you can leave it blank. On the first connection through DAVMail, there will be a pop-up asking you to verify and accept the certificate.

On the ‘encryption’ tab, you can configure a private keystore to allow the client to communicate over SSL. I used a PKCS12 store (Windows type), but a java keystore should work too (you may need to add the key signing key {a.k.a. CA public key} to the ca truststore for your java instance).

On the advanced tab, I did not enable Kerberos because the OpenHAB CalDAV binding passes credentials. I did enable KeepAlive – not sure if it is used, the CalDAV binding seems to poll. Save changes and open up the DAVMail log viewer to verify traffic is coming through.

Then comes Scott’s part — enable the bindings in OpenHAB (there are two of them – a CalDAVIO and CalDAVCmd). In the caldavio.cfg, the config lines need to be prefixed with ‘caldavio’ even though that’s not how it works in OpenHAB2.


Then in the caldavCommand.cfg file, you just need to tell it to load that calendar identifier:


We have needed stop openhab, delete the config file from ./config/org/openhab/ related to this calendar and binding before config changes are ingested.

Last step is making a calendar item that can do stuff. In the big text box that’s where a message body is located (no idea what that’s called on a calendar entry):


The subject can be whatever you want. The start time and end time are the times for the begin and end events. Voila!

Cleaning Up Old OpenHAB Persistence Tables

So my husband asked for a program that would go out to the OpenHAB persistence database and identify all of the item tables that are no longer associated with active items. If you rename or delete an item from OpenHAB, the associated data is retained in the persistence database. Might be a good thing – maybe you wanted that data. But if it’s useless fluff … well, no need to keep the state changes from a door sensor that’s no longer around.

Wrote the code, and asked him how many days old he wanted the last update to be before the item table got dropped … and he told me this was a useless way to do it and maybe something really hadn’t updated in six months or three years and age of last update is no way to be identifying tables to be removed. Which, yeah, then why ask for it!? Then I needed to write something that takes a list of items from OpenHAB and identifies everything in the items table that does not appear in the OpenHAB list so those tables can be deleted. But I figured I’d post the original code too in case anyone else could use it. Both in perl, and neither in particularly well written perl. I trust the data and don’t want to protect against insertion attacks.

Drop tables for items that no longer appear in OpenHAB:

use strict;
use DBI;

my %strItemsFromOpenHAB = ();
        my $strCurrentItem = $_;
close INPUT;

my $dbh = DBI->connect('DBI:mysql:openhabdb;host=DBHOST', 'DBUID', 'DBPassword', { RaiseError => 1 } );

my $sth = $dbh->prepare("SELECT * FROM items");
while (my @row = $sth->fetchrow_array) {
        my $strItemID = $row[0];
        my $strItemName = $row[1];
        if(! $strItemsFromOpenHAB{$strItemName} ){              # If the current item name is not in the list of items from OpenHAB
#               print "DELETE FROM items where ItemID = $strItemID\n";
                print "DROP TABLE Item$strItemID;  # $strItemName \n";

close OUTPUT;


Identify tables that have not been updated in iTooOldInDays days:

use strict;
use DBI;
use Date::Parse;
use Time::Local;

my $iTooOldInDays = 365;

my $iCurrentEpochTime = time();

my @strItems = ();
my $iItems = 0;

my $dbh = DBI->connect('DBI:mysql:openhabdb;host=DBHOST', 'DBUID', 'DBPassword', { RaiseError => 1 } );

my $sth = $dbh->prepare("SELECT * FROM Items");
while (my @row = $sth->fetchrow_array) {
        $strItems[$iItems++] = $row[0];

for(my $i = 0; $i < $iItems; $i++){ my $strTableName = 'Item' . $strItems[$i]; my $sth = $dbh->prepare("SELECT * FROM $strTableName ORDER BY Time DESC LIMIT 1");
        while (my @row = $sth->fetchrow_array) {
                my $strUpdateTime = $row[0];
                my @strDateTimeBreakout = split(/ /,$strUpdateTime);
                my $strDate = $strDateTimeBreakout[0];
                my $strTime = $strDateTimeBreakout[1];

                my @strDateBreakout = split(/-/,$strDate);
                my @strTimeBreakout = split(/:/,$strTime);

                my $iUpdateEpochTime = timelocal($strTimeBreakout[2],$strTimeBreakout[1],$strTimeBreakout[0], $strDateBreakout[2],$strDateBreakout[1]-1,$strDateBreakout[0]);
                my $iTableAge = $iCurrentEpochTime - $iUpdateEpochTime;

                if($iTableAge > ($iTooOldInDays * 86400) ){
                        print "$strTableName last updated $strUpdateTime - $iUpdateEpochTime\n";

close OUTPUT;

Smart Home (In)Security

I’ve seen a lot of articles recently about hacked IoT devices (and now one about a malicious company disrupting the customer’s service in retaliation for poor reviews (and possibly abusive calls to technical support). I certainly don’t think *everything* needs to be connected to the Internet. If you want to write messages on toast remotely, whatever … but beyond gimmicks, there are certainly products where the Internet offers no real advantage. But a lot of articles disparage the idea of a smart home based on goofy products.

There are devices that are more convenient than their ‘dumb’ counterparts. Locks that unlock when you are nearby. Garage lights that come on when the door is unlocked or opened. And if that was the extent of home automation, I guess you could still call it a silly fad.

But there are a LOT of connected devices that save resources: Exterior lighting that illuminates as you near your house. With motion detectors controlling light switches and bulbs, you (or the kids) cannot forget to turn out the lights. An outlet that turn OFF to eliminate draw when appliances are in ‘standby’ mode saved us about 50$/year just on the television/receiver. Use moisture sensors to control a sprinkler system so the grass is only watered when there is actual need. Water flow sensors that can alert you to unusual usage (e.g. when the water filter system gasket goes and it starts dumping water through the thing 24×7).

And some that prevent real damages to your home or person. If your house uses combustion for heat, configure the carbon monoxide sensor to shut off the HVAC system when CO levels are too high. Leak sensors shut off the water mains when a leak is detected (and turn off appliances in the wet area if there’s potential for shorting).

The major security problem with any IoT device, smart home systems included, is that you’ve connect private resources to the Internet. With all the hackers, punks, and downright malicious people out there. And from a privacy standpoint, you are providing information that can be mined to enhance marketing profiles — very carefully read the privacy policies of any company whose platform you will be using. Maybe a ‘smart’ coffee machine sounds good to you — but are they collecting (and potentially selling to third parties) information about how many cups of coffee you brew and the times of day you brew them? If you care is a personal decision, but it’s something that should be considered just the same.

When each individual device has its own platform, the privacy and security risks grow. A great number of these devices don’t need to be connected to the INTERNET directly but rather a relay point (hub). From a business perspective, this is a boon … since you have a Trane furnace (big money, not apt to be replaced yearly), you should also buy these other products that we sell and pay the monthly recurring to use our Nexia platform for all of your other smart devices. Or since you have a Samsung TV with a built-in hub … you should not only buy these other Samsung products, but hook all of your other smart ‘things’ up to SmartThings. And in a year or two when you’re shopping for a new TV … wait, you need one with a SmartThings hub or you’re going to have to port your existing configuration to a new vendor. Instant customer loyalty.

For an individual, the single relay point reduce risk (it’s not one of a dozen companies that need to be compromised to affect me, just this one) and confusion (I only have to keep track of one company’s privacy policy). *But* it also gives one company a lot more information. The device type is often indicative, but most people name the devices according to location (i.e. bedroom light, garage light, front door). Using SmartThings, Samsung knew when we went to bed and woke up, that we ate breakfast before brushing teeth (motion in hallway, motion in kitchen, water usage, power draw on appliances, motion in hallway, motion in bathroom, water usage) or showering (power draw on hot water tank, increased water usage). Which rooms we frequented (motion), when we watched TV (not what we watched, but when), when we left the house (no motion, presence change). How often we wash laundry (power draw on washer, water usage) and dishes (power draw in dishwasher, water usage). Temperature in the house (as reported from multi-sensor devices or from a smart thermostat), if we change settings for day/night. How often we drive a car (garage door open/closed with presence change, or speed of location change on presence), how much time we spend away from home. How often we have overnight guests (motion in guest bedroom at night).

And, yeah, the profile they glean is a guess. I might open the garage door when mowing grass. Or I might have rooms with no motion sensors for which they cannot account. But they have a LOT of data on which to base their guesses and no one selling targeted advertising profiles claims to be 100% accurate. Facebook’s algorithm, for quite some time, had me listed as a right-leaning Trump supporter. I finally tired of seeing campaign ads on their site and manually updated my advertising profile. Point is, one company has a lot of data from which they build fairly good targeted profiles. How much of our house is actually used (a lot of bedrooms that rarely get motion, get a ‘downsizing specialist’ real estate flyer. All rooms constantly with motion, get a flyer specific to finding a larger home to give you all some space). If the HVAC system is connected, they could create a target group “people who could use additional insulation or sealing in their house” (outdoor temp for location v/s indoor temp for location v/s energy draw).

In some ways, it’s cool that a company might be able to look at my life and determine a need of which I am not even aware. Didn’t realize how much of our energy bill was HVAC – wow, tightening the house and insulation will save how much?! But it’s also potentially offensive: yeah, we could use a bigger house for all of these people. We could also use a bigger pay cheque, what of it? Yeah, the kids moved out … but this is our house and why would you tell me I should be leaving? And generally invasive — information that doesn’t really cause harm but they’ve got no reason to know either.

What articles highlighting the insecurity of IoT devices seem to miss is that the relay point can reside on your local network with no Internet access. We personally use OpenHAB – which enables our home automation to function completely inside our local network. You trust the developers (or don’t, ours is open source … you can read the whole thing if you don’t want to trust developers), but you own the data and what is done with it.

You don’t need an expensive dedicated server to host your own home automation controller – a Raspberry PI will do. What you do need is technical knowledge and a good bit of time (or hire someone to do it for you, in which case you need money and someone else’s time). But the end result is the same — physical presence is required to compromise the system. Since physical presence will also let you bump locks, smash windows, cut power, flick light switches, open doors … you’re not worse off than before.

Serial Port Sniffer

We use a Wink hub to communicate with our ZigBee devices – scripts on the OpenHAB server make web calls over to the Wink hub to set bulb levels. Works great on outbound communication to the bulbs, but it is not real-time bi-directional (i.e. if a bulb level is changed elsewhere, OpenHAB would need to poll and get the new value). Doesn’t matter for the GE Link bulbs because there isn’t another way they get set beyond dropping and returning power (which turns the bulb on at 100%), but we cannot use the Wink hub to communicate with interactive devices — unlock the door manually and OpenHAB has no idea the light should be turned on until the next polling cycle. And polling is a lot of extra overhead – check every device every minute 24×7. And it’s slow – hit the polling cycle wrong and it takes a minute from unlocking the garage door before the light turns on.

Had the idea of monitoring data that moves across the serial interfaces and use a script to communicate real-time inbound changes over to OpenHAB. Watching the serial interface, we get lots of cryptic traffic from socat:

socat -x /dev/SerialPort,raw,echo=0,crnl PTY,link=/dev/ttyV1,raw,echo=0,crnl

Web-Accessible History From OpenHAB MySQL Persistence Database

My husband has wanted a quick/easy way to see the data stored in OpenHAB’s MySQL persistence database. He didn’t care for the mysql command line client. He didn’t care for PHPMyAdmin either. I’ve suggested the MyODBC client — which allows you to use MySQL databases as an ODBC data source so you can view the data in MS Access, Excel, etc. Nope – he wanted a web site.

So I put together a very quick (and ugly) PHP page that provides a list of all Items. If you click on an item, you can page through the item’s records. The index.php from the page is available here. You need a web server (I am using Apache on Fedora), PHP (I am using 5.6) and MySQLi (php-mysqlnd package).

This is a bit of paranoia on my part, but even on a page that is ONLY available internally … I don’t like to use an account with read/write access to display data. I create a new user and assign read access:

CREATE USER 'YourUserName'@'localhost' IDENTIFIED BY 'P#ssw0rdH3r3';
GRANT SELECT ON openhabdb.* to 'YourUserName'@'localhost';

Then use *that* user in the php code. This example has a web server running on the database server – and you connect to the MySQL server via localhost. If your web server is located on a different host, you’ll need to create and grant ‘YourUserName’@ the web server hostname.

OpenHAB Through A Reverse Proxy

This isn’t something we do, but my Google dashboard says a lot of people are finding my site by searching for OpenHAB and reverse proxy. I do a lot of other things through Apache’s reverse proxy, so I figured I’d provide a quick config.

To start, you either need to have the proxy modules statically built into Apache or load them in your httpd.conf file. I load the modules, so am showing the httpd.conf method. I have the WebStream module loaded as well because we reverse proxy an MQTT server for presence – the last line isn’t needed if you don’t reverse proxy WebStream data.

LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_http_module modules/mod_proxy_http.so
LoadModule proxy_wstunnel_module modules/mod_proxy_wstunnel.so

If I were reverse proxying our OpenHAB site, I would only do so over HTTPS and I’d have authentication on the site (i.e. any random dude on the Internet shouldn’t be able to load the site and turn my lights off without putting some effort into it). There are other posts on this site providing instructions for adding Kerberos authentication to a site (to an Active Directory domain). You could also use LDAP to authenticate to any LDAP compliant directory – config is similar to the Kerberos authentication with LDAP authorization. You can do local authentication too – not something I do, but I know it is a thing.

Once you have the proxy modules loaded, you need to add the site to relay traffic back to OpenHAB. To set up a new web site, you’ll need to set up a new virtual host. Server Name Indication was introduced in Apache 2.2.12 — this allows you to host multiple SSL web sites on a single IP:Port combination. Prior to 2.2.12, the IP:Port combination needed to be unique per virtual host to avoid certificate name mismatch errors. You still can use a unique combination, but if you want to use the default HTTP-SSL port, 443, and identify the site through ServerName/ServerAlias values … Google setting up SNI with Apache.

Within your VirtualHost definition, you need a few lines to set up the reverse proxy. Then add the “ProxyPass” and “ProxyPassReverse” lines with the URL for your OpenHAB at the end

ProxyRequests Off
        ServerName openhab.rushworth.us
        ServerAlias openhab
        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 / https://openhabhost.rushworth.us:9443/
        ProxyPassReverse / https://openhabhost.rushworth.us:9443/

        SSLEngine On
        SSLProxyEngine On
        SSLProxyCheckPeerCN off
        SSLProxyCheckPeerName off
        SSLCertificateFile /apache/httpd/conf/ssl/www.rushworth.us.cert
        SSLCertificateKeyFile /apache/httpd/conf/ssl/www.rushworth.us.key
        SSLCertificateChainFile /apache/httpd/conf/ssl/signingca-v2.crt

Reload Apache and you should be able to access your OpenHAB web site via your reverse proxy. You can add authentication into the reverse proxy configuration too — this would allow you to use the OpenHAB site directly from your internal network but require authentication when coming in from the Internet.

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.

Text-to-speech (TTS) On Fedora

Scott has been setting up our OpenHAB server, and the latest project was controlling our network speakers. You can play Internet radio stations to the speakers, you can stream music from the NAS … but we also want to be able to play announcements. For that, we needed a text to speech engine.

Festival is in Fedora’s yum repository, but everything I’ve read about Festival says the output is robotic. Which is likely fun at first, but tiring after the first three or four times. Even if you have it say “beep, boop” at the end.

SVox (Nuance, which a long LONG time ago was spun off from Stanford Research Labs) has an open-source version of their text to speech product. Not in convenient package form, but close. Someone maintains a shell install script. Download the script:

wget https://raw.githubusercontent.com/stevenmirabito/asterisk-picotts/master/picotts-install.sh

Then read through it and make sure it’s not doing anything untoward. It wasn’t. Ran the script and a minute later, we can use “pico2wave -w /tmp/ljr.wav “I am your TTS engine”

Quick. Easy. And now we’ve got a wave file to send to the speaker (and remove when we’re done!)

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`;\”]”}

Parsing JSON In JavaScript

We’ve been trying to get our BloomSky data parsed and reflected in OpenHAB — we can automatically turn the lights on when there is motion *and* the luminescence is lower than some desired value.  Bloomsky has an API which allows us to retrieve JSON formatted data from our weather station. I never worked with JSON before – I’d heard the term, but didn’t actually know what it was … but I needed to parse it in a JavaScript transform. Does JavaScript do JSON? D’oh! Turns out JSON is an abbreviation for JavaScript Object Notation, and JavaScript parses JSON data really well.

Still need to turn my example web code into a transform that runs from OpenHAB, but getting values out of a JSON formatted string is as easy as using the “parse” function:

	      function parseMyData() {
		var input = '{"DeviceID":"83237E","LAT":41.226644299999997,"LON":-81.7224322,"ALT":292.78720092773438,"UTC":-4,"DST":1,"Searchable":true,"RegisterTime":1464494138,"CityName":"Hinckley","StreetName":"Bellus Road","FullAddress":"Bellus Road, Hinckley, Ohio, US","DeviceName":"Buzzard Cam 01","BoundedPoint":null,"NumOfFollowers":5,"Data":{"Temperature":80.528000000000006,"ImageURL":"http://storage.googleapis.com/bloomsky-img/eaB1rJytnZSmm5y3qJ1krJqwmJmtoJU=.jpg","Humidity":50,"Night":false,"ImageTS":1465938980,"Luminance":3445,"TS":1465938980,"Rain":false,"Pressure":29.087148500000001,"Voltage":2613,"UVIndex":"1"},"Point":{},"VideoList":["http://storage.googleapis.com/bloomsky-video/eaB1rJytnZSmm5y3_-4_2016-06-09.mp4","http://storage.googleapis.com/bloomsky-video/eaB1rJytnZSmm5y3_-4_2016-06-10.mp4","http://storage.googleapis.com/bloomsky-video/eaB1rJytnZSmm5y3_-4_2016-06-11.mp4","http://storage.googleapis.com/bloomsky-video/eaB1rJytnZSmm5y3_-4_2016-06-12.mp4","http://storage.googleapis.com/bloomsky-video/eaB1rJytnZSmm5y3_-4_2016-06-13.mp4"],"NumOfFavorites":0}'

		var jsonOfInput = JSON.parse(input);

		document.write("<P>Device ID is: " + jsonOfInput.DeviceID + "</P>");
		document.write("<P>Temp is: " + jsonOfInput.Data.Temperature + "</P>");
		document.write("<P>Luminance is: " + jsonOfInput.Data.Luminance + "</P>");
	  <h2>Press the button to start</h2>
	    <input type="button" onclick="parseMyData()" value="Parse"/>