Indigo Domotics

Hi!

I'm new to Indigo and I have problems understanding/using the SQL logger:

I have an Aeotec Multisensor 6 giving me temperature and humidity values every 5 minutes (I can see those in the event log) and I want exactly those values logged in the SQL database. In the logger's configuration I checked the

'Device state history: Store device state changes in the database'

and didn't check

'Changed states only: Store only device states that change...'

Unfortunately, it looks like the data in the database are only taken when the value is changing - which (from my understanding) is the behaviour resulting from the second checkbox. Am I missing something?

Thanks for your help,
k.

how do you test = could you post your sql statement?

I used

Code: Select all

sqlite3 -header -column indigo_history.sqlite "SELECT * FROM device_history_865130460;"

and this is a typical output (as I said, data are coming in every 5min!):

Code: Select all

2614        2017-01-17 14:13:16  21.8         21.8 °C
2615        2017-01-17 15:08:32  21.7         21.7 °C
2616        2017-01-17 15:18:35  21.8         21.8 °C
2617        2017-01-17 15:28:38  21.7         21.7 °C
2618        2017-01-17 16:03:49  21.8         21.8 °C
2619        2017-01-17 16:28:57  21.9         21.9 °C
2620        2017-01-17 16:33:58  21.8         21.8 °C
2621        2017-01-17 16:39:00  21.9         21.9 °C
2622        2017-01-17 17:14:11  22.0         22.0 °C
2623        2017-01-17 18:54:41  21.9         21.9 °C
2624        2017-01-17 20:00:01  21.8         21.8 °C

I just tried to reinstall the plugin - but doesn't help...

I guess this is a question to mat and jay.


Sent from my iPhone using Tapatalk

The SQL Logger plugin only adds rows to the table when a device state changes. The Changed states only checkbox changes how many states/columns are logged in each row. When it is disabled if any state changes the row will be written with all state values (the one that changes, and all the others). If the checkbox is enabled, then only the state(s) that change are added to the new rows resulting in a more compact table.

There isn't a way to have the SQL Logger plugin log all received values – it only triggers off of state changes. There is a low level Z-Wave command received hook you can patch into via a custom plugin if you wanted to try that route. It would require a bit of work though to parse out the temperature values from the raw commands.

You could give this plugin a go:

http://forums.indigodomo.com/viewtopic.php?f=134&t=17682
You would need to create a variable that contains your temperature, and a trigger to keep it up to date.

The data then gets logged to graphite every second, to make the graphing easier.

Thanks for clarifying, Matt!

However, I do find this logger behavior really strange: Logging a received value with time stamp _is_ an important information even if the sensor value didn't change - and I would like to collect these data!

I was already wondering why the plotting plugins I tried (matplotlib and indigoplotd) use their own database and don't just plot the SQL data points. I would guess that the graphite data logger works similar - I don't want an extreme oversampling (1-s-sampling of data coming in every 5min) producing unsynchronized, artificial data points.

Matt, would it be a problem to change the logger's behavior and add the option to store all data? Am I really the only one who would like that?

I believe it is easier to create the values that you want instead of increasing the load on the sql server.
Writing to the server works ok if you have some devices/states. I have about 300 devices times 20 states and 200 variables. That increase the size of the database
My problem is the other way around trying to reduce the growth of the dB




Sent from my iPhone using Tapatalk

kallovsky wrote:

Matt, would it be a problem to change the logger's behavior and add the option to store all data? Am I really the only one who would like that?

Unfortunately, it isn't just the logger that would require the change so it would non-trivial.

kallovsky wrote:

I was already wondering why the plotting plugins I tried (matplotlib and indigoplotd) use their own database and don't just plot the SQL data points.

For matplotlib, my design scheme was based off my individual desire to display simple charts using a nice even time series--for example, plot the outside temperature every 15 minutes. So rather than try to figure out which SQL observation was closest to my 15 minute requirement, or try to interpolate "missing" data, I found it easier to create the data that exactly met my needs. An added bonus was to not have to try to rely on the installation and maintenance of something I don't control (I didn't want to try to do technical support for SQL either to be honest). But the best part on the data side for matplotlib is that if something does go wrong that we can't fix, we can just delete the one data file and the plugin will start a new data file automatically.

OK - so I am trying the following atm:

I created a variable Temperature which is updated by a trigger Z-Wave Command Received, Incoming Command Received from Device. The trigger inserts the state of the temperature sensor into the variable and tells the thingspeak plugin to upload data to the server. This seems to work (with only a few seconds offset) and comes close to what I had in mind (although I'm still surprised that there's no built-in data management with exact time stamps ... ).

Dave, you are right, as long as the SQL library only stores infrequent data points, that solution is much easier! I will try the thingspeak solution for a while (Thanks for that plugin, too!) - and see how that works out!

Thank you all for your help,
k.

kallovsky wrote:

OK - so I am trying the following atm:

I created a variable Temperature which is updated by a trigger Z-Wave Command Received, Incoming Command Received from Device. The trigger inserts the state of the temperature sensor into the variable and tells the thingspeak plugin to upload data to the server. This seems to work (with only a few seconds offset) and comes close to what I had in mind (although I'm still surprised that there's no built-in data management with exact time stamps ... ).

Dave, you are right, as long as the SQL library only stores infrequent data points, that solution is much easier! I will try the thingspeak solution for a while (Thanks for that plugin, too!) - and see how that works out!

Thank you all for your help,
k.

I don't know exactly how you're using the Thingspeak plugin (you're very welcome) but the plugin supports both remote upload as well as a local Thingspeak server. I know there is at least one user that's used the plugin for a local install.

Dave

I'm currently using the Mathworks' Thingspeak server and the upload seems to work without any problems. Will play with some custom visualizations in the next days...

Just two suggestions for your plugin: It would be great to be able to set the upload interval to only manually and to be able to manually update individual channels, not only all channels!

kallovsky wrote:

I'm currently using the Mathworks' Thingspeak server and the upload seems to work without any problems. Will play with some custom visualizations in the next days...

Just two suggestions for your plugin: It would be great to be able to set the upload interval to only manually and to be able to manually update individual channels, not only all channels!

Great suggestions. I'll add both those to the list for a future update.

Cheers!
Dave