Write to InfluxDB and different data types

[ksum]

Will there be a problem writing different datatypes to the same InfluxDB measurement? Currently via other means I have different Measurements with different data in InfluxDB. Temperatures, Humidity, Z-Wave Switch Value and Status, Watts. I guess they all could have the same data structure going to the database as far as the table is concerned, but is there a valid reason for not doing this?

My current structure only includes the Value for all of the above measurements except the Z-Wave Switches which have the HomeSeer Value and Status being reported. I could include both for everything, but that seems like additional data which is not necessary for my reporting. I could change my thinking on the database structure and make no pretext as to having a strong knowledge of such, but my thought was that the query would be more efficient if I was pulling most of the table as opposed to filtering via extended Where statements.

For instance, this is my experimenting with InfluxDB and Grafana dashboard wich I started about a week before the InfluxDB inegration was mentioned with mcsMQTT. (Great timing!) The Yellow/Red items are On/Off status of the HVAC system and Z-Wave Switches. The HVAC system is not a great report because the HomeSeer Ecobee devices for these do not update real time so some (most) of the Fan Status is probably missed.

[lveatch]

Will there be a problem writing different datatypes to the same InfluxDB measurement?

Yes if written to the same field. You should use 2 separate fields (e.g. value, status). It would be unlikely to store a string in a float or integer field.

You can use values mappings in grafana to translate values into text.

[Michael McSharry]

mcsMQTT is writing to "device" and "value" where "device" format is a user selection and "value" is the HS DeviceValue. The purpose is to collect the time history of a device's behavior.

When charts are drawn from mcsMQTT and status information is available for a device then the table of the status/value relationships will be created as a legend below the chart.

mcsMQTT maintains a database table in SQLite that has the value/status relationships. An equivalent table could be created in InfluxDB so something like Grafana could also do a query to build the relationships such as is shown by Iveatch in post #2. I have never used Grafana so do not know if such a table would be useful or not for it.

[ksum]

mcsMQTT is writing to "device" and "value" where "device" format is a user selection and "value" is the HS DeviceValue. The purpose is to collect the time history of a device's behavior.

So all the data for "value" is stored as text?

I used the value mapping in my sample Grafana charts for the Z-Wave light Switch status chart. That part is easy.

I am questioning the table design because I currently have to use other means to get the Z-Wave devices into InfluxDB (as discussed elsewhere) but am wondering if I want to use mcsMQTT where I can. I set this up right before the addition in mcsMQTT so I did not have a guide. Using mcsMQTT where I can would mean changing my Node Red flow to store the measurements differently. Not a huge deal add long-term easier to add devices which are also in HomeSeer. But less flexibility. For instance, the Grafana graphs (currently) like to connect previous entries but do not read past the report date. So I have to poll the devices which do not change often to get consistent charts, otherwise I get large gaps of what appears to be no data. I honestly have not looked at the charting/graphing capabilities in mcsMQTT, but suspect I would stay with Grafana IF I stick with charting anything at all. I need to see how much I will actually use it.

My other option is to keep things as is. I am running InfluxDB on a Raspberry Pi which is running from a USB SSD with plenty of room, so no worries on that end. But if I redo the table design, then I need to redo how I name the device in InfluxDB (Your Field Format) as the names I am using may not be related to any HomeSeer device values.

So bottom line, I am debating changing or not changing and just trying to see if there is a reason outside of "All information is entered the same" to change.

[Michael McSharry]

Using mcsMQTT where I can would mean changing my Node Red flow to store the measurements differently. Not a huge deal add long-term easier to add devices which are also in HomeSeer. But less flexibility. For instance, the Grafana graphs (currently) like to connect previous entries but do not read past the report date. So I have to poll the devices which do not change often to get consistent charts, otherwise I get large gaps of what appears to be no data.

If I understand correctly your issue is with how Grafana treats data rather than how data is stored. If you have two data samples that each occur at midnight over a span of two days and you want data to be viewed as lines vs. points then this should be a parameter in Grafana for what should be done between data points. In my charts using .NET datavisualization (and previously with Chart Director) I have the flexibility to present the data as points, as a stairstep, or as linear line between each point. For mcsMQTT charting I use the stair step.

So all the data for "value" is stored as text?

