Direct Storage from HS Devices to InfluxDB

[John245]

Looks interesting. Is there an option to change the storage location of the InfluxDB bucket. For e.g. to a NAS (running on Ubuntu 20.04 LTS).

---
John

The question came up of how to delete selective record(s) in InfluxDB. I found an easy to understand guidance at How to delete data from InfluxDB tables - Tutorials & Examples - openHAB Community. For my test I viewed data in a measurement then deleted the last row which is identified by its time value. There are other discussions on Google where the primary criteria is that the criteria needs to be based upon a time value.

here's another command to delete multiple series sharing the same naming structure (w/ partial string search) instead of doing it one by one:
It took me a while to find out the correct way to do this so hope it helps:

>USE YOURDATABASENAME

>DROP SERIES WHERE "device" =~ /SENSORS18B20/

which would delete the bolded series below with one action;
if you change a device name/location in HS, the original naming structure will remain used by MCSMQTT unless you un-associate and then re-associate the "I" column.
Which will leave you with two series and you'll probably want to delete the old ones.

Quite useful.


mcsMQTT,device=!HS\ STATUS_HS\ INTERNAL_HS\ UPTIME\ (CUSTOM)
mcsMQTT,device=!HS\ STATUS_HS\ INTERNAL_INTERNET\ PING
mcsMQTT,device=!SENSORS\ BRIDGES_OpenMQTTGateway_ESP32_BLE_LWT
mcsMQTT,device=!SENSORS\ BRIDGES_OpenMQTTGateway_SRFB_LWT
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-1:Temperature
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-2:Temperature
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-3:Temperature
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-4:Temperature
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-5:Temperature
mcsMQTT,device=!SENSORS\ TEMP-HUM_SONOFFBASIC3_SENSORS18B20-6:Temperature
mcsMQTT,device=!SERVER_Jon00\ PerfMon_System\ CPU\ Usage
mcsMQTT,device=!TIMING_Timers_TIME\ BASED\ LIGHTING\ ADJUSTMENT
mcsMQTT,device=!WEATHER_CHRISGO_SAINTE-JULIE-Current_summary_humidity
mcsMQTT,device=!WEATHER_CHRISGO_SAINTE-JULIE-Current_summary_pressure
mcsMQTT,device=!WEATHER_CHRISGO_SAINTE-JULIE-Current_summary_temperature
mcsMQTT,device=!WEATHER_CHRISGO_SAINTE-JULIE-Current_summary_wind
mcsMQTT,device=!WEATHER_CHRISGO_SAINTE-JULIE-Current_summary_windchill
mcsMQTT,device=!WEATHER_Jon00\ BBC\ Weather_Humidity\ now
mcsMQTT,device=!WEATHER_Jon00\ BBC\ Weather_Pressure\ now
mcsMQTT,device=!WEATHER_Jon00\ BBC\ Weather_Temperature\ now

Enjoy the long weekend, cheers,


Yann

[Michael McSharry]

I am not a regular InfluxDB user and best I can do for an answer is for google search. Perhaps others that have more experience with it will know the answer.

[Malosa]

The question came up of how to delete selective record(s) in InfluxDB. I found an easy to understand guidance at How to delete data from InfluxDB tables - Tutorials & Examples - openHAB Community. For my test I viewed data in a measurement then deleted the last row which is identified by its time value. There are other discussions on Google where the primary criteria is that the criteria needs to be based upon a time value.

DELETE FROM "mcsMQTT" WHERE time = 1611423088923916687

The test is shown below with first showing the records, then using the delete, then showing the records again and this second time the last record no longer exists.

many thanks, this indeed works for me using random times.


is there also a way to delete this max range?
because if i use delete WHERE time = 1616054238316000000 it does not work.
as my devices are saved like this:


SELECT max("Value") FROM "Homeseer" WHERE ("Name" = 'Gas consumed total today') AND time >= now() - 7d GROUP BY time(24h)

name: Homeseer
time max
---- ---
1615334400000000000 6.727
1615420800000000000 6.024
1615507200000000000 6.53
1615593600000000000 7.503
1615680000000000000 8.243
1615766400000000000 7.707
1615852800000000000 8.07
1615939200000000000 9.547
1616054238316000000 8.213

