Tag: Technology

Business Practices To Avoid

Don’t ignore your customers. Seems obvious, but failing to engage customers undermines large corporations. I worked for one of Novell’s last big customers back in 2000-2010. We had the misfortune of being in the same territory as their biggest customer, FedEx, so got little sales attention. We were having problems managing computers without using the Active Directory domain — the dynamic local user Zen component that hooked the Novell GINA and created/maintained local user accounts had been used before an NT4 domain even existed within the company. In perusing their web site, I identified a product that perfectly met our needs *and* managed mobile devices (which was an up and coming ‘thing’ at the time). Why, I asked the sales guy, would you not pitch this product to us when we tell you about the challenges we are trying to address? No good answer, but it really was a rhetorical question. There wasn’t a downloadable demo available, you had to engage your sales rep to get a working demo copy — I asked for one, and he said he’d get one to me when he got back to his office.

Nothing. Emailed him a week later in case he just forgot. Oh, yeah, I’ll get that right out to you. A few weeks later, emailed him again. A few weeks later — well, let’s be serious here. We started using Exchange in 2000, and had an Active Directory domain licensed for all users anyway. We were willing to consider paying real money for the Novell product because the migration path was easier … but from a software licensing perspective, switching workstation authentication to AD was a 0$ thing. Needed a few new servers to handle authentication traffic – I think I went with five at about three thousand dollars each. Deployment, now that’s a nightmare. I wrote custom code to re-ACL the user profile directory and modify the registry to link the new user.domain SID to the re-ACL’d old profile directory. It got pushed out via automated software deployment and the failures would call in each morning. Even a 1% failure rate when you’re doing 10,000 computers a week is a lot of phone calls and workstation re-images. (At a subsequent employer, we made the same change but placed workstations into the domain as they were re-imaged for other reasons. New computer, you’re in the domain. Big problems with your OS, you’re in the domain. Eventually we had a couple hundred computers not yet in the domain and the individual users were contacted to schedule a reimage. Much cleaner process.)

The company didn’t last much longer — they purchased SuSE not much later. The sales guys came back – we used RHEL but would have happily bundled our Linux purchases into the big million dollar contract. How much are you looking to charge for updates? Dunno. How much is support? Dunno. Do you know anything about the company’s sales plan for SuSE? Not a thing. Well … glad you could stop by? I guess.

As far as software companies go, this is ancient history. But it’s something I think of a lot when dealing with Microsoft these days. There’s a free mechanism that allows you to use your existing Active Directory to store local workstation admin account passwords. Local workstations manage their own passwords — no two passwords are the same; you can read the individual computer’s password out of AD and provide it to the end user. Expire the computer’s local admin password and next time it communicates with the domain, the password will be changed. Never heard of it from the MS sales guy – someone found LAPS through random web searching. Advanced Group Policy Management that provides auditing and versioning for group policies – not something our MS reps mentioned. Visual Studio Code – yet another find based on random web searching. I know it isn’t the sales guy’s job to tell me about every little bit of free add-on code they have created, but isn’t it in their best interest to ensure that the products that we have become an intrinsic part of our business processes? I tell our SharePoint group that all the time — there are a lot of web based content management platforms. If all you use it for is avoiding web coding … well, I’ve got WordPress that does that. Or some Atlassian wiki thing. And some Jive wiki thing. And some Xerox document repository that has web pages. You need to make something unique to your product intrinsically entwined with business oeprations so no one would ever think of replacing your product.

Really Wacky Exchange (ActiveSync) Error

My husband changed his Active Directory password. Routine enough – we’ve got 15k accounts at the office and require a password change every 90 days. That’s 150-200 people changing their password every day. They get themselves locked out a lot (mobile devices, cached workstation credentials, and a host of other unique places people manage to store their creds), but it’s trivial to unlock an individual user.

*Except* — after the account was unlocked, his Windows 10 mail client updated properly and was interacting with the Exchange server. Android, however, still wouldn’t accept his new password. If he typed the wrong thing, it would say invalid password. But whenever he typed the right thing, he got an error indicating the phone and tablet were unable to communicate with the server. Which was bogus — I could see the communication coming across the reverse proxy server. With 200 codes — although you can have a very successful HTTP call deliver an application error message. But it wasn’t like he couldn’t COMMUNICATE with the server. He turned sync off on the phones to avoid getting locked out again, and in the process of troubleshooting ended up deleting all of his accounts hosted on our Exchange 2013 server.

