Current State panel reports incorrect data when selecting different time range and "No data" randomly

[mcbirse]

Firstly @jasonacox great project! I have been running the Powerwall-Dashboard for a month now and it's fantastic.

I have also been using a shell script I had written for about a year to monitor my Powerwall and send e-mail alerts based on various events (e.g. grid outage, firmware updated, etc.). I had been thinking about modifying it to log additional data until I found this - thanks! I may change to Grafana e-mail alerts since I notice the grafana.env has been externalized now which is nice... however not sure about alerts on firmware updates though, so for the moment my shell script is still handy too... 😊

Anyway, back on topic. I have noticed what appears to be an issue with the Current State panel in the dashboards?

If you change the time range selection in Grafana to, for instance "Yesterday", then the Current State panel will show data based on the last value of that time range, and not the "Current State".

I assume the intention with the Current State panel would be to always show what is happening right now, regardless of the time range selected?

If so, I think the select query needs to be modified so it does not end with the standard WHERE $timeFilter

I have corrected this in my dashboard by manually editing the query in text edit mode, and changing it to WHERE time >= now() - 30s like below:

SELECT last("load_instant_power")  / 1000 AS "Home", last("solar_instant_power")  / 1000 AS "Solar", last("battery_instant_power")  / 1000 AS "Battery", last("site_instant_power")  / 1000 AS "Grid" FROM "raw"."http" WHERE time >= now() - 30s

So the query would only ever return the last 30 seconds of data, regardless of the time range selected.

CAUTION: I noticed a bug(?) in Grafana - if you click the pencil icon again to exit text edit mode, the where clause will get replaced by WHERE $timeFilter again, so it should be left in text edit mode to save correctly.

I also noticed sometimes my Current State data for Battery or Grid was sometimes showing "No data" when I was not using any battery or grid power.

This is displayed when null data is returned, however when checking the values returned they appeared to be "0" - not null? I wasn't sure how to fix this and ended up changing the "Null Handling" to simply show the "Idle" text and colour format instead - is there a better way to fix this perhaps?

[jasonacox]

Hi @mcbirse - thanks for opening this issue and for the encouraging feedback!

Great catch on the "current state" graph! You are right, this is not the behavior we want. The $timeFilter should be removed. On the "No data" fix, your idea is valid, but I also wonder if some of the issue is related to the sample size not having enough data points to flip it out of Null.

We could try a 5m set:

SELECT last("load_instant_power")  / 1000 AS "Home", last("solar_instant_power")  / 1000 AS "Solar", last("battery_instant_power")  / 1000 AS "Battery", last("site_instant_power")  / 1000 AS "Grid" FROM "raw"."http" WHERE time >= now() - 5m

I'm going to try that for a while to see if that addresses the Null case.

Are you interested in submitting the code change to the dashboard*.json files as a Pull request so you get credit? I welcome the help. If not, I'll still reference this issue thread when I update the dashboard*.json files.

One bit of feedback request, do you use the dashboard.json or the dashboard-animation.json ? The animation is the latest one and I'm considering making it the primary default. What do you think?

[mcbirse]

Hi @jasonacox - thanks for confirming the $timeFilter should be removed. Sure, I would be happy to submit a Pull request for this. I'm new to GitHub but willing to learn, so will try to give it a go this weekend!

The 5m set is a good idea. I will test this as well and see if that helps with the Null data issue.

I did test at some point a 1m set I think. I also tried changing each last() to mean() in the query, but was still seeing "No data" occasionally. I also tried to add a fill(0) or fill(previous) to the GROUP BY clause, to replace Nulls with 0 or a previous non-null value, without success. I may have been doing something wrong, but will try to experiment a bit more.

I'm using the dashboard-animation.json as my primary default now. I agree it makes sense to change it to the default, as it is really nice to see the Power Flow animation and Grid Status in my opinion.

[youzer-name]

I just took a look at how mine is set up as I have been customizing the dashboards for a while and am not working with the default environment. I don't have any 'WHERE' component in my query (nor did I have the $timeFilter). Using 'SELECT LAST' should always get the most recent value. Adding the 'WHERE time >= now() - 5m' should only result in sometimes NOT getting data. It should never include more data than if there had been no WHERE clause at all.

I am using the 'null handling' section as noted above, so if the data is null for any reason it will display 'Idle'. I don't remember if I added that because I was sometimes seeing the 'no data' message before.

One possible advantage to including a "where" statement is if you want to show an error if the data is stale. So using a combination of the "where time >= now() - 5m" and the null handling, you can display a message if there is no data for the past 5 minutes.

If my latest data is more than 1 minute old, my phone starts blowing up with notifications*, so I would know that the 'current state' was stale without setting the dashboard up that way. I had set up notifications when initially using WiFi to the Powerwall, since it kept dropping the connection. Now that I'm using ethernet the connection is stable and the alerts only fire during Powerwall firmware updates.

I also display the "Last Update" using a singlestat math panel where I can see the date/time of the data that is being displayed. I use SELECT last(battery_instant_power) FROM "raw"."http" as the query. In the panel options, I display the time of the last point with the unit set to "datetime local (no date if today)".

* I'm using Node-Red to send the notifications. I have a flow that runs every minute and queries the Influx DB for "select mean(PW1_temp) from raw.http where time > now() - 1m" and if the mean value = 0, the notification is triggered. I also had alerts via email set up in Grafana, but node-red lets me send the alerts via my self-hosted notification platform and Grafana wasn't able to do that.

[jasonacox]

Great analysis and points @youzer-name ! Are you using the dashboard-animation.json dashboard? Would you mind posting the panel json for those? I'm trying to figure out the best way to allow the community to share panel or full dashboards with each other. Would it make sense to create a dashboards directory or discussion board?