[Michael McSharry]

The question came up of how to delete selective record(s) in InfluxDB. I found an easy to understand guidance at How to delete data from InfluxDB tables - Tutorials & Examples - openHAB Community. For my test I viewed data in a measurement then deleted the last row which is identified by its time value. There are other discussions on Google where the primary criteria is that the criteria needs to be based upon a time value.

DELETE FROM "mcsMQTT" WHERE time = 1611423088923916687

The test is shown below with first showing the records, then using the delete, then showing the records again and this second time the last record no longer exists.

[oddjob]

Ah, that makes sense. The Pi is already up and running, might as well try something else cool with it. Thanks for all your work with this.

[Michael McSharry]

It is available at http://mcsSprinklers.com/mcsMQTT.pdf. It is also available in the HS store.

There really is no specific relationship between poolController and InfluxDB. If you want history saved of poolController states then InfluxDB and SQLite are both supported by mcsMQTT.

[oddjob]

Is the mcsMQTT manual still available? The link in Post #1 is not working. Thanks, trying to figure out if I need InfluxDB or not for poolController.

[Cor]

I would be great if you made the change in the PI.

But no hurry, it's working fine now.

[Michael McSharry]

It could be done either way. From the plugin I would just use the duration text box already on the History tab and make it apply to both SQLite and InfluxDB. I could then either write data with a retention tag or run query each day.

For you to do it then you need to define the default retention policy for the database in InfluxDB.

[Cor]

Ok, that is a valid line of reasoning. We will see how the use of the PI develops.

To implement the duration capability as for SQLite, is that something you could do in the PI or is it some direct action from me towards the database?

[Michael McSharry]

The default retention policy is setup with the database, but each write to the database can specify a specific retention policy. With the SQLite implementation the user specifies the retention duration and the plugin runs a query on the database once a day to remove old data. With InfluxDB the same can be done without the plugin doing the removal, but either way will work. The related consideration is subsampling the older data before removal such as keeping the hourly mean rather than the raw samples, then daily mean for even older data.

I do not want to work with the retention policy in the plugin until there is more discussion with the community as to how they have used it in the past so what is done in the plugin is in line with an effective use. At this time the user can setup the retention policy for the database they create. It would be easy to use the duration capability already that exists for SQLite and apply it InfluxDB.

[Cor]

Michael, I guess the database may grow very big without some retension policy. I my case I don't need the data for more than, say, 30 days,

Would it be a good idea to have a button as with the SQLite DB 'Days of History Retention'?

I think this is something that must be set up during the creation of the database.

[Michael McSharry]

Cleanup which I hope will take care of your observation at Multiple lines on each of two axis & InfluxDB Charting - HomeSeer Message Board

I can only explain it for the case where influx checkbox is selected before the associate checkbox. I handled this scenario in the above version 5.13.5.0. I also tested the updater override for HS4.

[Cor]

Michael, another observation. In the Field Format I selected Loc2_Loc1_Name_Ref but the Ref is missing in the device field.
See my other request (in post #108) if it is possible to make Ref a separate field in the Field Format section of History? So i.e. 'Loc2_Loc1_Name Ref'

Edit:
For the Non-Plugin HS Devices the Ref is correctly presented in the Field format

2021-01-19T13:19:39.513442898Z Sensor_Cozir_RV Buitenlucht 83
2021-01-19T13:19:39.558462155Z Sensor_Cozir_CO2 Buitenlucht 328
2021-01-19T13:19:39.570069908Z Sensor_Cozir_abs. vocht. buiten 5.9
2021-01-19T13:19:39.594940087Z Sensor_Cozir_dauwpunt buiten 7.6
2021-01-19T13:20:02.748364933Z PHEvoHome_Badkamer_Measured Temperature_1714 18.5
2021-01-19T13:20:02.783738396Z PHEvoHome_Badkamer_Setpoint_1715 17
2021-01-19T13:20:03.135415395Z PHEvoHome_Woonkamer_Setpoint_1709 22
>

PHEvoHome* are Non-Plugin HS Devices