Effort to update continuous queries to tasks for Influx 2+

[ThePnuts]

Starting a new thread to work on converting all of these continuous queries https://github.com/jasonacox/Powerwall-Dashboard/blob/main/influxdb/influxdb.sql into the Tasks equivalent for Influxdb 2. Apologies if this is not the best way to do this, new to github and this in general.

Mostly using this thread to track myself and progress as well as talk out loud if anyone wants to assist.

---

Edit:

As I learned that Influx 2+ changes how retention and databases are setup. As a result of this, I am working with the following differences from the way this currently works for Influx 1.8 which is used for this repository.

Weather data from weather411 is being logged to bucket: raw_weather
powerwall/solar data via Telegraf is being logged to bucket: raw_powerwall

As I am rewriting the continuous queries (influx 1.8) into tasks (influix2.0) for down sampling, these will go into the bucket: powerwall
This should maintain consistency with the majority of this repository.

[ThePnuts]

Starting with line 15

CREATE CONTINUOUS QUERY cq_autogen ON powerwall
  BEGIN
    SELECT
      mean(home) AS home,
      mean(solar) AS solar,
      mean(from_pw) AS from_pw,
      mean(to_pw) AS to_pw,
      mean(from_grid) AS from_grid,
      mean(to_grid) AS to_grid,
      last(percentage) AS percentage
    INTO powerwall.autogen.:MEASUREMENT
    FROM
      (
      SELECT
        load_instant_power AS home,
        solar_instant_power AS solar,
        abs((1+battery_instant_power/abs(battery_instant_power))*battery_instant_power/2) AS from_pw,
        abs((1-battery_instant_power/abs(battery_instant_power))*battery_instant_power/2) AS to_pw,
        abs((1+site_instant_power/abs(site_instant_power))*site_instant_power/2) AS from_grid,
        abs((1-site_instant_power/abs(site_instant_power))*site_instant_power/2) AS to_grid, percentage
      FROM raw.http
      )
    GROUP BY time(1m), month, year fill(linear)
END

Possible Flux Equivalent

option task = {
    name: "cq_autogen",
    every: 1m,
    offset: 0s,
}

from(bucket: "powerwall")
  |> range(start: -task.every)
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({
      r with
      from_pw: abs((1 + r["battery_instant_power"] / abs(r["battery_instant_power"])) * r["battery_instant_power"] / 2),
      to_pw: abs((1 - r["battery_instant_power"] / abs(r["battery_instant_power"])) * r["battery_instant_power"] / 2),
      from_grid: abs((1 + r["site_instant_power"] / abs(r["site_instant_power"])) * r["site_instant_power"] / 2),
      to_grid: abs((1 - r["site_instant_power"] / abs(r["site_instant_power"])) * r["site_instant_power"] / 2),
  }))
  |> group(columns: ["_time", "month", "year"])
  |> fill(column: "percentage", use: "last")
  |> to(bucket: "powerwall", org: "", fieldFn: (r) => ({
      home: r["home"],
      solar: r["solar"],
      from_pw: r["from_pw"],
      to_pw: r["to_pw"],
      from_grid: r["from_grid"],
      to_grid: r["to_grid"],
      percentage: r["percentage"],
  }))

[nberardi]

Depending on your goals. I have a working solution. nberardi@d0da1cc However if you are taking on the work due to the fun of solving a problem, I can completely understand. :)

[nberardi]

The 3 tasks I have posted in that commit, the telegraph config, and the Grafana dashboard are everything you need to get started.

[ThePnuts]

Oh nice @nberardi ! I really need to learn how github works, I didnt even know that was there. I might have to just put this in place to see the wonderful outcome.

I enjoy looking at your code, its helping me understand a different way to do something.

Looks like _instant_power your equivalent for the cq_autogen. I didn't realize that I could double up like you did with data, that makes it look a lot cleaner. It actually gives me a lot of other ideas... I think I originally was trying union, but had another error and did it the way I did once I had that error fixed and never went back. I think I should go back to using a union and then integrate how you did data to clean it up more. I can probably get rid of the yields as well doing this since I dont think they are needed for a task.

kwh aligns to _cq_kwh, ill probably give it ago myself and then compare

_plus looks like combining multiple other cq's together

---

Few other questions if you have time

How do you plan to address retention policies if at all? I was looking to mirror what is already in place which lead me to creating a separate bucket for raw data. I figured I could split it like this and just leave on forever retention, but if raw grew to much or became an issue, I could change it later.

In your Telegraf.conf

I had no idea you could do interval = "10m" on an input outside of global, thats awesome!

Is there a reason metric_batch_size = 360 instead of the default of 1000? I guess this is not specific to yours since its in the main branch as well.

is your inputs for openweathermap basically making weather411 obsolete? Seems to make it an unneeded middle man.

I was actually looking at their free pro tier which ups the API limit from about 1.38 a minute to 22.4 and debating if I would start collecting it like the powerwall data is collected and then down sampling it myself. Didnt seem straight forward on how to move to that free tier though so I put it on the back burner for now.

[ThePnuts]

Appreciate again sharing that with me @nberardi

I updated mine with what I learned from your code. I saw you also kept the original value for grid and did the same, as well as for powerwall. I also removed some of my shorthand like pw to powerwall, looks much better.

I have a couple of questions as well looking at it more to help me grasp it a little better.

at the end you have:

