Pushing Smarthings data to MySQL via REST interface

logging
requires_server

(Andrew Mc Grath) #1

Hi All,

So i wanted to get access to the “power” details of my sump pump as i can tell how often it’s running based on the energy usage. I found all the existing solutions for this to be pretty slow, non-free or just clunky…so i made something and now I’m sharing it with you :slight_smile:

This system works in 2 parts:

  1. A Smartapp you install and configure (I’ve tried to keep it as simple as possible)
  2. A REST server running Ubuntu 16 that you need to self configure (harder…but not impossible at all - I’m happy to help you if you want to try set this up!)

The code is available here: https://github.com/ndroo/smartthings

What it does:

Essentially the Smartapp subscribes to events from your devices. The subscription then makes a REST call to the URL you define in the Smartapp. The REST interface then pushes the data into a MySQL database.

I think this is pretty flexible, at this point you could modify the index.php file to do other things (turn stuff on/off, change the mode etc), or you could simply write up an interface to the MySQL database to produce graphs or info similar to what ActionTiles can do.

My objective has been met, I can now graph the usage of my Sump Pump by plotting the data related to the energy usage from the database. I’ll probably extend this, but for now, i’m going to leave it here.

If you have any questions, comments, or feature requests…let me know! You’re free to use this however you want. This took me a few hours to get working and it’s my first app / integration with Smartthings, hopefully it’s useful to someone else.

Example of the data you’ll start getting:

mysql> select * from sensors;
±-------------------------------------±------±-----±---------±-----------±----------+
| id | type | val | val_type | timestamp | name |
±-------------------------------------±------±-----±---------±-----------±----------+
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 5.4 | double | 1519344735 | Sump Pump |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 6.0 | double | 1519344760 | Sump Pump |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 6.7 | double | 1519344767 | Sump Pump |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 6.2 | double | 1519344772 | Sump Pump |
| f4d03594-4731-4d57-9850-87505eaffc28 | switch | off | bool | 1519344773 | Living Room Lamp |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 5.7 | double | 1519344777 | Sump Pump |
| ebb97883-da89-4cc6-b5dc-bc5903609fce | temperature | 22 | double | 1519344791 | Living Room Stairs |
| 638bad22-371d-4573-82cf-508ac6e94554 | temperature | 22.5 | double | 1519344794 | Master Bath |
| c2289509-6a37-4098-9118-d47b20cabcf7 | temperature | 17 | double | 1519344822 | basement window |
| 0d30c97b-6cd7-4bb3-b46c-36f629a15850 | temperature | 15 | double | 1519344909 | Basement Door |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 6.2 | double | 1519345000 | Sump Pump |
| 0fba1f29-80bd-4856-ad7d-462733cd89ca | power | 5.4 | double | 1519345008 | Sump Pump |
| 89431397-029b-48ba-8254-b1e7ca7a7e72 | temperature | 21.5 | double | 1519345029 | Living Room Ecobee |
| 1f842c28-e68a-487d-874d-3012783541fe | temperature | 21.0 | double | 1519345029 | Guest bedroom Ecobee |
±-------------------------------------±------±-----±---------±-----------±----------+


(Eddy) #2

Marking this thread for a future project :slight_smile:


(Andrew Mc Grath) #3

For anyone else who reviews this thread, a question:

I’ve been thinking about the fact that having to setup a server is too annoying for most people. If you were able to just install the app and then get access to a system where you could obtain access to the raw data as shown above (either via MySQL command prompt, API or file download) would that be more useful?

I kind of want to build some graphing tool on top of what I’ve got here for my own purposes, and making this available to others would only require a little extra work, so I’m considering doing it!


(Bill S.) #4

To be honest, there are ways of doing that. I’ve seen a few projects that push ST data to Google Sheets (which is what I currently use) for instance. There are also paid solutions for it as well.

As a Linux guy myself (and someone that does have his own fleet of servers, both physical and virtual, at home), I totally understand where you are coming from and your goal.

One suggestion I would make is ditch MySQL and go with a NoSQL solution. It’ll save you on both hardware and processing power. Elasticsearch (along with Kibana) would be my first choice (as it already has a rest interface), and Mongo as my second choice.

Also, sump, as in an aquarium sump??? If so, you’re a braver soul than I. I wouldn’t trust any smart plug to run my sump pumps off of. LOL


(Andrew Mc Grath) #5

Useful thoughts, thanks!

Yeah I considered the Google Sheets option, and tried it out for a bit. The issue I found was that days worth of data (for me and my larger than normal setup anyway) results in approx 3-5k rows of sensor readings, so i felt some larger and more robust database was called for if I intended to keep more than a few days worth of records. Producing reports on a Google sheet is a super nice option, i just didn’t love that they get real slow once the dataset grows into something significant.

I also considered something like a feed into a log management solution (Splunk, etc), but even then I got worried that my access to the raw data would be somehow hindered by it sitting between another application and my fingertips.

On the database choice, personally I’m less worried about the cost (It’s going to work out to be $24 / month to keep this running) and I just wanted to work with something I know really well, so I picked MySQL (Actually AWS RDS Aurora). Admittedly, for the moment at least, a relational database is total overkill and the structure is very simple (one table…) so it could really be in almost any database engine and work rather well.

The sump pump is a fun one. It is literally a hole in my basement floor (very common around here) that fills with ground water and gets pumped outside when it reaches a certain level. The objective is to pump water away from the foundation of the house, and off the basement floor. I have already got 2 of them (in case one fails), and they are on battery backups in case of a power failure, but i wanted to connect one of them to a “powerMeter” enabled device so i could see how much current is being drawn. From the current usage I can determine how often it’s turning on. My next step is to say “It hasn’t run in over XX hours, that’s unusual, go look at it to see if it’s a sensor issue or a pump issue”. I already have a water sensor in the sump pit and outside of it so that if the water does go above the normal waterline I can get alerted and go check it out. That has gone off a few times, but always erroneously (a weird spider was sitting on it, and surprisingly that triggered the alert). You’re right though, I don’t trust ST to keep it operational, so i’ve got it setup assuming that it could fail and if it does a audible alarm on the pump itself goes off (so far 1 year of runtime, no failures).