I looked through all of the event logs, Exchange logs … nothing interesting. In desperation, I enabled the individual user ActiveSync logging:

Set-CASMailbox mailNickName -ActiveSyncDebugLogging:$true

Had him attempt to add the mailbox profile again, and dropped the log myself:

Get-ActiveSyncDeviceStatistics -Mailbox mailNickName -GetMailboxLog:$true -NotificationEmailAddress mysmtp@mydomain.ccTLD

Bingo! An exception in the provisioning (Microsoft-Server-ActiveSync?Cmd=Provision) call — I see the phone information come across, the mobile device gets partially added to his account (no OS, phone number, carrier type information … but if you go into OWA and remove the mobile device, an Android device gets added). Error:

Command_WorkerThread_Exception :
— Exception start —
Exception type: System.IO.FileLoadException
Exception message: Could not load file or assembly ‘Microsoft.Exchange.Configuration.ObjectModel, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Exception level: 0
Exception stack trace: at Microsoft.Exchange.AirSync.DeviceInformationSetting.ProcessSet(XmlNode setNode)
at Microsoft.Exchange.AirSync.DeviceInformationSetting.Execute()
at Microsoft.Exchange.AirSync.ProvisionCommand.Microsoft.Exchange.AirSync.IProvisionCommandHost.ProcessDeviceInformationSettings(XmlNode inboundDeviceInformationNode, XmlNode provisionResponseNode)
at Microsoft.Exchange.AirSync.ProvisionCommandPhaseOne.Process(XmlNode provisionResponseNode)
at Microsoft.Exchange.AirSync.ProvisionCommand.ExecuteCommand()
at Microsoft.Exchange.AirSync.Command.WorkerThread()
Inner exception follows…
Exception type: System.IO.FileLoadException
Exception message: The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Exception level: 1
Exception stack trace:
— Exception end —

Now that is an error I’ve never seen before. As a programmer, I know what it means … you’ve basically got some components that don’t match another. But … huh? He changed his password. Connected to the Exchange server directly (instead of remotely viewing logs & files) and saw Windows Update had dropped files and a reboot was pending. Which … some files replaced, others staged for replacement pending a reboot. *That* is some components not matching others. Rebooted our box, and voila … registration goes through, mailbox sync started.

I don’t know how many people allow auto-updates with a manual reboot on a production enterprise server (we manually patch and reboot during a scheduled maintenance window) where this could happen … but evidently Windows Update can get your Exchange server into a state where already configured clients are able to send and receive mail. But clients are unable to update passwords, and new clients cannot be configured.

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.

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.

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 #.#.#.#:##>
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/

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.

Tracking Electrical Usage With SmartThings and AeonLabs Home Energy Meters

When we started shopping for solar generation installations, how much electricity we can consume was a challenging question. We were replacing our HVAC system, so “look at the last 12 months of electric bills” wasn’t an approach that would yield valid data. What we needed was a way to see electrical consumption for the next two or three weeks once our new HVAC system was installed.

We purchased several AeonLabs Home Energy Meters (HEM) and have been using them to track our power consumption for almost six months now. The HEM’s are set up in SmartThings & have a SmartApp-HEMLogger “SmartApp” attached to them that posts data to a MySQL table on our server via a web form (myURL needs to be … well, your URL).

Install a quick MySQL server (does not need to be Internet accessible) and a web server / programming language of your choice combination (*does* need to be Internet accessible – we are using Apache and PHP).

Create the database and a table to hold your energy data:

mysql> describe EnergyMonitors;
| Field | Type | Null | Key | Default | Extra |
| keyID | int(11) | NO | PRI | NULL | auto_increment |
| monitorID | varchar(50) | YES | | NULL | |
| clampID | varchar(50) | YES | | NULL | |
| eventTime | timestamp | NO | | CURRENT_TIMESTAMP | |
| kwatts | double | YES | | NULL | |
| kwhours | double | YES | | NULL | |
6 rows in set (0.00 sec)

Create an ID within your database that has read/write permission to this table. I create another ID that has read-only access (pages displaying data use this ID, the page to post data uses the read/write ID).

We also track temperature — ideally, we’d be able to compare power consumption based on temperature *and* sunlight (we use a lot less power on a cold sunny day than I expect … just don’t know how much less) … but I’m not there yet. Currently, the weather database only holds temperature:

mysql> describe weather;
| Field | Type | Null | Key | Default | Extra |
| recordedTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| temperature | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
3 rows in set (0.01 sec)

