[ZBXNEXT-2321] ODBC LLD discovery Created: 2014 May 28 Updated: 2015 Jan 16 Resolved: 2014 Sep 09 |
|
Status: | Closed |
Project: | ZABBIX FEATURE REQUESTS |
Component/s: | Frontend (F), Proxy (P), Server (S) |
Affects Version/s: | 2.2.3 |
Fix Version/s: | 2.5.0 |
Type: | New Feature Request | Priority: | Major |
Reporter: | Vadim Nesterov | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 6 |
Labels: | lld, odbc | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
Centos 6.5, Oracle Instant Client, unixODBC |
Description |
Please add LLD Discovery Item to ODBC: db.odbc.discovery[name_of_select,DSN] Query in item must return table. The table must be converted to JSON array, each column name is a filed in JSON array. For example: Oracle get jobs list: SELECT job_name, owner FROM dba_scheduler_jobs WHERE state != 'DISABLED'; will return table: +-------------------------------+-------------------------------+ | JOB_NAME | OWNER | +-------------------------------+-------------------------------+ | PURGE_LOG | SYS | | ORA$AUTOTASK_CLEAN | SYS | | DRA_REEVALUATE_OPEN_FAILURES | SYS | | BSLN_MAINTAIN_STATS_JOB | SYS | | RSE$CLEAN_RECOVERABLE_SCRIPT | SYS | | SM$CLEAN_AUTO_SPLIT_MERGE | SYS | | RLM$EVTCLEANUP | EXFSYS | | RLM$SCHDNEGACTION | EXFSYS | | GATHERSTATS_9725482 | DOCSDBADM | | MGMT_STATS_CONFIG_JOB | ORACLE_OCM | | MGMT_CONFIG_JOB | ORACLE_OCM | | J_SEARCH_CONTRACT | EPZ_CONTRACT_SEARCH | | J_SEARCH_PLAN_GRAPH | EPZ_PLAN_GRAPH_SEARCH | +-------------------------------+-------------------------------+ this table must be conveted to {"data":[{"{#JOBNAME}":"SM$CLEAN_AUTO_SPLIT_MERGE","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"RSE$CLEAN_RECOVERABLE_SCRIPT","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"BSLN_MAINTAIN_STATS_JOB","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"DRA_REEVALUATE_OPEN_FAILURES","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"ORA$AUTOTASK_CLEAN","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"PURGE_LOG","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"MGMT_CONFIG_JOB","{#JOBOWNER}":"ORACLE_OCM"},{"{#JOBNAME}":"MGMT_STATS_CONFIG_JOB","{#JOBOWNER}":"ORACLE_OCM"},{"{#JOBNAME}":"RLM$SCHDNEGACTION","{#JOBOWNER}":"EXFSYS"},{"{#JOBNAME}":"RLM$EVTCLEANUP","{#JOBOWNER}":"EXFSYS"},{"{#JOBNAME}":"GATHERSTATS_9725482","{#JOBOWNER}":"DOCSDBADM"},{"{#JOBNAME}":"JOB_INS_SEARCH","{#JOBOWNER}":"ZAKUPKI_DEV"},{"{#JOBNAME}":"JOB_PGP_SEARCH","{#JOBOWNER}":"ZAKUPKI_DEV"},{"{#JOBNAME}":"JOB_UNSCHED_INS_SEARCH","{#JOBOWNER}":"ZAKUPKI_DEV"},{"{#JOBNAME}":"JOB_PLAN_GRAPH_EPZ_UPDATE",{"{#JOBNAME}":"J_SEARCH_CONTRACT","{#JOBOWNER}":"EPZ_CONTRACT_SEARCH"},{"{#JOBNAME}":"J_SEARCH_PLAN_GRAPH","{#JOBOWNER}":"EPZ_PLAN_GRAPH_SEARCH"}]} |
Comments |
Comment by Vadim Nesterov [ 2014 May 28 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Sorry, field names in JSON must be: {#JOB_NAME} {#OWNER} | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Vadim Nesterov [ 2014 Jun 30 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Oleksiy, will this feature implemented in 2.2.3(4)? | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Juris Miščenko (Inactive) [ 2014 Sep 09 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Feature implemented at svn://svn.zabbix.com/branches/dev/ZBXNEXT-2321 | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Vadim Nesterov [ 2014 Sep 19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Juris, in which version this feature will be realesed? Any docs? | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Juris Miščenko (Inactive) [ 2014 Sep 19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Vadim, it is currently in the testing phase and will be merged into trunk as soon as it's gone through testing and reviews. It will be merged in trunk, and stabily it will become available in 3.0. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 10 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(1) Please review stylistic and warning fixes in r50528. Warnings complained about unused variables, other changes try to make new code more consistent with the rest. jurism Looks fine. RESOLVED. asaveljevs CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 10 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(2) There should be an error message in case column name has a character that is unsuitable for a macro name. Currently, there is no message. jurism Error message added. RESOLVED. asaveljevs CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 10 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(3) We currently have the following code: while (NULL != (row = odbc_DBfetch(&dbh))) { zbx_json_addobject(&json, NULL); for (i = 0; i < dbh.col_num; i++) { zbx_snprintf(colname, MAX_STRING_LEN, "{#%s}", column_names[i]); zbx_json_addstring(&json, colname, row[i], ZBX_JSON_TYPE_STRING); zbx_free(column_names[i]); } zbx_json_close(&json); } Something tells me "column_names[i]" should not be freed. jurism Column names are now freed when they're not necessary anymore, as opposed to after the first usage. RESOLVED. asaveljevs CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 10 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(4) It would be nice to have a specification for this development. For instance, it should be specified how column names from the following queries are converted to macro names (if at all): select field as f ... select count(*) ... select t1.field, t2.field ... select field, NULL ... asaveljevs Specification is in progress. What follows is a table that describes how columns in these queries are handled by SQLColAttribute() with SQL_DESC_LABEL:
asaveljevs Specification ready for review at https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-2321 . RESOLVED. asaveljevs Question regarding JSON null in filters moved to | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 25 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(5)
jurism wiper CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Nov 25 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
This comment describes the way column name is obtained from the SQL query in get_result_table_column_names() function. Internally, it uses SQLColAttribute() function. According to http://msdn.microsoft.com/en-us/library/ms713558(v=vs.85).aspx ODBC documentation, there are three relevant parameters that can be passed to obtain column name: SQL_DESC_BASE_COLUMN_NAME, SQL_DESC_LABEL, SQL_DESC_NAME. The first option, SQL_DESC_BASE_COLUMN_NAME, disregards aliases, so "select field as f ..." gives "field" and "select count(*) as c ..." gives an empty string. This option is thus unusable for our purposes. The second two options, SQL_DESC_LABEL and SQL_DESC_NAME, have been tested on MySQL, PostgreSQL, and Oracle, and the difference between the two has not been observed. Among these, SQL_DESC_LABEL seems better, because it comes from ODBC 2.0, while SQL_DESC_NAME comes from ODBC 3.0. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2014 Dec 17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(6) Added new translation strings:
wiper CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Andris Zeila [ 2015 Jan 07 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(7) To avoid any possible issues with utf-8 names we should cast characters to unsigned char when passing them to isalpha/toupper functions. asaveljevs RESOLVED in r51441. wiper CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Andris Zeila [ 2015 Jan 08 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Server side successfully tested | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Ivo Kurzemnieks [ 2015 Jan 12 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(8) [F]
asaveljevs Regarding tests, item key for regular database items and for item prototypes is still prefilled with "db.odbc.select[]". It is only prefilled with "db.odbc.discovery[]" for discovery rules. Therefore, the test still seems to be valid. asaveljevs Other suggestions RESOLVED in r51537. iivs Thanks! I refactored the code a bit. Please see r51541 asaveljevs Looks good. CLOSED. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Ivo Kurzemnieks [ 2015 Jan 13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(9) [F] LLD rule form choosing "Database monitor" it selects default example "db.odbc.discovery[<unique short description>,<dsn>]", this also should be checked in CItemGeneral.php: 283 asaveljevs RESOLVED in r51548. Undefined index: flags [items.php:505 → CFrontendApiWrapper->create() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → call_user_func_array() → CItem->create() → CItemGeneral->checkInput() in C:\Development\ZBXNEXT-2321-test\frontends\php\include\classes\api\services\CItemGeneral.php:288]
REOPENED. asaveljevs RESOLVED in r51551. iivs CLOSED. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Ivo Kurzemnieks [ 2015 Jan 13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Frontend TESTED. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2015 Jan 14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
Available in pre-2.5.0 (trunk) r51577. | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2015 Jan 14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(10) Documented since which versions "system.cpu.discovery" and SNMP discovery are supported:
wiper CLOSED | ||||||||||||||||||||||||||||||||||||||||||||||||
Comment by Aleksandrs Saveljevs [ 2015 Jan 15 ] | ||||||||||||||||||||||||||||||||||||||||||||||||
(11) Documented ODBC LLD functionality at the following locations:
For documentation purposes, a practical example from ZBXNEXT-2548 was chosen. wiper I'd suggest creating separate section for ODBC discovery in what's new, but otherwise looks great. |