The Vera Community forums have moved!

Advanced => Plugins & Plugin Development => Programming => openLuup => Topic started by: akbooer on July 08, 2018, 11:58:49 am

Title: openLuup: Data Historian
Post by: akbooer on July 08, 2018, 11:58:49 am
For your next stop, developments are in progress to archive openLuup device variables to DataYours completely without any configuration, which will make things easier.

The latest version of openLuup in the development branch now has the capability to archive variables.  You don't need DataYours at all, and can link Grafana directly to openLuup.
Title: Re: openLuup: Data Historian
Post by: Don Phillips on July 08, 2018, 07:37:47 pm
For your next stop, developments are in progress to archive openLuup device variables to DataYours completely without any configuration, which will make things easier.

The latest version of openLuup in the development branch now has the capability to archive variables.  You don't need DataYours at all, and can link Grafana directly to openLuup.

+1
Title: Data Historian
Post by: powisquare on July 10, 2018, 12:47:40 pm
Great to hear the news on Data Historian. Just wanted some clarification on this ..

 The on-disk archive is enabled by the single LuaStartup line luup.attr_set ("openLuup.Historian.Directory","history/"), where 'history/' is the path (including trailing '/') relative to cmh-ludl/ where you want the data to reside.

Where should I put the line?

Cheers
Title: Re: Data Historian
Post by: akbooer on July 10, 2018, 01:22:16 pm
Great to hear the news on Data Historian. Just wanted some clarification on this ..

Yes, I've been a bit low-key on this, but you do have all the data you need...

Quote
The on-disk archive is enabled by the single LuaStartup line luup.attr_set ("openLuup.Historian.Directory","history/"), where 'history/' is the path (including trailing '/') relative to cmh-ludl/ where you want the data to reside.

...yes, that's right.

Quote
Where should I put the line?

Anywhere you like in Lua Startup, so long as it gets executed.  Just save, and (ignoring the AltUI message that Vera will do so anyway) restart manually.

