We have moved at community.getvera.com

Author Topic: openLuup: Data Historian  (Read 5944 times)

Offline streilu

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
Re: openLuup: Data Historian
« Reply #75 on: August 22, 2018, 11:30:53 am »
Hello guys!
I'm pretty new to Vera and openLuup. At first I want to thank akbooer for this awesome work!

I bought a Vera Plus some weeks ago and now I'm playing around with it a bit. I was searching for a way to archive data for analysis if scenes would work properly (for example opening my venetian blinds when my anemeter measures strong gusts, etc.).

After walking throgh DataMine and so on. I saw that you added the Historian feature to openLuup, so I setup a Raspi with openLuup and connected the Vera with Verabridge. Historian is working basically and Grafana os a really nice tool to analyse the data.

Some questions:
- Is the Historian the right tool for longterm archiving data? Is it possible to change the aggregation to save the high resolution data say for example 3 months?
- I read that the resolution thing is configured in the header of the Whisper database files, is there manual somewhere? Is it planed to add the possibility to change these configs from the webconsole?

And a short remark to binary data:
I read through the thread and have seen you guys discussing about binary values and how to process them. I'm a plant automator, so I have quite some experience with archiving analog and binary data for analysis of malfunction and things like that.
I found out that archiving of binary data (in plant automating mostly alarms and things like pump running) works best with timestamp based data triggered by a change, without any aggregation.

In most industry automation systems analog and binary signals are used in different ways.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #76 on: August 24, 2018, 10:27:13 am »
Hello guys!
I'm pretty new to Vera and openLuup. At first I want to thank akbooer for this awesome work!

Hello! You're very kind, although perhaps you should wait a bit until you discover whether it's really right for you!  Sorry for the delay in replying to your post, but somehow I missed it earlier.

Quote
I setup a Raspi with openLuup and connected the Vera with Verabridge. Historian is working basically and Grafana os a really nice tool to analyse the data.

OK, glad you found it.  This is only the first release with this functionality so there may be a few rough edges to work on.

Quote
Is the Historian the right tool for longterm archiving data? Is it possible to change the aggregation to save the high resolution data say for example 3 months?

The Data historian is actually three tools in one:
  • a way to watch all the variables
  • somewhere to store them
  • graph plotting (built-in code uses Google Charts)
In order to make it fast, (2) and (3) are all integrated, and designed to work efficiently together.  However, other apps/services can be used, as you've found, Grafana for #3, for example.  What's not obvious (and, TBH, not fully exposed to the user ATM) is that, for #2, the data can also be mirrored to external databases, in particular, an external Graphite installation and/or InfluxDB.  In order to make this happen efficiently, UDP is used as a protocol since it requires no handshake.  Both those databases are easily configured to receive UDP datagrams.

Alternatively, as you suggest, the aggregation is easily changed to retain higher resolution data.  However, this will clearly entail an increase in archive file size.  It's basically 36 bytes/point, so, e.g., 10 minutes x 3 months = 12960 x 36 = 467 kB, not too shocking (per variable.)

Quote
I read that the resolution thing is configured in the header of the Whisper database files, is there manual somewhere? Is it planed to add the possibility to change these configs from the webconsole?

Actually, the resolution, etc., is stored in the file header, but is defined by rules.  I've described this earlier in the thread, and the Graphite/Whisper rule syntax is defined here: Configuring Graphite: Storage Schemas. If you want to change the defaults, you should create the files storage-schemas.conf and storage-aggregation.conf in the history/ directory.  You can copy the default contents of these from the file openLuup/virtualfilesystem.lua.  Once defined, and suitably edited, these will override the built-in defaults.

Quote
And a short remark to binary data:
I read through the thread and have seen you guys discussing about binary values and how to process them. I'm a plant automator, so I have quite some experience with archiving analog and binary data for analysis of malfunction and things like that.
I found out that archiving of binary data (in plant automating mostly alarms and things like pump running) works best with timestamp based data triggered by a change, without any aggregation.

Yes, fair point.  The Graphite/Whisper database is ideal for analogue data, but it's a bit of a squeeze to use it for state information.  This is one reason I'm allowing mirroring to external databases.  However, the in-memory cache stores events to sub-millisecond resolution, so any 'real-time' processing that needs it is possible (for the last 1000 or so events per variable.)  A huge plus for Whisper is that it requires exactly NO maintenance and its resource utilisation is fixed at file creation.  If I could find a suitable candidate of 'binary/digital' data, then I'd consider adding it, although a real problem with Vera is that you can't tell which variables are going to be 'analog' or otherwise.

Hope it does what you need, anyway.  Don't hesitate to ask further.

« Last Edit: August 24, 2018, 12:46:59 pm by akbooer »
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline bruring

  • Jr. Member
  • **
  • Posts: 63
  • Karma: +2/-0
  • Why not automate everything?
Re: openLuup: Data Historian
« Reply #77 on: August 24, 2018, 04:35:26 pm »
Hi all,

