Grafana Experts? Tile to show current power source

I’m wondering if there might be anyone here that is good in Grafana? Or if there is a better way to get the information from IoTaWatt

Here is my dashboard. You can see at the top there is a tile that says “100% Solar”. This tile should Go between “100% Solar”, “Grid + Solar”, “Grid Only”, “Portable Generator” and “Standby Generator”

I figured I could use Value Mappings to do it, but I’m not sure how to get it from multiple sources of information.

I have CT’s on my Solar Disconnect (Solar), The Main Lugs AFTER the Solar and BEFORE my panel (Main), On my Portable Generator Inlet (Portable), and on my Standby Generator (Generac). I have a calculation for Main - Solar, which gives me the Grid Import/Export

IF “Main” is a negative value, then display “100% Solar” and be Green in Color
IF “Main” is a Positive Value and “Solar” is over 50w, show “Grid + Solar” and be Yellow In Color
IF “Main” is a Positive Value and “Solar” is less than 50w, show “Grid Only” and be Red in Color
IF “Main” is under 50w and “Portable” is over 50w, show “Portable Generator” and be Purple In Color
IF “Main” is under 50w and “Generac” is over 50w, show “Standby Generator” and be Orange In Color

Does this sound possible?

Of course if I figure it out myself, I’ll post back here with the details

Can’t help on the question (still learning influx/grafana) but thank you for posting your dashboard. Much nicer than my very basic one - it’s given me some ideas.

It is a shame there isn’t a centre zero gauge in grafana (ideal for solar/grid) - have you got around it on your ‘current grid in/Out’ by using colours?

Thanks, if you would like the code for my dashboard or any specific information just let me know, happy to share

Yeah, I agree. I looked for a center zero and I couldn’t figure it out. I think there might be a way using thresholds, but I couldn’t get it to work right. Yeah, I have colors. Green is for exporting and red is for importing

Importing right now, here is what it looks like

I did get it to work between “100% Solar” and “Solar + Grid”, but still have not found a way to get “Grid Only” or any Generator ones

@BungledPossum are you using Influxdb v2 with the flux language? Here is my current dash with influxdb v2 through Grafana. I got some inspiration from the phisaver.com site (he has a live demo up). I’m new to the flux queries - and I’m stuck on some things. One is creating a daily (and monthly) bar graph with consumption, production, net - more or less like the one Home Assistant has in their energy panel.

1 Like

I’m not using Flux, but I have the IoTaWatt bucket added into Grafana as both Flux and influxql, so if I need to use Flux, I can.

I find influxql much more user friendly

here is a dash i made which uses the flux language.

I have separate quries for the 12x breakers i am monitoring which are graphed in the pit chart.

import "date"

month = date.truncate(t: now(), unit: 1mo)

from(bucket: "home_energy")
 |> range(start: month)
  |> filter(fn: (r) => r["_measurement"] == "house_power2")
  |> filter(fn: (r) => r["_field"] == "Breaker_2_W")
  |> filter(fn: (r) => r["tag1"] == "iotawatt.01")
|> aggregateWindow(
    every: 30d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Breaker 2 - Dinning Room Outlets, Front Porch, Upstairs LR outlets along stair case, Upstairs Bathroom Right GFCI", _time: "Date"})

per hour usage, this also needs a query per breaker

from(bucket: "home_energy")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "house_power2")
  |> filter(fn: (r) => r["_field"] == "Breaker_4_W")
  |> filter(fn: (r) => r["tag1"] == "iotawatt.01")
|> aggregateWindow(
    every: 1h,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Breaker 4 Total Energy", _time: "Date"})

usage per day for the month, no other queries needed, just this single one

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
  |> filter(fn: (r) => r["_measurement"] == "house_power2")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r.Mains1_W + r.Mains2_W }))
