SQl or RRDtool on Vera ?

Developer's Corner General Dev Discussions
1

Hi !

As we know, Vera is based on DD-WRT.
We have a little space, but since the last upgrade, Usb Key is supported for the logs.

So, do you think it would be possible to install a SQL database (or more light), or RRDtool, on the Vera, to have charts, per example, directly on the Vera ?

Anybody have tested this ??

2

Nobody interested ?? ???

3

Not so much as “not interested” but more that I wouldn’t attempt this on Vera itself. It’s on an older version of Linux, which will make it harder to get “fresh” packages, and it already is tight on RAM to run stuff in, which will cause you all sorts of instability problems.

I plan on using RRD, eventually, but will do it on another [Low Power] box that has both the RAM and the Storage to do RRD along with potentially the graphing component (although I may just use Google Charts since it’s richer).

4

@guessed

HI, How did you get on with your plans - I have a RRD currently running on my NAS to capture the readings from my Current Costs Energy monitor - Plus I have also just received my Raspberry Pi so there’s scope to use that too. I would like to see Ver

How are your developments with RRDs (and iPad website) coming along?

5

If you’re looking for charts, then I’ve just uploaded a UI5 version of dataMine. This stores data in its own format (not RRD or SQL) - effectively it’s CSV. It then supports graphing - directly on Vera…

http://forum.micasaverde.com/index.php/topic,7750.0.html

Chris

6

[quote=“parkerc, post:4, topic:167812”]@guessed

HI, How did you get on with your plans - I have a RRD currently running on my NAS to capture the readings from my Current Costs Energy monitor - Plus I have also just received my Raspberry Pi so there’s scope to use that too. I would like to see Ver

How are your developments with RRDs (and iPad website) coming along?[/quote]
Not yet, I’ve been building out a Server for some friends which took longer than expected. Next experiments involve the router device (not Vera) but also not for RRD just yet as I need to get some other stuff running on it first.

BTW: The intent of operating this isn’t for iPad, but more generically using SVG and/or Google Charts to display on any kinda of device… totally independent of Vera, for all sorts of datasets.

@Chris’s stuff looks great so if you need something now, and you plan to have a Vera long-term, that seems like a very good way to go.

7

Hi Cedric,
We have already been in touch per email. I have actually such solution running and wanted to propose you an article for your blog so for a while I am pushing the topic here :slight_smile:

Sent from my GT-I9100 using Tapatalk 2

8

Hi ,

Just curious if anyone can share any progress in this area?

@chevdor - you mentioned tat you had something running?

@guessed - how is your rrd and google chart project going?

9

… slowly, just background research at this time since I have a few other items on the plate. I was planning to use [tt]collectd[/tt], on another OpenWRT box, with it’s [tt]collectd-mod-curl-json[/tt] plugin, but that doesn’t seem to have been ported. This would have made it somewhat easy to pull&parse the [tt]user_data[/tt] JSON from a remote Vera unit.

Right now I’m looking at having to do it solo using the [tt]collectd-mod-exec[/tt], which will be a bunch more work to piece together all the parsing bits.

The OpenWRT-based host was easy to get setup. I’m using a [tt]TP-Link WL-703N[/tt] powered off one of my spare USB Ports on my AV system, so my data collector will be nicely isolated from Vera.

10

@guessed…

You know me, all ideas and desire, yet still no where near the skills.

I have an rrd that is gathering (kw) energy (current cost) data all the time, all running on a Linux NAS capturing temp and current energy usage constantly.

Is there a way for a ‘thermostat’ device to be set up that looks at the temp value in an rrd and maybe for another ‘Energy’ device to show usage? I followed the instructions here
http://www.jibble.org/currentcost/And created the following …

rrdtool create powertemp.rrd --step 5
DS:Power:GAUGE:180:0:U
DS:Temperature:GAUGE:180:U:U
RRA:AVERAGE:0.5:1:3200
RRA:AVERAGE:0.5:6:3200
RRA:AVERAGE:0.5:36:3200
RRA:AVERAGE:0.5:144:3200
RRA:AVERAGE:0.5:1008:3200
RRA:AVERAGE:0.5:4320:3200
RRA:AVERAGE:0.5:52560:3200
RRA:AVERAGE:0.5:525600:3200
RRA:MIN:0.5:1:3200
RRA:MIN:0.5:6:3200
RRA:MIN:0.5:36:3200
RRA:MIN:0.5:144:3200
RRA:MIN:0.5:1008:3200
RRA:MIN:0.5:4320:3200
RRA:MIN:0.5:52560:3200
RRA:MIN:0.5:525600:3200
RRA:MAX:0.5:1:3200
RRA:MAX:0.5:6:3200
RRA:MAX:0.5:36:3200
RRA:MAX:0.5:144:3200
RRA:MAX:0.5:1008:3200
RRA:MAX:0.5:4320:3200
RRA:MAX:0.5:52560:3200
RRA:MAX:0.5:525600:3200

