Announcement

Collapse
No announcement yet.

Direct Storage from HS Devices to InfluxDB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Cor
    It looks like #11 will be the one. I will confirm this works on my end as well. The startup build of the device list for Influx should be able to be solved. You have given me the parameters for the testing.

    WhitW
    The problem I have had the past many months is that HST Uploader does not like low bandwidth connections and it times out before the zip file or pdf file are uploaded. It means I need to go somewhere that has a faster connection to upload the files.

    The recent significant changes are for InfluxDB and an expanded graphing capability. It looks like closure is coming on these in a matter of days. Since it is hard for me to upload to Updater I want to make certain the plugin is stable. Later this week is a good target.

    Comment


      I believe everything is in order with http://mcsSprinklers.com/HSPI_mcsMQTT_5_14_0_0.zip. The same dll file was updated and that is all you need or the Uploader override process can be used. This also contains the charting updates that include data from InfluxDB and multiple lines on an axis. Another thing I did was to queue the InfluxDB writes so any problems with the InfluxDB server not responding will not hold up other event processing. Let me know if you have any issues with it.

      Comment


        Looks good now, also the device list for InlfuxDB. Great work!

        Is it a big deal to make Ref a separate field in the Field Format section of History?

        The Uploader override process of the latest version did not work correcly. It complained about missing zip file but it referenced the old version, not 5.14.0. If I change the updater_overide it still did no do the update. So I just moved the dll and the exe over.
        The general tab is still reporting version 5.13.4.0.

        Comment


          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

          Code:
          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

          Comment


            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.

            Comment


              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.

              Comment


                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.

                Comment


                  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?

                  Comment


                    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.

                    Comment


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

                      But no hurry, it's working fine now.

                      Comment


                        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.

                        Comment


                          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.

                          Comment


                            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.

                            Comment


                              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.

                              Code:
                              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.

                              Click image for larger version

Name:	Capture.PNG
Views:	187
Size:	74.9 KB
ID:	1463858

                              Comment


                                Originally posted by Michael McSharry View Post
                                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.

                                Code:
                                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
                                Preferred -> Jon's Plugins, Pushover, Phlocation, Easy-trigger,
                                Rfxcom, Blade Plugins, Pushbullet, homekit, Malosa Scripts




                                HS3Pro 4.1.14.0 on windows 10 enterprise X64 on hp quadcore laptop 8 GB.

                                Comment

                                Working...
                                X