MSSQL monitoring problem

[Spectator]

I'm stuck with Microsoft SQL monitoring

Based on this I tried:

https://www.zabbix.com/integrations/mssql

I ran these on the Zabbix server:

root@zabbixsrv:/etc# curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

root@zabbixsrv:/etc# yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
root@zabbixsrv:/etc# ACCEPT_EULA=Y yum install -y msodbcsql17

root@zabbixsrv:~# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
root@zabbixsrv:~#

root@zabbixsrv:/etc# odbcinst -q -d -n
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[Oracle 19 ODBC driver]
[ODBC Driver 17 for SQL Server]
root@zabbixsrv:/etc#

root@zabbixsrv:/etc# cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.2
UsageCount=1

In the /etc/odbc.ini:

[MSSQLSRV04]
Driver = ODBC Driver 17 for SQL Server
Server = 10.10.10.11\\\MSSQLSRV04,1433


root@zabbixsrv:/etc#

root@zabbixsrv:/etc# isql -v MSSQLSRV04 'zbx_monitor' 'zbx_monitor'
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
root@zabbixsrv:/etc#

Up to this point, I think everything is fine.
On the Zabbix frontend, I added a new host with the "Template DB MSSQL by ODBC" template.
The IP address of the interface is the IP address of the MSSQL server.
I set these two macros:
{$ MSSQL.USER}
{$ MSSQL.PASSWORD}
But unfortunately the measurements don't work, only this one MSSQL measurement works:
MSSQL: Service's TCP port state: Up (1)

I even tried to add this macro, but no change:
{$ MSSQL.DSN} 10.10.10.11\\\MSSQLSRV04,1433


Do you have any idea what could be wrong?

[cyber]

And now the most important part... Do you have any error messages somewhere...?

[Spectator]

Unfortunately, we don't see anything in either the zabbix_server.log or the MSSQL server log.

To see only the command line "isql -v MSSQLSRV04 'zbx_monitor' 'zbx_monitor'" attempt in the MSSQL server log:

Date 2021. 11. 23. 13:46:19
Log SQL Server (Current - 2021. 11. 23. 11:35:00)
Source Logon
Message
Login succeeded for user 'zbx_monitor'. Connection made using SQL Server authentication. [CLIENT: 10.10.10.1]


I also tried to specify these two macros also (separately and together):

{$MSSQL.DSN} 10.10.10.11\\\MSSQLSRV04,1433
{$MSSQL.INSTANCE} MSSQLSERVER

But unfortunately no change, see this in Zabbix 5.0 LTS:




Do you have any idea what could be wrong?

[vicbc]

Hey guys.

Spectator, don't know if that will help, but try to add to your odbcinst.ini file the lines:

Trace=Yes
TraceFile=/temp/odbctrace.log (or whatever other directory you want to direct it).

No need for reloads (ODBC magic).
After a while it will start to write log of ODBC connection to this file.
Maybe that can help you somehow.

Cheers

[Spectator]

Dear vibc,

Thank you for your advice. I tried in several ways, but unfortunately I was not successful.

I added the following lines to the Microsoft SQL section of /etc/odbcinst.ini:
###################
[ODBC Driver 17 for SQL Server]
Description = Microsoft ODBC Driver 17 for SQL Server
Driver = / opt / microsoft / msodbcsql17 / lib64 / libmsodbcsql-17.8.so.1.2
UsageCount = 1
# Trace = Yes
Trace = 1
TraceFile = / tmp / odbctrace.log
TraceOptions = 3
###################
(I tried Trace = yes and Trace = 1 also)

Then I tried to add these lines to /etc/odbc.ini:
###################
[ODBC]
Trace = 1
TraceFile = / tmp / odbctrace.log
TraceOptions = 3
###################

The result was always the same:
The /tmp/odbctrace.log file was not created.
If I manually created an empty /tmp/odbctrace.log file and gave it permission 777, no entries were written to it...

I welcome all ideas and suggestions

[splitek]

If you want you can try community templates that use agent not the ODBC.

https://github.com/zabbix/community-templates/tree/main/Databases/Microsoft_SQL_Server

[Spectator]

Thank you. I can monitor the SQL with Zabbix agent but I want to use the ODBC.

[jasdeep!8]

Hi, I used the templates that were provided on this link but I am getting an error for unsupported item key. Can anyone please help me with that?

[vicbc]

Hey there Spectator .

Can you send your ODBC files here?
It's really an odd result.

I figure that those "spaces" in the locations are just some sort of bug when pasting, right?

[Spectator]

Dear vicbc and others,

I installed the Zabbix 6 alpha7 appliance on a new virtual machine and also tried to monitor MSSQL via ODBC (The MSSQL server is the same one I tried before under Zabbix 5.0).
Unfortunately unsuccessfully (the error is in me, I am the common denominator: D).

The spaces in my post above were inserted by the forum, in reality they are not.

This is what /etc/odbc.ini looks like now:
###########
[MSSQLSRV04]
Driver = ODBC Driver 17 for SQL Server
Server = 10.10.10.11\\\MSSQLSRV04,1433