outputIndividual = union(tables: [home,solar,grid,battery,toGrid,fromGrid,toPowerwall,fromPowerwall,percentage, gridStatus])
    |> aggregateWindow(every: task.every, fn: mean, createEmpty: false)
    |> filter(fn: (r) => not math.isNaN(f: r._value))
    |> to(bucket: "powerwall")

outputAllExceptPercentage = outputIndividual
    |> filter(fn: (r) => r._field != "percentage")
    |> filter(fn: (r) => r._field != "grid_status")
    |> group(columns: ["_time", "_measurement", "_field"], mode:"by")
    |> sum()
    |> set(key: "host", value: "*")

outputAllPercentage = outputIndividual
    |> filter(fn: (r) => r._field == "percentage")
    |> group(columns: ["_time", "_measurement", "_field"], mode:"by")
    |> mean()
    |> set(key: "host", value: "*")

outputAllGridStatus = outputIndividual
    |> filter(fn: (r) => r._field == "grid_status")
    |> group(columns: ["_time", "_measurement", "_field"], mode:"by")
    |> min()
    |> set(key: "host", value: "*")

union(tables: [outputAllExceptPercentage, outputAllPercentage, outputAllGridStatus])
    |> to(bucket: "powerwall")

outputIndividual is doing the initial union, removing NaN and then writing to powerwall. Its doing this every minute for the previous hours worth of data, is there a reason to rewrite the entire previous hour instead of 5 min or less?

Next question, outputAllExceptPercentage, outputAllPercentage, and outputAllGridStatus are taking the output from outputIndividual (which was already written), grouping it so it essentially removed _start _stop and then writes it again. Because it is already aggregated by 1m, sum, mean, min are not actually doing anything right? Is this redundant or am I missing something? I did it a few times with different data sets and comparing the initial output against the final union is identical other then the removed start\stop columns and setting host to *.

---

Also from playing around with it, and is probably subjective, but I noticed a few things when doing queries on my end that I think are more efficient as well.

Before doing any of the writes:

1. Aggregating for each data set instead of after the outputIndividual union was consistently faster query times buy about .01s
2. Using the if else statement instead of the equation for math.abs(x: (1.0 + r._value / math.abs(x: r._value)) * r._value / 2.0) also seemed to be consistently .01s faster. This would also remove the need for the filter later for NaN
3. I did not see a difference on Map vs Set for adjusting the labelling, but I like how you did Set better

[ThePnuts]

Came back to put the final task I am using for this step

import "math"

option task = {name: "raw_tesla_energy => tesla_energy.instant_power", every: 1m, start: -3m}

data =
    from(bucket: "raw_tesla_energy")
        |> range(start: task.start)
        |> filter(fn: (r) => r["_measurement"] == "http")
        |> filter(
            fn: (r) =>
                r._field == "load_instant_power" or r._field == "solar_instant_power" or r._field
                    ==
                    "battery_instant_power" or r._field == "site_instant_power" or r._field
                    ==
                    "percentage" or r._field == "grid_status",
        )
        |> map(fn: (r) => ({r with _measurement: "instant_power"}))

home =
    data
        |> filter(fn: (r) => r["_field"] == "load_instant_power")
        |> map(fn: (r) => ({r with _field: "home"}))
        |> aggregateWindow(every: 1m, fn: mean)

solar =
    data
        |> filter(fn: (r) => r["_field"] == "solar_instant_power")
        |> map(fn: (r) => ({r with _field: "solar"}))
        |> aggregateWindow(every: 1m, fn: mean)

grid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _field: "grid"}))
        |> aggregateWindow(every: 1m, fn: mean)

fromgrid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value > 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "from_grid"}))
        |> aggregateWindow(every: 1m, fn: mean)

togrid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value < 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "to_grid"}))
        |> aggregateWindow(every: 1m, fn: mean)

powerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _field: "powerwall"}))
        |> aggregateWindow(every: 1m, fn: mean)

frompowerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value > 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "from_powerwall"}))
        |> aggregateWindow(every: 1m, fn: mean)

topowerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value < 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "to_powerwall"}))
        |> aggregateWindow(every: 1m, fn: mean)

percentage =
    data
        |> filter(fn: (r) => r["_field"] == "percentage")
        |> aggregateWindow(every: 1m, fn: mean)

gridstatus =
    data
        |> filter(fn: (r) => r["_field"] == "grid_status")
        |> aggregateWindow(every: 1m, fn: min)

union(
    tables: [
        home,
        solar,
        grid,
        togrid,
        fromgrid,
        powerwall,
        topowerwall,
        frompowerwall,
        percentage,
        gridstatus,
    ],
)
    |> to(bucket: "tesla_energy")

[ThePnuts]

Starting line 16

CREATE CONTINUOUS QUERY cq_kwh ON powerwall
  RESAMPLE EVERY 1m
  BEGIN
    SELECT
      integral(home)/1000/3600 AS home,
      integral(solar)/1000/3600 AS solar,
      integral(from_pw)/1000/3600 AS from_pw,
      integral(to_pw)/1000/3600 AS to_pw,
      integral(from_grid)/1000/3600 AS from_grid,
      integral(to_grid)/1000/3600 AS to_grid
  INTO powerwall.kwh.:MEASUREMENT
  FROM autogen.http
  GROUP BY time(1h), month, year tz('America/Los_Angeles')
END

Since this is pulling from autogen.http, I beleive I need to get line 15 functional before I can do this one. Thankfully, this one doesn't look like it will be to hard once line 15 is working.

