Discovery Database monitor

[hakna]

Hello guys,

I have the following problem,

I try to discover the database with the Template Template Microsoft SQL Server with the discovery Database discovery and the key "db.odbc.discovery [dbname, {$ ODBC}]" that contains the query select:

' {"data": [', (SELECT STUFF ((SELECT', '+' {"{#DBNAME}": "'+ [Name] +'"} 'FROM master..sysdatabases ORDER BY [Name] FOR XML PATH ('')), 1, 1, '')), ']}' FOR XML PATH ('').

Odbc.ini configuration:

[ss] Driver = FreeTDS
Server = 10.10.10.10
Port = 59 TDS_
Version = 8.0
#ClientCharset = UTF-8
User = Zabbix
Password = Zabbix
Language = us_english

Connect to the database OK Result of query in database:
{"data":[{"{#DBNAME}":"CAREMAN"},{"{#DBNAME}":"master"}, {"{ #DBNAME}":"model"},{"{#DBNAME}":"msdb"},{"{#DBNAME }":"psmanager"},{"{#DBNAME}":"tempdb"}]}

Result in zabbix:
Cannot convert column #1 name to macro.

Zabbix Versión : 3.2.4
SO: CentOS Linux release 7.3.1611

I do not have access to the agent's log to be instances of a database. Thank you very much for the help you can offer and sorry for the writing. Regards.

[hakna]

Hello,
I still have the same problem, any solution? Thank you

[aigars.kadikis]

Hello,
What Discovery Rule "Type" you have selected to execute db.odbc.discovery [dbname, {$ ODBC}] ?

[hakna]

Hello,the type of discovery is "database monitor".

Very Thanks

[Semiadmin]

Hi, hakha. If you use db.odbc.discovery, you don't need to artificially create json from result of select. Just name the columns as you want, because their names will be macros.

[hakna]

Hello, very thanks for reply,

ok I understand,How should it be?

I have to modify, select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' FROM master..sysdatabases ORDER BY [Name] FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')


How to avoid repeating names?


Thanks.

[hakna]

Thanks for the help, I will perform more tests. I will keep you informed.

[hakna]

This is the last change I make,

db.odbc.discovery[dbname,{$ODBC}] for db.odbc.discovery[name,{$ODBC}]

[Semiadmin]

https://www.zabbix.com/documentation/3.4/manual/discovery/low_level_discovery/sql_queries

[hakna]

Thanks,
I have finished many times in that link. What interests me is "this If it is not obvious how a column name would be transformed into a macro name, it is suggested to use column aliases like “COUNT(h2.host) AS count” in the example above"., right? Regards.

[Semiadmin]

Yes, and you'll get macro {#COUNT}

[hakna]

Hello, I was testing the last week Reasoning about your last answer, should I put {#COUNT} even without wanting to do a recount? for now I just want to discover the databases and it does not work.


EDIT 1

Do you want to tell me that I do not have to form the json in the sql output, but that the output will be standard and zabbix when I receive the creation information?

EDIT 2

I answer myself: By the internal workings of "db.odbc.discovery []" item, the result of this query gets automatically transformed into the following JSON

But I have not discovered any DDBB yet

[hakna]

hi,


This select NAME AS DBNAME FROM master..sysdatabases; it worked for me

Recover databases well

Thank you very much