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!

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.