Share & Show Off your Installations Here!

[David00]

Have a neat looking dashboard that you've built? Show it off here! The purpose behind this discussion thread is to share ideas and help newcomers get started making awesome looking dashboards.

If you'd like to share something you've made, please include:

• Screenshot(s) of the dashboard or panel
• A short description
• The JSON export for the dashboard or panel (get the JSON for a particular panel by clicking on the panel dropdown menu, then Inspect -> Panel JSON)
• Any dependencies (like Grafana plugins, variable definitions, InfluxDB modifications, etc).

[David00]

I'll start by sharing a custom panel that I've added to my Home Power Dashboard:

This panel looks at the last 60 days of daily usage, production, and cost. Each bar is a single day. The green is the amount of solar energy I produced for that day. The red is the amount of energy my home consumed. The bars are not stacked - they are overlapped - so if the red bar is taller than the green bar, I know I consumed more than I produced. The blue line tracks the daily power cost or credit (on the right axis).

Requirements

This panel cannot be used on the raw data alone because looking at 60 days worth of raw data would be an incredible amount of data. So, it requires you to setup continuous queries (CQs), which will automatically downsample the high resolution data into 5 minute averages for all future data. To get the historic data summed up into 5 minute averages, you'll have to run a query manually.

You'll also need to have the timezone variable defined on your dashboard like so. The timezone must be one of the standard TZ database names from the list on this page:
https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

Here are the commands to backfill the averages for all your historic data, followed by the commands to create the CQs for future data.

These need to be ran inside InfluxDB, so connect to your InfluxDB with: influx -database power_monitor

Also, the first set of commands (starting with SELECT may take a long time to run. You should stop the power monitor before running these commands to free up some resources and reduce the background operations with the DB: sudo systemctl stop power-monitor

Also, run the SELECT commands one by one.

CREATE RETENTION POLICY "rp_5min" ON "power_monitor" DURATION INF REPLICATION 1;

SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.net_5m FROM power_monitor.autogen.net GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.solar_5m FROM power_monitor.autogen.solar GROUP BY time(5m);
SELECT mean(voltage) AS voltage INTO power_monitor.rp_5min.voltage_0_5m FROM power_monitor.autogen.voltages WHERE v_input = '0' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.home_energy_5m FROM power_monitor.autogen.home_load GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.net_energy_5m FROM power_monitor.autogen.net GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.solar GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct1_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '1' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct2_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '2' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct3_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '3' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct4_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '4' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct5_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '5' GROUP BY time(5m);
SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct6_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '6' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct1_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '1' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct2_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '2' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct3_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '3' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct4_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '4' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct5_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '5' GROUP BY time(5m);
SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct6_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '6' GROUP BY time(5m);



CREATE CONTINUOUS QUERY cq_home_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.home_load_5m FROM power_monitor.autogen.home_load GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_net_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.net_5m FROM power_monitor.autogen.net GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_solar_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.solar_5m FROM power_monitor.autogen.solar GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_voltage_5m ON power_monitor BEGIN SELECT mean(voltage) AS voltage INTO power_monitor.rp_5min.voltage_0_5m FROM power_monitor.autogen.voltages WHERE v_input = '0' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_home_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.home_energy_5m FROM power_monitor.autogen.home_load GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_net_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.net_energy_5m FROM power_monitor.autogen.net GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_solar_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.solar GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct1_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct1_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '1' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct2_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct2_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '2' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct3_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct3_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '3' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct4_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct4_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '4' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct5_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct5_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '5' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct6_energy_5m ON power_monitor BEGIN SELECT integral(power) / 3600000 AS energy INTO power_monitor.rp_5min.ct6_energy_5m FROM power_monitor.autogen.raw_cts WHERE ct = '6' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct1_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct1_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '1' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct2_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct2_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '2' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct3_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct3_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '3' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct4_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct4_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '4' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct5_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct5_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '5' GROUP BY time(5m) END
CREATE CONTINUOUS QUERY cq_ct6_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current, mean(pf) AS pf INTO power_monitor.rp_5min.ct6_power_5m FROM power_monitor.autogen.raw_cts WHERE ct = '6' GROUP BY time(5m) END

After that, you should have the necessary data to deploy this panel! Panel JSON attached. (To deploy the panel, simply go to your dashboard, add a new empty panel, hit apply to add the empty panel to the dashboard, then click on the panel title, go to Inspect, then Panel JSON. Then, paste the contents of the attached file into the Panel JSON box, overwriting everything that was there.

Don't forget to restart the power monitor if you stopped it! sudo systemctl start power-monitor

Overview-Panel-JSON.txt

[David00]

Oh, ok - instead of rp_raw, can you try with default? I think the rp_raw RP might have been something else I created a long time ago for my specific deployment. If that works, I'll update the post above. If it doesn't work still, can you share the output of show retention policies from influx CLI?

[JeromeThiv]

That what i got :

> CREATE CONTINUOUS QUERY cq_home_power_5m ON power_monitor BEGIN SELECT mean(power) AS power, mean(current) AS current INTO power_monitor.rp_5min.home_load_5m FROM power_monitor.default.home_load GROUP BY time(5m) END
ERR: error parsing query: found DEFAULT, expected identifier at line 1, char 178

And :

> show retention policies
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true
rp_5min 0s       168h0m0s           1        false

[David00]

Oh ok, instead of rp_raw, it should be autogen.

[JeromeThiv]

Every thing is fine, thank you

[David00]

Awesome - I will update the instructions above then! Thanks for bringing this to my attention

[averter]

Hello, just a heads-up in that there is a new timezone global variable in grafana. Therefore, future versions of the dashboards should replace tz('${timezone}') by tz('$__timezone'). I can create a PR if of interest.

[David00]

Thanks for the heads up, @averter! I will look into this when I build the next OS image, which will likely include the latest version of Grafana at that time.