It is now regularly updated with temp reading and realtime energy (kw) usage.

11

Any thoughts on how a device could be set up that takes readings from an external dB (e.g an rrd) and then present them in the UI?

12

Presuming the RRD is local to/hosted on, Vera I’d guess that something like the following might work:
RRDtool - RRDLua

specifically [tt]rrd.info(…)[/tt], or maybe [tt]rrd.fetch(…)[/tt]

If the RRD isn’t local, then you’ll have to first work out how to reach out [from Vera] to wherever the RRD is located in order to get access to the values.

13

Hi guessed.

The rrd is located at e.g.

//192.168.1.111/var/lib/rrd/powertemp.rrd

If it’s easier, maybe I should look to try and work out how to Export the data to something like XML at regular intervals e.g. ?

http://oss.oetiker.ch/rrdtool/doc/rrdxport.en.html

Is that a better route, and easier for Vera to look at external XML data ?

14

Given that you already have some sort of webserver, you can also process it locally, in whatever scripting env it has, to avoid pulling all the data over the wire constantly. I

t depends upon how much of that data you want to process in Vera each time… All, or just the latest or acg value!

15

The latest information would be fine, understanding how I could present the data for Vera would be good. As the rrd contains both temp and kw usages, being able the have two devices on my dashboard representing them would be great !

The question is how ?

I already have pearl script running the following…

#!/usr/bin/perl -w

Reads data from Current Cost and energy devices via serial port. (code take from the web)

use strict;
use Device::SerialPort qw( :PARAM :STAT 0.07 );

my $PORT = “/dev/ttyUSB0”;

my $ob = Device::SerialPort->new($PORT);
$ob->baudrate(57600);
$ob->write_settings;

open(SERIAL, “+>$PORT”);
while (my $line = ) {
if ($line =~ m! ([-\d.]+).0*(\d+)!) {
my $temp = $1;
my $watts = $2;

system (“rrdtool update /var/lib/rrd/powertemp.rrd N:$watts:$temp”)

}

}

How best should this data be worked , to make it viisble in Vera ?

16

If you’re in an external script, you can use URL’s to “push” values back into Vera using variableset:
http://wiki.micasaverde.com/index.php/Luup_Requests#variableset

You’ll have to have a Virtual Device of some sort to push the data back to, but it’ll work. I just tested it by pushing a Wattage value back to one of my watt-meters.

It looked something like:

    http://192.168.x.x:3480/data_request?id=variableset&DeviceNum=yyyy&serviceId=urn:micasaverde-com:serviceId:EnergyMetering1&Variable=Watts&Value=0

You’d just add the relevant commands into whatever script is being used to capture this in RRD.

17

Hi guessed,

Thanks for responding, please forgive my non technical ignorane, but are you saying I need to modify the code of that pearl script of the other linux box collecting all this data, so it sends? (or creates?) a URL with the value being pulled from the temp/energy device?

How do those URLs send data to vers, if they are being created on another machine?

Huge thanks in advance.

18

Yes, you can modify the Perl script to do what you’d want. A normal plugin runs on Vera and “pulls” data from a device. In this case though you have something external to Vera that will “push” the data to Vera. It’s already got the most recent value, so you’d just need to formulate the command to call the URL (and put the value into the parameter)

By doing it this way, you completely avoid having to parse a largish XML file in a Vera plugin, and overall you distribute the processing workload over more machines.

In the URL example I show below, I’m “pushing” a value of “0” Watts into a Device (yyyy in the URL, but would be a number of a device from your system).

19

Thanks again guessed.

Ok, creating a virtual as that sounds like a world of adventure by itself - so I’ve started another post to do that bit, as I could only find details on a few,

http://forum.micasaverde.com/index.php/topic,11098.0.html

and then I can use this tread as a way to work on extracting or working with external SQL or rrd data with Vera

20

Hi,

Ok, speaking to a friend who helped me before I might have the code needed for the pearl script to send temp or Energy data to Vera.

However what device do I install in Vera to complete the URL , your URL suggests an EnergyMetering1? However under UI5 I can only see a D_PowerMeter1 device in the Luup file list?