Feed data to external database

Hi Bob , Community,
I want to find out if there is a way to get the logs into an external database ( maybe via http ). I am no coder but will hire a coder to do so if it is at all possible. I need the sensor data with its timestamps to be used in a differing platform. ( im battling to get devs for EmonCMS and InfluxDB).

I look forward to your reply.


Hello Wayne,

The data in the logs is kept in double binary and the values stored are probably not what you want anyway. There are classes and methods in the IoTaWatt source to access the log files and to return the various metrics. In particular the IotaLog class knows how to locate and return records by timestamp, and the IotaScript class knows how to process scripts and produce the various metrics from the raw records.

You would still need a way to copy the raw datalog for external processing, and right now the only way I can see to do that is top stop the IoTaWatt and remove the SDcard to copy it on a different machine. Also, the ESP8266 stores the data in little-endian format, so if you are using a big-endian processor, the bytes need to be resequenced.

There is another alternative. The latest releases, now in ALPHA auto-update, contain a new query API. It is documented here: https://docs.iotawatt.com/en/staged/query.html

It’s accessed via HTTP and returns either Json or CSV. The limitation is that doing a large query can dim the lights and have a detrimental impact on sampling. Keeping requests to 360 rows should minimize the impact and will return an hours worth of data with 10 second grouping. It doesn’t really matter how many series you are requesting as all of the series for a particular timestamp are stored in the same datalog record.

An external program could make repeated requests and aggregate the data into a larger file, in whatever format you like. As long as the requests are small, it should not have a serious impact on the IotaWatt’s primary mission of sampling power.

Thanks for the detailed response Bob.

The new API seems like a good start for me. 2 quick questions:

  1. From what i understand i could request Json or CSV in a limited number of rows - could this be for example all the channels per 30 minute granularity over a 24 hour period ( i.e 48 rows )? If so i would only need to poll once per day.
  2. Is there any way other than port forwarding to get an http connection to the device ? ( from my limited knowledge i would believe that the internal web server could push the data to an API without the need to risk local lan security ? )

Look forward to your feedback

That would be a fairly trivial query for IoTaWatt, even if you requested multiple metrics (Watts, Wh, Amps, etc.), although the response table would be pretty big.

Assuming that your router has no vulnerabilities when port forwarding is enabled, I don’t believe the IoTaWatt could be used to create a wormhole into your LAN. I say that because the firmware is pretty straightforward and uses a pretty secure signature verification to update. That said, there are lower level layers in the IP stack that I cannot vouch for, notwithstanding they are open source as well (lwip) and well used.

If the question whether the IoTaWatt itself can be secured when there is an open port to the internet, the answer is sort-of and indirectly.

IoTaWatt can’t do HTTPS. All traffic (except a special case to Emoncms) is plaintext POST payloads. So anyone positioned in the path could see the data passing through.

You can use a reverse proxy to add HTTPS to the portion of the communication exposed to the internet port. I use that system with a Rpi running Nginx exposing a HTTPS port to the internet and relaying to the IoTaWatt(s). I use openSSL and Lets Encrypt certs. This is mainstream protection against man-in-the-middle attacks and uses secure encryption.

There is still a need to authorize the HTTPS session, and the IoTaWatt uses Digest authentication when a password is specified in setup. Digest authentication does not expose the password to onlookers, and in fact, the actual password is not stored in the IoTaWatt.

So you can restrict access using the already available Digest Authentication, and you can get encryption and domain authentication using a reverse proxy.

Thanks for that Bob. Thinking out aloud, is there any reason I could not get a Dev to utilise the current influxdb webserver API in Iotawatt to send to a custom Mariadb ( could we simply mimic the request variables as the URL is an open field as it stands ). I’m far more interested in a push approach like this.

Look forward to your feedback

If you’re talking about modifying the open source to do what you want, I’m sure that could be done. The downside is that you lose the automatic update for new features, but there are users out there that have had their own firmware (and hardware) running for a long time.

That said, pushing isn’t as straightforward as you may think. IoTaWatt pushes to influxDB, Emoncms and PVoutput. While it would be fairly straightforward to simply send real-time data to those services, IoTaWatt doesn’t do that. All three of those servers have the ability to accept queries in one form or another that can be used to indicate the timestamp of the last data posted. IoTaWatt performs that query on startup of the service and initiate data upload beginning where it left off. Moreover, the posting protocols are synchronous so IoTaWatt requires acknowledgement of successful posting before moving on to the next frame.

All this insures data integrity. I recently ripped out my old WiFi router and installed a new mesh system. It took a few hours to get everything up and running. I have two IoTaWatt monitoring home usage and uploading to all three servers. A few minutes after getting everything working, the IoTaWatt had synched with the servers and uploaded all of the data collected while the WiFi was down. No holes.

So if you are pushing data, without the ability to figure out where to start or to determine if data packets are being accepted, you will end up with an incomplete record. For some folks doing only real-time monitoring, that may be enough. If you pull the data, you can use the existing query unmodified, and you can control whether or not to request and post historical data.

New user here… Had the system up and running in about two hours, including installation of the CTs in my distribution box. Very well done.

That said, I am as interested as several other commenters here in building a local database for collecting and reporting power/energy. This is for practice, my day job is embedded development for industrial monitoring and controls, but not really the higher level data collection and analysis. Thus the interest in data collection…

I’m guessing the most straightforward way to implement home-built data collection would be to emulate the InfluxDB interface locally. This preference is based on the tagging mechanism used to provide unique identification of measurement subset data blocks. Does that seem reasonable?

Being practice for data collection and analysis, I am also interested in selecting the measurement parameters that are most close to the original measurements performed by the unit, for transmission to my little server. I’m hoping to validate my analysis project results against the built-in reporting and graphing provided by the IotaWatt unit. So, which parameters would make the most sense for reporting?