@mcbirse thanks for being willing to try the PR this weekend! Let me know if you have any questions.

[mcbirse]

Thanks @youzer-name for your feedback.

Regarding the fix to ensure the Current State panel is always showing current data regardless of the time range selected, I am thinking a good compromise might be "where time >= now() - 1m" and will submit the PR with that instead of 30s. This should allow for the combination of current data (in the last minute), or no data if there had been a timeout for more than a minute. Without a WHERE clause at all and simply selecting the last value could mean returning very old data(?), if for instance communication to the Powerwall had stopped for a while.

With the "No data" issue, after further testing I've worked out what is causing this - it's a bug with the Boom Table visualization plugin!

I remembered thinking I was seeing "0" values being treated as null. So I checked this again to confirm.

Below is showing "No data" for when Battery = 0

Just to make sure a 0 in the data table was not a null, I confirmed actual true null data seems to be shown as dashes I believe?

So it definitely appeared to be that the Boom Table plugin null handling wasn't working properly, and was treating 0's as nulls.

Then I found this bug had been reported before - and might be fixed in the latest version of the plugin? https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/issues/152

So I downloaded and installed the 1.5.0-alpha.3 version of the plugin to test this.

I had some issues getting this installed though. Extracting the plugin to the grafana/plugins folder of the Powerwall-Dashboard kept resulting in the plugin being overwritten again by version 1.4.1 when grafana was restarted.

Not sure how to fix that. However I ended up installing the 1.5 version in a separate folder, and changing the plugin name/id so it didn't conflict, which resulted in both versions being installed.

This was handy actually, as I could easily switch to the newer plugin version by editing the panel JSON and changing the type.

Anyway, now confirmed it's the Boom Table plugin causing this issue!

Below was with Grid value = 0 and Boom Table plugin v1.4.1

Then I tested again with Grid value = 0 and Boom Table plugin v1.5

@jasonacox - is it possible to have the Boom Table plugin updated to the latest version in Powerwall Dashboard? My method of install was a bit of a hack just to get it tested.

[jasonacox]

Brilliant detective work, @mcbirse !

The plugins for Grafana are defined via environment variables (was in powerwall.yml now grafana.env with latest Powerwall-Dashboard). In there you will see this:

GF_INSTALL_PLUGINS=grafana-piechart-panel, natel-plotly-panel, blackmirror1-singlestat-math-panel, btplc-trend-box-panel, marcuscalidus-svg-panel, michaeldmoore-multistat-panel, yesoreyeram-boomtable-panel, ryantxu-ajax-panel, grafana-influxdb-flux-datasource, fetzerch-sunandmoon-datasource, andig-darksky-datasource, simpod-json-datasource

When Grafana starts, it seems to check and pull latest for all of these including boomtable (from here likely). However, it seems that does not include alpha/beta versions. When @yesoreyeram (thank for the great plug-in by the way!) releases the official v1.5.0 tag, we should see Grafana pick that up for everyone. In the meantime, drive over to https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/issues/152 and plus-one the request or use the method you described to run the alpha version.

[jasonacox]

https://github.com/jasonacox/Powerwall-Dashboard/pull/50 merged - Thanks @mcbirse !

[youzer-name]

Great analysis and points @youzer-name ! Are you using the dashboard-animation.json dashboard? Would you mind posting the panel json for those? I'm trying to figure out the best way to allow the community to share panel or full dashboards with each other. Would it make sense to create a dashboards directory or discussion board?

@mcbirse thanks for being willing to try the PR this weekend! Let me know if you have any questions.

@jasonacox - Just to close the loop here, I posted the json for this panel under discussions/show and tell: https://github.com/jasonacox/Powerwall-Dashboard/discussions/52

[jasonacox]

Thank you @youzer-name !

[jasonacox]

The random "No data" issue with the "Current State" boom table continues. I suspect others are still seeing this.

This is a known bug with the yesoreyeram-boomtable-panel plugin (https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/issues/152) but it has been fixed in the alpha version. I discovered that I can have Grafana use the alpha version and have been running that for a few days with no issues, and also more importantly, no "No data" occurrences.

The "fix" involves a simple edit to the grafana.env docker environment file to force the download of the alpha verion:

GF_INSTALL_PLUGINS=grafana-piechart-panel, natel-plotly-panel, blackmirror1-singlestat-math-panel, marcuscalidus-svg-panel, michaeldmoore-multistat-panel, https://github.com/yesoreyeram/yesoreyeram-boomtable-panel/releases/download/v1.5.0-alpha.2/yesoreyeram-boomtable-panel-1.5.0-alpha.2.zip;yesoreyeram-boomtable-panel, ryantxu-ajax-panel, grafana-influxdb-flux-datasource, fetzerch-sunandmoon-datasource, andig-darksky-datasource, simpod-json-datasource

I'm going to run this a few more days before I update the default grafana.env.sample, but so far it is working great.

Before:

After:

[mcbirse]

@jasonacox - That would be great if this was part of the standard plugin install for Grafana!

I had installed this manually since Jul and have not seen any issues since.

I do note however, the latest alpha release appears to be v1.5.0-alpha.3 and that is the one I installed (rather than alpha2 listed in your post).

I never checked the difference between alpha2 and alpha3 and if there were any other known issues with alpha3. I just blindly installed latest alpha and crossed my fingers! 😄

[jasonacox]

Thanks @mcbirse - I checked the diffs and it seems alpha.3 is predominantly spelling corrections. With your report, I'm more confident to move it in as the standard.