[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:
Duplicate

 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
>>can you please provide an example usecase how you would like to use such a functionality ?

One useful example for Oracle Database:
I wanna discover tablespaces of Oracle Database (to monitor freespace of them further).
I could use Database monitor item with query:

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.
Surely, I can create a function (which concatenates rows of query above) and use a query "select func_to_concat() from dual", but it is slightly complicated, isn't it?
Besides, oveflow can occurs.
Also I can use analytic function LISTAGG to concatenates rows and get single row result, but again, oveflow can occurs (ORA-01489).

Another useful example for MySQL:
I wanna discover databases of MySQL server (to monitor health of them further).
I could use Database monitor item with query to information_schema.schemata, but I can't because Zabbix takes only first row of result rowset.
Surely, I can use GROUP_CONCAT to concatenates rows and get single row result, but oveflow can occurs.

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 ZBXNEXT-2321

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:
Yes, I can use different workarounds to bypass the problem and I pointed to LISTAGG function as one of them, but the request has another idea.
The request states that expected behaviour of database monitor item (especially an item with "Type of information" set to "Text") is to concatenate rows of resultset. Such behaviour is expected by any database administrator who is familar with console utilities like sqlplus|mysql|sqlcmd|isql and use them to automate monitoring of a database.

to Marc:
I suppose that feature requested in ZBXNEXT-2321 is too special case to be implemented. Described task is solved easily with database monitor item with query that returns JSON value itself.

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 ]

ZBXNEXT-4033 about the same.

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 ZBXNEXT-4033, closing as a duplicate.

Generated at Thu Mar 28 22:31:48 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.