Table row from multiple measurements

Grafana Table Panel
1

Hello,

I have this data in influxdb:

time RX TX iface host

1503039823875558069 123 123 eth0 s1
1503039874277408633 234 234 eth1 s1
1503039922672467471 345 345 eth2 s1
1503050594762970160 456 456 eth3 s1
1503039823875558069 123 123 eth0 s2
1503039874277408633 234 234 eth1 s2
1503039922672467471 345 345 eth2 s2
1503050594762970160 456 456 eth3 s2

Is it possible to create a table in Grafana with rows like this:
host RX_eth0 RX_eth1 RX_eth2 RX_eth3
s1 123 234 345 456
s2 123 234 345 456

Not sure how to combine queries to get something like this.

2

You can hide the time column like this:

image
image868×273 16.3 KB

And the other columns should be in the same order as they are in your query if you have chosen “Format As Table”.

3

The time is not the issue, but the joining of the 4 rows, I did not find a query in influx to support joins (from what I can see it only supports joins based on time).

So the idea was to use for 4 queries like:
query A: SELECT RX FROM data WHERE host = s1 AND iface = eth0
query B: SELECT RX FROM data WHERE host = s1 AND iface = eth1
query C: SELECT RX FROM data WHERE host = s1 AND iface = eth2
query D: SELECT RX FROM data WHERE host = s1 AND iface = eth3
and combine the query’s in a single table row

Or if there is a scenario that I can use JOIN for a single query or multiple queries on a single line I’m open to that as well.

4

Are all 4 queries against the same table? If they are then you should combine them into one query. If not then as you have found it is hard to join tables with InfluxDB (and to be fair to InfluxDB, the only sensible way to join is on time fields so that aggregations are possible).

5

Not sure what you mean by table, didn’t see the table concept in influx, they are rows of the same database.

Does grafana have any function to merge the results of the 4 queries ?

6

Sorry, meant measurement not table (sort of the same thing in my head).

Not sure I understand why you have 4 queries? JOINs or merges are for different time series/measurements. But this looks like 4 queries against the same time series/measurement?

Here is demo dashboard that uses regex to match on hostname and datacenter:

image
image824×191 14.6 KB

InfluxDB docs for merging series if this is your use case:

https://docs.influxdata.com/influxdb/v0.8/api/query_language/#merging-series

7

I have had the same problem and finally managed to get something to work. The difference with my requirement is that I had to do aggregations based on at tag. I hope it helps anyone else that comes across this requirement.

The key that I discovered is that the aggregation has to take place in your outer query. I was initially trying to do it in the inner query and I was getting the correct values but the timestamps wouldn’t align even though they were the same.

What you will need to do is something like the following…

SELECT agg(“RX_0”) as “RX_eth0”, agg(“RX_1”) as “RX_eth1”, agg(“RX_2”) as “RX_eth2”, agg(“RX_3”) as “RX_eth3” FROM
(SELECT RX as “RX_0” FROM measurement WHERE iface=‘eth0’),
(SELECT RX as “RX_1” FROM measurement WHERE iface=‘eth1’),
(SELECT RX as “RX_2” FROM measurement WHERE iface=‘eth2’),
(SELECT RX as “RX_3” FROM measurement WHERE iface=‘eth3’)
WHERE $timeFilter GROUP BY host fill(0)

where agg is some aggregation or selector etc… In your case you might just want to use the last selector if there is only one value or you could probably omit it entirely.