I found this article to be quite useful to understand the actual Whisper DB file format:

http://falcolas.com/2014/12/24/graphite_whisper/

Also, for binary sensors, I have quite some success with the Sum aggregation method which shows me amount of triggers over a period of time.
Trying to make my house smarter than myself since 2012.
Vera Lite & Vera Edge UI7, 15x Fibaro, 10x Aeotec, 6x Zwave.Me, 3x GreenWave, 3x TKBHome, 1x Y-CAM, 1x Netatmo, Logitech Harmony, Google Home. ESXi with pfSense, openLuup, AltUI, Grafana and Vera Concierge amongst others.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #78 on: August 25, 2018, 11:59:32 am »
I found this article to be quite useful to understand the actual Whisper DB file format

Thanks for that, I had not seen it.  Although there is almost nothing there that is not in Graphite documentation, it's a better and more concise read.

Regarding the two 'weaknesses' it mentions at the end:
  • this is exactly the issue you ran into, more of a 'feature' than a weakness?
  • is not strictly correct for a genuine Graphite system, and even less so for my implementation. Whisper has the option to cache headers, so none of the header reads are done except when opening the file for the first time in a session.  I've gone further and it turns out that the need to read the first archive entry to establish a time baseline is totally unnecessary if you use a different algorithm for where in the circular file buffer to start writing.  It also turns out that this can be made compatible with a genuine Graphite Whisper file.

So, actually, Whisper is quite efficient, which is what it was designed to be in the first place.
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline bruring

  • Jr. Member
  • **
  • Posts: 63
  • Karma: +2/-0
  • Why not automate everything?
Re: openLuup: Data Historian
« Reply #79 on: August 26, 2018, 08:48:22 am »
I found this article to be quite useful to understand the actual Whisper DB file format

Regarding the two 'weaknesses' it mentions at the end:
1. this is exactly the issue you ran into, more of a 'feature' than a weakness?
I think the way Whisper works is very efficient and smart, but it's totally different (at least for me) from more SQLesque databases which I'm more used to. I really had to dive in but I think I've got quite some understanding of how it works now.

Quote
2. is not strictly correct for a genuine Graphite system, and even less so for my implementation. Whisper has the option to cache headers, so none of the header reads are done except when opening the file for the first time in a session.  I've gone further and it turns out that the need to read the first archive entry to establish a time baseline is totally unnecessary if you use a different algorithm for where in the circular file buffer to start writing.  It also turns out that this can be made compatible with a genuine Graphite Whisper file.

So, actually, Whisper is quite efficient, which is what it was designed to be in the first place.
Very true, I think caching can go a long way to optimise disk operations.
Trying to make my house smarter than myself since 2012.
Vera Lite & Vera Edge UI7, 15x Fibaro, 10x Aeotec, 6x Zwave.Me, 3x GreenWave, 3x TKBHome, 1x Y-CAM, 1x Netatmo, Logitech Harmony, Google Home. ESXi with pfSense, openLuup, AltUI, Grafana and Vera Concierge amongst others.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #80 on: August 26, 2018, 09:25:34 am »
I think the way Whisper works is very efficient and smart, but it's totally different (at least for me) from more SQLesque databases which I'm more used to. I really had to dive in but I think I've got quite some understanding of how it works now.

Indeed, time series databases (aka. historians) are somewhat different from relational ones.  But at least this sets you up to appreciate other options such as InfluxDB, if necessary.
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline bruring

  • Jr. Member
  • **
  • Posts: 63
  • Karma: +2/-0
  • Why not automate everything?
Re: openLuup: Data Historian
« Reply #81 on: August 26, 2018, 09:31:02 am »
Indeed - looking forward to using InfluxDB as well! Seems like the best of both worlds and gives you a lot of query options in Grafana. I saw some references in your Historian code, if you need help testing or something else, let me know.
Trying to make my house smarter than myself since 2012.
Vera Lite & Vera Edge UI7, 15x Fibaro, 10x Aeotec, 6x Zwave.Me, 3x GreenWave, 3x TKBHome, 1x Y-CAM, 1x Netatmo, Logitech Harmony, Google Home. ESXi with pfSense, openLuup, AltUI, Grafana and Vera Concierge amongst others.

Offline reneboer

  • Beta Testers
  • Hero Member
  • *****
  • Posts: 1574
  • Karma: +110/-31
Re: openLuup: Data Historian
« Reply #82 on: September 20, 2018, 11:35:34 am »
Hi akbooer,

Took me some time to look at this so just installed the latest development version 2018.08.23, very nice indeed. As usual.

I have two Vera's bridged and in the openLuup Console Historian view the variables of the first Vera (range 10000) there is a nice hyperlink to show the graph. The hyperlinks are missing for the second Vera though (range 20000).

If you need some debug info let me know.