[ThePnuts]

Ok, with @nberardi 's excellent work, I guess I don't technically need to do this (or anything anymore), but its fun learning. I also do need to do something so the data for this doesn't start "now" and I get my previous data done for this as well.

If I understand it correctly, this is basically calculating the total kWh produced this hour, updating the total each minute. Once the hour hits, its starts all over again for the new hour.
I'm curious why this is done on the already down sampled numbers instead of the raw data, but it probably accounts to a small rounding error if anything in the scheme of things. I'll probably do both just to see what the difference is for curiosity.

[jasonacox]

The reason for using autogen is to avoid doing all the math with the raw to determine to/from power flows, etc. I'm sure there is a better way (usually is) but the approach has been good enough to align to most other measures.

Keep experimenting and keep learning! And I suspect this discussion thread will be helpful and fun for others following along... including me. 😁

[ThePnuts]

So since, I didn't really have to solve this one, I focused on doing it in a way I could run it for older data to capture as well. This works by changing the -5h in line 4 to how ever you need to go back and it will do it for all but the current hour (which hopefully the task is doing)

import "date"

from(bucket: "tesla_energy")
  |> range(start: date.truncate(t: -5h, unit: 1h), stop: date.truncate(t: now(), unit: 1h))
  |> filter(fn: (r) => r["_measurement"] == "instant_power")
  |> aggregateWindow(every: 1h, fn: integral)
  |> map(fn: (r) => ({r with _time: date.sub(from: date.truncate(t: r._time, unit: 1h ),d: 1h)}))
  |> map(fn: (r) => ({r with _value: r._value / 1000.0 / 3600.0}))
  |> map(fn: (r) => ({r with _measurement: "kwh"}))
  |> drop(columns: ["_start", "_stop"])
// |> to(bucket: "tesla_energy")

and there here is the same iteration of it for the task to be run every minute

import "date"

start = date.truncate(t: now(), unit: 1h)

from(bucket: "tesla_energy")
  |> range(start: date.truncate(t: now(), unit: 1h))
  |> filter(fn: (r) => r["_measurement"] == "instant_power")
  |> aggregateWindow(every: 1h, fn: integral)
  |> map(fn: (r) => ({r with _time: date.truncate(t: r._start, unit: 1h )}))
  |> map(fn: (r) => ({r with _value: r._value / 1000.0 / 3600.0}))
  |> drop(columns: ["_start", "_stop"])
  |> map(fn: (r) => ({r with _measurement: "kwh"}))
//  |> to(bucket: "tesla_energy")

This is slightly slower then what @nberardi did\linked above, so I'm going to use his for efficiency for the task, but it works well enough above to do the older data as a 1 off.

For comparison of Raw data vs autogen

~40min into an hour
autogen - metric - raw (Difference)
1.9845902146464647 - Grid - 2.002752361111111 (0.018)
1.9834537089646465 - Home - 2.001681770833333 (0.014)

An hour a few hours ago when I had solar
autogen - metric - raw (Difference)
3.156721527777778 - ToGrid - 3.1753918055555554 (0.018)
0.9471744791666666 - Home - 0.9605353472222222 (0.013)
4.112048611111111 - Solar - 4.144070833333333 (.032)

So just say a difference of .02 kWh per hour for easy math
.48 per day
14.4 per month
172.8 per year

I dont have a functing dashboard yet (I couldnt get nberardi's json to work yet, lots of UID errors I need to google), but from the screenshots on the main git page, I do not see anything for metrics beyond a year and monthly stuff is by day it looks like, So this probably doesn't actually matter much in the scheme of things. For the sake of it though, I think I am going to aim for using the raw data instead of autogen since I'm in the middle of it all now anyways.

[ThePnuts]

Little more effort to get my old data in, but here is the code for the task. I essentially took the exact code I am using to create autogen, except removed my aggregates, so it contained all the data raw instead mean of each minute.

I also took a note from nberardi's code again and I cleaned up my naming for the database names and tables.

raw_tesla_energy = raw data from pypowerwall
tesla_energy = aggregate data
instant_power = what was autogen (stole this naming from nberardi as that table is basically x_instant_power. Made sense to me

import "math"
import "date"

option task = {name: "raw_tesla_energy => tesla_energy.kwh", every: 1m}

start = date.truncate(t: now(), unit: 1h)

data =
    from(bucket: "raw_tesla_energy")
        |> range(start: -2h)
        |> filter(fn: (r) => r["_measurement"] == "http")
        |> filter(
            fn: (r) =>
                r._field == "load_instant_power" or r._field == "solar_instant_power" or r._field
                    ==
                    "battery_instant_power" or r._field == "site_instant_power" or r._field
                    ==
                    "percentage" or r._field == "grid_status",
        )
        |> map(fn: (r) => ({r with _measurement: "instant_power"}))

home =
    data
        |> filter(fn: (r) => r["_field"] == "load_instant_power")
        |> map(fn: (r) => ({r with _field: "home"}))

solar =
    data
        |> filter(fn: (r) => r["_field"] == "solar_instant_power")
        |> map(fn: (r) => ({r with _field: "solar"}))

grid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _field: "grid"}))

fromgrid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value > 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "from_grid"}))

togrid =
    data
        |> filter(fn: (r) => r["_field"] == "site_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value < 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "to_grid"}))

powerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _field: "powerwall"}))

frompowerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value > 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "from_powerwall"}))

