Node-RED and Grafana

Last year - 2020 - I hosted (or tried to host) a year long photography contest that I named Photo52. The goal was to hold a contest with a new theme every week of the year, with a final victor crowned by vote when all the photos have finally been cast.

I discovered pretty quick that I needed a good method of organising and keep track of all the entries. My initial thought was to use a database, but what I really wanted was an easy way to keep it updated and view it. My initial tool of choice for this was Node-RED.

I like automation. I don't like to code. My career choice makes this statement incredibly ironic as, throughout the course of my career, I've had to learn python, perl, php, go, and even some C++. A further choice I've made in beginning to design mobile games has by necessity lead me to learning C#. Regardless, I try to shy away from coding as much as possible because... I really don't like doing it.

So for an upkeep need such as I had for this, using Node-RED was quick and easy and painless. Node-RED was, initially, a node.js application designed for quickly implementing Internet of Things solutions, and it has since turned into an open source way to automate just about anything. It is comprised of interlocking "nodes" which pass data between them from beginning to end until, ultimately, a final node completes the process.

The "nodes" are individual pieces of underlying code that perform a function. Some of the nodes perform very simple functions, and some are more complex and perform data manipulation. The underlying concept of each node, however, is straight forward: the first node ingests a piece of data into the entire flow; the next node accepts the data from the first node, performs calculations and modifications on it, and passes it to the next, and so forth, utnil the end is reached.

Take the above flows, for instance. These are flows which are designed to create a simple web page ([post] /addentry) with a form ("form"), pass the data from the form to a function ("function") which creates a mysql query which is then passed to a MySQL node ("Insert MySQL") which, in turn, inputs the data into a database.

The final product looks like this:

This made the job of keeping the contest entries up to date so very, very simple: simply putting the information for each entry into the form and hitting submit allowed me to keep a record of it all.

The underlying json code looks like this. One of the really neat features of Node-RED flows is that they are all stored as json snippets, which makes porting them really easy.

[{"id":"a57a25a2.aac7e8","type":"tab","label":"Post to Photo52","disabled":false,"info":""},{"id":"8f039e7b.74fe8","type":"http in","z":"a57a25a2.aac7e8","name":"","url":"/addentry","method":"post","upload":false,"swaggerDoc":"","x":320,"y":320,"wires":[["fe207375.4e46d"]]},{"id":"fe207375.4e46d","type":"function","z":"a57a25a2.aac7e8","name":"","func":"var username = msg.payload.Username;\nvar entry_url = msg.payload.URL;\nvar week_number = msg.payload.WeekNumber;\ninsert_sql = \"INSERT INTO weekly_entries (username,entry_url,week_number) VALUES ('\" + username + \"','\" + entry_url + \"','\" + week_number + \"')\";\nmsg.topic = insert_sql;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":560,"y":360,"wires":[["a39c62ab.0b23a"]]},{"id":"76f6d4fd.8b738c","type":"http response","z":"a57a25a2.aac7e8","name":"","statusCode":"","headers":{},"x":1010,"y":380,"wires":[]},{"id":"f05da2f8.8f9c9","type":"template","z":"a57a25a2.aac7e8","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"  \n  \n  \n  \n    Yay!
\n    <a href=\"http://bytecoinpi:1880/addentry\">Add another.
\n  \n ","output":"str","x":920,"y":460,"wires":[["76f6d4fd.8b738c"]]},{"id":"5b59b911.e623c","type":"debug","z":"a57a25a2.aac7e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":460,"wires":[]},{"id":"a39c62ab.0b23a","type":"mysql","z":"a57a25a2.aac7e8","mydb":"f1cf3a19.769738","name":"Insert MySQL","x":780,"y":380,"wires":[["f05da2f8.8f9c9"]]},{"id":"83debcb9.fe5f18","type":"ui_form","z":"a57a25a2.aac7e8","name":"","label":"","group":"b7948c2d.9e4118","order":0,"width":0,"height":0,"options":[{"label":"Username","value":"Username","type":"text","required":true,"rows":null},{"label":"URL","value":"URL","type":"text","required":false,"rows":null},{"label":"WeekNumber","value":"WeekNumber","type":"number","required":false,"rows":null}],"formValue":{"Username":"","URL":"","WeekNumber":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":350,"y":420,"wires":[["5b59b911.e623c","fe207375.4e46d"]]},{"id":"f1cf3a19.769738","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"photo52","tz":"","charset":""},{"id":"b7948c2d.9e4118","type":"ui_group","name":"Updates","tab":"d60b5dc3.251a6","order":1,"disp":true,"width":"12","collapse":false},{"id":"d60b5dc3.251a6","type":"ui_tab","name":"Add Entry","icon":"dashboard","disabled":false,"hidden":false}]

The next task at hand that I needed was a way to view the information. It's all in the database at this point, now I just needed to get it out. @robingreig has been doing a really great job of building out Node-RED tutorials, and I highly suggest reading through his blow to get more detailed information. Suffice it to say, I felt that a dashboard was what I needed (he provides some really good details) and that is where I started: a Node-RED dashboard.

It might be worth repeating that Node-RED is really just data manipulation from beginning to end. For the dashboard I wanted to build I needed data from the MySQL database that I had put the contest information into. Another quick dashboard form and a mysql node gave me all I needed to put this together:

This entire page is really just a combination of a pulldown node with a list of weeks, a MySQL query which pulls out all the entries for that week, and then finally a table node which formats the data.

There are really two flows, one which obtains the number of the week and passes it to the second flow, which then obtains all the entries and displays the table.

This looks good. I like it. But...

What I really want is a table that has a hyperlink to the URL of the contest entry. Now, I know I can do this in Node-RED with additional nodes (there really are nodes for everything) but I'll repeat that I do not like to code, and I really do not like to code HTML. My HTML skills do not progress beyond the basic, and I'm happy that. So instead of engaging in a project that I knew I wouldn't enjoy, I decided to use a program that is designed straight up for dashboarding: Grafana.

Grafana is designed to do dashboard, only dashboards, and nothing but dashboards and, quite frankly, it does it well and looks really snazzy. Here is waht I was able to do with really only a little bit of work and no eye-straining code. Node-RED is used to place the data I need into the MySQL database. Grafana fetches the data from the database and formats it in a way that I like to see.

Grafana also allows for hyperlinks not only to external sites (such as linking each entry to the URL) but also allows for easy hyperlinking between dashboards. With this I was able to quickly build links to look at each entry from any individual user:

I'm not going to go into the detail on the grafana deployment here; right now it's sufficient to say that grafana works in a similar fashion to Node-RED in that it takes data and formats it for display. It may look intimidating, but it really is only just data formatting.

Node-RED and Grafana are both free and open-source, which are two of my favourite things. Between them, users are almost unlimited in what they can do or display.

Next I'll do a deep dive of the Node-RED flows and explain each node and how it passes data.

About Me

Amateur photographer. Wannabe author. Game designer. Nerd. 
General all around problem-solver and creative type.

My Favourite Tags


#spaceforce3	#altphoto	#crappycameraphotos
#digitalpinhole	#pinhole	#firehydrant