Table showing last value with conditional

#1

I have an influxdb system and I’m trying to use a table panel to show the last value for devices with a value in a specific range. If i put the condition in the where clause, it limits the query and then returns the last value. Is there another way to limit the results either in another way in the query or after the results come back from the database?

As an example of the problem, here is a query that will not work properly:
SELECT last(“value”) FROM “battery” WHERE (“value” < 41) AND $timeFilter GROUP BY “deviceName”

When a battery is replaced, the value will be 100 but there are still values less than 41 so those will show up even though the last value is 100.

Thank you

#2

I have an influxdb system and I’m trying to use a table panel to show the
last value for devices with a value in a specific range.

So, you only want to knw about values in that range? ie: less than 41,
judging from the example you;ve given below?

If i put the condition in the where clause, it limits the query and then
returns the last value.

Yes, that sounds right.

Is there another way to limit the results either in another way in the query
or after the results come back from the database?

There are many other ways of limiting the results in the query, but I don’t
think we know what sort of limiting you want :slight_smile:

As an example of the problem, here is a query that will not work properly:
SELECT last(“value”) FROM “battery” WHERE (“value” < 41) AND $timeFilter
GROUP BY “deviceName”

When a battery is replaced, the value will be 100 but there are still
values less than 41 so those will show up even though the last value is
100.

So, what value do you want / expect to get out of a working query?

Suppose in some time interval the battery value goes below 41 (which I assume
means it’s going to need changing soon), gets changed, and then has values of
100 or the high 90s.

What result do you want to get from your query for this time interval?

Maybe another way of clarifying my puzzlement would be to tell us: why are you
selecting values below 41, if 100 means a new battery (which I would expect
you’d want to know about)?

Hopefully an explanation of what you want given an example of values during
the time interval will help someone assist with a query to do it for you.

Antony.

#3

Thanks for the response.

For my sample query I’ve ignored the time selection but you are correct that I would want the query to be over that time range. In this example, I’m trying to show a table that lists all devices with batteries at 40% or below (within the given time range although I expect right now to be the desired time range most of the time). I really only care about the last value for the battery in the time range - which I thought last would achieve (it sort of does). The issue is my where clause is filtering out everything I actually care about. So if within the time range the battery went from 25% to 100% (because it was replaced), I only care about the most recent value (which should be 100%) and for this table, it should be excluded from the result because I only care to show “low” batteries. But my query was only pulling the devices with batteries < 41 which this device had within the time range.

So is there a way to get the last value for a device in the time range if that last value is < 41

Thank you,
Julian

#4

I’m trying to show a table that lists all devices with batteries at 40% or
below (within the given time range although I expect right now to be the
desired time range most of the time).

So, you only want values 0 to 40…

I really only care about the last value for the battery in the time range -
which I thought last would achieve (it sort of does).

So, you only want the most recent value, not the lowest, understood.

So if within the time range the battery went from 25% to 100% (because it
was replaced), I only care about the most recent value (which should be
100%)

Er, that tells me that you are not only interested in values 0 to 40…

and for this table, it should be excluded from the result because I only
care to show “low” batteries.

Um, so you don’t want to know anything at all about a battery with a value
above 40?

But my query was only pulling the devices with batteries < 41 which this
device had within the time range.

Yes, that is what I would expect that query to do.

So is there a way to get the last value for a device in the time range if
that last value is < 41

Well, let me turn that around and ask “what result do you want to get if the
last value in the time range is >40?”

I mean, every query needs to return a result, so what result do you want in
that case?

Antony.

#5

If the value is > 40 then it shouldn’t be included in the table. I’m trying to show a table of low batteries so everything with a battery more than 40% would not need to be shown.

After playing with this in influx directly I’ve got a query that uses a subquery that appears to work. I dislike using the subquery and being forced to using the manual query editor but working is better than not working

SELECT * FROM (SELECT last(“value”) FROM “battery” WHERE $timeFilter GROUP BY “deviceName”) WHERE last < 41

So now my question is, is there a better way?

Thank you