Data Base information on gauge graph

First, let me say it's my first time using node-red and reading this forum has helped more than you can imagine. I knew nothing about node-red and now I'm about to finish my IoT project.

I'm managing hand sanitizer dispensers (my mqtt broker sends me a topic with the number of pumps, and then I send that data to mysql and calculate how much hand sanitizer is left).

So I have this table (view) on mysql that shows the last update from each device, with his full capacity and whats left.


I need to translate this information on gauge graphs showing how much is left in each device. But I can't seem to find out how to do it.

I only have this

on my function I wrote

msg.topic="SELECT full_capacity, (full_capacity-(nr_pumps*3)) FROM left_capacity;"
return msg;

but I know this is wrong, because node-red has no ideia which device to pull information, etc.

Any help would be much appreciated, thanks in advance.

Have a look on below documentation to understand how to customize the gauge properties.

and here a quick example...


I've been for hours trying to resolve this but just can't figure this out.

This is my current flow.

On my function node I have

var result;
result= "SELECT available From table_left_capacity WHERE device_id=1"
return msg;

And this is what I got


now I don't know how to send this value to the gauge graph, as it is an object and not a number. I tried Json and even writing a couple functions, but nothing seems to work.

Try this... Not an expert yet but getting there! :smiley:

Do a function node

msg.payload = msg.payload[1];
return msg;

Then do a change node

You're almost there.

To get the value into the chart, you need to get that value into msg.payload

Use a change node to copy that value to msg.payload

There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the but about the copy path button that appears under your cursor when you hover over a variable in the debug window.

You can tell the gauge to use that value directly. Feed it straight into the gauge node and in the Value Format field put {{msg.payload[0].available}}.

Thank you so much! I now have a working graph, can't believe I pulled an all nighter and it was so simple.


I don't want to push it, but I'd like to ask 2 more questions.

1- Is is possible to automatically do a gauge graph for each device on my data base? Instead of having the "WHERE device_id=1", "WHERE device_id=2", etc. Because more devices can be added at any given moment, so that would be the best.

2- As I said before this is a project about hand sanitizer dispensers, and they may vary in capacity, device one may have 1000ml full, device two 2000ml, etc. So is there a way to make this values (that yet again may vary, but updated values are stored at my data base) my max value for the gauge graph, instead of saying I want a pre defined value?

Thank you in advance.

I don't know the answer to those questions. But I remember that the question of dynamically created gauges was discussed here recently. Try searching the forum.

Thank you very much, as for question 1 I "solved" my problem with a switch node. I have a limited number of devices I can add, but that's enouth for what I'm looking for.

Thank you!

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.