Can I pull daily stats from API?

[dkerr64]

How can I pull daily stats from an API? I want to get current day home usage, solar generated, to Powerwall, from Powerwall, to grid and from grid kWh data. It is shown on the grafana dashboard but I cannot figure out if/how I can pull it from API.

Is it possible?

Thanks
David

[jasonacox]

Hi David, yes you can pull this data directly from InfluxDB. Here is a curl statement that pulls the last 7 days of HOME usage (change localhost to the host running the dashboard). The first uses the simple kwh data where the second computes the kwh data from raw (more current and is what the "meters" use on the Dashboard):

# Use the kWh series - this uses the CQ data so will lag a bit between CQ runs but will be fast
curl 'http://localhost:8086/query?pretty=true' \
    --data-urlencode "db=powerwall" \
    --data-urlencode "q=SELECT sum(\"home\") FROM \"kwh\".\"http\" WHERE (\"home\" > 0) AND time >= now()-7d GROUP BY time(1d) fill(0) tz('America/Los_Angeles')"

# Use the autogen series - this uses raw data so will be most current
curl 'http://localhost:8086/query?pretty=true' \
    --data-urlencode "db=powerwall" \
    --data-urlencode "q=SELECT integral(\"home\") / 1000 / 3600 FROM \"autogen\".\"http\" WHERE time >= now()-7d GROUP BY time(1d) fill(0) tz('America/Los_Angeles')"

Here is a simple script to pull all of the kwh meter data you are looking for:

#!/bin/bash

# Pull meter data from InfluxDB

function fetch {
    RESULT=""
    RESULT=`curl --silent 'http://localhost:8086/query?pretty=true' \
        --data-urlencode "db=powerwall" \
        --data-urlencode "q=SELECT integral(\"${1}\") / 1000 / 3600 FROM \"autogen\".\"http\" WHERE time >= now()-7d GROUP BY time(1d) fill(0) tz('America/Los_Angeles')" | \
        jq .results[].series[].values[7][1]`
}

for i in "home" "solar" "to_pw" "from_pw" "to_grid" "from_grid"
do
    fetch "$i"
    echo "$i: $RESULT"
done

Example run:

home: 19.943595978535356
solar: 39.01387626262627
to_pw: 15.200009722222225
from_pw: 9.927133585858586
to_grid: 14.08564575757576
from_grid: 0.2868274116161617

[dkerr64]

Excellent thank you. I'm really liking your powerwall-dashboard. I am integrating some data with a webpage I have that shows my security cameras... underneath the view of my front house I have some available space into which I already have managed to get the powerwall animated current flow graphic from pyPowerwall to display. Really cool.

btw... that graphic can take up to 10 seconds to load. I there a known bottleneck somewhere? I assume you have to get it realtime from the Powerwall.

[jasonacox]

Nice! Thanks for sharing the screenshot!

You are right about the animation taking a while to load. It pulls all the html, css, and js assets every time it is reloaded. We originally had pypowerwall cache those assets but when the firmware was upgrade, it would require either begin to flash annoyingly or error. To fix it, it required a refresh and restart of pypowerwall. We now have it no-cache all those files so that when firmware upgrades hit, no refresh or restart is needed. The downside is that it has a longer start-up time.

[dkerr64]

@jasonacox Is there a discussion/issue thread where the problem with the animation and firmware upgrade is discussed? I'd like to catch up on what is known about this before thinking too much about how I might improve things. Thanks.

[jasonacox]

First of all, we would not have this cool animation if it were not for the great work of @danisla and his PR to add this capability to pypowerwall here: jasonacox/pypowerwall#14

Key thing to remember is that pypowerwall proxy is just passing through (with some code injection) the Tesla Gateway html code (css, js included) that renders the animation. The calls for the data (e.g. kW values) are going to pypowerwall proxy APIs (cached). We have seen Tesla update the Animation code with every Firmware Upgrade. Hopefully they will keep it generally the same. We had to make some minor tweaks in the past due to upgrades, but nothing serious. Material changes will likely require us to make changes on the proxy or dashboard.

During the upgrade, we were originally caching even the html animation assets. That caused and issue during firmware upgrades that resulted in non-stop flashing loop in the browser (unable to render). Here is a bit of history on that:

• Introduction of Animation support: Pass through Powerwall Web interface and customize for iframe displays pypowerwall#14
• Intro Discussion on this: New Dashboard with Animation #26
• Flashing Animation during Firmware Upgrade: Powerwall Firmware 22.18.6 Upgrade - Flashing Powerflow Animation pypowerwall#23 and 22.18.6 Flashing Powerflow Animation #88
• Compliance Label During Firmware Upgrade - Fixed Powerwall Firmware Upgrade 22.26.1-foxtrot 4d562eaf - Bugs in Proxy pypowerwall#24 and Powerwall Firmware Upgrade 22.26.1-foxtrot 4d562eaf - Animation Issues #100

One final note, as of the last 2 firmware upgrade, the reports are that there were no issue and you didn't even need to refresh your browser to "fix" the animation. It just worked and the new firmware version showed up on the panel. Discussed here: #109 (reply in thread)

[dkerr64]

That feels like a bit of a sledge hammer approach to solving the problem (using no-cache). I can understand not caching inside pyPowerwall proxy, but I really don't think this should be enforced on browsers. It is pretty bad...

That is almost 3 seconds just for the basic CSS and JavaScript. And this is on a local 2.5Gbps ethernet (ok, so I confess, the Powerwalls are connected by 1Gbps ethernet, but at least not WiFi).

There are much more refined controls that can be placed on browser cache like use of max-age and must-revalidate which I think would be far more appropriate. At a minimum, forcing no-cache should be user configurable.

And if there is a problem with a cached resource being out-of-date, the user can easily refresh the browser... either clear the entire browser cache, or just for the current page by holding down the shift key when loading / clicking to reload a page. For a problem that occurs only on firmware updates, which are likely every few months, a solution that impacts every page load is pretty brutal.

Is there an easy way I can locally build pyPowerwall and test my theory? I assume I start here.

Thanks

[jasonacox]

Thanks @dkerr64 ! We would absolutely welcome the help. I'm sure it can be improved.

I know what you are saying about the load time, but except for the first load of the Dashboard, I haven't noticed any slowness for the animation once it is running. One thing we do need to consider is that many in the community have permanent mounted displays for the dashboard which is why it is so nice not to need user intervention to refresh. If we can get a solution in place that makes it faster but doesn't require the user to restart pypowerwall or refresh their browser when firmware updates happen, that would be ideal. Of course, we could also create an optional environmental parameter to select the type of caching for other types of displays (like yours?) that may be automatically refreshing (which would incur the terrible load time every time).

You can pull the source from https://github.com/jasonacox/pypowerwall and find the server.py in the /proxy folder.

You can build your own container or just run it from the command line. Run it from the proxy folder since it uses transform.py and the web folder for the injected JS to reformat the animation:

#!/bin/bash
export PW_PASSWORD="xxxxxxx"
export PW_EMAIL="xxxxxxx"
export PW_HOST="x.x.x.x"
export PW_TIMEZONE="America/LosAngeles"
export PW_DEBUG="yes"

echo "Starting proxy at http://localhost:8675..."
python3 server.py

[dkerr64]

Lets move this over to here... jasonacox/pypowerwall#31