Be sure to take the latest development release (just now, that's v18.7.10) it also has the capability to graph any of the variables listed on the console historian cache and DB pages, without even any need of using Grafana (uses Google Charts, so must be internet connected for this trick to work.)

You may want to customize things a bit for your own needs, but straight out of the box it should work OK.

_______________

PS: If there's an HTTP/CSS guru out there, I may need some help putting these plots into pop-up frames above the console page, rather than overwriting it.  (Must NOT involve JavaScript.)
Title: Re: Data Historian
Post by: powisquare on July 11, 2018, 08:07:18 am
Brilliant. Went to Altui/Misc/Lua Startup and added luup.attr_set ("openLuup.Historian.Directory","history/")

In More/Plugins typed 'development' into the openLuup update box and hit update.

Graphs can be seen by following the relevant link in the Console - openLuup/Historian.

I like the way it is possible to graph multiple variables against each other in datayours and might see if grafana will do something similar.   
Title: Re: Data Historian
Post by: akbooer on July 11, 2018, 08:58:09 am
Brilliant.
[...]
Graphs can be seen by following the relevant link in the Console - openLuup/Historian.

Excellent.  My aim was to make it that easy.

Quote
I like the way it is possible to graph multiple variables against each other in datayours and might see if grafana will do something similar.

Not sure what you mean here?  DataYours (and Data Historian console pages) both use Google Charts and can provide a variety of plots with multiple variables.  Grafana can do so much more, so you shouldn't have any trouble producing what you need...

Adding multiple metrics to a plot in Grafana is easy, and you can interactively walk though the machine / device / serviceId / variable menus to find what you need.  openLuup also provides a few special functions to tidy up the metric naming in legends, etc: alias(), aliasByMetric(), aliasByNode().
Title: Re: Data Historian
Post by: powisquare on July 13, 2018, 04:31:51 am
Nearly there! Able to plot Current Temp, Setpoint and Boiler switch status against each other (well almost). Curiously the Setpoint data has disappeared from yesterday. The History folder exists but when I look in the Console to Files/History DB I see 'On-disk archiving not enabled'. Would this be an issue?
Title: Re: Data Historian
Post by: akbooer on July 13, 2018, 05:16:07 am
The magic line in startup needs to be there to run at every startup.  Did you remove it?
Title: Re: Data Historian
Post by: akbooer on July 13, 2018, 05:28:36 am
...and, in fact, CurrentSetpoint is not a default parameter to archive.

I will add it, but you can easily do so by editing the servertables.lua file, line 202,adding that variable to the existing, CurrentTemperature, ... etc.

I am still confused by your 'On-disk archiving not enabled' message.
Title: Re: Data Historian
Post by: powisquare on July 13, 2018, 08:29:43 am
lua startup is good I think


-- You can personalise the installation by changing these attributes,
-- which are persistent and may be removed from the Startup after a reload.
local attr = luup.attr_set

-- Geographical location
attr ("City_description", "Greenwich")
attr ("Country_description", "UNITED KINGDOM")
attr ("Region_description", "England")
attr ("latitude", "51.48")
attr ("longitude", "0.0")

-- other parameters
attr ("TemperatureFormat", "C")
attr ("PK_AccessPoint", "88800000")
attr ("currency", "�")
attr ("date_format", "dd/mm/yy")
attr ("model", "Not a Vera")
attr ("timeFormat", "24hr")

-- Any other startup processing may be inserted here...
luup.attr_set ("openLuup.Historian.Directory","history/")
luup.log "startup code completed"




Added CurrentSetpoint to read   "*.*.{CurrentLevel,CurrentTemperature,CurrentPressure,CurrentSetpoint}",  -- temperature, humidity, generic sensors, ... and reloaded luup engine

There are various CurrentSetpoint whisper files which start with nil but none that are preceeded by controller number ? Have tried to attach a snapshot.

Title: Re: Data Historian
Post by: akbooer on July 13, 2018, 09:38:03 am
Those seems to be duplicates. I presume that they are not updating.  Can you check that and also their creation times cf. the real ones? 

Have you added one of the bridged Veras since starting to play with this?

Feel free to delete the nil ones and see what happens after a restart.

Title: Re: Data Historian
Post by: powisquare on July 13, 2018, 11:10:21 am
Just reread your post and seem to have missed the first line, sorry, but fwiw did not add any veras to muck things up.

Using Winscp logged in with sudo -s in Advanced/SCPShell/Shell (couldn't delete files with the normal pi login). Deleted all nil files and restarted. Changed the setpoint and am now seeing the new values in Console. Grafana however has lost the data and is currently looking into empty arrays and ... the nil files have returned. Will see if it populates over time.
Title: Re: Data Historian
Post by: akbooer on July 13, 2018, 11:39:45 am
Sorry this is causing trouble.  A few non-archived variables, I had expected (because I don't have thermostats or other exotic devices.)  But this nil nodename is odd.

Can you confirm the number of Veras you have bridged (looks like at least two) and also show me the Variables page for both of those devices?  A Startup Log might help me pinpoint the issue (also the first part of the main log, until all the plugins are initialised.)   It's not something I've seen on any of my systems, but hey, that's what beta testing is for.  (You DID know that you were a beta tester?)
Title: Re: Data Historian
Post by: powisquare on July 13, 2018, 12:31:52 pm
No trouble at all. Honored if truth be told : )

There are three veras bridged but Grafana is only looking to two at present tracking CurrentTemperature and CurrentSetpoint from two thermostats and CurrentSetpoint SwitchPower1.Status from two relays. Please see attachments - hope it's what you are looking for.

Title: Re: Data Historian
Post by: powisquare on July 13, 2018, 12:33:41 pm
...
Title: Re: Data Historian
Post by: akbooer on July 13, 2018, 03:01:57 pm
Please see attachments - hope it's what you are looking for.

Nearly all of what I needed... I was also wanting the variables of the VeraBridge plugins themselves (three, in this case, then.)  In particular, they should all have variables Offset, and PK_AccessCode.  I also need their device names (but may find them in the logs. haven't looked yet. I have now: the Vera with the nil issue is 'VBOld Forge')

Thanks for all the help.

Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 05:18:42 am
Morning - please find VB variables attached
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 05:27:51 am
Well, there's your problem.  PK_AccessPoint for the errant machine is nil.

I've never seen this, and, frankly, don't know how that machine can possibly work with the MiOS accounts.  What did you do to it?
Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 05:51:32 am
I have no idea? Has been running for over three years but I'm no expert in these things. Anything I should/could do?
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 06:14:41 am
I have no idea? Has been running for over three years but I'm no expert in these things. Anything I should/could do?

I think this is a relatively recent happening, and wonder (could be wrong, though) if you have inadvertently managed to delete this attribute?  Perhaps doing something in Lua Test?

Anyway, I can tell you that this seems like a Vera 3 / VeraLite class of machine.  In fact, I can dig out from the logs that its PK_AccessCode should be 30107174.

As a sanity check, can you first look at the results of these three requests in a browser:

Code: [Select]
http://192.168.1.170/port_3480/data_request?id=variableget&Variable=PK_AccessPoint
http://192.168.1.171/port_3480/data_request?id=variableget&Variable=PK_AccessPoint
http://192.168.1.172/port_3480/data_request?id=variableget&Variable=PK_AccessPoint

you should get:

Code: [Select]
30107174
30108355
45105824

...but I'm wondering is the first one gives nil or an error?  If so, we can try and fix this.
Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 06:46:07 am
Thanks for your patience : ) yes those requests returned the codes you provided and there was no nil error.

Have not been into Lua Test but I did fiddle with the openLuup setup a few days ago by updating openLuup to the latest dev release and updating the VeraBridge plugin. Only other thing I can recall was inserting luup.attr_set ("openLuup.Historian.Directory","history/") into Lua Startup.

Should I reinsert 30107174 into PK_AccessCode?
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 06:53:56 am
Thanks for your patience : )

No, thanks for yours!

Quote
Should I reinsert 30107174 into PK_AccessCode?

Well, it obviously is there in the system, which is good.  It's not there in that particular VeraBridge, which is bad.  My recollection is that this should be set every Luup reload.  You've probably done this already, but can you do that again and check the value of PK_AccessPoint amongst your 'VBOld Forge' device variables.

I don't have high hopes for that, since I'm sure you've have reloaded several times.  Just setting it to the correct value is worth trying, but I do beleive that it will get overwritten on the next reload.  That would be thing #2 to try, anyway.

I'll look at the code but, honestly, it's working in all my machines and in two of yours, so there is SOMETHING odd going on somewhere.  I have a hunch, but it will need a special bit of diagnostic code to try out. 

Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 07:06:41 am
Upon Reload Luup Engine the variable did not return. After inserting variable and 2 x reloads the variable has persisted! However, I checked on the other two verabridges and VBLodgeMaster has a nil value.
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 07:09:13 am
 :(
Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 07:12:13 am
lol
Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 07:15:31 am
Herding cats.
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 07:35:51 am
Yep.  I'm thinking, following up my hunch.  The problem is clearly mine.

Is there anything special about the network they're on?  I'm thinking this is a timing problem.
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 09:02:20 am
I checked on the other two verabridges and VBLodgeMaster has a nil value.

...so this, clearly, is nothing to do with the historian, but all to do with VeraBridge.  Very odd, because, again, I have never seen this before.  Can I have the first part of the main log file again, showing the bridges starting up?
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 09:11:39 am
SOLVED... (I hope.)

Your log shows that you are running a version of VeraBridge (2018.03.02) which does not match the openLuup version that you have.

My guess is that there is a copy of this old L_VeraBridge.lua file in cmh-ludl/ which should simply be deleted (then restart twice.)

Is this, in fact the case?
Title: Re: Data Historian
Post by: powisquare on July 14, 2018, 09:29:24 am
Found the file in the openLuup dir. Ok to delete this?

From earlier ..

I have a Ubiquity setup. The Old Forge and Cottages connect to the network via wifi/access points. The Lodge is wired but reaches the main network via wireless bridge.

Pinging 192.168.1.170 (Old Forge box) twice reply is 1112ms, 1ms, 4ms, 2ms | 3ms, 2ms, 2ms, 2ms. 192.168.1.171 (Cottages) 112ms, 2ms, 1ms, 2ms | 1ms, 5ms, 1ms, 2ms. 192.168.1.172 (Lodge) 4ms, 3ms, 2ms, 3ms | 2ms, 6ms, 3ms, 6ms

This is from my laptop which is connected wirelessly. From the openLuup box (wired to a switch) they are all under 3ms. (Not sure if any of that is useful!)

I did panic in that the unifi controller was down and only got it working after a firmware upgrade but not certain this would affect the network.
Title: Re: Data Historian
Post by: akbooer on July 14, 2018, 10:42:06 am
Found the file in the openLuup dir. Ok to delete this?

No, that's probably the right one.  If you look in that file it should have a much newer version number.

There MUST be another one.  Do you have a cmh-lu/ directory, or perhaps in files/?
Title: Re: Data Historian
Post by: Buxton on July 15, 2018, 12:41:37 am
I recently updated my unifi controller and it caused enormous havoc on my network.  What Unifi doesn't communicate well is that the version of the controller that you need on your network is highly contingent on the type of equipment you have.  In my case, my APs are a couple of years old, and the controller that works with that equipment is several revisions back from the latest and greatest. 

You can find out what controller you need by visiting the Ubiquity site and looking up the firmware for your specific devices.  There will be a link in the firmware device folder to the version of the latest controller that is compatible with your gear.  Backup your settings before a new install because if the controller that you need is older than the controller you're using, you will be forced to uninstall the existing controller first, potentially wiping out your settings.
Title: Re: openLuup: Data Historian
Post by: Buxton on July 15, 2018, 12:44:39 am
AK, I'm having a difficult time getting Grafana to recognize the Whisper files on my USB drive.  This, -- on the data source page of the Grafana homepage instance.  Any tips on what needs doing as I don't see any info on the web regarding this particular type of datafile.  OpenLuup opens the files fine via historian. 
Title: Re: openLuup: Data Historian
Post by: akbooer on July 15, 2018, 02:18:12 am
AK, I'm having a difficult time getting Grafana to recognize the Whisper files on my USB drive. 

I wasn't aware that Grafana could/should be able to access the database files directly? 

Quote
This, -- on the data source page of the Grafana homepage instance. 

...or are you trying to use the Graphite Carbon Metrics dashboard for this?

This relies on database queries of the form:
Code: [Select]
/metrics/find?query=carbon.agents.*.updateOperations
which I have not implemented (but could... most of the relevant metrics are gathered internally.)

Quote
Any tips on what needs doing as I don't see any info on the web regarding this particular type of datafile. 

The datafile format for the original Python implementation is described here:
http://graphite.readthedocs.io/en/latest/whisper.html

However, what you probably haven't looked at is my documentation for DataYours, which describes the Lua translation:

Quote from: DataYours User Guide, version 2016.02.04 , Design Notes section, page 15
The Whisper code, originally written in Python, has been translated to Lua, and then re- factored somewhat. The database code is pure Lua and will run anywhere, but it is not binary-compatible with Graphite Whisper files because I have chosen CSV rather than binary packing. This makes them exactly three times larger than real Whisper ones, but space (outside of Vera) is not a problem.

To make them binary compatible you would need to replace the 'struct' implementation with a binary version.

Quote
OpenLuup opens the files fine via historian.

Good, ... so what it is you are trying to do with Grafana?
Title: Re: Data Historian
Post by: akbooer on July 15, 2018, 12:01:11 pm
@powisquare

The latest development commit (v18.7.15) should fix your VeraBridge problem, if I have diagnosed things correctly.

I do still believe that this is nothing to do with the Data Historian and it's a side-effect of having multiple L_VeraBridge.lua files, but I have used this opportunity to rectify a long-standing inconsistency with file search paths, which should address such issues.

Hoping this does fix things... (and breaks nothing else!)
Title: Re: openLuup: Data Historian
Post by: Buxton on July 15, 2018, 06:39:08 pm
I was just playing around with the software--trying to understand the capabilities based on your quote: 

Quote
You don't need DataYours at all, and can link Grafana directly to openLuup.

I misread openLuup to stand as a proxy for the actual data-files.  I'll go over the documentation again.
Title: Re: Data Historian
Post by: powisquare on July 16, 2018, 05:23:26 am
Sorry guys - have not been receiving the usual email notifiactions from micasaverde (?!) so thanks for the pm. Have searched the whole pi and only found L_VeraBridge.lua in /etc/cmh-ludl/openLuup/. Have updated to latest development release openLuup and will see what happens. Just having trouble logging into Grafana now  'This site can't be reached'. arrgh

Had a new smart meter installed which may have sent the cloud key controller into a tiz. British gas now tell me the meter will provide a reading once a quarter automatically. Not quite what I was hoping for but will get onto energy metering on a another thread soon.
Title: Re: Data Historian
Post by: powisquare on July 16, 2018, 05:30:48 am
Ok grafana was not started for some reason.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 16, 2018, 05:40:51 am
I was just playing around with the software--trying to understand the capabilities based on your quote: 

Quote
You don't need DataYours at all, and can link Grafana directly to openLuup.

Well, as usual, I have not explained things very well.  It's certainly time for an update to the documentation.

DataYours has its own Whisper database, location defined by the contents of DataYours variable LOCAL_DATA_DIR, usually 'whisper/'.  Data Historian has its data wherever you've set the system attribute openLuup.Historian.Directory, usually 'history/'.

Pointing Grafana at openLuupIP:3480/, should allow its metric menus to find both databases, but I've just tried this, and it seems not to find the DataYours files.  This is perhaps the issue you raised.  I will look into it. [edit: it does work, I just didn't have DataYours running when I tested it!]

However, what DOES work, is to add the following line to Lua Startup

Code: [Select]
luup. attr_set ("openLuup.Historian.DataYours", "whisper/")               -- overriding DY finder

Assuming that your LOCAL_DATA_DIR points to 'whisper/', then after a restart, you should see all your original DataYours file under a metric tree called DataYours...

Code: [Select]
"DataYours.Vera-88800000.008.urn^micasaverde-com^serviceId^EnergyMetering1.EnergyUsage",
"DataYours.Vera-88800000.294.urn^micasaverde-com^serviceId^EnergyMetering1.EnergyUsage",
"DataYours.Vera-88800000.386.urn^micasaverde-com^serviceId^EnergyMetering1.EnergyUsage",
"DataYours.cpu.d",
"DataYours.memory.d",
"DataYours.unknown",
"DataYours.uptime.m",

...along with the other sub-trees generated by the historian (one for openLuup, and one for each bridged Vera.)

HTH, AK
Title: Re: Data Historian
Post by: powisquare on July 16, 2018, 10:02:26 am
Brilliant - the nil files are no longer created and PK_AccessPoint values are persistent. Will leave it running and see how it goes. Cheers.
Title: Re: Data Historian
Post by: akbooer on July 16, 2018, 10:35:32 am
 :)
