Create InfluxQL query with optional filter variables on Library Panel

Grafana InfluxDB
,
1

After much research I have managed to develop this trick to be able to invoke a library panel (for example, table type), where the query uses one or more variables as filters in an optional way.

In this way I can use the same library panel from several dashboards in which I can use a filter for a tag (or several) without having to change the query.

So for example if I have 5 tags that I can search, I can generate a query that can use 1 to 5 variables each filtering by a different tag, so that in a dashboard I can have only 2 of the 5 possible filters defined, or all, or none (and show all the data).

The query would be like this (modify to suit needs):

SELECT …
FROM “measurement”
WHERE $timeFilter
AND (‘${var1:raw}’ =~ /^${var1:raw}$/ OR “tag1” =~ /^${var1}$/)
AND (‘${var2:raw}’ =~ /^${var2:raw}$/ OR “tag2” =~ /^${var2}$/)
AND (‘${var3:raw}’ =~ /^${var3:raw}$/ OR “tag3” =~ /^${var3}$/)
AND (‘${var4:raw}’ =~ /^${var4:raw}$/ OR “tag4” =~ /^${var4}$/)
AND (‘${var5:raw}’ =~ /^${var5:raw}$/ OR “tag5” =~ /^${var5}$/)
GROUP BY …

The operation is as follows. If any of the variables (var1 for example) does not exist, grafana will NOT replace ‘${var1:raw}’ with its respective value, performing a comparison with a regex that contains THAT unmodified string.

Otherwise, it enters the OR condition in which the tag is already evaluated with the value or values ​​of said variable.

1 Like
2

Same approach on PostgreSQL query…

WHERE…
AND (
‘${var1:raw}’ ~ ‘${var1:raw}’ – If variable is not defined, this whill be TRUE
OR field = ANY(string_to_array(‘${var1:raw}’, ‘,’))
)

1 Like
3

After more tests and found mistakes on query I finally found that this query works when:

  1. There is no variables defined (one or more).
  2. There is one or more variables that have no values.
  3. There is one or more variables that have values (and may apply this for filter series)

SELECT …
FROM “measurement”
WHERE $timeFilter
AND (‘${var1:percentencode}’ =~ /${.}/ OR ‘${var1:percentencode}’ = ‘’ OR ‘${var1:percentencode}’ = ‘All’ OR “tag1” =~ /^${var1:regex}$/)
AND (‘${var2:percentencode}’ =~ /${.}/ OR ‘${var2:percentencode}’ = ‘’ OR ‘${var1:percentencode}’ = ‘All’ OR “tag1” =~ /^${var2:regex}$/)
AND (‘${var3:percentencode}’ =~ /${.}/ OR ‘${var3:percentencode}’ = ‘’ OR ‘${var3:percentencode}’ = ‘All’ OR “tag1” =~ /^${var3:regex}$/)
AND (‘${var4:percentencode}’ =~ /${.}/ OR ‘${var4:percentencode}’ = ‘’ OR ‘${var4:percentencode}’ = ‘All’ OR “tag1” =~ /^${var4:regex}$/)
AND (‘${var5:percentencode}’ =~ /${.*}/ OR ‘${var5:percentencode}’ = ‘’ OR ‘${var5:percentencode}’ = ‘All’ OR “tag1” =~ /^${var5:regex}$/)

4

Detected a mistake:

final filter should be:
AND (‘${var1:percentencode}’ =~ /${var1:percentencode}$/ OR ‘${var1:percentencode}’ = ‘’ OR ‘${var1:percentencode}’ = ‘All’ OR “tag1” =~ /(?i)^${var1:regex}$/)

AND (‘${var2:percentencode}’ =~ /${var2:percentencode}$/ OR ‘${var2:percentencode}’ = ‘’ OR ‘${var2:percentencode}’ = ‘All’ OR “tag2” =~ /(?i)^${var2:regex}$/)
…