# Grafana Dashboard Project

**URL:** <https://community.openenergymonitor.org/t/grafana-dashboard-project/9829>\
**Category:** Integrations\
**Created:** [21 January 2019 19:27 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829 "2019-01-21T19:27:28Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [21 January 2019 19:27 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/1 "2019-01-21T19:27:28Z")

</div>

I’ve spent a bit of time recently reflecting upon what data I need from my emonTX, emonTH & solar diverter, and how I would like the data presented.  
As a result, I’ve built a system which instead of using emoncms, uses node-RED to capture the data, [influxdb](https://www.influxdata.com/) to store data, and [Grafana](https://grafana.com/) to display the results.

I get the data by listening on /dev/ttyAMA0 with a node-RED serial node and capture the RF binary packets. To decode the data packets, I’ve written a node-RED node - [node-red-contrib-rf-decode](https://flows.nodered.org/node/node-red-contrib-rf-decode) to do just that, which is available from the node-RED palette.  
The complete flow that I use is;

 ![emon](https://community.openenergymonitor.org/uploads/default/original/2X/8/82235616d8082056359f704ae0a3f4e6d15e423e.png)

The 4 function nodes contain minimal code to format the data so that the [Influxdb](https://flows.nodered.org/node/node-red-contrib-influxdb) node is able write it to a Influxdb time-series database.

Grafana, is querying the database and present the data in it’s own dashboard. _(click the image for full size)_

 ![lightdesk](https://community.openenergymonitor.org/uploads/default/original/2X/9/912dbbdecdcb6d4981d00d31ba2a97ec9d5a386b.png)

…or a dark theme…

 ![darkdesk](https://community.openenergymonitor.org/uploads/default/original/2X/f/f906414682e92123eb51c8f57f09ab7150c988a8.png)

Each of the charts are refreshed every 5 seconds and can be zoomed in or out, or panned, and the gauges & boxes change colour depending upon the data value.

The database automatically downsamples the data, and only retains 1 day’s worth of realtime data, but retains the downsampled data for 1 year, to stop the database boating.

The dashboard is published over SSL, and is password protected.

_ **NOTE - This is not a replacement for emoncms.** _ Emoncms contains more features, and more detailed analysis tools than my personal project. Also influx & grafana can present a learning curve not suited to everyone. _(as I found out!)_

---

<div class="post-metadata">

**Author:** ![nchaveiro](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/nchaveiro/32/12392_2.png) [@nchaveiro](https://community.openenergymonitor.org/u/nchaveiro)\
**Post date:** [21 January 2019 21:31 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/2 "2019-01-21T21:31:27Z")

</div>

Good stuff 👍

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [22 January 2019 02:31 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/3 "2019-01-22T02:31:13Z")

</div>

Looks good! ![thumbsup](https://community.openenergymonitor.org/uploads/default/original/2X/3/3c587556c0cc11389317c03a60788d77ceebd307.gif)

> [@Paul](#):
>
> The database automatically downsamples the data, and only retains 1 day’s worth of realtime data, but retains the downsampled data for 1 year, to stop the database boating.

Did you use a Retention Policy set at database creation time to give you the one day / one year limits?

---

<div class="post-metadata">

**Author:** ![Greebo](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/greebo/32/4556_2.png) [@Greebo](https://community.openenergymonitor.org/u/Greebo)\
**Post date:** [22 January 2019 06:21 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/4 "2019-01-22T06:21:44Z")

</div>

> [@Paul](#):
>
> Also influx & grafana can present a learning curve not suited to everyone. _(as I found out!)_

Been there, done that, came out the other side with the same conclusion as you!

Nice dashboard as well.

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [22 January 2019 11:40 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/5 "2019-01-22T11:40:40Z")

</div>

> [@Bill.Thomson](#):
>
> Did you use a Retention Policy set at database creation time to give you the one day / one year limits?

I’m using 2 retention policies;

```auto
name duration shardGroupDuration replicaN default
---- -------- ------------------ -------- -------
one_year 8736h0m0s 168h0m0s 1 false
one_day 24h0m0s 1h0m0s 1 true

```

and also a Continuous Query, to group and average data into 5 minute chunks;

```auto
cq_5m CREATE CONTINUOUS QUERY cq_5m ON emondata BEGIN SELECT mean(grid) AS grid, mean(solar) AS solar, mean(divert) AS divert, mean(usage) AS usage INTO emondata.one_year.downsampled_iot FROM emondata.one_day.iot GROUP BY time(5m), grid, solar, divert, usage END

```

---

<div class="post-metadata">

**Author:** ![pb66](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/pb66/32/27_2.png) [@pb66](https://community.openenergymonitor.org/u/pb66)\
**Post date:** [22 January 2019 12:08 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/6 "2019-01-22T12:08:01Z")

</div>

Thanks for sharing @Paul,

Another great feature of influx/grafana is the ability to store and display notes on the timeline graphs so that comments to explain certain activity profiles, notable changes in tech, circumstance or environment and/or highlighting issue’s/anomolies/fixes in the data etc etc.

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [22 January 2019 13:34 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/7 "2019-01-22T13:34:18Z")

</div>

> [@Paul](#):
>
> and also a Continuous Query,

Tnx for sharing the CQ. I’ve never used one, but now I’ll have to give 'em a try. 😉 😁

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [23 January 2019 03:08 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/8 "2019-01-23T03:08:37Z")

</div>

> [@Bill.Thomson](#):
>
> to stop the database boating.

Hi Paul,

I got to thinking about your comment about database bloat, so I took a look at my system.  
To give an idea how much data is being collected, I took a screenshot of my data sources page.  
Measurements for the energy, stove temp, test and udp data sources are collected at 5 second intervals,  
enviro and temp data at one minute intervals.  
A total of 17 metrics for the energy data source, 4 for the test DS, one for the temp,  
one for the enviro and one for the StoveTemp DS.

 ![tpp-data-sources](https://community.openenergymonitor.org/uploads/default/original/2X/e/e77bc6eb0762ca5baa83313430678f0af7d2073a.jpeg)

Here’s an example of one dashboard:

 ![tpp-dash](https://community.openenergymonitor.org/uploads/default/original/2X/8/826cab17a80bd7a0e51c0481c9012a9157e3caed.jpeg)  
'twas a really crappy day for PV production. ☹

and another:

 ![image](https://community.openenergymonitor.org/uploads/default/original/2X/a/ae077d743b4133e794a86ee17220951319266bda.png)

* * *

The _total_ amount of data in my /var/lib/influxdb directory (about 7 months worth) - which inclues the WAL and metadata as well as the metrics, is 428 MB.

```
bt@61:/var/lib/influxdb$ sudo du -csh
428M .
428M total
bt@61:/var/lib/influxdb$

```

* * *

With the small number of metrics you have, you might not have any issues with a longer retention policy  
or even no RP at all.

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [23 January 2019 09:54 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/9 "2019-01-23T09:54:58Z")

</div>

Nice dashboards Bill.

> [@Bill.Thomson](#):
>
> With the small number of metrics you have

There lots more to add! I haven’t had chance to look at the emonTH’s, emonTX’s, weather api data and data from my ESP’s. The above dashboard was really a POC as I’ve never used influxdb or grafana before.

I suppose different people will have different requirements, but, I had a good think about exactly what data I needed and what use I put it to.  
For example, I can’t recall needing _historical_ 5 second granularity, or data beyond the previous 12 months, so why keep it?  
But, I do want graph data to load quickly, and remember that I’m processing & serving the data from a Raspberry Pi, albeit a Pi 3B+ with attached HD drive.

Paul

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [23 January 2019 12:31 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/10 "2019-01-23T12:31:16Z")

</div>

> [@Paul](#):
>
> Nice dashboards Bill.

TY,S!

> [@Paul](#):
>
> The above dashboard was really a POC

POC?

> [@Paul](#):
>
> _historical_ 5 second granularity, or data beyond the previous 12 months, so why keep it?

Yep. I’ve never looked at any of my data that’s more than a year old. My thought about that was  
_I’ve managed to get along just fine before I started collecting the data, so why worry about_  
_losing any of it now?_

I’ve noticed that display delay on a Pi3 is reasonable when retrieving six months (or less) of data.  
There’s definitely some delay when retrieving a years worth of data. But, as you mentioned,  
how often does that actually happen?

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [23 January 2019 12:35 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/11 "2019-01-23T12:35:18Z")

</div>

> [@Bill.Thomson](#):
>
> POC?

Proof Of Concept  
ie. I didn’t know if the learning curve to use influx & grafana would be too steep a learning curve for me!  
I’m still struggling with db queries…

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [23 January 2019 12:41 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/12 "2019-01-23T12:41:18Z")

</div>

> [@Paul](#):
>
> Proof Of Concept

Ah yes. (Slaps self in face) Should’ve known _that_ one. 😉

Have you had a look at the Influx Line Protocol docs?

> **[Line protocol | InfluxDB OSS v2 Documentation](https://docs.influxdata.com/influxdb/v1/write_protocols/line_protocol_reference/)**
>
> InfluxDB uses line protocol to write data points. It is a text-based format that provides the measurement, tag set, field set, and timestamp of a data point.

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [23 January 2019 12:47 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/13 "2019-01-23T12:47:23Z")

</div>

Are you trying to work out the queries manually or through the Grafana interface?

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [23 January 2019 22:33 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/14 "2019-01-23T22:33:50Z")

</div>

Both really Bill, mostly through the Grafana interface, but some are direct influxdb queries made by node-RED.

Mostly both are straightforward, but things like calculating kwh/d from power readings was not easy, but ‘google as my friend’, we eventually got there!!

Paul

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [24 January 2019 02:44 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/15 "2019-01-24T02:44:07Z")

</div>

> [@Paul](#):
>
> calculating kwh/d from power readings

This is what I came up with. Is yours similar?

`SELECT INTEGRAL(*,1h) FROM GENW WHERE TIME >= now() - 30d GROUP BY time(1d)`  
(`GENW` is PV output in Watts)

Gives me this:

 ![calcd_energy](https://community.openenergymonitor.org/uploads/default/original/2X/9/93e1c0a6c8a4a3c35b826a5ad257d5cf96e21c36.jpeg)

The top chart uses the query shown above.  
The bottom chart shows the data as reported by a WattsOn universal power transducer.

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [24 January 2019 09:05 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/16 "2019-01-24T09:05:49Z")

</div>

I’ve not got as far as charting kwh/d yet, but I’m also using `integral` to get the daily total;

`SELECT cumulative_sum(integral("usage")) / 3600 FROM "iot" WHERE ("device" = 'diverter') AND $timeFilter GROUP BY time(1s) fill(null)`

![singlestat](https://community.openenergymonitor.org/uploads/default/original/2X/b/b965150f67cd249a83a64c109088139998c62235.png)

The time frame is set in the ‘Singlestat’ `Time range` tab setting.

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [24 January 2019 18:44 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/17 "2019-01-24T18:44:07Z")

</div>

> [@Bill.Thomson](#):
>
> `SELECT INTEGRAL(*,1h) FROM GENW WHERE TIME >= now() - 30d GROUP BY time(1d)`  
> ( `GENW` is PV output in Watts)

@Bill.Thomson have you a screenshot of your grafana metrics tab please. I don’t seem to be able to get your query working here 🙄

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [24 January 2019 18:48 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/18 "2019-01-24T18:48:19Z")

</div>

Have you changed the query edit method so it looks like this?:  
(via the hamburger menu at the right side of the query editor window)

 ![image](https://community.openenergymonitor.org/uploads/default/original/2X/d/d4c966f38890f4812a0222ffa3ac57ca462dc5c8.png)

I can’t access the machine running that particular query ATM, but I’m off work in an hour, so I’ll  
shoot you a copy when I get home.

---

<div class="post-metadata">

**Author:** ![Paul](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/paul/32/28_2.png) [@Paul](https://community.openenergymonitor.org/u/Paul)\
**Post date:** [24 January 2019 18:52 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/19 "2019-01-24T18:52:58Z")

</div>

> [@Bill.Thomson](#):
>
> Have you changed the query edit method so it looks like this

Yes, and pasted your query (editing for my database details) but whenever I toggle it back, I lose anything that I’ve added. Do you leave it permanently toggled to query view?  
I think that I’ll probably find it easier working from a screenshot, so thanks.

---

<div class="post-metadata">

**Author:** ![Bill.Thomson](https://community.openenergymonitor.org/user_avatar/community.openenergymonitor.org/bill.thomson/32/1834_2.png) [@Bill.Thomson](https://community.openenergymonitor.org/u/Bill.Thomson)\
**Post date:** [24 January 2019 18:54 UTC](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829/20 "2019-01-24T18:54:58Z")

</div>

> [@Paul](#):
>
> Do you leave it permanently toggled to query view?

Yes.

Odd that it didn’t work. Could you show me _your_ query window with the contents you pasted?

[Next page](https://community.openenergymonitor.org/t/grafana-dashboard-project/9829.md?page=2)