Title: Re: openLuup: Data Historian
Post by: powisquare on July 19, 2018, 09:47:37 am
Quick update - the CurrentTemperature  values are all plotting like a dream. CurrentSetpoint and SwitchpowerStatus only display when I set the timeframe to last  7 days. Part of me was thinking only a change in value is recorded but then SwitchpowerStatus has not changed in value ever so that probably is not it. Console/Files/History DB is still showing 'On-disk archiving not enabled' if that makes a difference.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 19, 2018, 10:24:53 am
Quick update - the CurrentTemperature  values are all plotting like a dream. CurrentSetpoint and SwitchpowerStatus only display when I set the timeframe to last  7 days.

Has your system been up and running continuously for just over 7 days?

Quote
Part of me was thinking only a change in value is recorded but then SwitchpowerStatus has not changed in value ever so that probably is not it.

Except for startup, when current values are written, only variable changes are stored in cache (this saves memory space.)  However, all variable_set values are written to disk archive.  This helps with the application of retention policies between archives.

Quote
Console/Files/History DB is still showing 'On-disk archiving not enabled' if that makes a difference.

AHA!  A eureka moment.

Am I correct in saying that you have not ever installed DataYours on your openLuup system?  This is fine, you don't need it, but in early implementations I used one of its files and I did have it installed, so I was not getting this message.

I believe that you are only seeing data from the in-memory cache, although it will be archived to disk... you just can't see it!

All fixed (I hope) in development release v18.7.19
Title: Re: openLuup: Data Historian
Post by: powisquare on July 19, 2018, 12:33:08 pm
Bingo - as you deduced there is no Datayours installed. History DB is now populated and all is appearing on Grafana. Huge thanks.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 19, 2018, 12:38:03 pm
Excellent.  Thanks for your help... several bugs squashed in the process!
Title: Re: openLuup: Data Historian
Post by: Buxton on July 20, 2018, 05:53:49 pm
I'm seeing a bug in the latest development version.  Setting the startup luup.attr_set ("openLuup.Historian.Directory","/media/usb/whisper/") does not start the archive writing to disk (though the cache is functional and can be accessed from the historian tab on the console.)

If I change the attribute pointer to luup.attr_set ("openLuup.Historian.Directory","/media/usb/whisper/data"), the process of writing to disk begins but each whisper file name is prepended with "data" and accordingly, cannot be opened from the historian tab (I get a series of lua errors.) 

The three core datayours metrics write to disk regardless of the startup attribute.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 20, 2018, 06:05:23 pm
Can I see the beginning of the actual log just after startup?

This should look something like:

Code: [Select]
2018-07-20 23:04:02.901   :: openLuup LOG ROTATION :: (runtime 0.0 days)
2018-07-20 23:04:02.939   openLuup.init:: init phase completed
2018-07-20 23:04:02.940   openLuup.io.server:: starting HTTP server on port: 3480 tcp{server}: 0x19b7a90
2018-07-20 23:04:02.940   openLuup.io.server:: starting SMTP server on port: 2525 tcp{server}: 0x1967ca8
2018-07-20 23:04:02.940   openLuup.io.server:: starting POP3 server on port: 11011 tcp{server}: 0x745890
2018-07-20 23:04:02.941   openLuup.historian:: starting data historian
2018-07-20 23:04:02.941   openLuup.historian:: using on-disk archive: history/
2018-07-20 23:04:02.941   openLuup.historian:: Graphite schema/aggregation rule sets: 15/3
2018-07-20 23:04:02.941   openLuup.historian:: disk archive storage rule sets: 9
2018-07-20 23:04:02.941   openLuup.historian:: mirroring archives to Graphite at 127.0.0.1:2003
2018-07-20 23:04:02.941   openLuup.historian:: using memory cache size (per-variable): 1024
2018-07-20 23:04:02.942   openLuup.scheduler:: starting
2018-07-20 23:04:02.942   openLuup.scheduler:: [2]     openLuup device startup
2018-07-20 23:04:02.942   luup_log:2: v18.7.19
Title: Re: openLuup: Data Historian
Post by: akbooer on July 20, 2018, 06:11:38 pm
If I change the attribute pointer to luup.attr_set ("openLuup.Historian.Directory","/media/usb/whisper/data"), the process of writing to disk begins but each whisper file name is prepended with "data" and accordingly, cannot be opened from the historian tab (I get a series of lua errors.) 

The directory path should end with '/'
Title: Re: openLuup: Data Historian
Post by: Buxton on July 20, 2018, 07:00:47 pm
With startup luup.attr_set ("openLuup.Historian.Directory","/media/usb/whisper/")

