Sporadic measeruments to timeseries db in a regular interval

Hi together,

I have some idea to save sporadic measurements in a regular interval to a time series database but I'm not sure if that's the right approach or if there are better ones.

Some info:
I'm using node-red-contrib-huemagic to get the events from the hue motion sensor / hue lux sensor.
So whenever there is a motion that node "fires" and event but if there is no motion, there is no event / very sporadic and so nothing gets saved to the time series database ( in my case influxdb )
The node not only has a bool payload for motion, but also for daylight, dark which might be interesting at some point.

The main reason to have regular measurements is to have some nicer graphs with grafana and the discrete plugin. I also understand, that this includes the cost of using more disk space.

Since these hue nodes have no possibility to trigger them I wanted to save the values when there is an event using the context feature.

So whenever the context gets updated by a newer event, the newer values will be saved then.

Is that a good approach or am I totally on the wrong path.

Regards Stefan

I do exactly the same thing with influx to make sure the gap between samples is not too great. I use node-red-contrib-msg-resend which can be setup to keep resending the last message every 5 minutes (or whatever) till the next message arrives.

1 Like

With InfluxDB and Grafana, you don't need to do this. I have similar records for things like my door bell and a digital sound sensor in the hall. But you don't need to do regular records because InfluxDB doesn't care - it is a time series DB so just records the time. It will work out averages and counts and whatever regardless. Grafana doesn't need anything special since you can simply tell it to use the previous value which will give you a continuous display as you would expect.

This shows sound events over the last 7d:


Highlight from the middle:
image

1 Like

I had some problems using Previous Value in the Fill specifier, but I don't remember exactly what the problem was, I will have to carry out some experiments to see what it was. Does it pick up the previous value if there are no samples at all in the selected time range, in grafana for example?

I seem to remember that was a problem until I configured it correctly. Sorry, I'm not at home now, travelling for work so I can't grab my config.

I have determined the problems I have with using Fill(previous).
Firstly I like to have charts showing the time range Today. These start at midnight and show the complete day, with right hand side of the chart empty as it is in the future. If I use Fill(previous) then it fills the time from now to the end of the day with the latest values so I get horizontal lines to the right hand edge.
Secondly, if there are no samples at all for a field in the time range shown then it does not show the value from off the front of the chart. So for something like a setpoint that may not change for days the chart showing Today may not show the setpoint line at all.
I could not find a solution to those issues other than injecting repeat values into Influx occasionally.

@Colin @TotallyInformation thanks a lot for your feedback.

Like @Colin I have problems with filling the gaps in between two events / measurements.

An example of todays output of grafana + discrete plugin with values from influxdb:

@TotallyInformation it would be really nice whe you are back from your work travel if you could check and maybe share your config / query.

Regards Stefan

The trick is to use the "Mapping" tab to map null to a default value. Probably false in the example above. On other examples it might be 0 or Off or whatever.

Been a while since I'd done it so I'd forgotten how.

@TotallyInformation Thanks for your feedback. Did that already but the problem is that there is no value until 19:30 for the last measurement.

btw. my query looks like this:

OK, so you also need to change the query to something like this:

Note that I have a fill(null). I've also used mean() which is because I copied it from another display:

In fact though, I'd be better using count for this visualisation.

I must be going blind, I cannot find a mapping tab anywhere. I found references to it on the older interface (with the tabs along the top) but cannot find it in the newer i/f.
Though I don't understand how, when viewing a time range with no samples for a field, this will show the current actual value (ie the last value before the start of the time range), I would have expected it to show the default value. But since I can't find where to set it I can't try it. Can someone point me in the right direction?

You are indeed going blind :rofl:

I'm running Grafana v5.3.1 here. Just noticed that there is an update for Discrete though:

image

OK, now on v0.0.9 and mappings still there - phew!

Am I in completely the wrong place? Mine looks like this

Grafana 6.4.3

As mentioned, I'm on v5

Can I just check, if you zoom in on a time range where there are no samples in the database for a field what do you see for that field? Also if you select Today as the time range what do you see for the time after the current time?

Discrete is a plugin for Grafana that you have to install first.
sudo grafana-cli plugins install natel-discrete-panel

However with the panel the settings that Totallyinstall shows are available, but still no filed chart if you zoom in.
Any more hints..

On Grafana v6, mappings are here:

Here is what I see with the above settings when zoomed into the last 5 minutes

Note that the initial grey area is actually the value N/A because that's what this is set to, if you map null to 0 for example then you will get zero at the start of your display until a real value is written to the db.

1 Like

@TotallyInformation Thanks a lot for you screenshots and all the other infos.

Can you maybe also post the query for the last screenshot, I think that might help @Colin most :wink:

@Colin I'm using Grafana 6.5.2 and 0.0.9 of the discrete plugin.

For my version settings like in the screenshot of @TotallyInformation:

The mapping stuff you find when you click the second Icon Visualization

And then you've to scroll down on the page, and you'll find all the mappings stuff.

image

I am not using the Discrete plugin, so it is not surprising that I don't see it :slight_smile:
I am using the Graph panel. That explains some of our crossed wires.
Unfortunately showing zero or other default is not what I need. The use case is, for example, a room setpoint that only gets modified occasionally, maybe not for days, but I want to see it on the graph. I want it to show the actual value, not a default value so I can see how well the temperature control is working.

1 Like