See www.zabbix.com for the official Zabbix site.

Docs/howto/Microsoft SQL Server monitoring with unixODBC and LLD

From Zabbix.org
Jump to: navigation, search

Template with Low Level Discovery rule for MS SQL Server


Zabbix has a great feature - Low Level Discovery (LLD) (https://www.zabbix.com/documentation/2.2/manual/discovery/low_level_discovery), which we can use for MS SQL Server monitoring.

For this, we should create next discovery rule:

Name: Database discovery
Type: Database monitor
Key: db.odbc.select[dbname,{$ODBC}]
User name: {$USER}
Password: {$PASSWORD}
SQL query: select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' FROM master..sysdatabases ORDER BY [Name] FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')

This rule will return the list of databases in JSON format:

{
    "data": [
        {
            "{#DBNAME}": "DB 1"
        },
        {
            "{#DBNAME}": "master"
        },
        {
            "{#DBNAME}": "model"
        },
        {
            "{#DBNAME}": "msdb"
        },
        {
            "{#DBNAME}": "ReportServer"
        },
        {
            "{#DBNAME}": "ReportServerTempDB"
        },
        {
            "{#DBNAME}": "tempdb"
        }
    ]
}

Here you can download MS SQL Server template with this LLD.

Note:

  1. Works with MS SQL Server 2012. Not tested with older SQL Server versions!
  2. Uses "Zabbix agent (active)" type of items!
  3. Starting from 3.0 version result for any SQL query automatically transformed into a JSON (i.e. it is possible to use a simple SQL query in the LLD). All you have to do is to use "db.odbc.discovery" key (instead of "db.odbc.select") and "SELECT name AS dbname FROM master..sysdatabases ORDER BY name" query in the LLD rule. More information: https://www.zabbix.com/documentation/3.0/manual/discovery/low_level_discovery#discovery_using_odbc_sql_queries

How to apply this template:


Check settings and credentials:
isql -v <instance_from_odbc.ini> <username> <password>
SQL> SELECT name FROM master..sysdatabases
 0 ⇒ ONLINE
 1 ⇒ RESTORING
 2 ⇒ RECOVERING
 3 ⇒ RECOVERY PENDING
 4 ⇒ SUSPECT
 5 ⇒ EMERGENCY
 6 ⇒ OFFLINE
 7 ⇒ Database Does Not Exist on Server
Also, check that value mapping "Windows service state" exists. Add, if it doesn't exist.
Name: Databases for discovery
Expression: ^(master|model|msdb|ReportServer|ReportServerTempDB|tempdb)$
Type: Result is FALSE
This expression we'll use for default database filtering. So Zabbix won't monitor "tempdb" database, etc.
{$USER} = MS SQL Server user name
{$PASSWORD} = Password for MS SQL Server user
{$ODBC} = sql1 #instance from odbc.ini
  • Create host "SQL Server 2" in Zabbix frontend and configure user macros:
{$USER} - MS SQL Server user name
{$PASSWORD} - Password for MS SQL Server user
{$ODBC} - sql2 #instance from odbc.ini
etc
  • Apply "Template App Microsoft SQL Server" on these hosts and check result.