Tag: Technology

Recovering A Seriously Screwed Up Fedora System

The graphical interface on a Fedora 28 laptop was unavailable — buggered up video device/driver. Change to what used to be called run level 3, and we could not log in! We know the root password, but it would not take it. Single user is password protected too — and we were unable to log in there.

Normal recovery process:

Get to the grub menu, highlight the kernel you want to boot, and hit ‘e’ to edit it. Scroll down. On line that starts with linux16, change “rhgb quiet” to say “rd.break enforcing=0”
ctrl-x to boot

Once you get a shell:
mount -o remount,rw /sysroot
chroot /sysroot

Voila, you’ve got access to your files. Use vi to edit whatever has the box seriously screwed up (passwd if your problem is that you don’t know the root password) and you’re set. We reset the root password just in case. Aaaand … we still couldn’t log in on init 1 or init 3! And at this point I was feeling stubborn about getting logged into the box.

Now you can tweak up the system so it is not using sulogin when booting into single user mode but that isn’t a good way to install network-sourced packages. For some reason, we had to disable selinux before we could log into anything other than the graphical target. I’m sure there is a policy we could have tweaked, but it was far easier to disable the thing, boot into the multi-user target, sort the video driver, and then boot into the graphical target.

Debugging An Active Directory Custom Password Filter

A few years ago, I implemented a custom password filter in Active Directory. At some point, it began accepting passwords that should be rejected. The updated code is available at https://github.com/ljr55555/OpenPasswordFilter and the following is the approach I used to isolate the cause of the failure.

 

Technique #1 — Netcap on the loopback There are utilities that allow you to capture network traffic across the loopback interface. This is helpful in isolating problems in the service binary or inter-process communication. I used RawCap because it’s free for commercial use. There are other approaches too – or consult the search engine of your choice.

The capture file can be opened in Wireshark. The communication is done in clear text (which is why I bound the service to localhost), so you’ll see the password:

And response

To ensure process integrity, the full communication is for the client to send “test\n” then “PasswordToTest\n”, after which the server sends back either true or false.

Technique #2 — Debuggers Attaching a debugger to lsass.exe is not fun. Use a remote debugger — until you tell the debugger to proceed, the OS is pretty much useless. And if the OS is waiting on you to click something running locally, you are quite out of luck. A remote debugger allows you to use a functional operating system to tell the debugger to proceed, at which time the system being debugged returns to service.

Install the SDK debugging utilities on your domain controller and another box. Which SDK debugging tool? That’s going to depend on your OS. For Windows 10 and Windows Server 2012 R2, the Windows 10 SDK (Debugging Tools For Windows 10) work. https://docs.microsoft.com/en-us/windows-hardware/drivers/debugger/debugger-download-tools or Google it.

On the domain controller, find the PID of LSASS and write it down (472 in my example). Check the IP address of the domain controller (10.104.164.110 in my example).

From the domain controller, run:

dbgsrv.exe -t tcp:port=11235,password=s0m3passw0rd

Where port=11235 can be any un-used port and password=s0m3passw0rd can be whatever string you want … you’ve just got to use the same values when you connect from the client. Hit enter and you’ve got a debugging server. It won’t look like it did anything, but you’ll see the port bound on netstat

And the binary running in taskman

From the other box, run the following command (substituting the correct server IP, port, password, and process ID):

windbg.exe -y “srv:c:\symbols_pub*http://msdl.microsoft.com/downloads/symbols” -premote tcp:server=10.104.164.110,port=11235,password=s0m3passw0rd -p 472

This attaches your WinDBG to the debugging server & includes an internet-hosted symbol path. Don’t worry when it says “Debugee not connected” at the bottom – that just means the connection has not completed. If it didn’t connect at all (firewall, bad port number, bad password), you’d get a pop-up error indicating that the initial connection failed.

Wait for it … this may take a long time to load up, during which time your DC is vegged. But eventually, you’ll be connected. Don’t try to use the DC yet – it will just seem hung, and trying to get things working just make it worse. Once the debugger is connected, send ‘g’ to the debugger to commence – and now the DC is working again.

Down at the bottom of the command window, there’s a status (0:035> below) followed by a field where you enter commands. Type the letter g in there & hit enter.

The status will then say “Debuggee is running …” and you’re server is again responsive to user requests.

When you reach a failing test, pause the debugger with a break command (Debug=>Break, or Ctrl-Break) which will veg out the DC again. You can view the call stack, memory, etc.

To search the address space for an ASCII string use:

!for_each_module s -[1]a ${@#Base} L?${@#Size}  "bobbob"

Where “bobbob” is the password I had tested.

Alternately, run the “psychodebug” build where LARGEADDRESSAWARE is set to NO and you can search just the low 2-gig memory space (32-bit process memory space):

s -a 0 L?80000000 "bobbob"

* The true/false server response is an ASCII string, not a Boolean. *

Once you have found what you are looking for, “go” the debugger (F5, Debug=>Go, or  ‘g’) to restore the server to an operational state. Break again when you want to look at something.

To disconnect, break and send “qd” to the debugger (quit and detach). If you do not detach with qd, the process being debugged terminates. Having lsass.exe terminate really freaks out the server, and it will go into an auto-recovery “I’m going to reboot in one minute” mode. It’ll come back, but detaching without terminating the process is a lot nicer.

Technique #3 – Compile a verbose version. I added a number of event log writes within the DLL (obviously, it’s not a good idea in production to log out candidate passwords in clear text!). While using the debugger will get you there eventually, half an hour worth of searching for each event (the timing is tricky so the failed event is still in memory when you break the debugger) … having each iteration write what it was doing to the event log was FAAAAAR simpler.

And since I’m running this on a dev DC where the passwords coming across are all generated from a load sim script … not exactly super-secret stuff hitting the event log.

Right now, I’ve got an incredibly verbose DLL on APP556 under d:\tempcsg\ljr\2\debugbuild\psychodebug\ … all of the commented out event log writes from https://github.com/ljr55555/OpenPasswordFilter aren’t commented out.

Stop the OpenPasswordFilter service, put the verbose DLL and executables in place, and reboot. Change some passwords, then look in the event viewer.

ERROR events are actual problems that would show up either way. INFORMATION events are extras. I haven’t bothered to learn how to properly register event sources in Windows yet 🙂 You can find the error content at the bottom of the “this isn’t registered” complaint:

You will see events for the following steps:

DLL starting CreateSocket

About to test password 123paetec123-Dictionary-1-2

Finished sendall function to test password123paetec123-Dictionary-1-2

Got t on test of paetec123-Dictionary-1-2

The final line will either say “Got t” for true or “Got f” for false.

Technique #4 – Running the code through the debugger. Whilst there’s no good way to get the “Notification Package” hook to run the DLL through the debugger, you can install Visual Studio on a dev domain controller and execute the service binary through the debugger. This allows you to set breakpoints and watch variable values as the program executes – which makes it a whole lot easier than using WinDBG to debug the production code.

Grab a copy of the source code – we’re going to be making some changes that should not be promoted to production, so I work on a temporary copy of the project and delete the copy once testing has completed.

Open the project in Visual Studio. Right-click OPFService in the “Solution Explorer” and select “Properties”

Change the build configuration to “Debug”

Un-check “Optimize code” – code optimization is good for production run, but it will wipe out variable values when you want to see them.

Set a breakpoint on execution – on the OPFDictionary.cs file, the loop checking to see if the proposed word is contained in the banned word list is a good breakpoint. The return statements are another good breakpoint as it pauses program execution right before a password test iteration has completed.

Build the solution (Build=>Build Solution). Stop the Windows OpenPasswordFilter service.

Launch the service binary through the debugger (Debug=>Start Debugging).

Because the program is being run interactively instead of through a service, you’ll get a command window that says “Press any key to stop the program”. Minimize this.

From a new command prompt, telnet to localhost on port 5995 (the telnet client is not installed by default, so you may need to use “Turn Windows features on or off” and enable the telnet client first).

Once the connection is established, use CTRL and ] to get into the telnet command prompt. Type set localecho … now you’ll be able to see what you are typing.

Hit enter again and you’ll return to the blank window that is your telnet client. Type test and hit enter. Then type a candidate password and hit enter.

Program execution will pause at the breakpoint you’ve set. Return to Visual Studio. Select Debug =>Window=>Locals to open a view of the variable values

View the locals at the breakpoint, then hit F5 if you want to continue.

If you’re set breakpoints on either of the return statements, program execution will also pause before the return … which gives you an opportunity to see which return is being used & compare the variable values again.

In this case, I submitted a password that was in the banned word list, so the program rightly evaluated line 56 to true and returns true.

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=15.0.0.0, 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
<VirtualHost 10.1.2.25:8443>
        ServerName openhabExternalHost.domain.gTLD
        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://openhabInternalHost.domain.gTLD:9443/
        ProxyPassReverse / https://openhabInternalHost.domain.gTLD: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
</VirtualHost>

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.
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.

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

<?php
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";
}
else{
$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')";
}
elseif($strKWHValue){
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWHValue')";
}
else{
$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;
}

$conn->close();
}
?>

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.

StackedBarChart-KWHAndTemp

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.