Cheers Rene
2xVeraLite, VeraEdge, openLuup, ALTUI, 20 switches, 10 dimmers, 20 sensors, 10 scene controllers, 1 Harmony Hub, many plug-ins. Not enough time.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #83 on: September 20, 2018, 01:18:33 pm »
Thanks for that!

Interesting.  I have 3 linked Veras and I thought everything was working OK.  Let me check again.
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #84 on: September 20, 2018, 01:27:22 pm »
So you're saying that links like these are missing?

3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline reneboer

  • Beta Testers
  • Hero Member
  • *****
  • Posts: 1574
  • Karma: +110/-31
Re: openLuup: Data Historian
« Reply #85 on: September 21, 2018, 03:57:26 am »
Correct,

Could it be because my first Vera has device numbers going over 10000 and thus show with device IDs on the Verabrige from 10000 - 23500? the second bridged Vera has the 20000 range. I guess I need to up that? However, there goes my DataMine data and scenes  ???

Cheers Rene
2xVeraLite, VeraEdge, openLuup, ALTUI, 20 switches, 10 dimmers, 20 sensors, 10 scene controllers, 1 Harmony Hub, many plug-ins. Not enough time.

Offline reneboer

  • Beta Testers
  • Hero Member
  • *****
  • Posts: 1574
  • Karma: +110/-31
Re: openLuup: Data Historian
« Reply #86 on: September 21, 2018, 04:12:34 am »
Guess what.

I enabled the historian directory in the startup lua and magic. All links now show!

Cheers Rene
2xVeraLite, VeraEdge, openLuup, ALTUI, 20 switches, 10 dimmers, 20 sensors, 10 scene controllers, 1 Harmony Hub, many plug-ins. Not enough time.

Offline akbooer

  • Moderator
  • Master Member
  • *****
  • Posts: 6387
  • Karma: +291/-70
  • "Less is more"
Re: openLuup: Data Historian
« Reply #87 on: September 21, 2018, 08:29:16 am »
Could it be because my first Vera has device numbers going over 10000 and thus show with device IDs on the Verabrige from 10000 - 23500? the second bridged Vera has the 20000 range. I guess I need to up that? However, there goes my DataMine data and scenes  ???

I knew someone would manage this one day.  It just had to be you!

I would have to think hard about the effects of that, but at the very least, I think that devices in the wrong-numbered block will show the wrong node name.  What duplicate device numbers do, I hate to think...

...time to tidy up your Vera device numbering.
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline reneboer

  • Beta Testers
  • Hero Member
  • *****
  • Posts: 1574
  • Karma: +110/-31
Re: openLuup: Data Historian
« Reply #88 on: September 21, 2018, 12:23:36 pm »
Could it be because my first Vera has device numbers going over 10000 and thus show with device IDs on the Verabrige from 10000 - 23500? the second bridged Vera has the 20000 range. I guess I need to up that? However, there goes my DataMine data and scenes  ???

I knew someone would manage this one day.  It just had to be you!

I would have to think hard about the effects of that, but at the very least, I think that devices in the wrong-numbered block will show the wrong node name.  What duplicate device numbers do, I hate to think...

...time to tidy up your Vera device numbering.
LOL.

I wish I could sort of reset the Vera device numbering. At one point to Worldweather plugin went haywire and kept recreating its child devices. I did once change all the device numbers over 10000, but when you add a device the Vera just keeps numbering up. I guess I need to bit the bullet on the openLuup side.

For the historian view, maybe you can test with and without the on disk archiving and see if all bridged variables show an hyperlink.

Cheers Rene
2xVeraLite, VeraEdge, openLuup, ALTUI, 20 switches, 10 dimmers, 20 sensors, 10 scene controllers, 1 Harmony Hub, many plug-ins. Not enough time.

Offline ChrisTheC

  • Full Member
  • ***
  • Posts: 146
  • Karma: +4/-1
  • Baltimore, MD & Washington, DC
Re: openLuup: Data Historian
« Reply #89 on: September 21, 2018, 05:10:49 pm »
Correct,

Could it be because my first Vera has device numbers going over 10000 and thus show with device IDs on the Verabrige from 10000 - 23500? the second bridged Vera has the 20000 range. I guess I need to up that? However, there goes my DataMine data and scenes  ???

Cheers Rene

Rene,
While wasting your time with my response, I'm also laughing at myself.
My devices are up to 93, but being an obsessive/compulsive type (read anal), I'm having heart palpitations because of the missing numbers in between.

I need to calm down now.

 :-[

Chris
Karma for all you do here.

openLuup with ALTUI, ALTHUE, 8x iPhoneLocators, Harmony Hub, Honeywell Wifi T-stat, Foscam Fi8608w & 2x R2, bridged to . . .
Vera 3/UI5 with Schlage BE469NX, 3x EZMotion+, 3x Ecolink PIR, 3x Schlage door sensors, 2x Aeotec micro switches, 2x Aeotec appliance switches, Leviton RZM10 & DZMX1, Wemo