dashboard + filter set to single values + influxdb breaks query

[steenzout]

Hello,

I'm using Grafana with InfluxDB backend.
I had a problem with a dashboard with a filter Server and a graph using a query that is defined like

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server = [[Server]]
group by time(1s), server order asc

the values for the filter is something like:

SELECT
  DISTINCT(server)
FROM
  "series_name"
WHERE
  time > now() - 15m

now, one of the values that this query returns is 192.168.1.2 (no single quotes surrounding this).
this is what will get executed:

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server = 192.168.1.2
group by time(1s), server order asc

which doesn't work (single quotes need to surround the [[Server]] value).
I changed the main query to

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server = '[[Server]]'
group by time(1s), server order asc

my problem is related to issue #570 I posted earlier but
slightly different.
If Grafana would expand all the values of the filter in (element1, ..., elementN) and
I manipulated the query to use a IN clause,
I have no way to surround each value with single quotes.
The where clause I would need to have generated would be this:

server IN ('192.168.1.2', '192.168.1.3')

Possible solution 1 (broken query):

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server IN [[Server]]      # => server IN (192.168.1.2, 192.168.1.3)
group by time(1s), server order asc

Possible solution 2 (broken query):

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server IN '[[Server]]'      # => server IN '(192.168.1.2, 192.168.1.3)'
group by time(1s), server order asc

am I missing another alternative?

with a fix for issue #570
I'd need the ability to tell Grafana to surround values with single quotes:

select
  server, count(server)
from
  "series_name"
where
  time > now() - 15m and
  server IN [[Server]]      # plus something to tell surround with single quotes => server IN ('192.168.1.2', '192.168.1.3')
group by time(1s), server order asc

I've make a hack and created yet another series just for filter values with single quotes but
it seems overkill since now I have to keep this new grafana.filters.servers up-to-date.

cheers!

[torkelo]

Yes, if filter is a string you need to wrap it in quotes. If it was done automatically it could cause issues for numeric properties, and where influxdb sql queries contain math, for example value > [[limit]]

As for how to handle multiple values in filters (like 'All'), that is trickier and not supported yet. The whole filter feature is something that needs to be rewritten to better support influxdb scenarios. Something that is likely to happen some time after summer.

[looztra]

we will also need to check that the future filter behaviour will fit the
opentsdb use case too

On 9 July 2014 17:33, Torkel Ödegaard notifications@github.com wrote:

Yes, if filter is a string you need to wrap it in quotes. If it was done
automatically it could cause issues for numeric properties, and where
influxdb sql queries contain math, for example value > [[limit]]

As for how to handle multiple values in filters (like 'All'), that is
trickier and not supported yet. The whole filter feature is something that
needs to be rewritten to better support influxdb scenarios. Something that
is likely to happen some time after summer.

—
Reply to this email directly or view it on GitHub
#571 (comment).