# Query mean value from influxDB into a gauge
**URL:** https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078
**Category:** General
**Created:** [23 March 2021 11:37 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078 "2021-03-23T11:37:19Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [23 March 2021 11:37 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/1 "2021-03-23T11:37:19Z")
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.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 March 2021 11:48 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/2 "2021-03-23T11:48:47Z")
Which version of Influx are you using? 1.8 or 2.0. It makes a big difference as the query language has changed.
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [23 March 2021 11:55 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/3 "2021-03-23T11:55:04Z")
I'm using version 1.8
---
### Author:  [@Simon01011](https://discourse.nodered.org/u/Simon01011)
#### Post date: [23 March 2021 11:58 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/4 "2021-03-23T11:58:59Z")
If you are using 2.0, then an example query might be:
```auto
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
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [23 March 2021 12:09 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/5 "2021-03-23T12:09:57Z")
Are there any tutorials on web for someone like me who is from completely different field? 😐
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 March 2021 12:13 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/6 "2021-03-23T12:13:59Z")
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](https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/)
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.
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [23 March 2021 12:22 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/7 "2021-03-23T12:22:19Z")
@Colin 👍 Will go through the commands. Will reply back if unable to get output.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 March 2021 13:32 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/8 "2021-03-23T13:32:09Z")
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.
---
### Author:  [@mtoko](https://discourse.nodered.org/u/mtoko)
#### Post date: [23 March 2021 13:37 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/9 "2021-03-23T13:37:06Z")
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
[ ](https://www.youtube.com/watch?v=JdV4x925au0)
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 March 2021 13:40 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/10 "2021-03-23T13:40:12Z")
> [@mtoko](#):
>
> I would consider using Grafana to view the Influxdb data
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.
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [24 March 2021 11:29 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/11 "2021-03-24T11:29:36Z")
@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.`
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [24 March 2021 12:26 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/12 "2021-03-24T12:26:22Z")
> [@sahsha](#):
>
> Flux query service disabled
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?
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [24 March 2021 12:36 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/13 "2021-03-24T12:36:13Z")
@Colin, It worked in command line.
Also, I've configured server with v1.8. Do I need v1.x instead of v1.8 ?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [24 March 2021 12:38 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/14 "2021-03-24T12:38:04Z")
As I said:
> [@Colin](#):
>
> 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.
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [24 March 2021 12:49 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/15 "2021-03-24T12:49:19Z")
@Colin , yes it worked thankyou. Now how should I display in gauge. Should I use buffer parser node?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [24 March 2021 13:34 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/16 "2021-03-24T13:34:22Z")
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}}`
---
### Author:  [@sahsha](https://discourse.nodered.org/u/sahsha)
#### Post date: [25 March 2021 07:53 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/17 "2021-03-25T07:53:14Z")
@Colin, Thanks a lot. was able to get what needed.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [20 April 2021 05:23 UTC](https://discourse.nodered.org/t/query-mean-value-from-influxdb-into-a-gauge/43078/18 "2021-04-20T05:23:19Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.