Code: [Select]
2018-07-20 15:39:36.254   :: openLuup LOG ROTATION :: (runtime 0.0 days)
2018-07-20 15:39:36.255   openLuup.init:: init phase completed
2018-07-20 15:39:36.255   openLuup.io.server:: starting HTTP server on port: 3480 tcp{server}: 0x1e30bc48
2018-07-20 15:39:36.255   openLuup.io.server:: starting SMTP server on port: 2525 tcp{server}: 0x1e688758
2018-07-20 15:39:36.255   openLuup.io.server:: starting POP3 server on port: 11011 tcp{server}: 0x1e115848
2018-07-20 15:39:36.256   openLuup.historian:: starting data historian
2018-07-20 15:39:36.256   openLuup.historian:: using on-disk archive: /media/usb/whisper/
2018-07-20 15:39:36.258   openLuup.historian:: Graphite schema/aggregation rule sets: 17/16
2018-07-20 15:39:36.258   openLuup.historian:: disk archive storage rule sets: 9
2018-07-20 15:39:36.258   openLuup.historian:: using memory cache size (per-variable): 1024
2018-07-20 15:39:36.258   openLuup.scheduler:: starting
2018-07-20 15:39:36.258   openLuup.scheduler:: [2]     openLuup device startup
2018-07-20 15:39:36.258   luup_log:2: v18.7.19
2018-07-20 15:39:36.258   luup_log:2: synch in 23.7 s
2018-07-20 15:39:36.258   luup.variable_watch:: callback=openLuup_watcher, watching=2.openLuup.HouseMode
2018-07-20 15:39:36.258   luup.register_handler:: global_function_name=openLuup_email, request=openLuup@openLuup.local
2018-07-20 15:39:36.258   luup.register_handler:: global_function_name=openLuup_images, request=images@openLuup.local
2018-07-20 15:39:36.258   luup.register_handler:: global_function_name=openLuup_events, request=events@openLuup.local
2018-07-20 15:39:36.259   luup.register_handler:: global_function_name=openLuup_mailbox, request=mail@openLuup.local
2018-07-20 15:39:36.259   luup.chdev.append:: [AltAppStore] Alternate App Store
2018-07-20 15:39:36.259   luup.chdev.sync:: [2]     openLuup, syncing children
2018-07-20 15:39:36.259   luup_log:2: 12 Mb, cpu 3%, 0 days
2018-07-20 15:39:36.260   openLuup.scheduler:: [2]     openLuup device startup completed: status=true, msg=synch in 23.7 s, name=L_openLuup
2018-07-20 15:39:36.260   openLuup.scheduler:: [3] Alternate UI device startup
2018-07-20 15:39:36.260   luup_log:3: ALTUI: initstatus(3) starting version: v2.29
2018-07-20 15:39:36.260   openLuup.scheduler:: [3] Alternate UI device startup completed: status=nil, msg=nil, name=nil
2018-07-20 15:39:36.260   openLuup.scheduler:: [4] Alternate App Store device startup
2018-07-20 15:39:36.260   luup_log:4: AltAppStore : starting...
2018-07-20 15:39:36.260   luup.variable_set:: 4.urn:upnp-org:serviceId:altui1.DisplayLine1 was: AltAppStore now: AltAppStore #hooks:0
2018-07-20 15:39:36.261   luup.variable_set:: 4.urn:upnp-org:serviceId:altui1.DisplayLine2 was:  now:  #hooks:0
2018-07-20 15:39:36.261   luup_log:4: AltAppStore : v18.6.28
2018-07-20 15:39:36.261   luup.set_failure:: status = 0

With luup.attr_set ("openLuup.Historian.Directory","/media/usb/whisper/data")

Code: [Select]
2018-07-20 15:44:20.222   :: openLuup LOG ROTATION :: (runtime 0.0 days)
2018-07-20 15:44:20.223   openLuup.init:: init phase completed
2018-07-20 15:44:20.223   openLuup.io.server:: starting HTTP server on port: 3480 tcp{server}: 0xea07a98
2018-07-20 15:44:20.223   openLuup.io.server:: starting SMTP server on port: 2525 tcp{server}: 0xe1bb5a8
2018-07-20 15:44:20.223   openLuup.io.server:: starting POP3 server on port: 11011 tcp{server}: 0xe6ac1b8
2018-07-20 15:44:20.223   openLuup.historian:: starting data historian
2018-07-20 15:44:20.223   openLuup.historian:: using on-disk archive: /media/usb/whisper/data
2018-07-20 15:44:20.225   openLuup.historian:: Graphite schema/aggregation rule sets: 15/3
2018-07-20 15:44:20.226   openLuup.historian:: disk archive storage rule sets: 9
2018-07-20 15:44:20.226   openLuup.historian:: using memory cache size (per-variable): 1024
2018-07-20 15:44:20.226   openLuup.scheduler:: starting
2018-07-20 15:44:20.226   openLuup.scheduler:: [2]     openLuup device startup
2018-07-20 15:44:20.226   luup_log:2: v18.7.19
2018-07-20 15:44:20.226   luup_log:2: synch in 99.8 s
2018-07-20 15:44:20.226   luup.variable_watch:: callback=openLuup_watcher, watching=2.openLuup.HouseMode
2018-07-20 15:44:20.226   luup.register_handler:: global_function_name=openLuup_email, request=openLuup@openLuup.local
2018-07-20 15:44:20.226   luup.register_handler:: global_function_name=openLuup_images, request=images@openLuup.local
2018-07-20 15:44:20.226   luup.register_handler:: global_function_name=openLuup_events, request=events@openLuup.local
2018-07-20 15:44:20.226   luup.register_handler:: global_function_name=openLuup_mailbox, request=mail@openLuup.local
2018-07-20 15:44:20.227   luup.chdev.append:: [AltAppStore] Alternate App Store
2018-07-20 15:44:20.227   luup.chdev.sync:: [2]     openLuup, syncing children
2018-07-20 15:44:20.227   luup_log:2: 12 Mb, cpu 3%, 0 days
2018-07-20 15:44:20.228   openLuup.scheduler:: [2]     openLuup device startup completed: status=true, msg=synch in 99.8 s, name=L_openLuup
2018-07-20 15:44:20.228   openLuup.scheduler:: [3] Alternate UI device startup
2018-07-20 15:44:20.228   luup_log:3: ALTUI: initstatus(3) starting version: v2.29
2018-07-20 15:44:20.228   openLuup.scheduler:: [3] Alternate UI device startup completed: status=nil, msg=nil, name=nil

And the error message when trying to open a variable.  And the directory listing.

Title: Re: openLuup: Data Historian
Post by: akbooer on July 21, 2018, 02:58:28 am
Simply rename all the files to start with 0 rather than data0 and all should be well after a restart.  I'll add some checks to catch a path not ending in /

It's straight-forward to write a Lua script to do the rename if you don't otherwise know an easy way to do it for all the files.

...or just delete them and start again.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 21, 2018, 03:15:10 am
Another problem I note:

...you must NOT mix the historian directory with a regular DataYours Whisper database.  They must be kept separate, although the Grafana interface is able to access and display data from both separate directories.
Title: Re: openLuup: Data Historian
Post by: Buxton on July 21, 2018, 06:15:59 pm
I think the combined folder situation was the culprit.  I created a folder explicitly for the historian data and after a startup pointer, the folder immediately populated upon a luup reload.
Title: Re: openLuup: Data Historian
Post by: powisquare on July 26, 2018, 10:43:54 am
Hi - am tracking a 'Tripped' state which is either 1 or 0. The google graph from console shows a  value of 0.5. Not sure how this has happened? Have attached what I believe to be the wsp file ..
Title: Re: openLuup: Data Historian
Post by: akbooer on July 26, 2018, 01:58:57 pm
Yes, this is not a surprise, I know what it is...

The default action for aggregating values from one archive to the next (lower time resolution) one is average.  For some things, like temperature and the like, this is exactly what you want.  For others, it may not be, this being a case in point.

