Help with zabbix agent2 custom postgresql query

[ramakris]

I am trying to monitor and report any query in the database which is running for > 5s. So, I wrote this query and tried multiple variations of it, but with no luck.

select row_to_json(T) from ( select datname, usename, query_start, query from pg_stat_activity where state='active' AND (now() - pg_stat_activity.query_start) > interval '5 seconds' ) T;


But, when during the monitoring it does not report any output. In the agent2 logfile I see the following entries-

2022/09/01 22:49:50.693666 received passive check request: 'pgsql.custom.query["tcp://localhost:xxxxx","zbx_monitor","postgres","xxxxx", "pgsql.long_running_queries"]' from 'xx.xx.xx.xx' 2022/09/01 22:49:50.693779 [1] adding new request for key: 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' 2022/09/01 22:49:50.693791 [1] created direct exporter task for plugin 'Postgres' itemid:0 key 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' 2022/09/01 22:49:50.693830 executing direct exporter task for key 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' 2022/09/01 22:49:50.701371 executed direct exporter task for key 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' 2022/09/01 22:49:51.858983 received passive check request: 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' from 'xx.xx.xx.xx' 2022/09/01 22:49:51.859070 [1] adding new request for key: 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]' 2022/09/01 22:49:51.859082 [1] created direct exporter task for plugin 'Postgres' itemid:0 key 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]'

If you notice above I do not see response output for the queries. Would appreciate it if I can help or guidance here, I just started testing zabbix so have limited knowledge about it. For some reason, I am not getting output in text format, if modify the query to get a count of queries executing for >5s I get a count. But, I am unable to pull other information as above.


Thanks,
Rama

[pclark]

Did you ever figure this out? I am in the same situation...

[denmon]

key: 'pgsql.custom.query["tcp://localhost:xxxx","zbx_monitor","postgres","xxx","pg sql.long_running_queries"]'

Just guessing here, but your queryname looks to have both a space and a period in it. I would try renaming the custom query file (which should be in the directory specified by the Plugins.PostgreSQL.CustomQueriesPath directive) so it has no spaces or punctuation.