|> aggregateWindow(
    every: 1d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Total Energy Uage Per Day", _time: "Date"})

total kw hours used since the start of the month

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r.Mains1_W + r.Mains2_W }))
|> aggregateWindow(
    every: 30d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Total Energy Uage Per Day", _time: "Date"})

cost per month

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: (r.Mains1_W + r.Mains2_W) * 0.14552 }))
|> aggregateWindow(
    every: 30d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Total Energy Uage Per Day", _time: "Date"})

i am fairly used to the flux language, feel free to ask for assistance

2 Likes

Thanks for this I had a look at flux language and got a bit confused , I had tried using something like this for calculating the power costs for last 2mths
SELECT INTEGRAL(“value”,6s) *0.2932 / 1000000 FROM “total/watts” WHERE “value” >= 0 AND $timeFilter GROUP BY time(24h,-8h)

So will have a look at how you have done it and compare results

I’d really like to be able to create something to compare the current flat rate charging we are on to time of day charging to see if its worth the switch

Not sure I have things working configured correctly but also have been pre occupied on other stuff
Was doing things like
SELECT mean(“total”) * 29.21 /100 FROM “power_play”.“autogen”.“weekend_shoulder_kwh_daily” WHERE “ct”=‘1’ GROUP BY time(1d) FILL(0)

SELECT mean(“total”) * 15.3645 /100 FROM “power_play”.“autogen”.“everyday_off_peak_kwh_daily” WHERE “ct”=‘1’ GROUP BY time(1d) FILL(0)

SELECT mean(“total”) * 29.21 /100 FROM “power_play”.“autogen”.“weekday_shoulder_kwh_daily” WHERE “ct”=‘1’ GROUP BY time(1d) FILL(0)

SELECT mean(“total”) * 55.7734 /100 FROM “power_play”.“autogen”.“weekday_peak_kwh_daily” WHERE “ct”=‘1’ GROUP BY time(1d) FILL(0)

then using a total transform but not sure that is the best way, only a newbie at this stuff

here is a breakdown of my code

the first lines allow me to extract the current month. the command now() will return somethign formated as YYYY-MM-DDTHH:MM:SSZ
so i am using substring to extract the first 8 chars from this return which amount to YYYY-MM- which i then concatenate with 01T00:00:00Z so that i get the string of the current year/month starting at the first day of that month at midnight.

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

next i select which database i am using and filter it. in this case, i am pulling from my database home_energy using the month value i previously calculated before and the current date/time and the start and end. i am choosing the two values returned from the IoTaWatt Mains1_W and Mains2_W which are the watts variable for my two mains

from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")

the next lines are needed to perform the calculations to add the two separate values and to multiply them by the total cost i pay per KWh. the first row makes a pivot table to combine the two values in the table so they are marched by time and creates a intermediary variable “_value”

|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: (r.Mains1_W + r.Mains2_W) * 0.14552 }))

the next lines perform the windowing and the integral to get watt-hours from the watts the IoTaWatt returns. if you see the second line “every: 30d,” this is telling grafana to display 30 days worth of information. the lines “|> integral(unit: 1h) |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))” are used to calculate watt hours and then dividing by 1000 gives me kilo-watt hours

|> aggregateWindow(
    every: 30d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))

the last lines are “cleanup” lines where i am making things more presentable. right now if i was to look at all of the RAW data InfluxDB has sent, i would have the Mains1_W, mains2_W, the total watts i calculated, the integral, and many other columns of data which i no longer care about since i have used them to generate my final value. the “keep” command tells grafana which columns i want to keep, and so i drops all of the other data. the final one renames the column to what i want. in this case i rename it to describe the particular breakers are being monitored etc.

|> keep(columns: ["_value", “_time”])
|> rename(columns: {_value: “Total Energy Uage Per Day”, _time: “Date”})

here is additional information

i want to make a separate post to discuss how you can do what you want so much easier in flux

in this you can full on if statements.

so you can your first set of queries (all within the same grafana query)

for example, this would all be one query

variable1=from(bucket: “your_bucket”)
|> range(start: when_to_start, stop: now())
|> filter(fn: (r) => r._field == “item1” or r._field == “item2”)

variable2=from(bucket: “your_bucket”)
|> range(start: when_to_start, stop: now())
|> filter(fn: (r) => r._field == “item3” or r._field == “item4”)

variable3=from(bucket: “your_bucket”)
|> range(start: when_to_start, stop: now())
|> filter(fn: (r) => r._field == “item5” or r._field == “item6”)

then you can combine all of the different variables into one table for processing and organize then by time. you will want to use the “fill” commands to fill in any columns with a non-null value if the same columns are not in each of your different queries

overview = union(tables: [variable1, variable2, variable3])
|> group(columns: ["_time"], mode:“by”)
|> fill(column: “total_size”, value: 0.0)
|> fill(column: “vol_used”, value: 0.0)

now you can do IF statements with your data in the combined table named “overview” and if values are one way, do something and if they are a different value do something else

here is an example (not with if statements, but with multiple queries and combining them to perform a bunch of math

////////////////////////////////////////////////
//TOTAL AVAILABLE SPACE ON THE DIFFERENT VOLUMES
////////////////////////////////////////////////
server2_vol1_total=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_totalsize")
  |> filter(fn: (r) => r["nas_name"] == "Server2")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "total_size"})

server2_vol2_total=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_totalsize")
  |> filter(fn: (r) => r["nas_name"] == "Server2")
  |> filter(fn: (r) => r["volume"] == "/volume2")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "total_size"})