topowerwall =
    data
        |> filter(fn: (r) => r["_field"] == "battery_instant_power")
        |> map(fn: (r) => ({r with _value: if r._value < 0 then math.abs(x: r._value) else 0.0}))
        |> map(fn: (r) => ({r with _field: "to_powerwall"}))

percentage =
    data
        |> filter(fn: (r) => r["_field"] == "percentage")

gridstatus =
    data
        |> filter(fn: (r) => r["_field"] == "grid_status")

union(
    tables: [
        home,
        solar,
        grid,
        togrid,
        fromgrid,
        powerwall,
        topowerwall,
        frompowerwall,
        percentage,
        gridstatus,
    ],
)
    |> range(start: start)
    |> filter(fn: (r) => r["_measurement"] == "instant_power")
    |> integral()
    |> map(fn: (r) => ({r with _time: date.truncate(t: r._start, unit: 1h)}))
    |> map(fn: (r) => ({r with _value: r._value / 1000.0 / 3600.0}))
    |> drop(
        columns: [
            "_start",
            "_stop",
            "host",
            "month",
            "url",
        ],
    )
    |> set(key: "_measurement", value: "kwh")
    |> to(bucket: "tesla_energy")

Also just a side note: Since I only really started logging data from about a week ago, I am missing a ton of historic data from about May 2022. I found a way to pull some data from Tesla, but its limited to 5min aggregates and essentially these fields:
home, solar, powerwall, from_powerwall, to_powerwall. grid, from_grid, to_grid
I'll probably get this info put in with the rest at some point as well so I can compare, even if less accurate @5m vs 5s.

[ThePnuts]

Line 17

CREATE CONTINUOUS QUERY cq_daily ON powerwall
  RESAMPLE EVERY 1h
  BEGIN
    SELECT
      sum(home) AS home,
      sum(solar) AS solar,
      sum(from_pw) AS from_pw,
      sum(to_pw) AS to_pw,
      sum(from_grid) AS from_grid,
      sum(to_grid) AS to_grid
    INTO powerwall.daily.:MEASUREMENT
    FROM powerwall.kwh.http
    GROUP BY time(1d), month, year tz('America/Los_Angeles')
  END

[ThePnuts]

This seems super easy, but everything is logging in UTC, with me being in the Pacific timezone (-8) that makes 4pm my time, essentially midnight to start a new day UTC. Even when I am shifting my time, I cant seem to match my daily reported in the Tesla app, which makes me think I have something wrong somewhere...

[ThePnuts]

Just for reference...

Using this at the top of a flux query will cause any time related anything to take place in the time zone specified. However, the time column will still show in UTC.

import "timezone"

option location = timezone.location(name: "America/Los_Angeles")

So, you can then do this after your range line to visually adjust the _time column for you.

|> timeShift(duration: -8h)
just change -8h to whatever your time zone equivalent is.

Also not 100% if this offset remains on the time if you send the results somewhere, so once its how you like it, comment that line out to be safe.

[ThePnuts]

So I think I tracked this down to maybe something to do with how kwh is being calculated. I ended up creating a few temp dockers to spin up the dashboard as it exists here for influx 1.8.

Directly comparing today, my kwh seems to be slightly less then whats being calculated normally.
Example:
Metric - Tesla App - mine - 1.8 dashboard
Home - 17.1 - 15.9 - 16.5
Solar - 11.2 - 10.8 - 10.9
To+Fr grid - 5.9 - 5.2 - 5.6

So while the 1.8 dashboard doesnt exactly match Tesla's app, Its much closer. I would hope I would at least match the 1.8 dashboard, so something must be off. Unless I am just way overthinking this.

Comparing to the formula @nberardi created for his conversion, I am matching there at least.

I also started to recreate the dashboard. I was unable to import nberardi's, to many UID failures I couldnt figure out. I think because I am using multiple\different bucket setups?

How far I've gotten, I cant get the Tesla graphic to be transparent though like in the example screenshots:

[ThePnuts]

Well, I don't think its the data at kwh, since looking at each hour over a 24hr period, they all see, to be "close enough"
Variance of .01-.03 kWh, sometimes one, sometimes the other, also sometimes identical number.
Grafana makes it easier to see both to compare :)

So maybe the issue is coming down to the daily totals somehow?

[ThePnuts]

I forgot to come back and add this.

I had 2 issues above, first was figuring out the time zone thing for a daily aggregate. I posted the code above that resolved that, if I wanted to make a daily aggregate. In the end, I decided to not do this, which also lead to me not creating a monthly aggregate as well. If this changes, I have the data to generate it, but its not being used anywhere I can see or need, so I moved on.

The second issue was the data not matching the influxdb 1.8 I spun up to see the "normal" dashboard in action (also helped me make it in flux since I could see the queries this way). I learned that if I use the relative time option (overriding the global time dropdown), it also ignores the global time zone. So after manually setting the time zone for these I am matching once again.

From all of this, I also learned a lot about the date.truncate function and cleaned up a lot of my code so it always runs at the start of the desired interval instead of being based on the time it is run at. Like the previous minute will always start at 1:00:00 instead of at 1:02:45 or whatever fraction of a minute/second/millisecond you ran it at.

[ThePnuts]

Current status:
Been working on the dashboard, which also uses Flux.

I am trying to mirror the screen shots in the readme her: https://github.com/jasonacox/Powerwall-Dashboard/

Issues so far:

1. I can not figure out how to match the Tesla powerwall image on the top right. I cant get a transparent\black background and I can see from the updates, it now also lists firmware there and such.
2. I can't seem to find all of the plugins listed via the search in Grafana. I know one I need to install one manually, ill get to that, have a temp workaround in place now.
3. I have not yet done the power+ section of the DB, so I also am missing that on Grafana right now.
4. II still need to hardwire my PW...

Changes:

1. Weather, Rain seemed to be in mm instead of inches, simple conversion, but I feel like the data from OpwnWeatherMap isnt that accurate, but nothing I can do there.
2. I added Gusts line in similar fashion as Feels Like.
3. I added a variable on Grafana for kWh cost. I started to work on how to account for TOU cost charges at different times, about done with that and will have a 2nd variable added. For PGE, we also have different rates at different times of the year, so that will come next.

[jasonacox]

The background is set for pypowerwall via Environmental variable. However, the default should be clear.

PW_STYLE=clear

The only other thing it could be is that the local web files are missing. To verify, try to go to http://localhost:8675/example.html - it should load an example animation:

If it doesn't, that would indicate that you are missing the local files: https://github.com/jasonacox/pypowerwall/tree/main/proxy/web

[ThePnuts]

Oh interesting, it works correctly using the above link. This is definitely something to do with Grafana then. I had to edit the default config file from the official docker to even allow me to use the iFrame.

Maybe the answer: grafana/grafana#38165 (comment)

[jasonacox]

For the Grafana setup, were you able to set all of these environmentals?

GF_PATHS_PROVISIONING=/var/lib/grafana/provisions
GF_SERVER_HTTP_PORT=9000
GF_INSTALL_PLUGINS=grafana-piechart-panel, natel-plotly-panel, blackmirror1-singlestat-math-panel, btplc-trend-box-panel, marcuscalidus-svg-panel, michaeldmoore-multistat-panel, https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/releases/download/v1.5.0-alpha.3/yesoreyeram-boomtable-panel-1.5.0-alpha.3.zip;yesoreyeram-boomtable-panel, ryantxu-ajax-panel, grafana-influxdb-flux-datasource, fetzerch-sunandmoon-datasource, andig-darksky-datasource, simpod-json-datasource
GF_ANALYTICS_CHECK_FOR_UPDATES=false
GF_ANALYTICS_REPORTING_ENABLED=false
GF_PANELS_DISABLE_SANITIZE_HTML=true

The last one is used to enable iFrames.

[ThePnuts]

I do have the sanitize disabled (had to google that one to get as far as I did) and I have a different port set, ill need to check on these:

GF_PATHS_PROVISIONING=/var/lib/grafana/provisions
GF_INSTALL_PLUGINS=grafana-piechart-panel, natel-plotly-panel, blackmirror1-singlestat-math-panel, btplc-trend-box-panel, marcuscalidus-svg-panel, michaeldmoore-multistat-panel, https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/releases/download/v1.5.0-alpha.3/yesoreyeram-boomtable-panel-1.5.0-alpha.3.zip;yesoreyeram-boomtable-panel, ryantxu-ajax-panel, grafana-influxdb-flux-datasource, fetzerch-sunandmoon-datasource, andig-darksky-datasource, simpod-json-datasource
GF_ANALYTICS_CHECK_FOR_UPDATES=false
GF_ANALYTICS_REPORTING_ENABLED=false

I was installing the plugins I could find manually through the Grafana interface search. At some point I did break something though and lost everything in Grafana (oops...), so I started over fresh.

Currently the only plugin I have installed is Sun and Moon that isnt from grafana themselves. I know I cant replicate the one panel without yesoreyeram-boomtable-pane, but other then that I think I am matching it pretty good so far. This ones on my list after I finish the powerwall+ stuff in the dashboard.

I think darksky might be end of life in a few weeks too due to the apple changes. I didnt look to much into it: https://darksky.net/dev

[jasonacox]

Thanks @ThePnuts ! Good find on darksky. It may not be needed. I should go through and audit all of the plugins and see what we can remove. These tend to get added over time and may not apply to you since they are for the stock dashboard.json. The boomtable version is to fix a bug that was causing it to show "No data" using the stock plugin.

[ThePnuts]

As mentioned, skipping daily and monthly aggregates.
Line 19 - pw temps

I am debating taking a page from @nberardi and doing the rest all in a single task, but for now, still breaking out each one code wise.

This is the new way I am using the date.truncate. It also allows this same query to be run to populate older data by adjusting the start period from -1m to whatever while ensuring all timing is on the minute exactly.

Also fun new trick I learned. I'm not sure if Tesla supports up to 20 powerwalls, but the query does :)

from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> filter(fn: (r) => r["_field"] =~ /PW[0-20]_temp/)
  |> drop(columns: ["host", "month", "url", "year"])
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({r with _time: date.sub(d: 1m, from: r._time)}))
  |> drop(columns: ["_start", "_stop"])
  |> map(fn: (r) => ({r with _measurement: "pwtemps"}))
  |> to(bucket: "tesla_energy")

[ThePnuts]

Lines 20-23
Also technically supports additional string/inverter sets beyond 4 if you have some crazy big system

import "date"

from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> filter(fn: (r) => r["_field"] =~ /.*_Current/ or r["_field"] =~ /.*_Power/ or r["_field"] =~ /.*_Voltage/)
  |> drop(columns: ["host", "month", "url", "year"])
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({r with _time: date.sub(d: 1m, from: r._time)}))
  |> drop(columns: ["_start", "_stop"])
  |> map(fn: (r) => ({r with _measurement: "strings"}))
  |> to(bucket: "tesla_energy")