[ODBC]
Trace=1
TraceFile=/tmp/odbctrace.log
TraceOptions=3
###########

The odbcinst.ini:
###########
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[FreeTDS]
Description=Free Sybase & MS SQL Driver
Driver=/usr/lib/libtdsodbc.so
Setup=/usr/lib/libtdsS.so
Driver64=/usr/lib64/libtdsodbc.so
Setup64=/usr/lib64/libtdsS.so
Port=1433

[MariaDB]
Description=ODBC for MariaDB
Driver=/usr/lib/libmaodbc.so
Driver64=/usr/lib64/libmaodbc.so
FileUsage=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.2
UsageCount=1
###########

In Zabbix the host:
Host: MSSQLSRV04
Agent: 10.10.10.11
Template: MSSQL by ODBC
Macros:
{$MSSQL.USER} zbx_monitor
{$MSSQL.PASSWORD} zbx_monitor
{$MSSQL.DSN} - I tried this with multiple values, but it didn't work at all.
With this macro value {$MSSQL.DSN}, I saw the following error in /var/log/zabbix/zabbix_server.log:

10.10.10.11\\\MSSQLSRV04,1433

1148:20211211:121200.342 In substitute_key_macros_impl() data:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
1148:20211211:121200.342 End of substitute_key_macros_impl():SUCCEED data:'db.odbc.get[get_status_variables,"10.10.10.11\\\MSSQLSRV04,143 3"]'
1148:20211211:121200.343 In get_value() key:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
1148:20211211:121200.343 In get_value_db() key_orig:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]' query:'SELECT object_name,counter_name,instance_name,cntr_value
1148:20211211:121200.343 In zbx_odbc_connect() dsn:'10.10.10.11\\\MSSQLSRV04,1433' user:'zbx_monitor'
1148:20211211:121200.343 End of zbx_odbc_connect()
1148:20211211:121200.343 Item [MSSQLSRV04:db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]] error: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
1126:20211211:121200.572 error reason for "MSSQLSRV04:db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]" changed: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
1126:20211211:121200.573 query [txnlev:1] [update item_rtdata set error='Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]' where itemid=39836;

I tried these macro values also. The error messages are very similar but different:

ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER
ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER,1433
ODBC Driver 17 for SQL Server,\\\MSSQLSERVER,1433
ODBC Driver 17 for SQL Server,MSSQLSRV04\\\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11\\\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11,1433
ODBC Driver 17 for SQL Server,MGZABBIX,1433
'ODBC Driver 17 for SQL Server',10.10.10.11\\\MSSQLSRV04,1433
"ODBC Driver 17 for SQL Server",10.10.10.11\\\MSSQLSRV04,1433

1146:20211211:103600.387 In substitute_key_macros_impl() data:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
1146:20211211:103600.387 End substitute_simple_macros_impl() data:'ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER'
1146:20211211:103600.387 End of substitute_key_macros_impl():SUCCEED data:'db.odbc.get[get_status_variables,"ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER"]'
1146:20211211:103600.400 In get_value() key:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
1146:20211211:103600.400 In get_value_db() key_orig:'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]' query:'SELECT object_name,counter_name,instance_name,cntr_value
1146:20211211:103600.405 In zbx_odbc_connect() dsn:'ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER' user:'zbx_monitor'
1146:20211211:103600.408 End of zbx_odbc_connect()
1146:20211211:103600.409 Item [MSSQLSRV04:db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]] error: Cannot connect to ODBC DSN: [SQL_ERROR]:[HY090][0][[unixODBC][Driver Manager]Invalid string or buffer length]


From command line the isql is working good, so I think the "zbx_monitor" user rights are OK in the SQL:

# isql -v MSSQLSRV04 'zbx_monitor' 'zbx_monitor'
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%SQL Statistics%';
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| object_name | counter_name | instance_name | cntr_value | cntr_type |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| SQLServer:SQL Statistics | Batch Requests/sec | | 171677 | 272696576 |
| SQLServer:SQL Statistics | Forced Parameterizations/sec | | 0 | 272696576 |
| SQLServer:SQL Statistics | Auto-Param Attempts/sec | | 1465 | 272696576 |
| SQLServer:SQL Statistics | Failed Auto-Params/sec | | 1369 | 272696576 |
| SQLServer:SQL Statistics | Safe Auto-Params/sec | | 24 | 272696576 |
| SQLServer:SQL Statistics | Unsafe Auto-Params/sec | | 72 | 272696576 |
| SQLServer:SQL Statistics | SQL Compilations/sec | | 61473 | 272696576 |
| SQLServer:SQL Statistics | SQL Re-Compilations/sec | | 15097 | 272696576 |
| SQLServer:SQL Statistics | SQL Attention rate | | 358 | 272696576 |
| SQLServer:SQL Statistics | Guided plan executions/sec | | 0 | 272696576 |
| SQLServer:SQL Statistics | Misguided plan executions/sec | | 0 | 272696576 |
+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
SQLRowCount returns 0
11 rows fetched
SQL> SELECT name, database_id, create_date FROM sys.databases;
+---------------------------------------------------------------------------------------------------------------------------------+------------+------------------------+
| name | database_id| create_date |
+---------------------------------------------------------------------------------------------------------------------------------+------------+------------------------+
| master | 1 | 2003-04-08 09:13:36.390|
| tempdb | 2 | 2021-11-23 11:34:41.760|
| model | 3 | 2003-04-08 09:13:36.390|
| msdb | 4 | 2016-04-30 00:46:38.773|
| TESZT_DB | 5 | 2021-11-16 09:58:24.823|
| TDB1 | 6 | 2021-12-08 14:25:47.980|
+---------------------------------------------------------------------------------------------------------------------------------+------------+------------------------+
SQLRowCount returns 0
6 rows fetched
SQL>


