How to use Influxdb subqueries in grafana

1

Hi,
I’m trying to use a subquery for influxdb source, and I get the error

{“error”:“error parsing query: found (, expected identifier at line 1, char 470”}

Any hints?

I’m using Grafana 4.1 and Influxdb 1.2.

2

Sounds exactly like this: https://github.com/grafana/grafana/issues/7692

We do not have support in the query editor yet but you can write them by switching to raw mode.

1 Like
3

Thanks for the link. That’s actually the error I get when I use it in raw mode…

4

Can you share the query?

5

It’s very similar to the query used in InfluxDB documentation as an example for subqueries (with just different names for my measurements)
SELECT SUM(“max”) FROM (SELECT MAX(“water_level”) FROM “h2o_feet” GROUP BY “location”)
or
SELECT SUM(“max_water”) FROM (SELECT MAX(“water_level”) AS “max_water” FROM “h2o_feet” GROUP BY “location”)

I started to think it’s not Grafana’s issues, as I tried the same in Chronograf and it doesn’t work either.

6

Oh

I have 2 measurements in influxDB. Below:

select * from check_load limit 9;
name: check_load
time hostname metric service value

1489464807000000000 host001 load1 LOAD AVR 0.68
1489464807000000000 host001 load5 LOAD AVR 0.51
1489464807000000000 host001 load15 LOAD AVR 0.4
1489464807000000000 host002 load1 LOAD AVR 0.8
1489464807000000000 host002 load5 LOAD AVR 0.6
1489464807000000000 host002 load15 LOAD AVR 0.4
1489464807000000000 host003 load1 LOAD AVR 0.6
1489464807000000000 host003 load5 LOAD AVR 0.5
1489464807000000000 host003 load15 LOAD AVR 0.4

//
select * from check_disk limit 3;
name: check_disk
time hostname metric service value

1489457127000000000 host001 used_space DISK 25
1489457127000000000 host002 used_space DISK 20
1489457127000000000 host003 used_space DISK 24

I want to select all of them to a table as:

hostname load1 load5 load15 used_space

host001 0.68 0.51 0.4 25
host002 0.8 0.6 0.4 20
host003 0.6 0.5 0.4 24

But I can’t. I can’t select with join 2 tables in influxDB. I just only select and result as:
SELECT mean(“value”) FROM “check_load” WHERE “hostname” =~ /^$hostname$/ AND “metric” = ‘load1’ AND $timeFilter GROUP BY "hostname"
SELECT mean(“value”) FROM “check_load” WHERE “hostname” =~ /^$hostname$/ AND “metric” = ‘load5’ AND $timeFilter GROUP BY "hostname"
SELECT mean(“value”) FROM “check_load” WHERE “hostname” =~ /^$hostname$/ AND “metric” = ‘load15’ AND $timeFilter GROUP BY "hostname"
SELECT mean(“value”) FROM “check_disk” WHERE “hostname” =~ /^$hostname$/ AND “metric” = ‘used_space’ AND $timeFilter GROUP BY “hostname”

hostname load1 load5 load15 used_space hostname load1 load5 load15 used_space

host001----0.68----0.51----0.4----25 -------- ----- ----- ------ --------
-------- ----- ----- ------ -------- host002----0.8----0.6----0.4----20

Please kindly help me and show for me option in tab Options.
Thank you.