[ThePnuts]

Version 2 of this. it renames the numberless fields with a 0, not sure why tesla didnt do that?

Ex:
A_Current to A0_Current
This fits the name scheme on additional inverters after the first.

import "date"
import "regexp"

withoutnum = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D]_.*/)
  |> map(fn: (r) => ({ r with _field: regexp.replaceAllString(v: r._field, r: /_/, t: "0_") }))

withnum = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D][1-99]_.*/)

strings = union(tables: [withoutnum, withnum])
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> drop(columns: ["_start", "_stop"])
  |> map(fn: (r) => ({r with _measurement: "strings"}))
  |> to(bucket: "tesla_energy")

[ThePnuts]

ok, even better v3. Now adding in line 24 as well, still maintaining support for an unlimited number of inverters.

This one was a fun puzzle to allow for the unlimited potential of people with large systems. Inverter aggregates follow the same naming starting at 0. This is different then the continuous query as it starts inverter numbering at 1

import "date"
import "regexp"

data = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -2m for previous minute final total
  |> range(start: date.truncate(t: -3m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")

withoutnum = data
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D]_.*/)
  |> map(fn: (r) => ({ r with _field: regexp.replaceAllString(v: r._field, r: /_/, t: "0_") }))

withnum = data
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D][1-99]_.*/)

strings = union(tables: [withoutnum, withnum])
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "strings"}))
  
inverters = strings
  |> filter(fn: (r) => r["_field"] =~ /.*_Power/)
  |> map(fn: (r) => ({r with _field: regexp.replaceAllString(v: r._field, r: /[_Power]/, t: "")}))
  |> map(fn: (r) => ({r with _field: regexp.replaceAllString(v: r._field, r: /[A-D]/, t: "Inverter")}))
  |> aggregateWindow(every: 1m, fn: sum, createEmpty: false)  

union(tables: [strings, inverters])
  |> drop(columns: ["_start", "_stop"])
  |> to(bucket: "tesla_energy")

[ThePnuts]

v4 - cleaned up the last regex so its a single line instead of 2 and split into separate tasks

Strings

import "date"
import "regexp"

//option task = {name: "tesla_energy.strings", every: 1m}

data = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -2m for previous minute final total
  |> range(start: date.truncate(t: -2m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")

withoutnum = data
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D]_.*/)
  |> map(fn: (r) => ({ r with _field: regexp.replaceAllString(v: r._field, r: /_/, t: "0_") }))

withnum = data
  |> filter(fn: (r) => r["_field"] =~ /\A[A-D][1-99]_.*/)

union(tables: [withoutnum, withnum])
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "strings"}))
  |> drop(columns: ["_start", "_stop"])
//  |> to(bucket: "tesla_energy")

Inverters

import "date"
import "regexp"

//option task = {name: "tesla_energy.inverters", every: 1m, offset: 5s}

from(bucket: "tesla_energy")
//for historical generation change start here, otherwise use -2m for previous minute final total
  |> range(start: date.truncate(t: -2m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "strings")
  |> filter(fn: (r) => r["_field"] =~ /.*_Power/)
  |> map(fn: (r) => ({r with _field: regexp.replaceAllString(v: r._field, r: /[A-D]([0-99])_Power/, t: "Inverter$1")}))
  |> aggregateWindow(every: 1m, fn: sum, createEmpty: false)
//  |> to(bucket: "tesla_energy")

[ThePnuts]

Hey @jasonacox I am working on lines 25-31, which is basically vitals and I have a few questions whenever you get a minute.

I see several fields that are are currently not aggregating, so they are lost with the retention policy. I do not see them being reported on, but would there be any reason to capture these long term?

ISLAND_L1L2PhaseDelta
ISLAND_L1L3PhaseDelta
ISLAND_L2L3PhaseDelta
ISLAND_PhaseL1_Main_Load
ISLAND_PhaseL2_Main_Load
ISLAND_PhaseL3_Main_Load
METER_X_CTA_InstReactivePower
METER_X_CTA_InstRealPower
METER_X_LifetimeEnergyExport
METER_X_LifetimeEnergyImport

Second question, this might relate to more people then me. I see you are capturing a few fields on line 30 like METER_Z and similar. I do not have any METER_Z but I do have a METER_Y which you would not be capturing.
Additionally, my METER_Y has the same fields as METER_X with VL#N, nothing with VL#G

So now I am debating on trying to 1:1 move this to Flux or if I should keep with the variable method I am doing to account for these differences like Y vs Z and allowing a range. Also on why some fields are abandoned and not others, even though I do not see the ones that are captured represented anywhere on the dashboard, like the METER_Z stuff.

[ThePnuts]

Well, without knowing the above, here is the code to best match the current Continuous Queries in lines 25-31

This is capturing the same data with the following exceptions.

1. Allows for METER_? as current CQ has METER_Z and I have METER_Y, so now both are covered.
2. Grabbing all METER_?_CTB instead of just Z as I have both X and Y
3. Grabbing all METER_?_VL?? instead. I have VL?N, while current CQ also has VL?G which I do not
4. As mentioned above, these fields are not currently capture in current CQ or by me

ISLAND_L1L2PhaseDelta
ISLAND_L1L3PhaseDelta
ISLAND_L2L3PhaseDelta
ISLAND_PhaseL1_Main_Load
ISLAND_PhaseL2_Main_Load
ISLAND_PhaseL3_Main_Load
METER_?_CTA_InstReactivePower
METER_?_CTA_InstRealPower
METER_?_LifetimeEnergyExport
METER_X_LifetimeEnergyImport

Code for this:

import "date"
import "regexp"

data = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")

pinv = data
  |> filter(fn: (r) => r["_field"] =~ /PW[0-99]_PINV/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "vitals"}))
  
