ODBC Low Level Discovery (MSSQL)

[savianae]

Hello,

I have a zabbix installation with somes proxies 3.0.7.

I am getting an error "Value should be a JSON object." . And I am unable to identify my problem.

I have set the regex :

Databases for discovery	
1	»	^(master|model|msdb|ReportServer|ReportServerTempDB|tempdb)$	[Result is FALSE]

I have set the Value mapping

	MS SQL Server database state	0 ⇒ ONLINE
1 ⇒ RESTORING
2 ⇒ RECOVERING
3 ⇒ RECOVERY PENDING
4 ⇒ SUSPECT
5 ⇒ EMERGENCY
6 ⇒ OFFLINE
7 ⇒ Database Does Not Exist on Server

Macro is set

Macro		Effective value			
{$INSTANCE}    ⇒ INSTANCE1 		
{$ODBC}           ⇒ MyDbHost
{$PASSWORD} ⇒ myDbPassword

[savianae]

osql is working as the zabbix user :

[CODE]
osql -S VRINTDBSI05 -U 'domain\mon_user' -P 'xxxxx'


checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
trying /tmp/sql ... no
trying /tmp/sql ... no
trying /etc ... OK
checking odbc.ini files
reading /root/.odbc.ini
[MyDbHost] not found in /root/.odbc.ini
reading /etc/odbc.ini
[MyDbHost] found in /etc/odbc.ini
found this section:
[MyDbHost]
Description = MyDbHost
Driver = FreeTDS
ServerName = MyDbHost
Instance = INSTANCE5
TDS_Version = 7.4

[savianae]

looking for driver for DSN [MyDbHost] in /etc/odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [MyDbHost] in odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /etc/odbcinst.ini
found driver line: " Driver = /usr/local/lib/libtdsodbc.so"
found driver /usr/local/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/usr/local/lib/libtdsodbc.so is an executable file
Using ODBC-Combined strategy
DSN [MyDbHost] has servername "MyDbHost" (from /etc/odbc.ini)
cannot read "/root/.freetds.conf"
/usr/local/etc/freetds.conf is a readable file
looking for [MyDbHost] in /usr/local/etc/freetds.conf
found this section:
[MyDbHost]
host = myIpAddress
instance = INSTANCE5
tds version = 7.4


looking up hostname for ip address myIpAddress
/usr/local/bin/osql: line 344: host: command not found
osql: warning: no DNS hostname found for "myIpAddress"

Configuration looks OK. Connection details:

DSN: MyDbHost
odbc.ini: /etc/odbc.ini
Driver: /usr/local/lib/libtdsodbc.so
Server hostname: myIpAddress
Address: myIpAddress

Attempting connection as MyDomain\mon_svc ...
+ isql MyDbHost 'MyDomain\mon_svc' myPassword -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' FROM master..sysdatabases ORDER BY [Name] FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| XML_F52E2B61-18A1-11d1-B105-00805F49916B |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"data":[{"{#DBNAME}":"MyDB1"},{"{#DBNAME}":"master"},{"{#D BNAME}":"model"},{"{#DBNAME}":"msdb"},{"{#DBNAME}" :"MyDB2 "},{"{#DBNAME}":"tempdb"},{"{#DBNAME}":"wiki"}]} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
[/CODE]




It seems I am not fare away ... but still not working.

[savianae]

http://jsonlint.com/ claims my JSON is valid :

{
"data": [{
"{#DBNAME}": "MyDB1"
}, {
"{#DBNAME}": "master"
}, {
"{#D BNAME}": "model"
}, {
"{#DBNAME}": "msdb"
}, {
"{#DBNAME}": "MyDB2 "
}, {
"{#DBNAME}": "tempdb"
}, {
"{#DBNAME}": "wiki"
}]
}


did I miss something ?

[Pada]

The JSON does look OK to me too, however I'm not sure if its just a formatting issue when you pasted it into these forums, but the following doesn't look right to me:

1. space in "{#DBNAME}":

"{#D BNAME}": "model"

2. space after MyDB2:

"{#DBNAME}": "MyDB2 "

Also, can you run the 'isql' command from the zabbix user?

Only other suggestion that I can make is to create a new item (not as a discovery rule) for that host with:
Type = Database monitor
Type of information = Text
and then with the same SQL query that you have in your discovery

Then have a look at the output that it produces in that item when you go under Monitoring > Latest data.

One thing that it could also be is escaping of " or ' in Zabbix when it tries to perform the SQL query.

[tym]

Try this key, db.odbc.discovery[dbname,{$ODBC}]

[tym]

Try this key, db.odbc.discovery[dbname,{$ODBC}]
db.odbc.select work only for items

[tym]

Hola again sorry on my first post not full)
Use key: db.odbc.discovery[name,{$ODBC}]
SQL query: SELECT name FROM master..sysdatabases;

and replace {#DBNAME} on {#NAME} in all items.

but next time read manual on off site