[quote=“airedale, post:379, topic:181793”]So… you just solved a problem that was already solved?
Barrier Breaker is what is running on the VeraEdge. Still not working on VeraEdge.[/quote]
Wow, never mind then.
Hello,
Thanks Akboer for this great plugin. I have installed datayours about a month ago without any problems. (vera lite UI5). I have created a CIFS share on a USB stick connected to my Tomato router. I have also been able to create some graphs and all is working fine.
My question starts here: Now I have gathered a month of data, what else can I do with it ? I would love to be able to export the data in csv format and use it further in excel. Is this possible?
Just as a concrete example of what I would like to do: I have 6 electric heaters in my house which are all reporting their energy usage in watts. How can I use the data captured in the whisper data base to create a weekly report, ideally in excel, which gives me the actual energy cost per week?
Thanks a lot for any advice on this.
Jacques
I’m in the middle of re-working all the DataYours modules to play nicely with UI7. Actually, they already do, but I’m taking the opportunity to make a few changes:
[ul][li]this will be an App Store release (after initial testing)[/li]
[li][tt]DataYours[/tt] will now create a bona fide Vera device[/li]
[li]all the (optional) data ‘daemons’ will appear as child devices (mini plugins) of [tt]DataYours[/tt]:
[list]
[li][tt]DataWatcher[/tt] - front end which watches for Vera device variable changes[/li]
[li][tt]DataCache[/tt] - archival storage using the [tt]Whisper[/tt] database[/li]
[li][tt]DataGraph[/tt] - the graphics engine[/li]
[li][tt]DataDash[/tt] - the dashboard user interface[/li]
[/list]
[/li]
[li]the [tt]carbon.conf[/tt] configuration file will no longer be used[/li]
[li]basic configuration of the various ‘daemons’ is done through their own device variables[/li]
[li]all the ‘daemons’ can report separately to syslog[/li]
[li]the dashboard interface remains the primary input for watching and plotting[/li]
[li]an additional optional plugin will interface to a [tt]dataMine[/tt] database[/li][/ul]
The goal is to make installation and configuration much easier, plus future extensions. As before, [tt]DataYours[/tt], and some or all of its components, may be installed on multiple machines and made to function as an integrated, but distributed, whole (without bridging the Veras.) I already have this running together on three VeraLites (under UI5) and one VeraEdge (on UI7).
So things are coming along, and I just wanted to confirm that I’m moving on getting an App Store release (at last!)
That is awesome! Will this be available for die hard U1.5 users?
Yes, it’ll run on UI5 too. Most of the code is totally machine independent, so doesn’t even need Vera to run. I’m porting it to a BeagleBone Black as well. It’s really only the configuration bits which I’m adding specifically for Vera.
Super!!
Great!
This is one of the most used plugins by me on Vera. It is as valuable as PLEG for me. And when PLEG became paid I first thought that was not so nice but now I think the author deserves to be paid for his excellent work and so do you akbooer! So before publishing to the app-store I would suggest to make it a paid version. People would have a choice: stay with the current version for free or buy a license for the newer versions to come.
This sort of feedback is always welcome!
My question starts here: Now I have gathered a month of data, what else can I do with it ? I would love to be able to export the data in csv format and use it further in excel. Is this possible?Yes, absolutely. The [tt]DataGraph[/tt] component not only has the capability to render data to SVG graphics, but also to CSV and JSON formats. If you copy the contents on any plotting link (day / week / month) of the variable you want and paste that into a new browser window, you'll get a plot of the variable over that timescale. If you add [tt]&format=csv[/tt] or [tt]&format=json[/tt] to the URL, you will get data in that format. CSV is a bit verbose because I have (hopefully correctly) fully followed the [url=http://graphite.readthedocs.org/en/latest/render_api.html#data-display-formats]Carbon/Graphite standard[/url] (on which [tt]DataYours[/tt] is based) and it seems to need the channel name on each line. JSON is more compact. Either way, you could access this data directly from any program which can issue an HTTP request and read the response.
For example, for the last hour, my external temperature in CSV is given by:
[tt]ht[/tt][tt]tp://172.16.42.10:3480/data_request?id=lr_render2&from=-1h&format=csv&target=Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature[/tt]
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 14:30:00,nil
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 14:40:00,9.2
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 14:50:00,nil
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 15:00:00,9.1
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 15:10:00,nil
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 15:20:00,8.9
Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature,2015-02-10 15:30:00,nilIn JSON:
[tt]ht[/tt][tt]tp://172.16.42.10:3480/data_request?id=lr_render2&from=-1h&format=json&target=Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature[/tt]
which is much more compact…
[{
"target": "Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature"
, "datapoints": [
[null, 1423578600],
[9.2, 1423579200],
[null, 1423579800],
[9.1, 1423580400],
[null, 1423581000],
[8.9, 1423581600]
]
}]…the nil (or null) values are to be expected since the granularity of this particular archive is finer that of the updates from the temperature sensor, which anyway only gives a new value when things change. You’d have to deal with this in your own handling of the data.
Just as a concrete example of what I would like to do: I have 6 electric heaters in my house which are all reporting their energy usage in watts. How can I use the data captured in the whisper data base to create a weekly report, ideally in excel, which gives me the actual energy cost per week?
Longer term, I think there’s a definite need for energy-specific reporting to be added to [tt]DataYours[/tt] (just like there was in [tt]dataMine[/tt], if that means anything to you.) The new architecture that I’m currently using for the UI7-compatible version is designed to simplify future extensions such as this.
Hi akbooer,
I’ve update my Veraedge to 1.7.961. Do you think I can install your actual app on it ? Now I’m using it on Veralite UI5 .
If you have a new version I’m glad to test.
Thanks
donato
[quote=“d55m14, post:389, topic:181793”]I’ve update my Veraedge to 1.7.961. Do you think I can install your actual app on it ? Now I’m using it on Veralite UI5 .
If you have a new version I’m glad to test.[/quote]
Thanks for the offer. This would be great, especially since you are already running on UI5. We can incrementally install components on the Edge, and get them to talk with the VeraLite… Since we can’t yet install CIFS on the Edge.
My question starts here: Now I have gathered a month of data, what else can I do with it ? I would love to be able to export the data in csv format and use it further in excel. Is this possible?Yes, absolutely. The [tt]DataGraph[/tt] component not only has the capability to render data to SVG graphics, but also to CSV and JSON formats. If you copy the contents on any plotting link (day / week / month) of the variable you want and paste that into a new browser window, you'll get a plot of the variable over that timescale. If you add [tt]&format=csv[/tt] or [tt]&format=json[/tt] to the URL, you will get data in that format. CSV is a bit verbose because I have (hopefully correctly) fully followed the [url=http://graphite.readthedocs.org/en/latest/render_api.html#data-display-formats]Carbon/Graphite standard[/url] (on which [tt]DataYours[/tt] is based) and it seems to need the channel name on each line. JSON is more compact. Either way, you could access this data directly from any program which can issue an HTTP request and read the response.For example, for the last hour, my external temperature in CSV is given by:
[tt]ht[/tt][tt]tp://172.16.42.10:3480/data_request?id=lr_render2&from=-1h&format=csv&target=Vera-35104005.308.urn:upnp-org:serviceId:TemperatureSensor1.CurrentTemperature[/tt]
Great! I will give this a try. Once I get the data into excel I will be able to slice and dice it anyway I want.
Again thanks a lot,
Jacques
[quote=“akbooer, post:390, topic:181793”][quote=“d55m14, post:389, topic:181793”]I’ve update my Veraedge to 1.7.961. Do you think I can install your actual app on it ? Now I’m using it on Veralite UI5 .
If you have a new version I’m glad to test.[/quote]
Thanks for the offer. This would be great, especially since you are already running on UI5. We can incrementally install components on the Edge, and get them to talk with the VeraLite… Since we can’t yet install CIFS on the Edge.[/quote]
Hi akbooer,
I’ve just moved to VeraEdge some devices (Current Cost, wall plug, thermostat, etc.) so is it possible to archive these informations (watts, Kwh, temperature, etc…)
tnks
donato
Good luck - please do remember to tell us all how you get on.
@d55m14 (and anyone else with VeraEdge already running [tt]DataYours[/tt] on another system)…
OK, let’s try this. This is, effectively, the VERSION 3 Beta release. I’m confident of the stability and operation of the UI7 versions of the underlying Graphite system daemons, so let’s set up [tt]DataYours[/tt] on your Edge to talk to your existing installation.
This won’t need any changes to your existing system aside from adding the IP address of your Edge to the [tt]carbon.conf[/tt] file - you do this by adding it to the very end of your file (obviously with your particular IPs) thus:
[dashboard]
VERAS = 172.16.42.1:3480, 172.16.42.2:3480The new UI7 architecture starts with the installation of the basic [tt]DataYours[/tt] plugin. The files for this are attached. Simply manually create a new device (Develop Apps > Create Device) with a [tt]device_file[/tt] of type [tt]D_DataYours7.xml[/tt]. We’ll not bother with device icons yet. Restart twice, and you should end up with the following variables under the Advanced tab:
[ul][li]SYSLOG - if you run a syslog server, then set this to the IP and port number eg. [tt]172.16.42.112:514[/tt][/li]
[li]DAEMONS - this is going to list the optional components which you want to start up (you don’t have any yet)[/li][/ul]
On its own, the only thing of the slightest interest that it does is update a device variable saying how much app memory it is currently using.
…and now for the first [tt]DataYours[/tt] daemon: [tt]DataWatcher[/tt], which is the data collection front-end, and all you need if you’re going to be sending the data to another installation on another machine (as we are.)
Steps to follow:
[ol][li]upload the attached files in the usual way[/li]
[li]modify the [tt]DataYours[/tt] device parameter [tt]DAEMONS[/tt] to [tt]Watcher[/tt][/li]
[li]RESTART (twice)[/li][/ol]
This should create a child device with the following device variables under the Advanced tab:
[ul][li][tt]SYSLOG[/tt] - once again you can put the same (or different) syslog server address and port here[/li]
[li][tt]DESTINATIONS[/tt] - this is where you can put possibly multiple IP and PORT destinations for your data[/li][/ul]
To link to your existing UI5 [tt]DataYours[/tt] system (and database) you simply need [tt]DESTINATION to be set to something like “172.16.42.10:2003”[/tt] (port 2003 is the default for the Graphite/Carbon system to be listening on.) RESTART and refresh browser page.
Hey presto! (I hope) you should now see your VeraEdge appear on the devices page of your UI5 system dashboard, and on its configuration page, and you can turn on watching variables on your VeraEdge remotely. Once some data accumulates, you should be able to select them for plotting as usual.
That’s the theory… now tell me what is not working for you (or, on a good day, what IS).
Hi akbooer,
I previously installed on VeraEdge Datayours V2. I’ve to cancel or do something prior to install the Beta ?
tnks
donato
… and for now i’d like to plot only variables/device defined on VeraEdge
Oh, OK, good question. I thought we were doing a clean install. All that you HAVE to do is remove the [tt]require[/tt] statements in your Startup Lua that refer to the old install. Something else you COULD do is delete the old [tt]Dataxxxx.lua/xml/json[/tt] files from [tt]/etc/cmh-ludl/[/tt].
.... and for now i'd like to plot only variables/device defined on VeraEdgeAh, a full install then. I'm just finishing off changes to the back-end daemons ([tt]DataGraph[/tt] and [tt]DataDash[/tt].) You'll also need to run a database locally (since we can't mount CIFS yet.) This is why I was suggesting you use your existing UI5 install as a backend. It really will have no impact on a running system.
Hi akbooer,
in UI5 I’m using a USB stick connected to Veralite to store the data. The same is on VeraEdge.
In this configuration (USB stick directly connected to VeraEdge) is it possible to use a complete installation ?
tnks
donato
Great! I will give this a try. Once I get the data into excel I will be able to slice and dice it anyway I want.
Good luck - please do remember to tell us all how you get on.
Ok, so I did a quick test. It was indeed very easy to create the html string for last week’s energy usage and output this to csv.
In Excel (2013) I used the html string to download the data straight into an empty worksheet and I was able to create a quick graph showing the daily energy usage and cost. see also linked screenshot.
[url=http://1drv.ms/1CUZVTy]http://1drv.ms/1CUZVTy[/url]
Quick question, just to be sure I am looking at the right data here. Is the daily energy usage indeed the accumulation of all measuring points during 24 hours or should I apply a different calculation?
Cheers,
Jacques