servernvr_vol1_total=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_totalsize")
  |> filter(fn: (r) => r["nas_name"] == "Server_NVR")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "total_size"})

servernvr_vol2_total=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_totalsize")
  |> filter(fn: (r) => r["nas_name"] == "Server_NVR")
  |> filter(fn: (r) => r["volume"] == "/volume2")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "total_size"})

serverplex_vol1_total=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_totalsize")
  |> filter(fn: (r) => r["nas_name"] == "Server-Plex")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "total_size"})

////////////////////////////////////////////////
//USED SPACE ON THE DIFFERENT VOLUMES
////////////////////////////////////////////////

server2_vol1_used=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_used")
  |> filter(fn: (r) => r["nas_name"] == "Server2")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "vol_used"})

server2_vol2_used=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_used")
  |> filter(fn: (r) => r["nas_name"] == "Server2")
  |> filter(fn: (r) => r["volume"] == "/volume2")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "vol_used"})

servernvr_vol1_used=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] =="vol_used")
  |> filter(fn: (r) => r["nas_name"] == "Server_NVR")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "vol_used"})

servernvr_vol2_used=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_used")
  |> filter(fn: (r) => r["nas_name"] == "Server_NVR")
  |> filter(fn: (r) => r["volume"] == "/volume2")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "vol_used"})

serverplex_vol1_used=from(bucket: "Test/autogen")
  |> range(start: -5m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "synology_volume")
  |> filter(fn: (r) => r["_field"] == "vol_used")
  |> filter(fn: (r) => r["nas_name"] == "Server-Plex")
  |> filter(fn: (r) => r["volume"] == "/volume1")
  |> aggregateWindow(every: 1h, fn: last)
  |> rename(columns: {_value: "vol_used"})

////////////////////////////////////////////////
//COMBINE ALL TABLES INTO ONE TABLE
////////////////////////////////////////////////

overview = union(tables: [server2_vol1_total, server2_vol2_total, servernvr_vol1_total, servernvr_vol2_total, serverplex_vol1_total,server2_vol1_used, server2_vol2_used, servernvr_vol1_used, servernvr_vol2_used, serverplex_vol1_used])
|> group(columns: ["_time"], mode:"by")
|> fill(column: "total_size", value: 0.0)
|> fill(column: "vol_used", value: 0.0)
|> reduce(
       fn: (r, accumulator) => ({
          total_size: r.total_size + accumulator.total_size,
          total_used: r.vol_used + accumulator.total_used
         }),
        identity: {total_size: 0.0, total_used: 0.0},
    )
|> map(fn: (r) => ({ r with _value: (r.total_size - r.total_used) }))
|> rename(columns: {_value: "Free"})
|> rename(columns: {total_size: "Total"})
|> rename(columns: {total_used: "Used"})
|> drop(columns: ["_time"])
|> yield()

Thanks for taking the time to post your comprehensive reply, and taking the time to break things out and explain the various sections, I just realised I forgot to mention that I was using NodeRed to do the time of day and day of week splits but I’d much rather just have it all in one place
I’ll have play as time permits

no problem, feel free to IM me or discuss over email if you desire.

one thing that helps is if you open the web-interface of influxDB and choose your data/plot it, in the lower right there is a “script editor” button. this will actually generate the Flux language code needed for the query you just made. this helped a lot in my initial understanding.

then i just googled a lot about how to do xyz or how to do abc and ready a lot of the documentation on influxdb’s site as they explain the new flux language is fairly good detail.

i just the played around figuring out how to do different things etc. once you understand which commands to use, it is easy to understand the code, and now that i have been using it, it is so much better to use as it is crazy powerful if you need it to be.

Thanks for posting this! I’ll need to read through these and get it going

We really appreciate the work

your welcome.
small comment to my previous posts, when you see “every: 30d” replace that with “every: 1mo”

Hi! I am starting with grafana and looking for information I saw your post. I am looking for something similar, but simplier, just know if it is 100% solar, combined or all from the line. I don’t know if you figured out how to do it, because the post didn’t continue, but i would like to know if you fixed it.

Also it would be really great if you could share your code, because it has almost the same structure i want to follow, but i am starting and have it as an example would be great.

Thank you in advance!

I will gladly help

Can you send me an screen shot or something of your data layout in Influx?

It should be fairly easy to do what you want.

We can make a single flux language query that pulls the data from your AC mains, and pulls data from your solar mains.

We then need to join the two different tables

After they are joined we can perform several IF statement where we determine the sources of power and have it report “all solar” / “All AC Mains” / combination

It would also then be easy to make a panel that shows what percentage is from mains and what is from solar when you are getting combined power.

Feel free to email me at wallacebrf@hotmail.com

1 Like