Can anyone help me?
Maybe I didn't find the correct syntax for {$MSSQL.DSN}?
I think this is very, very underdocumented in the Zabbix documentation:
https://www.zabbix.com/documentation...ox/odbc_checks

What information do I need to provide in {$MSSQL.DSN}? And in what order and with what syntax?
Maybe you see something wrong elsewhere (too)?

[vicbc]

Hey Spectator how you doing?

So, about this:

I tried these macro values also. The error messages are very similar but different:

ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER
ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSERVER,1433
ODBC Driver 17 for SQL Server,\\\MSSQLSERVER,1433
ODBC Driver 17 for SQL Server,MSSQLSRV04\\\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,MSSQLSRV04\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11\\\MSSQLSRV04,1433
ODBC Driver 17 for SQL Server,10.10.10.11,1433
ODBC Driver 17 for SQL Server,MGZABBIX,1433
'ODBC Driver 17 for SQL Server',10.10.10.11\\\MSSQLSRV04,1433
"ODBC Driver 17 for SQL Server",10.10.10.11\\\MSSQLSRV04,1433

The DSN in your case is simply MSSQLSRV04, as you specified here:

This is what /etc/odbc.ini looks like now:
###########
[MSSQLSRV04]
Driver = ODBC Driver 17 for SQL Server
Server = 10.10.10.11\\\MSSQLSRV04,1433

The fact that you are able to access the Database with the isql command indicates that the configurations are ok, and that the server has access to it.

In the odbcinst.ini you are defning the drivers that will be used.
In the odbc.ini you are defining the DSN (Data Source Name).

In the quote above, you chose the name [MSSQLSRV04] for your DSN. All the informations inside it will be related to it, as in "Using Driver ODBC Driver 17 for SQL Server to access server 10.10.10.11\\\MSSQLSRV04,1433.

Try this out, and let us know

[jhboricua]

Spectator I just finished figuring out how to make this work in my environment, so let me give you a few pointers.

On the host agent configuration of the server hosting the SQL instance, did you override the $MSSQL.DSN macro to match the DSN name you defined in the odbc.ini file?

Did you define the {$MSSQL.USER} and {$MSSQL.PASSWORD} macros on either the main MSSQL template or the host agent you want to monitor?

In the zabbix server odbc.ini file, the server line should only have the actual hostname of the server that SQL is runing on, be it IP or FQDN. No need to specify port if its the mssql default.

And finally, is the SQL instance you are connecting to a NAMED SQL instance or a default SQL instance? It is unclear from what you posted so far which one is it. If it is a default instance, then with the steps above you should be good to go. If it is a named instanced called MSSQLSRV04, then override the {$MSSQL.INSTANCE} macro on the host agent to match that.

I have to say, the Zabbix documentation on how to setup this integration is somewhat lacking.

[Spectator]

Dear vicbc and jhboricua,

I have been very busy lately. I recently tried vicbc's suggestion and everything works perfectly!
My MSSQL instance is the default instance.

In summary, I had to configure the following for MSSQL ODBC monitoring:

The /etc/odbc.ini looks like now:

[MSSQLSRV04]
Driver = ODBC Driver 17 for SQL Server
Server = 10.10.10.11\\\MSSQLSRV04,1433

The odbcinst.ini:
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.2
UsageCount=1

In Zabbix frontend:
Configuration, Hosts, Create host.
Host name: MSSQLSRV04
Interfaces: Agent, 10.10.10.11 (IP of the MSSQL server)
Templates: "Template DB MSSQL by ODBC"
Macros:
{$MSSQL.DSN} MSSQLSRV04 (only this, not needed the server IP address, port, etc.)
{$MSSQL.USER} zbx_monitor
{$MSSQL.PASSWORD} zbx_monitor


Thanks guys for your help!

[vicbc]

Glad I could help man.
Cheers, and a happy new year

[Bairagi]

I have the simillar issue
But my scenirio is slight different, In my sql server there two sql instance is running production and support. custion port .example (server ip-10.10.10.1 , prod sql instance ip=10.10.10.3, support sql instance ip=10.10.10.3) please help me on this.