Custom variables containing sql where clause

Grafana Dashboards
,
1

I looking for a way to define sql filter values as a dashboard variable.
The variables with the filter statement look like this

All: LIKE ‘%’
Extern: IN(‘1111’,‘2222’)
Intern: NOT IN(‘1111’,‘2222’)

Depending on the selected filter the where clause in the sql statement should look like this:
For selected value extern: SELECT .... WHERE (id= 'test' AND type NOT IN('1111','2222') and $__timeFilter(timestamp))

or for selected value intern: SELECT .... WHERE (id= 'test' AND type IN('1111','2222') and $__timeFilter(timestamp))

Unfortunatly custom variables with key value pairs can not be used because of the comma separated sql statement and if you use single quotes in a variable it will be “single quoted” again in the sql statement.

Somebody an idea how to solve this?

2

You can use it, just escape that comma. IMHO: \,
Escape also other special characters, which may be misinterpreted by variable definition.
Use :raw variable value in SQL, otherwise Grafana may add own escaping/quoting there, which will break SQL syntax.

3

Thanks, It does work when i only define values like that

IN(‘1111’,‘2222’)
NOT IN(‘1111’,‘2222’)

When i define it as key :values i can’t read only the value in the sql statement.
I tried this in my sql statement ${my_variable:value:raw} but it always return the key and the value. Is this the correct syntax?

4

But you don’t need to specify value, just use ${variable:raw} in the SQL

5

When i define ${variable:raw} then i have the value “key:value” in the sql statement instead of only “value”

6

Pls provide reproducible example first:

  • how is variable defined
  • how is variable used in the query
  • grafana version
  • …
7

the variable is define as custom variable like this:

Extern:IN(‘1111’\,‘2222’),Intern:NOT IN(‘1111’\,‘2222’)

And is used in the sql statement like that

SELECT .... WHERE (id= 'test' AND type ${my_variable:raw} and $__timeFilter(timestamp))

grafana version is 10.0.2

8

It doesn’t work for me:

image
image609×1031 47.3 KB

9

sorry, the \was not properly shown:
Extern:IN(‘1111’\,‘2222’),Intern:NOT IN(‘1111’\,‘2222’)

10

It doesn’t work for me:

image
image557×1017 44.8 KB

Let me guess, that you want:

Extern : IN('1111'\,'2222'), Intern : NOT IN('1111'\,'2222')

(Don’t delete any spaces, they have meaning there!)

1 Like
11

yes your are right. that was the problem :see_no_evil:. Thanks for the help

1 Like