islandfreq = data
  |> filter(fn: (r) => r["_field"] =~ /ISLAND_FreqL[1-3]_Load/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "vitals"}))

islandvlXn = data
  |> filter(fn: (r) => r["_field"] =~ /ISLAND_VL[1-3]N.*/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "vitals"}))

meter = data
  |> filter(fn: (r) => r["_field"] =~ /METER_/)
  |> filter(fn: (r) => r["_field"] !~ /.*Inst/)
  |> filter(fn: (r) => r["_field"] !~ /.*Life/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> drop(columns: ["host", "month", "url", "year"])
  |> map(fn: (r) => ({r with _measurement: "vitals"}))

union(tables: [pinv, islandfreq, islandvlXn, meter])
  |> drop(columns: ["_start", "_stop"])
  |> to(bucket: "tesla_energy")

[jasonacox]

I see several fields that are are currently not aggregating

It would be good to capture these long term. Other than some additional space, there isn't any harm. Generally, we are only adding CQs when there is a need to display them so these were not in consideration.

METER_Z

This will be active in many 3-Phase systems which are more common outside the US. We have several the community who have this data in their system so it was added. I see that you got these already! Nice! 👍

[ThePnuts]

Awesome, ok then, in that case lets just grab it all then

import "date"
import "regexp"

data = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -2m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> drop(columns: ["host", "month", "url", "year"])

pinv = data
  |> filter(fn: (r) => r["_field"] =~ /PW[0-99]_PINV/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({r with _measurement: "vitals"}))

island = data
  |> filter(fn: (r) => r["_field"] =~ /ISLAND_/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({r with _measurement: "vitals"}))
  
meter = data
  |> filter(fn: (r) => r["_field"] =~ /METER_/)
  |> filter(fn: (r) => r["_field"] !~ /.*Life/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({r with _measurement: "vitals"}))

life = data
  |> filter(fn: (r) => r["_field"] =~ /_Life/)
  |> aggregateWindow(every: 1m, fn: last, createEmpty: false)
  |> map(fn: (r) => ({r with _measurement: "other"}))

union(tables: [pinv, island, meter, life])
  |> drop(columns: ["_start", "_stop"])
  |> to(bucket: "tesla_energy")

[ThePnuts]

Lines 33-35

I did not include backup_reserve_percent. I liked the idea of this being in the instant_power(autogen) area since it is used in that same are on the dashboard. I am going to amend it there.

Following the same thoughts, I am capturing all POD data here.

import "date"
import "regexp"

data = from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "http")
  |> drop(columns: ["host", "month", "url", "year"])

podnom = data
  |> filter(fn: (r) => r["_field"] =~ /PW[0-99]_POD_nom/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)

podavail = data
  |> filter(fn: (r) => r["_field"] =~ /PW[0-99]_POD_av/)
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)

podalert = data
  |> filter(fn: (r) => r["_field"] =~ /PW[0-99]_POD_/)
  |> filter(fn: (r) => r["_field"] !~ /PW[0-99]_POD_nom/)
  |> filter(fn: (r) => r["_field"] !~ /PW[0-99]_POD_av/)
  |> aggregateWindow(every: 1m, fn: max, createEmpty: false)

union(tables: [podnom, podavail, podalert])
  |> drop(columns: ["_start", "_stop"])
  |> map(fn: (r) => ({r with _measurement: "powerwall_pod"}))
  |> to(bucket: "tesla_energy")

[ThePnuts]

Line 36

import "date"

from(bucket: "raw_tesla_energy")
//for historical generation change start here, otherwise use -1m for previous minute final total
  |> range(start: date.truncate(t: -1m, unit: 1m), stop: date.truncate(t: now(), unit: 1m))
  |> filter(fn: (r) => r["_measurement"] == "alerts")
  |> drop(columns: ["host", "month", "url", "year"])
  |> aggregateWindow(every: 1m, fn: max, createEmpty: false)
  |> drop(columns: ["_start", "_stop"])
  |> to(bucket: "tesla_energy")

[ThePnuts]

Ok @jasonacox This is just about done Is there a better way for me to share all the queries and tasks then in this thread? Still extremely new to this and don't want to mess something up inadvertently.

Couple of notes on differences at this point in time.

• InfluxDB
  • Database
    • Changed database names to allow for retention policies if needed
      • Raw Tesla data goes to raw_tesla_energy. (only way for it to have its own retention)
      • tesla_energy for all aggregate data from http
  • Tables names
    • raw is simply http in the raw_tesla_energy database
    • autogen is now instant_power
  • Aggregation
    • I am aggregating all data from raw instead of just what is used in Grafana in case it is ever useful to be reported on
      • Not yet implemented for "instant_power" fields (battery, load, site, solar => _i_a_current, instant_average, etc.)
    • I am aggregating net for powerwall and grid (in addition to to_, from_)
    • Powerwall and inverter tasks are written to allow for up to 99 automatically
