Grafana Dashboard Project

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 to store data, and Grafana 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 to do just that, which is available from the node-RED palette.
The complete flow that I use is;

The 4 function nodes contain minimal code to format the data so that the 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)

…or a dark theme…

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!)

7 Likes

Good stuff :+1:

1 Like

Looks good! thumbsup

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

2 Likes

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

Nice dashboard as well.

2 Likes

I’m using 2 retention policies;

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;

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
1 Like

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.

2 Likes

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

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.

Here’s an example of one dashboard:


'twas a really crappy day for PV production. :frowning_face:

and another:


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.

[email protected]:/var/lib/influxdb$ sudo du -csh
428M    .
428M    total
[email protected]:/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.

Nice dashboards Bill.

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

TY,S!

POC?

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?

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…

1 Like

Ah yes. (Slaps self in face) Should’ve known that one. :wink:

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

https://docs.influxdata.com/influxdb/v1.7/write_protocols/line_protocol_reference/

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

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

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:

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

2 Likes

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

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

1 Like

@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 :roll_eyes:

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)

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.

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.

Yes.

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