The choices you have for aggregation functions are:
You can, retrospectively, change the aggregation function on an existing archive file, and although this will not change the existing data in the file, it will ensure that this does not happen for new data.

The tricky question is: what aggregation function is correct for your situation?  Depending on the sensor type, you could, say, use: average to calculate a room occupancy; sum for the total number of trips in that time interval, last for the most recent state in that interval, ... (max/min unlikely to be useful for security-like data)

You can set up rules to automatically configure whichever you want for any new metrics of the same type, but that's a somewhat separate topic.

There is a Whisper API function to make this change to a file, but it really needs to be wrapped into a simple-to-use way.  I have on the 'to do' list, a utility to do this, but in the meantime, here's one rationale for having chosen plain text as the Whisper file format - you can change the aggregation with a normal text editor.  The file is fixed-width format, so be careful about keeping the number of characters in a line the same.  The aggregation is indicated by a single digit, per the list numbering above, and is the first number in a whisper archive file.

For example, the first few lines of your attached file are:
Code: [Select]
          1,  315360000,          0,          6
        264,          1,         60
       2424,         60,       1440
      54264,        600,       1008
      90552,       3600,        720
     116472,      10800,       2920
     221592,      86400,       3650
          0,                      0
 1531746841,                      1

Changing it to:
Code: [Select]
          2,  315360000,          0,          6
        264,          1,         60
       2424,         60,       1440
      54264,        600,       1008
      90552,       3600,        720
     116472,      10800,       2920
     221592,      86400,       3650
          0,                      0
 1531746841,                      1

will switch it to sum aggregation.  Be careful that your editor doesn't mess with line terminators.

You can read more about Whisper archives here: http://graphite.readthedocs.io/en/latest/whisper.html#archives-retention-and-precision

Title: Re: openLuup: Data Historian
Post by: powisquare on July 27, 2018, 04:37:38 am
Super - I'll try 3 for last and see how things turn out. Many thanks.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 27, 2018, 05:36:44 am
OK.  Let us know how it goes.

Actually, max is not such a bad idea either - it will tell you if the sensor was ever tripped at any time in that interval.
Title: Re: openLuup: Data Historian
Post by: powisquare on July 28, 2018, 07:22:46 am
Well - gave it a go : ) Edited said file in WinSCP by changing just that number. Hoping I didn't muck up any formatting. Wierdly some nil files have returned for that vera box? Also, even though there are wsp files for the variables I was tracking the console & grafana seem unable to access them.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 28, 2018, 09:03:39 am
No idea why the nil files are back, but you need to delete them and restart.  Although, before you do you might check the creation time and see if that happened after your edit.  Things will not work until they're gone.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 28, 2018, 09:40:00 am
Wierdly some nil files have returned for that vera box? Also, even though there are wsp files for the variables I was tracking the console & grafana seem unable to access them.

I don't like the proliferation of this line in the logs:
Code: [Select]
2018-07-28 11:33:52.950   openLuup.http:: WGET status: timeout, request: http://192.168.1.170/port_3480/data_request?id=status2&output_format=json&DataVersion=

I'm not able to tell which machine that corresponds to... is it the one with nil node name again?  Looks like a network issue, OR a constantly crashing Vera??

You've done an openLuup update since we previously fixed that?  HOW are you doing that exactly?  Once again, it would be very diagnostic to have the startup of the main log directly after a reload.
Title: Re: openLuup: Data Historian
Post by: akbooer on July 29, 2018, 02:58:10 am
I've added further checks in VeraBridge to flag errors when it can't access the remote Vera. This should stop the Historian creating archive files with nil node names.

Github development branch commit 2018.07.29

Title: Re: openLuup: Data Historian
Post by: powisquare 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.
Title: Re: openLuup: Data Historian
Post by: bruring 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?
Title: Re: openLuup: Data Historian
Post by: akbooer on August 18, 2018, 06:12:50 pm
If it's created the files, then the file directory permissions must be OK. 

It would be diagnostic to share the Console > Files > History DB and Console > openLuup > Parameters pages.
Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: akbooer on August 19, 2018, 05:30:09 am
Hmmm...

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

...or did you already?
Title: Re: openLuup: Data Historian
Post by: bruring 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!
Title: Re: openLuup: Data Historian
Post by: akbooer 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.
Title: Re: openLuup: Data Historian
Post by: bruring 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?
Title: Re: openLuup: Data Historian
Post by: akbooer 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!

