How to filter noise from a time-series graph?

InfluxDB 2 Dashboards
, , ,
1

I’m using Grafana to create a time-series graph of snow depth data stored in an InfluxDB database. I’m on a PC running Grafana version 7.1.1 (this configuration was set up by my team at work so I can’t update without permission).

Occasionally, the snow depth sensor sends a bad reading causing a spike in the data (e.g. goes from a consistent reading every minute of 2cm to 760cm for a minute, then back down to a steady 2cm). I don’t want these sorts of spikes getting plotted. More specifically, I want all values in range [0, 760] getting plotted, everything else I want excluded.

What I’ve been trying to do is write the query to include such a filter in the WHERE clause, something like:

SELECT (154 - last(“value”))
FROM “tsdata”
WHERE (“var” = ‘1588’ ) AND $timeFilter AND (154 - last(“value”)) <= 760 AND (154 - last(“value”)) >= 0
GROUP BY time($__interval) fill(null)

When I add the bolded portions to my WHERE clause, no data displays at all. I posted about this to the Grafana forum and the consensus seemed to be that this is the way to establish the filter I’m looking for. I am truly stumped as to why this isn’t working for me.

Is there a way to filter out noisy data above/below a certain value in a time series graph? Any advice would be much appreciated!

2

@rzieber I think if you remove the last calls from the WHERE clause, it will work. The only caveat here is that it will return the last value within each interval that falls within the defined value range rather than a null value if the last value in an interval doesn’t fall within the defined range. I’m not sure what your desired behavior is.

SELECT (154 - last("value"))
FROM "tsdata"
WHERE ("var" = '1588' ) AND $timeFilter AND (154 - "value") <= 760 AND (154 - "value")) >= 0
GROUP BY time($__interval) fill(null)
3

That worked! Thank you so much for your help, and for the explanation as to what the last() call does. For my purposes, the exclusion of last shouldn’t be an issue as I don’t want null values getting plotted either.