Grafana - table with power counter reading for every 1st and 15th of month

Grafana
1

Dear community,

I already have my influxDB running and some Grafana energy meter dashboards but I struggle with a maybe simple idea.

For (external, analog) statistic reason I need the power counter reading for every 1st of the month and for every 15th.

This dashboard has the data “total_consumption” which is the couter meter.

grafana_power_metering3
grafana_power_metering31920×2212 159 KB

I think there should be an way to have a table showing the counter reading for every 1st and 15th of each month?!

Thanks for any help in advance.

Cheers
Eike

2

@E-J-D It looks like you’re using InfluxQL, which lacks to capability to run a query that returns data on specific days of the month. What version of InfluxDB are you using? If you’re using InfluxDB OSS 2.x or InfluxDB Cloud (TSM), Flux does support this type of query. If you’re using InfluxDB Cloud Serverless, you should be able to do this with SQL.

3

@scott Thanks for your answer. I am running InfluxDB OSS v2.7.3 locally in my homelab. Do you have any detailed information where to find examples or further details how to realize my idea?

4

@E-J-D You first need to create a new Flux-based InfluxDB data source in Grafana. Based on the screenshot of your InfluxQL query builder (Note: I don’t know the actual name of the bucket you’re querying), the Flux query would look something like this:

import "date"

addOrdinal = (n) => {
    _ordinal = if n == 1 then "st" else "th"
    
    return "${n}${_ordinal}"
}

from(bucket: "your-bucket")
    |> range(start: -6mo)
    |> filter(fn: (r) => r["_measurement"] == "total_consumption")
    |> filter(fn: (r) => r["site_name"] == "StromZ_04_SUM_DR18")
    |> aggregateWindow(every: 1d, fn: last)
    |> filter(fn: (r) => date.monthDay(t: r._time) == 1 or date.monthDay(t: r._time) == 15)
    |> map(fn: (r) => ({
        month:  "${date.month(t: r._time)}.${date.year(t: r._time)}",
        day: addOrdinal(n: date.monthDay(t: r._time)),
        _value: r._value
    }))
    |> pivot(rowKey: ["month"], columnKey: ["day"], valueColumn: "_value")

I did get a little fancy with this where it adds ordinals to the dates, but this query should return a table that looks something like this:

month 1st 15th
2.2024 123 456
3.2024 123 456
4.2024 123 456
5.2024 123 456
6.2024 123 456
7.2024 123 456

You can then configure the Grafana visualization to display the data how you want.

2 Likes
5

@scott thank you very much for your input. I will test this asap.

6

Hi @scott, thanks a lot for your help! I got this working with your template :partying_face:.

grafik
grafik906×106 4.96 KB

(There is no value for the 1st of July because the bucket I used doesn’t have that value)

Do you have any idea how to divide the value in the table with 1000? The meter tracks the values like this … [total_consumption: 23229908.4] but the real value is 23230.

7

@E-J-D You can do the divide-by-1000 math in a Grafana transformation or via a map() function in Flux. I prefer the latter approach.

1 Like
8

@E-J-D In raw Flux, there are two ways you can to this that both involve the map() function. You can perform the division in your existing map() call like this:

// ...
    |> map(fn: (r) => ({
        month:  "${date.month(t: r._time)}.${date.year(t: r._time)}",
        day: addOrdinal(n: date.monthDay(t: r._time)),
        _value: float(v: r._value) / 1000.0
    }))
// ...

Note: This examples casts the value to a float to preserve the fraction in the result. If left as an integer, the result truncates at the decimal point. If you don’t care about preserving the fraction, you can just do _value: r._value / 1000.

The other options is to perform the division at the end of your current query:

// ... the rest of your query
    |> map(fn: (r) => ({ r with
        1st:  float(v: r["1st"]) / 1000.0,
        15th: float(v: r["15th"]) / 1000.0,
    }))

Both should give you the same result. I’d generally lean towards the first approach to avoid multiple map() calls.

1 Like
9

@scott thanks again for your great help which answered my question completely!