Title: Re: openLuup: Data Historian
Post by: bruring 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?
Title: Re: openLuup: Data Historian
Post by: akbooer 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
Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: akbooer 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?
Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: akbooer 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.
Title: Re: openLuup: Data Historian
Post by: streilu 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.
Title: Re: openLuup: Data Historian
Post by: akbooer 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:
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 (https://graphite.readthedocs.io/en/latest/config-carbon.html#storage-schemas-conf). 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.

Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: akbooer 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:

So, actually, Whisper is quite efficient, which is what it was designed to be in the first place.
Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: akbooer 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.
Title: Re: openLuup: Data Historian
Post by: bruring 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.
Title: Re: openLuup: Data Historian
Post by: reneboer 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
Title: Re: openLuup: Data Historian
Post by: akbooer 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.
Title: Re: openLuup: Data Historian
Post by: akbooer on September 20, 2018, 01:27:22 pm
So you're saying that links like these are missing?

Title: Re: openLuup: Data Historian
Post by: reneboer 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
Title: Re: openLuup: Data Historian
Post by: reneboer 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
Title: Re: openLuup: Data Historian
Post by: akbooer 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.
Title: Re: openLuup: Data Historian
Post by: reneboer 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
Title: Re: openLuup: Data Historian
Post by: ChrisTheC 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.

Title: Re: openLuup: Data Historian
Post by: akbooer on September 21, 2018, 06:00:55 pm
I did once change all the device numbers over 10000, but when you add a device the Vera just keeps numbering up.

There's a system attribute called Device_Num_Next.  After backing up, you could try changing this in the user_data.json file, or even on the fly with luup.attr_set().

I don't know what would happen if you ran into an existing device, though, but I imagine that you must have some fairly large gaps in the device numbering!

__________________

Edit: tried luup.attr_set() but didn't work.
Title: Re: openLuup: Data Historian
Post by: reneboer on September 24, 2018, 04:29:39 am
Thanks, I'll give it a shot. All devices could fit in two digits indeed.

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: reneboer on September 24, 2018, 04:48:50 pm
Hi akbooer,

It looks simpler (and less risky) if I can change the offset in VeraBridge. Looking at the current code this gets calculated again after each restart. Could it be made the initial offset gets calculated at first install and then taken from the off-set variable? This way you can also remove a VeraBridge without impacting the numbering of any remaining. I must admit I do not understand the role of the Primary Bridge (OFFSET == BLOCKSIZE).

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: akbooer on September 25, 2018, 06:17:48 am
Could it be made the initial offset gets calculated at first install and then taken from the off-set variable? This way you can also remove a VeraBridge without impacting the numbering of any remaining.

That's an interesting thought, and certainly would fix that particular problem.  It may raise other issues - I'll have to look closer at that.

Quote
I must admit I do not understand the role of the Primary Bridge (OFFSET == BLOCKSIZE).

Not sure I understand this?  Are you asking why the first bridge has an offset at all?  Clearly, native plugins and virtual devices start numbering in openLuup using low numbers, so any bridge variables have to be offset.  The only concession made to the first bridge is that the Zwave controller gets mapped to device #1 so that somw exotic plugins, which directly use the Zwave controller, work.
Title: Re: openLuup: Data Historian
Post by: reneboer on September 25, 2018, 06:24:24 am
I must admit I do not understand the role of the Primary Bridge (OFFSET == BLOCKSIZE).

Not sure I understand this?  Are you asking why the first bridge has an offset at all?  Clearly, native plugins and virtual devices start numbering in openLuup using low numbers, so any bridge variables have to be offset.  The only concession made to the first bridge is that the Zwave controller gets mapped to device #1 so that some exotic plugins, which directly use the Zwave controller, work.
No, I get why even the first needs an offset so it won't clash with the openLuup native devices. It is that bit of special logic I did not get the reason for. But  that Zwave device is why. Learning again  :D

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: akbooer on September 25, 2018, 06:51:23 am
An obvious way to skip a VeraBridge block of device numbers is to install another copy of the bridge.  The blocks are currently assigned from lowest to highest VeraBridge plugin id, so you could, for example, use bridges 1 and 3, simply not mapping bridge 2 to a remote Vera at all.
Title: Re: openLuup: Data Historian
Post by: reneboer on September 25, 2018, 09:24:08 am
Hi akbooer,

I fear it is a bit more tricky than that. Looking at the historian files I see that the devices from the first bridge with numbers over 10000 show up with just the first four digits with the second bridge vera number. I.e. device ID 13523 of vera xxxx1711 show on the History DB page as xxxx0073.3523.EnergyMetering1.Watts where xxxx0073 is the vera number of the second bridge. Maybe getting the device numbers on my Vera down is indeed the best option. Unless you want me to be your guinea pig for this scenario.

Also looking at the History DB page the page shows links like  20m:30d,3h:1y,1d:10y. However, clicking them only produces empty graphs. Also for the openLuup native devices. The same parameters do show a graph on the Historian page. Any idea? Or do I need to install a graphana system or so?

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: akbooer on September 25, 2018, 12:48:30 pm
I fear it is a bit more tricky than that. Looking at the historian files I see that the devices from the first bridge with numbers over 10000 show up with just the first four digits with the second bridge vera number. I.e. device ID 13523 of vera xxxx1711 show on the History DB page as xxxx0073.3523.EnergyMetering1.Watts where xxxx0073 is the vera number of the second bridge.

Yes, I knew that something like that would happen with Historian.  What would be needed here is to identify children of bridge devices by their parent.  I didn't do that initially because it is harder - you have to search up the tree for grandparents, etc. (OK, not too hard.)  However, these name space/number conversions actually take place in both directions between three different naming conventions.  I haven't yet worked out whether this is actually possible to do...

Quote
Also looking at the History DB page the page shows links like  20m:30d,3h:1y,1d:10y. However, clicking them only produces empty graphs. Also for the openLuup native devices. The same parameters do show a graph on the Historian page. Any idea? Or do I need to install a graphana system or so?

That's much more concerning.  Do none of them work?  Do those links show number of points updated?  There should be a counter.
Title: Re: openLuup: Data Historian
Post by: reneboer on September 26, 2018, 07:04:01 am
Also looking at the History DB page the page shows links like  20m:30d,3h:1y,1d:10y. However, clicking them only produces empty graphs. Also for the openLuup native devices. The same parameters do show a graph on the Historian page. Any idea? Or do I need to install a graphana system or so?

That's much more concerning.  Do none of them work?  Do those links show number of points updated?  There should be a counter.
Go figure, today there is data showing?!? It seemed to have needed an extra luup restart or so as it is recording from the time I updated ALTUI yesterday.

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: akbooer on September 26, 2018, 07:19:43 am
Go figure, today there is data showing?!? It seemed to have needed an extra luup restart or so as it is recording from the time I updated ALTUI yesterday.

Well, that's a relief!
Title: Re: openLuup: Data Historian
Post by: akbooer on November 27, 2018, 10:55:39 am
Data Historian now supports mirroring to external Grafana Graphite and InfluxDB databases.  (as of Development branch v18.11.26)
Title: Re: openLuup: Data Historian
Post by: bruring on November 27, 2018, 11:55:31 am
Great! I'm going to try it on InfluxDB!

Does that also support custom date ranges (not only ... so far ranges) in Grafana do you know?
Title: Re: openLuup: Data Historian
Post by: bruring on November 27, 2018, 12:08:29 pm
Another question, I've been logging my sensors with Historian in a Whisper database for quite some time now.

If I look at graphs in Grafana and look at larger timespans, I notice that sometimes a sensor logs an invalid value (like a way too high temperature or light measurement) which gives a big peak in the graph, flattening all the other measurements.

My question is twofold:
- is there a way to remove invalid data from the whisper files? I've tried this with whisper-tools, but it gives me errors about not readable metadata.
- is there a way to specify a range for the logged variables, thus ignoring and eliminating the invalid values from getting stored in the DB in the first place?

Thanks,

Joris
Title: Re: openLuup: Data Historian
Post by: akbooer on November 27, 2018, 12:10:52 pm
Does that also support custom date ranges (not only ... so far ranges) in Grafana do you know?

Not sure I fully understand.  The date range as applied by the Grafana menu bar works, as do the per-graph duration and timeshift fields.  I haven't tried an explicit time interval in the metric query line on a graph, since I'm not really SQL-savvy.


Title: Re: openLuup: Data Historian
Post by: akbooer on November 27, 2018, 12:40:56 pm
If I look at graphs in Grafana and look at larger timespans, I notice that sometimes a sensor logs an invalid value (like a way too high temperature or light measurement) which gives a big peak in the graph, flattening all the other measurements.

Yes, this is, IMHO, a ZWave transmission problem.  It's particularly prevalent with some devices, notably, for me, energy meters.  It is not an historian problem, per se. It is, however, a nuisance.

Quote
- is there a way to remove invalid data from the whisper files? I've tried this with whisper-tools, but it gives me errors about not readable metadata.

Yes, there is.   And I use it about once a week to fix such problems.  You can't use the Whisper tools, since the database is not binary compatible.  I have, however, written an openLuup CGI, originally for DataYours, which allows editing of the data.  I've made a version which is tailored towards the Historian database.  You exercise the basic editing functionality through an HTML form - I have a crude webpage which suffices, but really would like to do better.  However, I'm thinking that Graphite/InfluxDB mirroring, means that you could use the standard tools instead.

Quote
- is there a way to specify a range for the logged variables, thus ignoring and eliminating the invalid values from getting stored in the DB in the first place?

I wrote a user-defined processing module for DataYours, which would allow just this, but, in the spirit of recording what's actually received, rather than what you would like to be received, I haven't implemented that for the Historian.  There's a philosophical discussion to be had there (one which has been going on here on this forum for at least five years in the context of the dataMine plugin, see: http://forum.micasaverde.com/index.php/topic,14692.0.html)

I'm open for good suggestions as to how to proceed.

So, to recap, there is a simple (and crude) solution, which I'm happy to share, if that addresses your need.

Title: Re: openLuup: Data Historian
Post by: bruring on November 29, 2018, 12:15:43 pm
Thanks for your reply akbooer.

I'm sure it's a Zwave transmission glitch or sensor glitch (they are getting smaller and cheaper, so I think the ICs are prone to measuring errors once in a while).

If you could share the crude clean-up code that would be awesome. I'm going to test the InfluxDB route in the near future as well.
Title: Re: openLuup: Data Historian
Post by: akbooer on November 29, 2018, 01:31:28 pm
If you could share the crude clean-up code that would be awesome.

I'm looking at improving this massively, possibly adding it as an editable HTML table to the Console > History DB page, along with the graphic.  However, for the moment here's a file graphite-editor.lua which you should put into cmh-ludl/cgi/.

I actually invoke this from a link on my Grafana pages:
Code: [Select]
http://openLuupIP:3480/cgi/graphite-editor.lua

This brings up an HTML page with three sections:

There are some subtleties.  All archives older than the one retrieved and edited get updated using the correct aggregation function - this means that everything is taken care for you and you don't need to do separate edits for each individual archive within the Whisper file, which will remain data consistent.  BUT, the question is "which archive did you retrieve?"  The answer depends on the OLDEST time interval that you asked for, and the definition of the file's retentions, so you are best to set the From time to relatively close to the time of the errant sample, and do so as soon as you spot the error.  It's not a problem, though, because after a time all the younger archives will have been overwritten.

So, as I warned you, it is very crude, but quite easy to use - the explanation takes far longer to read than it does to make an edit. 
Title: Re: openLuup: Data Historian
Post by: skogen75 on February 07, 2019, 04:04:13 pm
This is really great stuff.  Definitely lucky to have someone as dedicated as you akbooer.  I have been playing catch-up over the last month or so and have, for the most part, done so. 

That is, I have openLuup and Grafana running on a pi.  I am logging system metrics to a nas.  I have been able to view both old DataYours whisper files and new Historian whisper files in Grafana.  Really great stuff, but...I have a bit of a problem currently and another question about pushing data to a whisper file using a scene (save that one for later). 

First the problem I'm having...I can plot openLuup metrics in Grafana, but when I try to zoom to a time period using the mouse to select a new time range, no data or partial data shows.  I've used the built-in Grafana query inspector to look at the data request and the response, and something doesn't seem quite right.  When I 'zoom' into old data using a relative time request like this (note:  from=-2d and until=-1d),

Code: [Select]
    xhrStatus:"complete"
    request:Object
    method:"POST"
    url:"api/datasources/proxy/1/render"
    data:"target=openLuup.2_openLuup.openLuup.MemAvail_Mb&from=-2d&until=-1d&format=json&maxDataPoints=2000"

the data shows in the graph, but if I try to use UNIX Epoch time in the request like this (note: from=1549399500)...

Code: [Select]
    data:"target=openLuup.2_openLuup.openLuup.MemAvail_Mb&from=1549399500&until=-1d&format=json&maxDataPoints=2000"
or...

Code: [Select]
data:"target=openLuup.2_openLuup.openLuup.MemAvail_Mb&from=1549399500&until=1549485900&format=json&maxDataPoints=2000"
I get nothing showing on the graph.  If I inspect the response, I see that in the first case I get back 256 data points with the first one being two days ago from the present system time.  But, if I use the UNIX epoch time for that same time I get back 3 points with the first one being ~24 hours after the requested time, it's like I'm getting data back, but for the wrong time range.   

I've searched and searched for what is going wrong, even peeked at some of openLuup source, but haven't yet been able to find the problem.  Could this be a bug in the historian.lua or whisper.lua in the conversion from one time to another?
Title: Re: openLuup: Data Historian
Post by: akbooer on February 07, 2019, 05:15:15 pm
Great diagnostic work!  I wish everyone was as thorough as describing and investigating problems...

I know where the problem might be - there is a syntax ambiguity between Unix epoch and ISO format times if they're not fully expressed.

I'll take a look over the weekend, because I'm otherwise busy until then.

Sorry for the problem, but glad that you are, on the whole, enjoying the system!
Title: Re: openLuup: Data Historian
Post by: skogen75 on February 07, 2019, 07:47:18 pm
Wonderful, I will patiently wait for the results of your investigations.  Please let me know if you would like me to provide more information. 
Title: Re: openLuup: Data Historian
Post by: reneboer on February 08, 2019, 07:15:44 am
Hi,

I see an occasional LUA error on line 101 in whisper.lua. The f: format fails with the error it expects a number but gets a string.

Maybe this helps.

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: akbooer on February 08, 2019, 11:41:18 am
I see an occasional LUA error on line 101 in whisper.lua. The f: format fails with the error it expects a number but gets a string.

Interesting... I've not noticed that, and the code hasn't changed for over 5 years!

Must be something using it incorrectly... any more diagnostics available on that (ie. what's going on when it happens?)

Thanks
Title: Re: openLuup: Data Historian
Post by: akbooer on February 08, 2019, 11:58:42 am
First the problem I'm having...I can plot openLuup metrics in Grafana, but when I try to zoom to a time period using the mouse to select a new time range, no data or partial data shows.  I've used the built-in Grafana query inspector to look at the data request and the response, and something doesn't seem quite right.  When I 'zoom' into old data using a relative time request like this (note:  from=-2d and until=-1d),

I've tried, and failed, to replicate this error.  Using the mouse in Grafana to zoom into a graph seems to work just fine for me.  Perhaps we are using different Grafana versions?  I'm somewhat out of date, running on:

Grafana v3.1.1 (commit: v3.1.0+20-g2bfccd7).

With your HTML examples, I'm not surprised.  It is, as I mentioned, the fact that the current implementation of the Graphite API uses ISO 8601 date/time format (YYYY-MM-DDThh:mm:ss and variants.)  This is, indeed, not strictly in keeping with the Graphite standard, but is for historical reasons, and hasn't for me caused any problem with Grafana.

I need to go an check the Graphite documentation again to see which formats are supported there.  Some of their relative time syntax can be quite exotic, and I didn't implement all of them.


Title: Re: openLuup: Data Historian
Post by: skogen75 on February 09, 2019, 11:50:43 am
I checked the Grafana version and I'm running v5.4, so much newer. 

Yes, it seems that Grafana is using epoch time when querying the database using a mouse selection box, and for things like 'yesterday' and 'day before yesterday' according to the query inspector.  I suppose the built-in query inspector was not yet implemented in v3.1.1 (it arrived in V4.5), so you cannot easily look to see how the request from Grafana looks on your system. 

The things that work are the relative times that use a 'now', like 'from=now-2d' and 'to=now-1d' gives me yesterday. 

I have searched the documentation and cannot find a setting that allows one to choose the time format for the query.  Perhaps there is a config file or something.  The other option would be to add a handler for epoch time in the graphite API that openLuup uses.  How to move forward?
Title: Re: openLuup: Data Historian
Post by: akbooer on February 09, 2019, 02:30:31 pm
I checked the Grafana version and I'm running v5.4, so much newer. 

Clearly, I need to update.

Quote
Yes, it seems that Grafana is using epoch time when querying the database using a mouse selection box, and for things like 'yesterday' and 'day before yesterday' according to the query inspector.  I suppose the built-in query inspector was not yet implemented in v3.1.1 (it arrived in V4.5), so you cannot easily look to see how the request from Grafana looks on your system. 

Actually, easy.  Just enabling debug on the Graphite API implementation tells me what's going on.  Indeed, I see that epoch time is used.  Curious, though, since this seems to be in direct conflict with the official Graphite API:

https://graphite.readthedocs.io/en/latest/render_api.html#from-until

Quote
The things that work are the relative times that use a 'now', like 'from=now-2d' and 'to=now-1d' gives me yesterday. 

Yes, that's explicitly coded, per the documentation.

Quote
I have searched the documentation and cannot find a setting that allows one to choose the time format for the query.  Perhaps there is a config file or something.  The other option would be to add a handler for epoch time in the graphite API that openLuup uses.  How to move forward?

Maybe there isn't a setting anywhere.  Curiously, the epoch time seems to work sometimes.  It's been over 5 years since I implemented this.  I need to look at it closely again.  I'm sure we can fix this.
Title: Re: openLuup: Data Historian
Post by: akbooer on February 09, 2019, 06:12:02 pm
I've pushed a fix to the development branch (v19.2.9) ... trusting that this doesn't break any other app.

Simply type development into the openLuup Update box on the Plugins page and click the update button.

The use of Unix epoch in the from/until parameters still defies the official documentation*, IMHO, but that's what Grafana seems to do.

Note that if you're requesting a time interval a long time in the past, then it may be that the archive retention definition is such that no data actually exists in that interval.  i.e. the sample rate of the archive is coarser than the requested rendering interval.

_____________

* I do note, however, that epoch IS used in the Metrics (rather than the Render) API, per this doc page:

https://graphite-api.readthedocs.io/en/latest/api.html#the-metrics-api

Title: Re: openLuup: Data Historian
Post by: skogen75 on February 09, 2019, 07:18:09 pm
Updated to the latest development version of openLuup as you noted (v19.2.9). 

Genius, absolute genius.  It's working as expected with the update.   

I have a couple more questions that I will get to in the next day or so.  Thanks.
Title: Re: openLuup: Data Historian
Post by: reneboer on February 10, 2019, 08:33:44 am
I see an occasional LUA error on line 101 in whisper.lua. The f: format fails with the error it expects a number but gets a string.

Interesting... I've not noticed that, and the code hasn't changed for over 5 years!

Must be something using it incorrectly... any more diagnostics available on that (ie. what's going on when it happens?)

Thanks
It could be some variable with an off value type populated on my system. I'll keep an eye out.

Cheers Rene
Title: Re: openLuup: Data Historian
Post by: skogen75 on February 10, 2019, 08:38:48 pm
Now that I have data collection humming along I, of course, need to up my game and have a couple questions/clarifications.

1.  I see that the 'storage-schemas.conf' file is in the /history path.  I also understand that the storage-schema for a new file is determined by regex matching to the pattern, such as '/.d$' matches a file ending in '.d'.  My question is how do I set the filename?  In DataYours, I request to watch a variable who's metric ends with a '.watts' for example and I can modify the storage-schemas for that metric type.  Now, in data historian I simply check a box next to a variable, is there a way to specify the metric filename so that I can match a particular storage-schema?  Yes, I want to customize the storage-schema for my metric types. 

2.  I have an outdoor temperature sensor (MySensor-based).  I have PLEG resetting a string variable each day at midnight to the current outdoor temperature.  I also have PLEG watching for variable changes and setting two string variables, one tracking the lowest temperature of the day, and another tracking the highest temperature of the day.  I would like to set up a PLEG trigger at 23:59 to push each of these temperatures to two whisper files (high and low) with a timestamp of noon and have a storage-schema 1d:10y. 

How would I create the files initially, and secondly push these variables with timestamp to the file.  To push the variables, I think part of the answer is 'netcat' command, but not exactly sure.  (maybe this is not a DataHistorian topic, sorry).  I know I could set it up as a storage-schema of '5m:1d,1d:10y' with aggregation of 'max' or 'min' depending on whether it the 'high' or 'low' variable, respectively.  But, what time will be reflected in the '1d' aggregation?  I've sort of tried this and I see that the timestamp of the capture is some seemingly random time, but always the same time, just not when I want it to be captured. 

Many thanks for some quick guidance on these topics. 
Title: Re: openLuup: Data Historian
Post by: akbooer on February 11, 2019, 06:22:40 am
Now that I have data collection humming along I, of course, need to up my game and have a couple questions/clarifications.
[...]
Many thanks for some quick guidance on these topics.

This ain't going to be quick...

Quote
1.  I see that the 'storage-schemas.conf' file is in the /history path.  I also understand that the storage-schema for a new file is determined by regex matching to the pattern, such as '/.d$' matches a file ending in '.d'.  My question is how do I set the filename?  In DataYours, I request to watch a variable who's metric ends with a '.watts' for example and I can modify the storage-schemas for that metric type.  Now, in data historian I simply check a box next to a variable, is there a way to specify the metric filename so that I can match a particular storage-schema?  Yes, I want to customize the storage-schema for my metric types. 

Quote
2.  I have an outdoor temperature sensor (MySensor-based).  I have PLEG resetting a string variable each day at midnight to the current outdoor temperature.  I also have PLEG watching for variable changes and setting two string variables, one tracking the lowest temperature of the day, and another tracking the highest temperature of the day.  I would like to set up a PLEG trigger at 23:59 to push each of these temperatures to two whisper files (high and low) with a timestamp of noon and have a storage-schema 1d:10y. 

How would I create the files initially, and secondly push these variables with timestamp to the file.  To push the variables, I think part of the answer is 'netcat' command, but not exactly sure.  (maybe this is not a DataHistorian topic, sorry).  I know I could set it up as a storage-schema of '5m:1d,1d:10y' with aggregation of 'max' or 'min' depending on whether it the 'high' or 'low' variable, respectively.  But, what time will be reflected in the '1d' aggregation?  I've sort of tried this and I see that the timestamp of the capture is some seemingly random time, but always the same time, just not when I want it to be captured. 

Several ways to do this:

In Historian, you cannot lie about the time that the variable is written.  In the other two options your can write using any timestamp you please...

HOWEVER...

...be aware that the aggregation functionality of Whisper files means that the precision of the timestamp will only be as good as the sampling of the archive which is holding the value.  Thus with a '1d' precision, the timestamp of any value is truncated to 00:00 on the start of the day in question.  If you wanted '12h' precision, you could do that with that schema but it would mean that you would only be using every-other slot in the archive file (which may not be a problem.)

The example post I linked to shows that to create a one-off file directly (rather than using rules) you just need: a call like

Code: [Select]
whisper.create (filename, "1s:1m,1m:1d,5m:7d,1h:90d,6h:1y,1d:5y", 0, "sum")

To write a datapoint:

Code: [Select]
whisper.update (filename, value, timestamp)

I would caution against using this direct method, unless you have very good reasons, preferring to use the Historian either directly, or as a Graphite server via the UDP datagram (which again is a couple of one-liners in openLuup):

Code: [Select]
local io = require "openLuup/io"
local myUDP = io.udp.open "123.12.34.56:1234"    -- create the port

Code: [Select]
myUDP:send "filename value timestamp"   -- write a data value

Do not, whatever you do, write arbitrary Whisper file in the Historian directy, although you may do so in the DataYours/Whisper directory.

So, as I said, not 'quick' guidance, but perhaps having waded through it, not very hard at all.

I feel sure you'll have further questions, though, ...
Title: Re: openLuup: Data Historian
Post by: rafale77 on February 11, 2019, 12:06:20 pm
Just started playing with it on grafana and I have to say kudos. It is pretty awesome and I had been looking for something like this on and off since I never really implemented DataYours.
Title: Re: openLuup: Data Historian
Post by: skogen75 on February 12, 2019, 09:13:43 pm
@akbooer

...still processing the information you provided...

In the meantime I've run into a problem with the built-in openLuup console and Historian.  Clicking on one of the metrics, my browser tries to open

Code: [Select]
http://192.168.0.XXX:3480/render?target=openLuup.2_openLuup.openLuup.MemAvail_Mb&from=2019-02-11T04:06:01
Then I get back

Code: [Select]
./openLuup/graphite_cgi.lua:782: bad argument #3 to 'format' (string expected, got nil)
Yet in Grafana the data is plotted just fine.  In the openLuup console Historian reads 1024 points for this particular metric.  Something to do with parsing the 'to' value?

Title: Re: openLuup: Data Historian
Post by: skogen75 on February 12, 2019, 09:19:39 pm
Yea...I tried entering (I manually entered '&until=')

Code: [Select]
http://192.168.0.XXX:3480/render?target=openLuup.2_openLuup.openLuup.CpuLoad&from=2019-02-10&until=4:26:01
And the data shows up. Strange.
Title: Re: openLuup: Data Historian
Post by: akbooer on February 13, 2019, 05:22:29 am
Strange indeed.  Not specifying 'until' has always worked before and, indeed, none of the links defined on the Console Historian pages have an 'until' parameter and they work just fine.  Need to dig further.

Just tried
Code: [Select]
http://172.16.42.129:3480/render?target=openLuup.2_openLuup.openLuup.CpuLoad&from=2019-02-10

on my machine and this works just fine.

As does:

Code: [Select]
http://172.16.42.129:3480/render?target=openLuup.2_openLuup.openLuup.MemAvail_Mb&from=2019-02-11T04:06:01
Title: Re: openLuup: Data Historian
Post by: akbooer on February 13, 2019, 05:29:56 am
Ah, no, I have it!

The error showing on line #782 is from a debug statement that I inserted while trouble-shooting your earlier problem, and didn't remove!

Delete that line.  All will be well.  Sorry.

I've updated both development and master branches.