How InfluxDB stores data is transparent to me. I suspect it is in a text format so that dates, numbers, and text can all be retained without any special setup of a bucket.

[ksum]

Grafana can show points as well, but to show a line or filled area as in my charts you need to connect previous data points. If I am reporting on a 12 hour period and the device never changes, then it shows nothing for that device because the query returns no data. If it has 1 piece of data then it cannot connect anything because it does not read beyond the time frame for a previous record.

I think my pertinent question for this thread has been answered, though. You are storing the device's value as well as the device name as selected in the Field Format setting and writing it all to one measurement.

[Michael McSharry]

When I was specifying fixed X axis ranges with HS1/HS2 mcsTemperature I needed to complete the data set by artificially setting a point at the last X datapoint that was the same as the value of the last X datapoint in the query. Grafana should be able to do this too.

[ksum]

When I was specifying fixed X axis ranges with HS1/HS2 mcsTemperature I needed to complete the data set by artificially setting a point at the last X datapoint that was the same as the value of the last X datapoint in the query. Grafana should be able to do this too.

I did not think about that. Thank you for the tip. I will have to see if it is possible.

AND thank you for all the work on the programming and especially support here, Michael. Much Appreciated!

[lveatch]

So all the data for "value" is stored as text?

Probably not, but you can find out.

Log onto you influxdb server and run the influx cli by typing 'influx' at the command line. You can enter 'help' to get a list of commands.

type 'show databases' to display a list of your databases.
type 'use <databasename>' to connect to your homeseer database.
type 'show field keys' to display the fields and their data type for all measurements in that database.

For example, here is one measurement whcih has several data types. for a single measurement.

name: http_response
fieldKey fieldType
-------- ---------
content_length integer
http_response_code integer
response_string_match integer
response_time float
result_code integer
result_type string

For my homeseer database, I have only one measurement, one field (float) and one tag.

[Michael McSharry]

Looks like my 1.8 writes are going into a float type. Likely the same for 2.0

[Guest]

Hi ksum ,
started building all my Grafana Dashboards and wondering which vizualization mode/config you use for the combined red/yellow timelines in your original post ("switch use" and "HVAC status");
Especially how to stack multiple devices, text overlay + current status on the right.
Nice work, Thanks for your help!

Yann

[ksum]

Note first that I am using Node Red to save the data and saved it different than is being done by mcsMQTT. This is for 2 main reasons:

1. I set it up before mcsMQTT saved to InfluxDB
2. I have a bunch of Z-Wave devices which do not yet have Features and at least at first these were not saved by mcsMQTT

For your needs, this means the FROM and WHERE fields may be different. I save the light switches to switch_status and the HVAC items to HVAC.

I installed the Discrete Panel Plugin and use it for those graphs. Note that Grafana does not look farther back than you tell it, so the purple section is where I have no data from the earliest time to the first piece of data. I have Node Red get the Status of the devices hourly, which is good enough for my needs. If I wanted more granular data at the far left I would poll more often, but since a record is written when a change occurs as well, hourly was enough.

Here is how the pertinent parts of the panel are set up. Once I set up item A I copied it for item B, C, D, etc and made the minor changes.





I had to Map the values as shown below since most of my lights are dimmable. I don't care about the dim levels, just On/Off status:

[Guest]

Thanks for the detailed response ksum,
discrete plugin was the key;
This will keep me busy today

for some reason, the plugin shows as "unsigned" so had to modify the defaults.ini :

[plugins]
allow_loading_unsigned_plugins = natel-discrete-panel

did you have the same issue ?

[ksum]

Thanks for the detailed response ksum,
discrete plugin was the key;
This will keep me busy today

for some reason, the plugin shows as "unsigned" so had to modify the defaults.ini :

[plugins]
allow_loading_unsigned_plugins = natel-discrete-panel

did you have the same issue ?

No, I did not have that. My Grafana install is on a Raspberry Pi, just as a standard installation. The same one running InfluxDB and Node Red. The only thing unique about the whole thing is that it is running using a USB SSD as opposed to an SD Card. This is because the SD Cards end up corrupting and the USB SSD is considered the better approach.

So on the install there is no Docker, Home Assistant OS, etc. Just the Linux For Dummies installs on each of the applications.