[ZBXNEXT-2307] using the Database Monitor to collect more than one row and column Created: 2014 May 16 Updated: 2019 Sep 19 Resolved: 2019 Sep 19 |
|
Status: | Closed |
Project: | ZABBIX FEATURE REQUESTS |
Component/s: | Frontend (F), Templates (T) |
Affects Version/s: | None |
Fix Version/s: | None |
Type: | New Feature Request | Priority: | Minor |
Reporter: | Luiz Sales | Assignee: | Unassigned |
Resolution: | Duplicate | Votes: | 5 |
Labels: | database, lld, odbc | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Issue Links: |
|
Description |
The Database Monitor currently has a restriction to only grab the first row and first column of the select collected would be of great value if he sought all the information given in the select, so that we would not use scripts from outside the zabbix create LLDs, as is the case of the oracle tablespace |
Comments |
Comment by richlv [ 2014 May 16 ] |
can you please provide an example usecase how you would like to use such a functionality ? |
Comment by Luiz Sales [ 2014 May 16 ] |
db.odbc.select using the current form when I want to fetch all tablespaces of an Oracle instance , it brings me only the first column and the first row and in fact I wanted to get all lines : [ zabbix @ bngmon02 ~ ] $ isql -v BEP <user> <pass> + --------------------------------------- + | Connected ! | | | | Sql -statement | | Help [ tablename ] | | Quit | | | + --------------------------------------- + SQL > select tablespace_name , CONTENTS from dba_tablespaces + ------------------------------- + --------- + | Tablespace_name | CONTENTS | + ------------------------------- + --------- + | SYSTEM | PERMANENT | | PSAPUNDO | UNDO | | SYSAUX | PERMANENT | | PSAPTEMP | TEMPORARY | | PSAPSR3USR | PERMANENT | | USERS | PERMANENT | | PSAPSR3STATS | PERMANENT | | PSAPSR3701 | PERMANENT | | PSAPSR3 | PERMANENT | + ------------------------------- + --------- + SQLRowCount returns -1 9 rows fetched this way I can generate my historic tablespaces or what are the tables I have in a bank SQL > show tables ; + ----------------------------------------------------------------- + | Tables_in_zabbix | + ----------------------------------------------------------------- + | ACKnowledges | | Actions | | Alerts | | Application_template | | Applications | | Auditlog | | Auditlog_details | | Autoreg_host | | Conditions | | Config | | Dbversion | | Dchecks | | Dhosts | | Drules | | Dservices | | Escalations | | Events | | Expressions | | Functions | | Globalmacro | | Globalvars | | Graph_discovery | | Graph_theme | | Graphs | | Graphs_items | | Group_discovery | | Group_prototype | | Groups | | History | | History_log | | History_str | | History_str_sync | | History_sync | | History_text | | History_uint | | History_uint_sync | | Host_discovery | | Host_inventory | | Hostmacro | | Hosts | | Hosts_groups | | Hosts_templates | | Housekeeper | | Httpstep | | Httpstepitem | | Httptest | | Httptestitem | | Icon_map | | Icon_mapping | | Ids | | Images | | Interface | | Interface_discovery | | Item_discovery | | Items | | Items_applications | | Maintenances | | Maintenances_groups | | Maintenances_hosts | | Maintenances_windows | | Mappings | | Average | | Media_type | | Node_cksum | | Nodes | | Opcommand | | Opcommand_grp | | Opcommand_hst | | Opconditions | | Operations | | Opgroup | | Opmessage | | Opmessage_grp | | Opmessage_usr | | Optemplate | | Profiles | | Proxy_autoreg_host | | Proxy_dhistory | | Proxy_history | | Regexps | | Rights | | Screens | | Screens_items | | Scripts | | Service_alarms | | Services | | Services_links | | Services_times | | Sessions | | Slides | | Slideshows | | Sysmap_element_url | | Sysmap_url | | Sysmaps | | Sysmaps_elements | | Sysmaps_link_triggers | | Sysmaps_links | | Timeperiods | | Trends | | Trends_uint | | Trigger_depends | | Trigger_discovery | | Triggers | | User_history | | Users | | Users_groups | | Usrgrp | | Valuemaps | | Zbxe_preferences | | Zbxe_translation | + ----------------------------------------------------------------- + SQLRowCount returns 110 110 rows fetched and selects the best deal |
Comment by richlv [ 2014 May 16 ] |
right, but what do you want to do with the result ? just store it ? |
Comment by Luiz Sales [ 2014 May 17 ] |
I really do not have to store this information, and the use of select correct this if it was not for a reason, do not support the inclusion of extra parameters ... I can get the tablespaces correctly, was writing the wrong select, but when I need calculate the size of them, I need to pass a parameter and the db.odbc.select can so that the short description and the DSN. Sorry for the confusion |
Comment by v99glu [ 2014 May 31 ] |
>>richlv added a comment - 2014 May 16 18:41 One useful example for Oracle Database: select '{ "data":[' as s from dual union all select '{ "{#TSNUM}":"' || ts# || '", "{#TSNAME}":"' || name || '" },' as s from v$tablespace union all select '{ "{#TSNUM}":"X", "{#TSNAME}":"a DUMMY row to avoid comma after last brace" } ]}' as s from dual; but I can't because Zabbix takes only first row of result rowset. Another useful example for MySQL: So, concatenation of rows of result row set (moreover, concatenation of columns of row and then rows) on the side of Zabbix instead of selecting first column of first row would resolve this problem. |
Comment by Marc [ 2014 May 31 ] |
As for discovery you might be interested in |
Comment by Oleg Ivanivskyi [ 2014 May 31 ] |
v99glu, for MS SQL Server you could use next query for LLD: select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' FROM master..sysdatabases ORDER BY [Name] FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('') Perhaps you could use a similar workaround for Oracle... |
Comment by v99glu [ 2014 Jun 02 ] |
to Oleg Ivanivskyi: to Marc: |
Comment by richlv [ 2017 Nov 07 ] |
if this is implemented, it might work well with dependent items to split such values in individual items (if not possible already) |
Comment by Oleg Ivanivskyi [ 2018 Oct 08 ] |
Zabbix does support LLD using ODBC SQL queries. It looks like the initial issue is fixed (https://www.zabbix.com/documentation/current/manual/discovery/low_level_discovery/sql_queries). Zabbix has preprocessing and dependent items now. It would be great to be able to select more than one value/column with "db.odbc.select" and manage values with the new features. |
Comment by Oleg Ivanivskyi [ 2018 Oct 08 ] |
palivoda - please follow this ticket/git, started specification.
|
Comment by Vladislavs Sokurenko [ 2019 Sep 19 ] |
Thank you for your report, it will be fixed under newly reported issue |