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.
-
-
here's another command to delete multiple series sharing the same naming structure (w/ partial string search) instead of doing it one by one: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.
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
Comment
-
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
Comment
-
The options that now exists are shown below. The IP of InfluxDB, Bucket name, Measurement name, format of the HS Device identification and any additional data to be attached to the same record are user-selectable. device and value are hard-coded fields where device contains the HS Device identification and value contains DeviceValue.
Comment
-
To my understanding the "Bucket (Database) Name" is the Bucket created in InfluxDB and here all the data is stored. Please correct me if I'm wrong.Originally posted by Michael McSharry View PostThe options that now exists are shown below. The IP of InfluxDB, Bucket name, Measurement name, format of the HS Device identification and any additional data to be attached to the same record are user-selectable. device and value are hard-coded fields where device contains the HS Device identification and value contains DeviceValue.
What is the function of "Measurement (Table) Name"?
So I need an option to move the Bucket to another location as the InfluxDB server.
---
John
Comment
-
My understanding of the architecture is there is one InfluxDB server at some IP address.
That server will manage one or more respositories. Each is called a Bucket. In SQLServer & MySQL they are called a Database
Each Bucket/Database will organize data into Measurements. Measurements are called Tables in SQLServer & MySQL
Each Measurement will have fields of related information about the measurements (such as Device and Value). These are columns in the SQLServer & MySQL Table.
If you have an existing Ubuntu computer that is running InfluxDB and you are storing your data in Bucket XYZ then mcsMQTT setup would be the IP of the server and the XYZ for Bucket and the same name you are now using for the Measurement.
Where you will run into problems is with the fields. If two of the fields are Device and Value then you will just be adding new data to the existing set of measurements. If not, then the Measurement is not compatible. The format to identify the HS Device may also not be compatible with one of the options provided in mcsMQTT so you would have issues with queries. I could add user selection of the fields, but it is also possible to create a new Measurement and import your original Measurements into the new one. If you have other fields then you may or may not be able to get the expected information to support these. Just depends from where the data supporting them is derived.
You could also continue to use your existing process to store data in InfluxDB and in parallel use a new Bucket for mcsMQTT. You could then later import data into the mcsMQTT Bucket. This allows you to not disturb your existing capabillity and "trial" mcsMQTT support of InfluxDB.
When I went from Influx 1.8 to Influx 2.1 I never did import my prior data back into the 2.1 InfluxDB Bucket. If I need it some day then I can do an import at that time.Comment
-
We have the same understanding of the architecture.
My basic question is can the InfluxDB bucket be stored on another location as the standard location.
I'm afraid that the 32.00 GB for the Influx DB Server will not be sufficient to store all my data.
---
John
Comment
-
I configured the following in mcsMQTT
Based on that I expect that I can filter on Location 2, Location 1 and Name. However I'm only able to filter on Name in InfluxDB.
Do I need to do an additional configuration as data is flowing into InfluxDB.
---
John
Comment
-
If you want separate fields for location information to support queries then you need to specify these in the Extra Identification Fields text box. The radio for Device name is a single "device" field in InfluxDB. Section 8.1 of mcsMQTT user manual provides some guidance in this area. An excerpt is below. The emoji is a colon followed by a parenthesis.
If you entered data for organization Id then mcsMQTT assumes you are using Influx 2.x. Influx 1.x does not require it. This is a recent change when I added the flux query to support reading Influx 2.x data. You are likely using a plugin version before this change was made.Code:[FONT=Calibri]The additional identification fields can be used to help with query of the data for Graphana or other uses. The data is entered as a set of key-value pairs which each pair separated by comma. Replacement variables will often be used and expressions are also allowed. An example of adding three fields (Categroy, Derived, and Location) to the measurement is shown below. The value of each being determined by replacement variables.[/FONT] [FONT=Calibri][FONT=Courier][COLOR=#111111]Category=$$TAG:,Derived=<<(ROUND($$DVR:(123):+$$VALUE:)/2,1)>>,Location=$$FLOOR:_$$ROOM:_$$NAME:[/COLOR][/FONT][/FONT]
Comment
-
I will have a look tomorrow.Originally posted by Michael McSharry View Post
I;m using the latest version available in the updater: 5.21.7.2
---
JohnComment
-
The plugin change log is at mcsMQTT Change Log (HS4 & HS3) - HomeSeer Message Board and the release notes for each Updater version. I believe the only changes made since 5.21.7.2 relate to Influx 2.x. I provided awareness to minimize the future breakage potential.Comment
-
Seems that there a no changes impacting the flow from HS to InfluxDB.Originally posted by Michael McSharry View Post
Where do I need to enter the following:
"Category=$$TAG:,Derived=<<(ROUND($$DVR
123):+$$VALUE
/2,1)>>,Location =$$FLOOR:_$$ROOM:_$$NAME:"
---
John
Comment
-
This text box is part of the InfluxDB setup on the MQTT Page, History Tab.If you want separate fields for location information to support queries then you need to specify these in the Extra Identification Fields text box.
The example extracted from the user manual was to show some flexibility where tagged values and derived values could be included. In your case where you just want location information it would be the following in the Extra Identification Fields text box. This will result in the device, value, location, location2 and timetag fields being stored in every write to the InfluxDB database.
Code:location=$$ROOM:,location2=$$FLOOR:
Comment
-
Thanks a lot for the help.Originally posted by Michael McSharry View Post
This will solve indeed 95% of my issues.
But where to put the info in case I want some devices with an additional field. For e..g. in case of two switches in the same room with identical feature names. In HS there can be distinguished based on the root device
---
John
Comment
-
I can think of three approaches. One is to include the Ref as part of the device id from the radio selector that has four options. This will make the device field unique. It also means less robustness in the data since it is now tied to a specific HS Ref number for which you have no control.
The second is to include a tag field using replacement variable $$TAG: where the Tag text is a text box entry of the Edit tab for each device feature. This complicates the query and if you are using the internal charts in mcsMQTT it will not consider the $$TAG: value as part of its query to get the data for the internally generated charts.
The third is to add another replacement variable that would be $$PARENTNAME: and/or $$PARENTREF: that could be used like the existing $$NAME: and $$REF: . Let me know if you would like me to add these replacement variables. Same downside as approach #2 with the advantage of not needing to enter data for a Tag field to assure uniqueness.Comment
Comment