Hello! being a mechanical engineer & new to node red, I've managed to get data from an OMRON PLC using FINS read node. And using influxdb out node I've also logged the data. Now I want to show mean value from logged values in weekly, monthly, yearly manner on node-red dashboard gauge. I've tried searching for solution on internet yet I'm unable to understand how to write query in influxdb in node and then show it in a gauge. Can anyone help on this problem or direct me to a solution.
Which version of Influx are you using? 1.8 or 2.0. It makes a big difference as the query language has changed.
I'm using version 1.8
If you are using 2.0, then an example query might be:
msg.query =
'from(bucket: "testbucket")'+
' |> range(start: -24h)' +
' |> filter(fn: (r) => r._measurement == "system" and (r._field == "load1" or r._field == "load5" or r._field == "load15"))' +
' |> timedMovingAverage(every: 10m, period: 10m)';
return msg;
This reads the values for the last 24 hours and computes an average for a window of 10 minutes.
Edit: Oh, I was writing this reply while you were saying you are using 1.8, so forget this post, please
Are there any tutorials on web for someone like me who is from completely different field? 
With 1.8 you can use the influxql language which is similar to regular sql. I suggest you experiment with the command line program influx which will let you try things out to see the effect. This link shows you how you can query the data. Explore data using InfluxQL | InfluxDB OSS 1.8 Documentation
If you run, on the machine running influx, the command
influx
then it should open the s/w. Then you can say, for example
show databases
and it will show them, then
use mydatabase
where you put the name of the database you are using, then
show measurements
will show you the measurements that exist in the database, then
select * from mymeasurement limit 10
will show you the first ten samples.
Will go through the commands. Will reply back if unable to get output.If you want to feed the result to a gauge then presumably you want the most recent value so you may want to something like
select * from mymeasurement order by time desc limit 1
which will give you the last row.
Edit, I have re-read your first post and see you want to show averages over times, in which case you can use the MEAN function and GROUP BY time in the query to do that.
I would consider using Grafana to view the Influxdb data
1.) Node Red to format the data to InfluxDb
2.) Grafana to visualise the data
This is all possible on the same device
I used this tutorial to get started and have many variations of this
Definitely, if you want to show charts. If you just want gauges then the dashboard may well be good enough.
[Edit] Unfortunately the Grafana UI has changed significantly since that tutorial, so there may be some difficulty following that section of it. The user documentation for Grafana is rubbish, as is the support on the forum, at least in my experience. Luckily the software itself is excellent which makes it worth the pain.
1 Like
@Colin , I've read the InfluxDB 1.8 documentation and below is the query I came up with
SELECT mean("value") AS "mean_value" FROM "OEE"."autogen"."OEE M02CN001" WHERE time > now() -4w
But now I'm getting an error in node-red:
HttpError: 403 Forbidden : Flux query service disabled. Verify flux-enabled=true in the [http] section of the InfluxDB config.
I think you have selected 1.8-flux as the version in the influx database node. Select 1.x instead. 1.8 can do flux and sql, you have to tell it which one.
Did that query work in the command line influx?
@Colin, It worked in command line.
Also, I've configured server with v1.8. Do I need v1.x instead of v1.8 ?
As I said:
@Colin , yes it worked thankyou. Now how should I display in gauge. Should I use buffer parser node?
Feed it straight into the gauge and configure the Value Format to {{msg.payload[0].mean_value}}. If you want the text to only show, for example, one decimal place then use {{msg.payload[0].mean_value | number: 1}}
1 Like
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
