PostgreSQL and variable problems (ALL Option)

Grafana PostgreSQL
, ,
1

Hi guys,
decided to create advanced filtering in the dashboard for printers by vendor and model.

  1. I have grafana integrated with zabbix (PostgreSQL DB).
  2. I define a variable “prtDeviceVendor” that contains the names of the printer vendors in string form: HP, Lexmark, Brother, etc. Data for the variable is taken from the Zabbix database.
  3. Then I define the second variable (prtDeviceModel) for a SQL query that finds a list of printer models by the given parameter:
SELECT distinct on (model) history_str.value as model FROM
hosts as id2
LEFT JOIN items ON id2.hostid = items.hostid
LEFT JOIN history_str ON history_str.itemid = items.itemid
RIGHT JOIN (
SELECT
distinct hosts.hostid, value as vendor
FROM
hosts
LEFT JOIN items ON hosts.hostid = items.hostid
LEFT JOIN history_str ON history_str.itemid = items.itemid
WHERE items.key_ like 'prtDeviceVendor' AND value like '$ {prtDeviceVendor: raw}'
) AS id1 ON id2.hostid = id1.hostid
WHERE items.key_ like 'prtDeviceModel' and history_str.value is not null

This request works great! And returns me a list of printer models for the selected vendor!
4. I am trying to add the All option to the prtDeviceVendor variable. I define it as “%” (Probably not an optimal solution, but I haven’t come up with a better solution yet).
And it works - I get a list of all printers for all models and all vendors. But the previously obtained Brothher, Lexmark and etc. stop working. I get this error:

Templating
Template variables could not be initialized: pq: syntax error at or near “Brother”
(prtDeviceVendor values are displayed insted of Brother): Brother,Lexmark,HP and etc.
I tried different Advanced variable format options but it not help.
1
2
It feels like grafana is doing data preprocessing. But I don’t understand how I can fix it!!

Help as much as you can!

  1. Where can I see the actual SQL query that grafana is doing?
  2. Maybe my solution is not optimal and i can filter data from zabbix by several parameters in another way?

I would be grateful for any advice!

2

Try to create view with this query

SELECT distinct on (model) history_str.value as model FROM
hosts as id2
LEFT JOIN items ON id2.hostid = items.hostid
LEFT JOIN history_str ON history_str.itemid = items.itemid
RIGHT JOIN (
SELECT
distinct hosts.hostid, value as vendor
FROM
hosts
LEFT JOIN items ON hosts.hostid = items.hostid
LEFT JOIN history_str ON history_str.itemid = items.itemid
WHERE items.key_ like 'prtDeviceVendor' 
) AS id1 ON id2.hostid = id1.hostid
WHERE items.key_ like 'prtDeviceModel' and history_str.value is not null

Then create second the variable using this query

select * from view_that_created where value IN '${prtDeviceVendor}'

Please make sure the fields have “value” field

3

A similar problem, but the error is slightly different, with quotes

3
31657×520 50.9 KB

The most interesting!

But I went further:
I created a dashboard and a table in it and made this query there! And … there was no error, the request is correct.

[DONE!] Amazingly, I found where the problem was - I renamed the variable prtDeviceVendor to prtDeviceVendorNew and the error was gone!
Apparently, grafan has a feature: when the name of a variable coincides with the name of variables in an SQL query something extra escaping…

mb need fix it? or not?

Important!
use “RAW” option…
${Variable} - doesn’t work with “ALL” option (extra quotes)
but ${Variable:raw} work - they r removed.

1 Like