How to filter noise from time series graph?

I’m relatively new to Grafana and still trying to learn the basics, I’ve done some basic SQL coding. I’m on a PC running version 7.1.1

I have a time series graph of snow depth data. Occasionally the sensor sends a bad reading causing a spike (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 getting plotted.

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

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

This hasn’t been working for me (no data displays at all). I imagine there’s some syntax I’m not understanding, MySQL within Grafana has been confusing to work with. Or perhaps I’m trying to filter in the wrong spot. I’ve poked around in the Tranformations tab next to Query and the binary operators are limited to arithmetic, and the thresholds/overrides in the time series options are all for visual aesthetics but don’t include any options that are relevant for what I need. I just don’t want anything above a certain value getting plotted, I don’t care if it’s a visual fix or if it gets filtered from the dataset.

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! Side note: I realize the version is very out of date, this configuration was set up by my team at work so I can’t update without permission.

What happens if you use Explore for your data source and just use the following query:

SELECT (154 - last(“value”)) AS val
FROM “tsdata”
WHERE (“var” = ‘1588’ ) AND val < 760

Unfortunately it doesn’t seem to work, the results disappear.

You want what values NOT to get plotted? 2 and 760?
Above 760, less than 760?

val not in (2,760)

By the way your question is better answered on a mysql forum though someone might answer it here

Sorry, I meant I want the values in range [0, 760] getting plotted, everything outside of that range I want ignored.

I can try posting to an SQL forum, I feel like the idea is correct though. There’s weird syntax that Grafana requires in its queries that make me think the way I’m writing this out is wrong by Grafana’s standards, but what do I know lol

between 0 and 760

Or

(val >= 0 and val <= 760)

Are you sure that “val” column used in WHERE condition exists? Because you used “value” column (not “val”) in SELECT.

1 Like

It looks like you are working with InfluxDB (not MySQL) as the underlying database for Grafana, given the use of last("value") and the fill(null) syntax. In InfluxDB, the language used is InfluxQL.

To filter out noisy data in Grafana, you can use a combination of InfluxQL functions and Grafana settings. Here’s how you might modify your query:

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

I didn’t realize I was working with InfluxQL, thanks for pointing that out. It might explain why my alias in the SELECT statement isn’t being recognized. Unfortunately, I’ve already tried writing out the modification you suggested (154 - last(“value”)) < 760 by appending it in the WHERE clause, and that still doesn’t work.

Update:

Here’s the solution that worked for me in case anyone else has the same issue:

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

Removing the call to last() from the WHERE clause was the missing key. The only caveat to this is it’ll choose the last value from the valid range of values; if null values are in the dataset, they will be ignored.

1 Like