Since we brought our Bloomsky online, I use the Bloomsky API to record temperature. Prior to that, I was parsing the XML from NWS’s closest reporting station’s – for Cleveland, that is  http://w1.weather.gov/xml/current_obs/KCLE.xml … there’s probably one near you. I grabbed both observation_time_rfc822 and temp_f in case observations were not updated regularly – the observation time was stored as the recordedTime. The temperature recording script is in cron as an hourly task.

You also need a small web page where SmartThings can post data — in PHP, I have

if(!$_POST["monitorID"] || !$_POST["clampID"] ){
echo "<html><body>\n";
echo "<form action=\"";
echo $_SERVER['PHP_SELF'];
echo "\" method=\"post\">\n";
echo "Monitor ID: <input type=\"text\" name=\"monitorID\"><br>\n";
echo "Clamp ID: <input type=\"text\" name=\"clampID\"><br>\n";
echo "KW Value: <input type=\"text\" name=\"kwatts\"><br>\n";
echo "KWH Value: <input type=\"text\" name=\"kWHours\"><br>\n";
echo "<input type=\"submit\">\n";
echo "</form>\n";
echo "</body></html>\n";
$strMeter = $_POST["monitorID"];
$strClamp = $_POST["clampID"];
$strKWValue = $_POST["kwatts"];
$strKWHValue = $_POST["kWHours"];
print "<pre>Meter: $strMeter\nClamp: $strClamp\nKW: $strKWValue\nKWH: $strKWHValue\n</pre>\n";
$servername = "DatabaseHostname";
$username = "MySQLUID";
$password = 'MySQLPassword';
$dbname = "HomeAutomation";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
if($strKWHValue && $$strKWValue){
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwatts, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWValue', '$strKWHValue')";
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWHValue')";
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwatts) VALUES ('$strMeter', '$strClamp', '$strKWValue')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
else {
echo "Error: " . $sql . "<br>" . $conn->error;


DatabaseHostname, MySQLUID and MySQLPassword need to be yours too. Since posted data is meant to come from a trusted source (a source where I’ve supplied the URL) and in a known good format, these are quick INSERT statements *not* the safest.

Check in your database that you are getting data, then let it run for a few hours. Once you have a little bit of history, you can start viewing your energy usage. Link it into Excel/Access using MyODBC for ad-hoc reporting, write your own code to do exactly what you want, use a generic charting package capable of reading MySQL data …

I am using pChart to display data – the chart I use most frequently is the stacked bar chart for kWH and a line chart for temperature. Here is the PHP code which generates this PNG: energyUsage-StackedBarChart-Flat – again, DatabaseHostname, MySQLUID, and MySQLPassword need to be yours.


We no longer have our heat pump, air handler, and heat strips being monitored – but for periods where there is data from the other sources, we had several segments to our energy usage report (“other” is the report from the HEM on our mains MINUS all of the other reporting segments). You can yank all of the non-mains segments (or change them to be whatever sub-segments you are monitoring. The monitor ID comes from the HEM name in SmartThings – since those are user-configured, I have the names hard coded. You *could* hard code the Mains and then use “select distinct” to get a list of all the others and make the code more flexible.).

Short term charts (past 24 hours or so) can render out real-time, but longer term views take a long time to load. Since we’re looking more for trends and totals – being up to the second isn’t critical. I’ve got cron tasks that generate out PNG files of the charts.

Two enhancements for a nothing-doing rainy day – adding authentication to the HEM Logger (SmartThings posts from multiple netblocks, so there isn’t a good way to IP source restrict access to the post data page. Anyone with your URL could post rogue energy usage info into your database – or more likely try to hack your servers.) and add lumen to the weather data / graph displays.

Same word, different meaning

I issue a lot of certificates from our internal company certificate authority – they’re free, and since I can publish trusted root signers out to the domain, they’re trusted to anyone who would be using the site. You can type pseudo-random values into your request and my CA will issue a certificate for you.
Today, though, I needed a certificate for a site that would be used by non-employees. People who are not subject to my domain GPO. People who do not trust my CA. So I did what everyone else does – got a real certificate 🙂
I generated my CSR (and actually typed in good data – my server is in Conway, AR, USA type location instead of Z or A). Went out to Verisign’s site … “The CSR contains an invalid state. Please click your browser’s Back button and enter a new CSR.”
Thought the CSR might have gotten corrupted somehow, so I tried again. Same result. Tried some different information – same result. Finally resorted to reading the instructions – locality names may not contain abbreviations. D’oh. State like organized political community not state like condition.