Displaying time duration where a certain value is greater than / equal too

Grafana Configuration
1

I have sensor data in my InfluxDB. The sensor posts a 1 or 0, depending on the measured status. Now I want to display in my Grafana (8.2.3) the duration the sensor is 1. So, if the sensor is 6 minutes =1 during one hour, I want to see 10%.

I was googling around, but it seems I miss a certain keyword to have success. Even reading and searching the forum for some time did not enlighten me.

Can someone provide me with they right keyword or link or short explanation what too look for, how to achieve that goal?

Thanks a lot.
Peter

2

Hi Peter, and welcome to the Grafana forum.

What you describe is certainly possible. Can you first clarify if you are using InfluxQL or Flux language to write your queries?

If InfluxQL, then one solution would be something like this:

Query A…SELECT statement that contains a COUNT of ALL values over the given time window
Query B…SELECT statement that contains a COUNT of ONLY VALUES = 1 over the given time window
QUERY C…divides B by A

Make query A and B not visible on the chart, but make C visible as a % value in a Stat panel

1 Like
3

Thanks a lot.

Seems I am using InfluxQL.

I am a beginner in Grafana, but SQL (mssql, postgresql, …) is my bread & butter during the dayjob. I get the overall picture of your idea. However, is COUNT the right function for this? I do not know about InfluxQL very much, I admit.

My timeseries will look something like

2021-12-20 12:34:00, 0
2021-12-20 12:34:55, 1
2021-12-20 12:35:05, 0
2021-12-20 12:35:55, 1
2021-12-20 12:36:00, 0

if I COUNT, I will get a 2 and a 5. This means 40% with the Math given. But I would need something like

Select duration no matter which status
Select duration where status equals 1

In the example I need to know

timespan covered by data: 120 seconds
timespan with status=1: 10 seconds

I am wrong with my assumption of count being the wrong function?

Best Regards,
Peter

4

Hi Peter. OK, thanks for explaining further. So as you correctly pointed out, you need the total duration (in seconds) of the timespan and also the duration (in seconds) in the same timespan where status = 1. I think this can definitely be done in InfluxQL.

Here is a thread from the InfluxDB forum that poses / solves a similar question. It basically uses an Integral function like this:

SELECT integral(*) from your_data where time >= '2021-12-20T12:34:00Z' and time <= '2021-12-20T12:36:00Z'

Here is another that is not as elegant, but still might work.

1 Like
5

Thanks for the links, Grant. Seems, this needs some more fiddling on my side :slight_smile:

Wish you great holidays.