zabbix_export: version: '6.0' date: '2021-11-21T21:42:13Z' groups: - uuid: 748ad4d098d447d492bb935c907f652f name: Templates/Databases templates: - uuid: 834c615063d74b61bc7572efb113454d template: 'Postgres ODBC' name: 'Postgres ODBC' description: | ## Overview I have been looking for templates to use with the new ODBC database monitoring item type with out success. I have created a rudimentary one for postgres. To use it follow these instructions for setting up ODBC on the server side. https://www.zabbix.com/documentation/4.0/manual/config/items/itemtypes/odbc\_checks We run multiple instances on each server so I create a host for each database and use the {HOST.NAME} macro in the item key. If you want to use a different macro like {$DATABASE.NAME} then do a find and replace before you import this. As well all items have the name zabbix set as the user and the password set as " my\_password". Again you may want to do a find and replace before importing. Then import this template and add it to the host you are using and then go through and double check the items. I know this functionality was in Zabbix 3 so this may work there but I created this in 4 so that is all I know it works with. I hope some one finds this useful. ## Author J Villain groups: - name: Templates/Databases items: - uuid: 9011ec68e7704a0eaff3fdedc856ac58 name: accessexclusivelock type: ODBC key: 'db.odbc.select[accessexclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''AccessExclusiveLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: c9e33569cfb1466792d6f48265e89a0f name: accesssharelock type: ODBC key: 'db.odbc.select[accesssharelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''AccessShareLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: d946945170834f63aeb7d16ed95495df name: activeconn type: ODBC key: 'db.odbc.select[activeconn,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(numbackends) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics triggers: - uuid: 4f2962075aff4fbca8eeaa33b674fe8e expression: 'last(/Postgres ODBC/db.odbc.select[activeconn,{HOST.NAME}])>=100' name: 'Too many active connections' priority: WARNING manual_close: 'YES' - uuid: 6c41d3c0f0bd434e85a85716842a9a73 name: buffers_alloc type: ODBC key: 'db.odbc.select[buffers_alloc,{HOST.NAME}]' delay: 60s trends: 732d params: 'select buffers_alloc from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: d5b7df44d22148da9c65fb67552f53ee name: buffers_backend type: ODBC key: 'db.odbc.select[buffers_backend,{HOST.NAME}]' delay: 60s trends: 732d params: 'select buffers_backend from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: 7003a5a483644c2f9d36e6baef535668 name: buffers_checkpoint type: ODBC key: 'db.odbc.select[buffers_checkpoint,{HOST.NAME}]' delay: 60s trends: 732d params: 'select buffers_checkpoint from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: 0261b3980da0480cb203f385c365fb64 name: buffers_clean type: ODBC key: 'db.odbc.select[buffers_clean,{HOST.NAME}]' delay: 60s trends: 732d params: 'select buffers_clean from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: 879a3b51004740e3a71fdd4eddeb560a name: checkpoints_req type: ODBC key: 'db.odbc.select[checkpoints_req,{HOST.NAME}]' delay: 60s trends: 732d params: 'select checkpoints_req from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: 4ddc501569974d269243dbc5917eaf3e name: checkpoints_timed type: ODBC key: 'db.odbc.select[checkpoints_timed,{HOST.NAME}]' delay: 60s trends: 732d params: 'select checkpoints_timed from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: c93465b159e94be3aa1557714957feb1 name: exclusivelock type: ODBC key: 'db.odbc.select[exclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''ExclusiveLock''' username: zabbix password: my_password tags: - tag: Application value: Locks triggers: - uuid: 50ffc916c0bb4119a1ef0e98e7700114 expression: 'last(/Postgres ODBC/db.odbc.select[exclusivelock,{HOST.NAME}])>2' name: 'To many exclusive locks' priority: WARNING manual_close: 'YES' - uuid: 7e5841d1bd2a4ec1906d3f02d136c77b name: maxwritten_clean type: ODBC key: 'db.odbc.select[maxwritten_clean,{HOST.NAME}]' delay: 60s trends: 732d params: 'select maxwritten_clean from pg_stat_bgwriter' username: zabbix password: my_password tags: - tag: Application value: 'BG writer' - uuid: 0c4a4f6b940c4a69a64f0ec3e2414ddc name: one type: ODBC key: 'db.odbc.select[one,{HOST.NAME}]' delay: 60s trends: 732d params: 'select 1 as "C1"' username: zabbix password: my_password tags: - tag: Application value: Statistics triggers: - uuid: 92855eaf900447c69c5c440a55a9c487 expression: 'last(/Postgres ODBC/db.odbc.select[one,{HOST.NAME}])<>1' name: 'Database problem' priority: AVERAGE description: 'This query does a select 1 as 1 if it doesn''t return 1 some thing is really messed up.' manual_close: 'YES' - uuid: d620f3c294714498a276af0bc7e0fe03 name: pmbcexclusivelock type: ODBC key: 'db.odbc.select[pmbcexclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) from public.vwlock where lock_duration_in_minutes > 5' username: zabbix password: my_password tags: - tag: Application value: Locks triggers: - uuid: 7ef07f013c824807ab29ca3c9a7efc31 expression: 'last(/Postgres ODBC/db.odbc.select[pmbcexclusivelock,{HOST.NAME}])>6' name: 'To many PMBC exclusive locks' priority: AVERAGE manual_close: 'YES' - uuid: d0b614349f0547c6b35906d094bf3c04 name: rowexclusivelock type: ODBC key: 'db.odbc.select[rowexclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''RowExclusiveLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: c2c2f4205bdc4cbb97cb194ffa631065 name: rowsharelock type: ODBC key: 'db.odbc.select[rowsharelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''RowShareLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: 9a7bf212af9548dcaac6cf68a1368820 name: sharerowexclusivelock type: ODBC key: 'db.odbc.select[sharerowexclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''ShareRowExclusiveLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: f60488ac70f74bd0913fc0ecd0ba80db name: shareupdateexclusivelock type: ODBC key: 'db.odbc.select[shareupdateexclusivelock,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT count(*) FROM pg_locks where mode=''ShareUpdateExclusiveLock''' username: zabbix password: my_password tags: - tag: Application value: Locks - uuid: 708adff720c84ec0b04428f96ba53dd0 name: tupdeleted type: ODBC key: 'db.odbc.select[tupdeleted,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(tup_deleted) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: bb9a92f55db74acea9eeff7a847729ec name: tupfetched type: ODBC key: 'db.odbc.select[tupfetched,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(tup_fetched) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: f98a07c97a164b30b8a54dfcb475ad05 name: tupinserted type: ODBC key: 'db.odbc.select[tupinserted,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(tup_inserted) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: a7abd38df4924facad8c6a19f6f1b9aa name: tupreturned type: ODBC key: 'db.odbc.select[tupreturned,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(tup_returned) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: 81b76b7431e64c2a958f8f071e26e1f2 name: tupupdated type: ODBC key: 'db.odbc.select[tupupdated,{HOST.NAME}]' delay: 60s trends: 732d params: 'select sum(tup_updated) from pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: 7271ca7f9c484f3aad56ede169a47ce2 name: xactcommit type: ODBC key: 'db.odbc.select[xactcommit,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT sum(xact_commit) FROM pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics - uuid: 5174bf05133a4a54ace3833eb23f6a1e name: xactrollback type: ODBC key: 'db.odbc.select[xactrollback,{HOST.NAME}]' delay: 60s trends: 732d params: 'SELECT sum(xact_rollback) FROM pg_stat_database' username: zabbix password: my_password tags: - tag: Application value: Statistics