Vera can produce an immense amount of data, some of that data could be useful if it were processed and organized. DataMine is a great plugin and is very helpful for storing small amounts of data (Thank you). But it cannot handle large amounts of data, such as collecting power usage from a single device that reports every 5 seconds.
?Side note: it would be great if DataMine could collect/store data at a rate different from the rate that Vera polls the node ?
It is also limited in its ability to organize and manipulate the data that it collects. There are many programs available for PC?s, specifically designed to handle large amounts of data and to perform a wide array of functions on that data; Vera and DataMine are not among these programs.
The program I would prefer is Excel (or Access); the issue is that getting the data out of Vera or DataMine is not easy. I think that a standalone app or better yet and add-on to DataMine that could collect data from one or more devices (like DataMine already does) store it, temporarily or semi permanently (like DataMine already does), and automatically periodically upload it to another device, such as a PC or a Google Drive, would be a great addition to Vera. This would allow a user to collect and view his/her data using the Vera/DataMine UI for basic information and to use a separate more appropriate program for long term storage and/or advanced data manipulation and or graphing. The automatic and periodic upload of date could also have the option of deleting the source data from DataMine, to eliminate large, unnecessary, log files left on Vera/USB.
I feel like once this data is out of Vera it can become a very powerful tool with too many possible uses to list. I do not have the Lua skills to make this happen but I do have the VBA/excel skills to perform the data manipulation and would be happy to help anyone who needs it but my hands are tied until we can liberate the data from Vera.
Datamine is not currently in active development and support for it is very limited. There are a few options that are in current development. I’m currently mobile but I’ll post these options when I can.
http://forum.micasaverde.com/index.php?topic=24669.0
http://forum.micasaverde.com/index.php?topic=16984.0
I have been using EventWatcher for a few months now and it is useful in some of the same ways and in some new ways compared to DataMine but it does not allow for the collected data to be automatically uploaded to a remote system.
I have been reading about the DataYours plug in, since I posted, and it looks like a nice step forward from DataMine. But does it allow for the collected data to be easily and automatically exported or uploaded to a remote system? Can DataYours dump the collected data to a Google Drive file every night? I appreciate all the effort that has gone into and continues to go into the development of these apps but they are inherently limited in their functionality, and will never compete on the same level as a program like Excel, or many others (I am not blindly supporting Excel, it?s just so prominent).
It is a one line HTTP call to [tt]DataYours[/tt] to retrieve data over any date range for a single variable in CSV or JSON format. A [tt]cron[/tt] task or similar could do the upload you need. If you have a suggestion for how to streamline this, I’m more than happy to try and accommodate.
[Edit: fully agree that getting data out and into a more industrial strength analysis environment is very desirable. I use Matlab, myself for ad hoc plotting and processing.]
Would it be possible to have settings page, separate from the Vera settings page and similar to the UI found in DataMine, that would allow a user to enter a Google Drive account credentials, set a repeating schedule, and define a list of variables. Then have the DataYours upload and patch a the list of variables to a CSV file. The Google Drive API (Files: update | Google Drive | Google Developers) does allow for patching existing files, so this should be a relatively easy thing to do. Scheduling could be implemented using the Vera’s scheduling or trigger system, allowing other devices to trigger the upload.
This sounds quite ambitious!
Would it be possible to have settings page, separate from the Vera settings page and similar to the UI found in DataMine, that would allow a user to enter a Google Drive account credentials, set a repeating schedule, and define a list of variables.
...so far, so good...
Then have the DataYours upload and patch a the list of variables to a CSV file. The Google Drive API (https://developers.google.com/drive/v2/reference/files/update) does allow for patching existing files, so this should be a relatively easy thing to do.
Looks like (as expected) the API doesn't have a Lua binding. I ran into the same problem with Google Charts, and in the end made one from scratch.
Scheduling could be implemented using the Vera's scheduling or trigger system, allowing other devices to trigger the upload.
again, that bit is very doable.
So, apart from the auto-upload, it would be possible.
If this were to happen on a daily basis, uploading all of yesterday’s data, the database archives would only need to be two days long. For ‘normal’ usage, with sampling every 5 minutes, say, then the database would only use 20 kb per channel, so could probably be entirely on the internal virtual drive. For 5 second sampling, as in your OP, each channel of those would take ~1.2 MB.
what about emailing the CSV files? I don’t have a IFTTT (https://ifttt.com/connect/gmail/google_drive) account but i believe that it could help automate the process.
If DataYours could email the CSV file with a ‘flag’ word in the subject line then IFTTT should be able to save it to Google drive, i am not sure if it could be amended to the end of an existing file but if the files are uploaded daily with a file name that contains a date stamp then they can be manually integrated, or I am confident I could write a simple VBA script to do it.
Even without the IFTTT, getting the file out of Vera and into an email would allow the user to store it wherever they see fit.
I use a USB dongle for my DataMine logs, so I have not run into a problem; if DataYours is currently restricted to the internal memory then perhaps the auto email could be generated at say 75% full.
It would be helpful if the auto email was sent at a predictable time of day, but if the data itself is time stamped then integrating it shouldn’t be an issue.
[quote=“pulpman52, post:7, topic:181553”]what about emailing the CSV files? I don’t have a IFTTT (Quickly connect Gmail to Google Drive - IFTTT) account but i believe that it could help automate the process.
…
Even without the IFTTT, getting the file out of Vera and into an email would allow the user to store it wherever they see fit.[/quote]
Yes, I think that getting it out would be useful.
I use a USB dongle for my DataMine logs, so I have not run into a problem; if DataYours is currently restricted to the internal memory then perhaps the auto email could be generated at say 75% full.
It would be helpful if the auto email was sent at a predictable time of day, but if the data itself is time stamped then integrating it shouldn't be an issue.
No, it's not restricted at all. I ran mine for months pointing the whisper database to a sub directory on the dataMine USB.
The data are time stamped, but it would take less space if this were to be omitted. They are all uniformly spaced, so unambiguous.
would it be easy to add a periodic automatic email feature?
I would imagine the settings would include which variables to export, how often (time of day) to export them or if the Vera Triggers could trigger the email, the email account to send the file to (from the email account already set up for notifications), and the subject line of the email (for use as a flag word).
I must admit I have still not had a chance to set up DataYours on my Vera, but I am looking forward to getting it running… I have a newborn at home so not sure when it will happen :).
There’s little point in duplicating the emailing functionality that several other plugins already provide. It would be a matter of generating the file and passing it to one of those. I am concerned about the size of this, though.
I think most people and most channels will be more likely using a 5-20min reporting time which as you mentioned is a much more reasonable size for daily exporting/emailing. As for my one channel that I would ideally like to have reporting every 5 seconds, this could be sent on a more frequent basis perhaps every 6 hours.
For this reason I think it would be important to allow different groupings of variables to be sent at different intervals and with different ‘flag’ words.
I would also note that Gmail’s attachment limit is 25MB, much larger than most providers. I assume if the outgoing account and the receiving account are both Gmail then Vera/plugins don’t have any restriction on attachments sizes.
Actually in my case I don’t need (or want) DataYours to be logging my variable every 5 seconds. I do want Vera to poll it that frequently, I want the live data to be updated very frequently but the stored/logged data does not have to be that detailed. This goes back to my OP, it would be great if DataYours could collect and store/log the variables at different rates from Vera. Perhaps a setting that would expect an integer for the number of polls to ignore.
If Vera is reporting every 5 seconds and the setting in DataYours is “60” then DataYours would record the node every 5min.
OK, just as well.
DataYours already stores variables on a different (and user-defined) timescale from Vera.
Actually, thinking about this more, there’s nothing that needs to be added to DataYours to do what you want. A simple Lua script embedded in a scheduled scene can easily construct a CSV file with whatever variables you care for.
Any chance I could get you to write an example, I don’t need a polished snippet just something with enough keywords that I can Google.
Yes, of course, but have you got things running yet?
Here’s a piece of code that will make a text dump of yesterday’s data from a single Whisper database file.
(the hardest bit is calculating the start and stop times for yesterday
- if someone has a better way, that would be good to know):
--
-- text dump of yesterday's values for a Whisper database variable
--
local whisper = require "whisper2"
-- yesterday (), return start and stop times for yesterday
local function yesterday ()
local x = os.date ("*t")
local y = os.time {year = x.year, month = x.month, day = x.day, hour = 0}
return y - 24*60*60, y - 1
end
local file = "/dataMine/whisper/Vera-35104005.307.urn^micasaverde-com^serviceId^GenericSensor1.OutdoorDewPoint.wsp"
local data = whisper.fetch (file, yesterday ()) -- this is the 'one-liner' data retrieval
-- print the data
print (file)
print ('number of points:', data.values.n)
for i, v,t in data:ipairs() do
print (os.date ("%H:%M", t), v)
end
print 'done'
…and here’s the start of the output, as produced by Rex Beckett’s excellent LuaTest module:
Results
No errors
Runtime: 130.1 ms
Code returned: nil
Print output
/dataMine/whisper/Vera-35104005.307.urn^micasaverde-com^serviceId^GenericSensor1.OutdoorDewPoint.wsp
number of points: 144
00:00 11.1
00:10 11
00:20 10.7
00:30 10.9
00:40 10.7
00:50 10.6
01:00 10.5
01:10
01:20
01:30 10.3
01:40 10.1
Note that the gaps in the values columns are ‘nil’ in the data, where no variable change occurred in that interval.
Also note that I have released a version 2 of [tt]DataYours[/tt], which is hopefully easier to configure: http://forum.micasaverde.com/index.php/topic,25800.msg182682.html#msg182682
Thank you for this and of course for the plugin. I installed datayours2 yesterday and so far it is working as expected, I haven’t had a chance to fully explorer yet but I will leave some feedback in the other forum when I do.