• Grafana
  • Plugins
    • I currently am stock Grafana with only the Sun and Moon plugin installed
    • Current State: uses yesoreyeram-boomtable-panel normally, I do not yet have this installed so this section looks different
  • Panel Issues
    • Tesla Power Flow: Do not yet have transparent background (This is a Grafana setting I have yet to change on default Grafana Docker)
    • Current State: Closest I can get with stock plugins(Grid negative makes it look silly imho), will adjust with correct plugin later
    • Voltages: I have not yet figured out how to allow for countries with 240v automatically. Its either as 120v or as 240v for PW loads. This is directly because I am coding to allow for up to 99 powerwalls automatically instead of a separate query for each potential powerwall
  • Version Differences
    • All queries are against tesla_energy. I saw some queries against raw data which would be missing data on time frames longer then retention policies if implemented on raw data
    • Donut Pie Chart circle is thin, I'm unable to find a way to fatten it up like the older version
    • Savings: Not sure how to add spacing buffer on each side of bar charts
    • Order of some legend information is in a different order
  • Differences from 2.8.3
    • Energy Use: I kept temps and clouds
    • Grid Usage: Added a Blue line for net usage
    • Solar Energy Year: I chose 1 year vs 12 months, same difference
    • Powerwall+ section I did a table for the legends on Frequency and Voltages like was done for Powerwall capacity.
    • Wind, Pressure and Precipitation: I added Wind Gusts

[jasonacox]

Thank you @ThePnuts ! This is beautiful! ❤️

I would like to capture all of this in the repository! My thought is that for now, we store this as an optional path (tool) in the https://github.com/jasonacox/Powerwall-Dashboard/tree/main/tools folder as InfluxDB 2.x Support. You can use Github to edit those pages and add files. It will generate a local branch for you to do so. When you are ready, generate a pull request for us to merge your changes.

If there is interest, I will look at making InfluxDB2 the default, but it would be more for the general population, "just run setup.sh or upgrade.sh" user base rather than a custom install like your Unraid version. Still, what you did for InfluxDB 2.x applies and would work for anyone willing to put in the extra effort to customize their installation. And it will be the foundation to build the InfluxDB 2.x version of "general population" version as well.

Thanks for contributing to the project and sharing your journey to your dashboard. 🙏

[ThePnuts]

Ok awesome. I think I got it. I guess next question is: Do I add the information as code like what I am doing in this thread or should I export my tasks and upload the files from that. The export is .json and can be imported easily into influxdb2. If I do the code route, someone would need to manually create the task in the web ui and copy paste.

The copy paste method would also error as they would need to remove the option line and enter that into the form.

[ThePnuts]

ok @jasonacox I think I did this correctly. I got everything added and provided some instructions which I hope are clear enough.

https://github.com/ThePnuts/Powerwall-Dashboard/tree/main/tools/influxdb2

[jasonacox]

Excellent!! Feel free to submit a PR!

[Maxcodesthings]

Super glad someone translated this to influxdbv2 so it is compatible with my running influx!

However it looks like the grafana dashboard is out of date. I am only getting power flow metrics and none of hte graphs are populating with data

When I look at the queries they look incomplete

Maybe its because im using a more up-to-date Grafana?

[jasonacox]

Thanks @Maxcodesthings !

[ThePnuts]

Sweet, glad it mostly worked for you. Some of the differences I think is related to influx dropping/moving away from Flux with version 3 and grafana adjusting how they do it. I never noticed since I'm just doing in-place upgrades now.

Need to actually get around to moving to influxdb 3 now, which I think will be pretty in line with the projects 1.8 usage query wise.

[Maxcodesthings]

Influxdb sounds like its becoming an increasingly frustrating product to use. Why are they completely removing query languages twice from their product??

[youzer-name]

I can't miss an opportunity to take a dig at Flux 😄

The answer to your question is (IMHO) because the first query language (InfluxQL) didn't allow for a lot of data manipulation, and the second query language is structured in a way completely different from the most common database syntax (SQL, MySQL, and related databases), requiring users to learn a completely new language and syntax to make even the simplest queries.

Here is a SQL query I just wrote off the top of my head. I'd bet that most people with any kind of technical background can read it and understand what it does. Most people could probably also figure out how to modify it to do other things:

select date(time), sum(value1) from powerwall.kwh where time >= '2024-01-01' and time < '2024-02-01' group by date(time)

I've spent a lot of time with SQL-like databases, so to me that almost reads as plain language - "select (this) from (here) where (conditions are true) group by (column)". It's like saying "get me the total weight of the pasta from the pantry where the box is at least half full, grouped by type of pasta".

I asked Google's AI to translate my query to Flux. I can't vouch for the accuracy, but it came up with this (and helpfully explained what a few of the lines do):

from(bucket: "powerwall")
|> range(start: '2024-01-01T00:00:00Z', stop: '2024-02-01T00:00:00Z')
|> filter(fn: (r) => r.measurement == "kwh")
|> group by(time: timeOfDay(time)) // Group by day using timeOfDay
|> aggregateWindow(fn: sum(value1), window: period(d: 1)) // Daily sum of value1

I once read a web page that tried to explain how to write Flux queries. This was how I felt after reading it: https://www.youtube.com/watch?v=yptXkLglKkA

[Maxcodesthings]

I also would prefer a more common knowledge syntax. Flux is incredible overcomplicated, but the complete removal of a language feels nonsensical to me. Just support both syntax?