HELP - Adding multiple time-series in InfluxDB/Grafana


#1

Dear friends,

I am still an early-stage user of InfluxDB with Grafana.

I would like to find a simple approach to “add” multiple data streams at once.

A quick example of a Grafana dashboard (find the attached) presenting time-series data points from InfluxDB is used for illustration.

The bar chart presents time-series record of energy consumption per day of different points.

In the above, I would like to simply “add” all values everyday to compute aggregated energy consumption.

How do I implement this?

Your quick help will be appreciated.

Thanks & regards.

Bala


#2

Hi Bala,

Thanks for your question! Based on what you’ve written, maybe you could try merging or joining the multiple time series into a single stream? Please take a look at the documentation: here

Another option is to take a look at the aggregation functions listed: here

If I’ve misunderstood, please reply back and let me know.


#3

Hello friend,

Appreciate your support :slight_smile:

Well, I think you understood my problem differently.

In the example that I showed using Grafana bar chart, I have presented daily energy consumption of multiple zones in a building individually for 60 days. I have used separate InfluxDB queries for computing daily energy consumption (for 24hrs) by integrating respective hourly energy consumption time series individually.

My question now is “how to compute daily energy consumption of entire building as a single time-series for 60 days?” The simple math representation of the above state problem can be represented as follows:

Days | Zone A | Zone B | Zone C | Entire Building
1 | 10 | 8 | 3 | (10+8+3) 21
2 | 23 | 0 | 10 | (23+0+10) 33
3 | 15 | 5 | 10 | (15+5+10) 30
4 | 6 | 9 | 21 | (6+9+21) 36


60 | 21 | 8 | 5 | (21+8+5) 34

How do I implement this in Grafana using InfluxDB query?

I think Merge & Join doesn’t help in achieving the desired result as above.

Thanks in anticipation.

Best,
Bala


#4

Hi Bala,

Thank you for explaining your dilemma more thoroughly. I think in order to compute the daily energy consumption for the entire building, you would need to manipulate (aggregate) the data from InfluxDB using a Kapacitor TICKscript.

Here is a thread where another user did something similar: [Solved] Writing a TICKscript to aggregate multiple measurements into one

Hope that helps!


#5

Hello friend,

I tried those but I’m not quite sure how to work/use TICK stack on my current InfluxDB installation. Infact, I face difficulty in knowing which version of InfluxDB that my setup is running on.

However, I came across “subQueries” supported by InfluxDB. I understand it could possibly help in solving similar problems.

I’m not sure to append related issue here or in anther thread.

Actually, the entire workflow was developed by my previous colleague including InfluxDB (to store time-series data from multiple sensors) + Grafana (to query & visualize data on the front-end).

However, I found that my Grafana version is v4.5.2.

Now, I would tried using InfluxDB subqueries in Grafana as follows:

Take note that first query which is an time-integral of time-series data works perfectly (as ticked). However, the second subquery (as crossed) in Grafana FAILS with “error parsing query: GROUP BY requires at least one aggregate function” as shown in the snapshot.

I’m not sure whether it fails coz of version mismatch or something else.

Your support would be appreciated.

Bala


#6

Hi Bala,

Here are a couple things that could be going wrong:

You need to put the GROUP BY into the inner query and the wildcard is likely going to cause an error at some point in the query, which is explained further in the following GitHub commit: https://github.com/influxdata/influxdb/commit/3d4d9062a0925314e4e9445de49ae1b132940d05

For more information on subqueries, please consult our documentation: https://docs.influxdata.com/influxdb/v1.2/query_language/data_exploration/

Additionally, I believe performing calculations along the lines of SELECT * / 2 is not currently possible for subqueries.

Hope this helps.