Smart Meter: Power consumption visualization with Shelly, InfluxDB, flux language, Grafana

Hi,

Goal

Starting point

  • Once configured, shelly devices do report their measured power consumption (Watt) every 5ish seconds via MQTT.
    For example:
00:00h - 10 Watt
00:05h - 60 Watt
00:10h -  5 Watt

Pre-requisites (won’t be described)

  • openHAB 3.x
  • Shelly device (e.g. Shelly3EM or Shelly1PM from Allterco Robotics)
  • openHAB Shelly Binding
  • MQTT broker
  • InfluxDB 2
  • Shelly and openHAB configured to use MQTT broker
  • openHAB persistence using InfluxDB 2
  • Grafana 9.x connected to InfluxDB 2

Setup

item

  • A NUMBER item will be used to save the values provided by Shelly (to the Influx DB via persistence)
Number:Power Shelly3EM_ALL_CURRENTWATTS "Power [%d %unit%]" { channel="shelly:shellyem3:c45bbe6a849a:device#accumulatedWatts" }

logging (optional)

  • As the openHAB event log will be flooded with value updates, I have disabled logging for all item names that contain the string CURRENTWATTS
  • I added the following line to my /var/lib/openhab/etc/log4j2.xml
<RegexFilter regex=".*(CURRENTWATTS).*" onMatch="DENY" onMismatch="NEUTRAL"/>
  • The whole important part looks like this:
<!-- Event log appender -->
<RollingRandomAccessFile fileName="${sys:openhab.logdir}/events.log" filePattern="${sys:openhab.logdir}/events.log.%i.gz" name="EVENT">
        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%-5.5p] [%-36.36c] - %m%n"/>
        <RegexFilter regex=".*(CURRENTWATTS).*" onMatch="DENY" onMismatch="NEUTRAL"/>
        <Policies>
                <OnStartupTriggeringPolicy/>
                <SizeBasedTriggeringPolicy size="16 MB"/>
        </Policies>
        <DefaultRolloverStrategy max="7"/>
</RollingRandomAccessFile>

Grafana / flux language

from(bucket: "openHAB")
	  |> range(start: -1y, stop: now())
	  |> filter(fn: (r) => r["_measurement"] == "Shelly3EM_ALL_CURRENTWATTS")
	  |> aggregateWindow(fn: mean, every: 1d)
	  |> map(fn: (r) => ({ r with _value: float(v: r._value) * 24.0 / 1000.0}))
	  |> aggregateWindow(fn: sum, every: 1mo)
		|> timeShift(duration:-1mo)

The code above belongs to a Grafana TimeSeries graph. I’ll try to explain each line separately.

  • from(bucket: "openHAB")
  • Grafana will read from Influx DB 2 bucket openHAB
  • |> range(start: -1y, stop: now())
  • The final graph will show values for each of the last 12 months (1 year in the past, until now).
  • |> filter(fn: (r) => r["_measurement"] == "Shelly3EM_ALL_CURRENTWATTS")
  • Values will be read from _measurement column for item Shelly3EM_ALL_CURRENTWATTS
  • |> aggregateWindow(fn: mean, every: 1d)
  • First calculation creates a mean value for each day.
    For example: If you constantly consumed 100 Watt for 12 hours and then you turn off the device (0 Watt measured) for the next 12 hours, the mean value will be 50 Watt for the whole day.
  • |> map(fn: (r) => ({ r with _value: float(v: r._value) * 24.0 / 1000.0}))
    Rewriting values or rather converting Watt to kWh.
    For example: 50 Watt consumption per day will be converted to 1.2kWh
    |> aggregateWindow(fn: sum, every: 1mo)
    As I would like to see the consumption per month instead of per day, let’s sum up the daily values per month. Btw: It’s great that Grafana provided this function as it would be a headache to manage all the different months with different amount of days.
    |> timeShift(duration:-1mo)
    Without this line the final Grafana chart would show your calculated consumption for January with a final timestamp as 1st of Feb! So we have to shift the time one month back.

6 Likes