We have moved at community.getvera.com

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

Offline powisquare

  • Full Member
  • ***
  • Posts: 146
  • Karma: +1/-0
Re: openLuup: Data Historian
« Reply #60 on: July 29, 2018, 08:18:26 am »
Have updated openLuup - AltUI/More/plugins - typed development into Update box and hit Update Now. That timeout is indeed for the problem box. It is connected via wifi so I will try to get it wired into the network asap. Will see what happens now and report back.

Offline bruring

  • Jr. Member
  • **
  • Posts: 63
  • Karma: +2/-0
  • Why not automate everything?
Re: openLuup: Data Historian
« Reply #61 on: August 18, 2018, 04:53:58 pm »
Hi akboer,

I've just installed the latest development release (18.8.10) of openLuup to use Data Historian.
I've set the startup attribute to the right directory, and after a reboot it nicely creates all the Whisper files.

I can also see the logging in the console, it shows graphs. However, the Whisper files don't seem te get updated so it seems it's only logging to memory. This is my startup log:

Code: [Select]
2018-08-18 22:45:28.345   openLuup.historian:: starting data historian
2018-08-18 22:45:28.345   openLuup.historian:: using on-disk archive: /etc/cmh-ludl/history/
2018-08-18 22:45:28.346   openLuup.historian:: Graphite schema/aggregation rule sets: 15/3
2018-08-18 22:45:28.346   openLuup.historian:: disk archive storage rule sets: 9
2018-08-18 22:45:28.346   openLuup.historian:: using memory cache size (per-variable): 1024

Anything I can try?
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 #62 on: August 18, 2018, 06:12:50 pm »
If it's created the files, then the file directory permissions must be OK. 
  • I assume that this problem arose after restarting the system?
  • Is the openLuup.Historian.Directory attribute still defined? (from the log, it looks like it is...)
  • Do you have code in Lua Startup which sets it?

It would be diagnostic to share the Console > Files > History DB and Console > openLuup > Parameters pages.
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 #63 on: August 19, 2018, 04:55:47 am »
Thanks for your reply.

