Grafana - Display Gen Set Runtime

Haider · February 10, 2020, 12:19pm

Hi Everyone -

Not sure how to create this query in Influxdb so hopefully someone can help me out:

I have attached 3 CTs to our gen set at the office and I would like to build a new Panel which simply displays the total time the generator has run. I would assume the best way is to create a query which adds the time for any value of “total_gen_power” which is > 0 and adds that time to says “total_gen_run_time”.

Any help on this would be highly appreciated. Thank you everyone!

Regards,

Haider

overeasy · February 10, 2020, 1:02pm

Taking a quick look at the influx functions documentation, you may want to try using the count() function to count the number of non-zero power values in each group, multiplying that total by the measurement interval in seconds should produce the total seconds runtime for each group. That can be converted to minutes, hours etc using the obvious math.

Haider · February 10, 2020, 1:04pm

Thank you so much, let me try this out. Apparently its an interesting issue among other IoT users:

github.com/influxdata/influxdb

querying a discrete state time series for intervals with the 'active state'

opened 01:11PM - 04 Nov 16 UTC

jack-kerouac

area/functions kind/feature-request area/influxql flux/triaged 1.x

# Feature request: 'active state query' ## Use case Besides time-series, whi…ch represent measurements of continuously changing values (e.g. CPU load, free disk space, measured temperature/humidity), we would also like to store time-series **representing a value out of a discrete set of values**. Consider the state of a light switch: 'on', 'off'. Or the enumeration value of the current weather condition (sunny, cloudy, rainy, snowing, ...). A common query we have for these types of time series is to **list which state was 'active' for a given query time window**. The **response should contain a set of intervals with the active state within this interval**. The intervals should cover ('partition') the whole query interval. ## Proposal I do not want to propose a concrete syntax for this type of query, nor for the response, but the following example covers the cases for the query. Assume the following weather condition changes: | time | weather | |-------|---------| | 6:00 | snowy | | 8:00 | rainy | | 12:00 | sunny | When querying for the current weather condition **between 7:00 and 13:00**, I expect the following response: | interval | weather | |---------------| --------| | 7:00 - 8:00 | snowy | | 8:00 - 12:00 | rainy | | 12:00 - 13:00 | sunny | When querying for the current weather condition **between 9:00 and 11:00**, I expect the following response (single value active): | interval | weather | |---------------| --------| | 9:00 - 11:00 | rainy | **Though the weather state change at 6:00 is not within the query interval, the result should assume that this weather condition is still present at the beginning of the query interval (7:00) and thus determine the value of the first interval.** This behavior would require InfluxDB to *'look back in time' until the last value before the interval is found*. The time window which is searched for the last value before the query start time ('retrospection window') could potentially be restricted in the query. If no value can be found (within the retrospection window) the interval would be filled with `null`. When querying for the current weather condition **between 4:00 and 7:00**, I thus expect the following response (single value active): | interval | weather | |---------------| --------| | 4:00 - 6:00 | `null` | | 6:00 - 7:00 | snowy | ## Current behavior This query can be 'simulated' by the following: 1. query all points within the desired interval `... WHERE time >= start AND time < end`. 2. query for `LAST() ... WHERE time < start` for the value of the first interval. Unfortunately, the `LAST()` query turns out to be very slow for our usage. With the described type of query in mind, maybe a different index structure can better support the lookup of values before the query interval to fill the first response interval. This issue supersedes #7029 (which was opened by @beckettsean for us). I wanted to clearly describe the use case again.

frogmore · January 13, 2021, 8:09pm

I was interested in something similar, so I looked at the links and didn’t see a real solution there. There were some really good hints, which were helpful. I now have something that appears to give decent results. Here is what I did:

I use Tasmota on many of my devices (based on the esp8266). I set them to use 0 and 1 for POWER state.
I have a flow/function node in Node-RED that listens to MQTT and creates arrays in InfluxDB format and then forwards to InfluxDB
The data looks like this:

> precision rfc3339
> select * from PowerControl where time > now() - 10m and "NodeName" = 'BasementFan'
name: PowerControl
time                     Name NodeName    POWER Sensor
----                     ---- --------    ----- ------
2021-01-13T19:26:36.233Z      BasementFan 0     POWER
2021-01-13T19:26:46.731Z      BasementFan 1     POWER
2021-01-13T19:26:57.136Z      BasementFan 1     POWER
2021-01-13T19:27:07.557Z      BasementFan 1     POWER
2021-01-13T19:27:18.092Z      BasementFan 0     POWER
2021-01-13T19:27:28.558Z      BasementFan 0     POWER

I then use Grafana to create an influx query:

SELECT integral("POWER") FROM "PowerControl" WHERE ("NodeName" = 'BasementFan') AND $timeFilter GROUP BY time($__interval) fill(null)

I use the new-ish Stat visualization (it replaces the old SingleStat)
There are two ways to reduce the data. The very new Transform and the older Display --> Show --> Calculate --> Total.
The old way preserves the “shape” of the data, so Stat can also do the sparkle line, which is cool
The last bit is to use the Units. I find using the Field tab makes it easy to set the units to seconds. There is a search box that makes it easier to find the units. I find the categorization they use not memorable, but search works well. Grafana does the math and switches the units from seconds to min or hour (or bigger as needed)

here is an example panel of a fan I have in the basement that is on 30s every 5min to move the air around

The numbers don’t quite add up, because there was a failure to send data this morning (the old “server” was updating).

Anyway, this meets my needs.