These are the parameters (I've used a full path just to be sure):
Code: [Select]
{
  "Backup":{
    "Compress":"LZAP",
    "Directory":"backup/"
  },
  "DataStorageProvider":{
    "-- Graphite = '127.0.0.1:2003'":"  -- EXAMPLE Graphite UDP port",
    "-- Influx = '172.16.42.129:8089'":"-- EXAMPLE Influx   UDP port"
  },
  "HTTP":{
    "Backlog":2000,
    "ChunkedLength":16000,
    "CloseIdleSocketAfter":90
  },
  "Historian":{
    "-- Directory   = 'history/'":"-- on-disc archive folder",
    "CacheSize":1024,
    "DataYours":"/etc/cmh-ludl/whisper/",
    "Directory":"/etc/cmh-ludl/history/",
    "Graphite_UDP":"",
    "InfluxDB_UDP":""
  },
  "Logfile":{
    "Incoming":"true",
    "Lines":2000,
    "Name":"logs/LuaUPnP.log",
    "Versions":5
  },
  "POP3":{
    "Backlog":32,
    "CloseIdleSocketAfter":600,
    "Port":11011
  },
  "SMTP":{
    "Backlog":100,
    "CloseIdleSocketAfter":300,
    "Port":2525
  },
  "Scenes":{
    "-- set Prolog/Epilog to global function names ":" to run before/after ALL scenes",
    "Epilog":"",
    "Prolog":""
  },
  "Status":{
    "CpuLoad":"0.9%",
    "IP":"172.20.2.10",
    "MemAvail":"23.5 Mbyte",
    "MemFree":"5.6 Mbyte",
    "MemTotal":"245.8 Mbyte",
    "Memory":"12.2 Mbyte",
    "StartTime":"2018-08-18T22:45:27",
    "Uptime":"0.5 days"
  },
  "UserData":{
    "Checkpoint":60,
    "Name":"user_data.json"
  },
  "Version":"v18.8.10",
  "Vnumber":180810

And a part of my History DB log:
Code: [Select]
Data Historian Disk Database, 2018-08-19 10:54:30 
       
   updates/min: 0.0,  time/point: -nan ms (cpu: -nan ms),  directory: /etc/cmh-ludl/history/ 
       
                                 archives       (kB)  #updates   filename (node.dev.srv.var) 

                                 openLuup 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.CpuLoad 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.HouseMode 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.MemAvail_Mb 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.MemFree_Mb 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.MemTotal_Mb 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.Memory_Mb 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.Uptime_Days 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.2.openLuup.Vnumber 

                             EventWatcher 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.AppMemoryUsed 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.CacheSize 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.CpuLoad05 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.Debug 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.MemAvail 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.MemFree 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.7.EventWatcher1.Uptime 

                                DataYours 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.9.DataYours1.AppMemoryUsed 
                5m:7d,1h:30d,3h:1y,1d:10y        335             0.9.DataYours1.UDP_RECEIVER_PORT 

                            Office Lights 
         1m:1d,10m:7d,1h:30d,3h:1y,1d:10y        351             45103840.6.SwitchPower1.Status 

                       Office Wall Lights 
         1m:1d,10m:7d,1h:30d,3h:1y,1d:10y        351             45103840.7.SwitchPower1.Status 

Also, LuaUPnP.log doesn't show any errors either.
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 #64 on: August 19, 2018, 05:30:09 am »
Hmmm...

Can you just try the relative path instead (just history/) ?

...or did you already?
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 #65 on: August 19, 2018, 06:06:38 am »
That's it!
First I tried history/, which didn't work, that's when I changed it to the full path.
Now I've changed it back and did a reload, and it's writing to the files. Also, the DataYours whisper files are exposed through Historian, which wasn't the case before.

Thank you!
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 #66 on: August 19, 2018, 10:04:20 am »
Glad it's going.  It's designed to be that simple.

Obviously something amiss with an absolute path that I need to investigate, because there shouldn't be anything fundamental to stop that working.
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 #67 on: August 19, 2018, 12:49:27 pm »
I think I know what it is.

I'm running openLuup on ESXi (virtualised). It seems that when I reboot the VM, the time is not correct. openLuup starts and uses this incorrect time, thus logging is not working, even though in the meantime the VM syncs it's time with an NTP server.

When I restart openLuup, everything works correctly because the VM time has been updated using an NTP server in the meantime and openLuup uses this time.

Now I'm going to make sure the time gets synchronised at startup. Does openLuup check the system clock once in a while?
« Last Edit: August 19, 2018, 01:14:15 pm by bruring »
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 #68 on: August 19, 2018, 01:15:11 pm »
I'm running openLuup on ESXi (virtualised). It seems that when I reboot the VM, the time is not correct. openLuup starts and uses this incorrect time, thus logging is not working, even though in the meantime the VM syncs it's time with an NTP server.

Ah yes!  I had the same problem with my main openLuup system on a BeagleBone Black.  It's now on the same UPS as the optic fibre modem, but another way to fix, of course, it to delay the openLuup startup.

Quote
Does openLuup check the system clock once in a while?

No, I thought of that but discounted it since so many things are wrong if the time is wrong, or changes.  I suppose I could adopt the 'Vera solution' and restart..., but one of openLuup's features is that it does NOT spontaneously restart!

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 #69 on: August 20, 2018, 07:24:38 am »
I took the freedom to make two small changes to the default archive and aggregation rules of Historian:

- Instead of only watching the status of a light (on/off) also watch the dimming state

In servertables.lua, archive_rules:
Code: [Select]
schema   = "every_1m",
      patterns = {
        "*.*.Status", -- on/off status
        "*.*.LoadLevelTarget", -- dim status 
        },

- For security sensor that get tripped, it would make more sense (IMHO) to use SUM instead of average so you can see how many times a sensor has been tripped over time.

In virtualfilesystem.lua, storage_aggregation_conf:
Code: [Select]
[sum]
pattern = [Ttripped]
xFilesFactor = 0
aggregationMethod = sum

Maybe it's possible to add this to a future release as well?
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 #70 on: August 20, 2018, 07:48:44 am »
Yes to both of those ideas, I think.

The details of the archives and aggregations certainly need fine tuning, although not everyone will ever be happy.  I've certainly used sum for security trips previously myself, and that is really the reason that security sensors have an initial 60 second archive, so that multiple trips in under a minute get counted.

I don't think that your pattern [Ttripped] is quite correct, since this just matches a single initial character from the list.  Perhaps you mean
Code: [Select]
[Tt]ripped
« Last Edit: August 20, 2018, 07:50:28 am 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 #71 on: August 20, 2018, 08:17:01 am »
Ah thanks your right, I've changed it to Tripped.

Also, I had to change the regex for minima to Min, because [Mm]in matched with Dimming1.LoadLevelTarget.

Now the files get created and they have the right aggregation headers.

The console shows me the Dimming level graph, but it doesn't get written to the Whisper database on disk. All other variables do get written so it's not the same problem from yesterday.
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 #72 on: August 20, 2018, 09:55:52 am »
Now the files get created and they have the right aggregation headers.

I'm toying with a manual override method of displaying and selecting on the History Cache menu which variables to archive.  See attached.

Quote
The console shows me the Dimming level graph, but it doesn't get written to the Whisper database on disk. All other variables do get written so it's not the same problem from yesterday.

Have you done a reload?
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 #73 on: August 20, 2018, 11:38:56 am »
That would be a great feature, nice!

After a openLuup reboot, the LoadLevelStatus files get created but not updated (no updates on the History DB page).

Afer a reload, the files do get updated, but with wrong values I think.

If you compare graph from the values in memory (which are correct) to the graph of the values from the DB (incorrect), it gives two separate charts.

Also, I found out that LoadLevelStatus of all dimmers get logged in files, whereas both LoadLevelStatus and LoadLevelTarget (same graphs/values) get logged in memory.

I've added the two graphs for reference, the Memory one shows correct values.

The header of the LoadLevelStatus whisper files are:
Code: [Select]
          1,  315360000,          0,          5
        228,         60,       1440
      52068,        600,       1008
      88356,       3600,        720
     114276,      10800,       2920
     219396,      86400,       3650

Which should be correct considering the every_1m scheme and average aggregation.
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 #74 on: August 20, 2018, 12:18:27 pm »
If you compare graph from the values in memory (which are correct) to the graph of the values from the DB (incorrect), it gives two separate charts.

I don't believe that these are incorrect values.  The schema you are using has a one minute resolution (for the last hour), with as you say average aggregation, and you are only plotting a two minute period in which all the changes happen in the first minute, and nothing changes in the second minute.  The 45, or so, that you see from the archives is in fact the last value from the previous minute.  The aggregations only take place after the data has gone into the archive, not before.  This is standard Whisper database behaviour, which works well for normal timescales.  You could, instead, try an initial archive of one minute's worth of one second sampling to get finer averages, if needed, but I'm not convinced that for this type of data it's really required.  After the first hour they would get further averaged anyway, with the existing schema.

My initial guesses at the archive durations seemed like reasonable defaults, and actually conform to a number of other constraints which make storage and plotting effective and efficient.  But they're not for everyone.


Quote
Also, I found out that LoadLevelStatus of all dimmers get logged in files, whereas both LoadLevelStatus and LoadLevelTarget (same graphs/values) get logged in memory.

Everything should be logged in memory, except for those variables explicitly blocked by the cache rules in the servertables file:

Code: [Select]
local cache_rules = {
  nocache = {
      dates_and_times = "*.*.{*Date*,*Last*,Poll*,Configured,CommFailure}",
      zwave_devices = "*.ZWaveDevice1.*",
    },
  }

(actually, in the latest, I've also added *Time*)

I should point out that the archive rules only apply to the creation of new files.  If you happen to have created, for example, a LoadLevelStatus file previously, then the Historian will spot that on disk and write to it, not caring how it was originally created.

So I think that all is well.  Anyway, try things out for a bit longer and see how it transpires.
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.