Zabbix监控 Windows SQL Server

作者: ygqygq2 分类: 系统/运维 发布时间: 2018-02-26 15:48

Zabbix监控 Windows SQL Server

[TOC]

1. 模板来源

此模板来自如下模板的修改和翻译。因为原模板为葡萄牙语。
https://share.zabbix.com/databases/microsoft-sql-server/template-windows-sql-server

2. 模板使用

假如zabbix agent目录为D:\zabbix
确保zabbix agent配置文件D:\zabbix\etc\zabbix_agentd.conf
有此配置
Include=D:\zabbix\etc\zabbix_agentd.conf.d\

自定义key文件
D:\zabbix\etc\zabbix_agentd.conf.d\discovery.mssql.server.conf
内容:

# key of zabbix
UserParameter=discovery.mssql.databases,powershell.exe -noprofile -executionpolicy bypass -File D:\zabbix\scripts\discovery.mssql.server.ps1 JSONDB
UserParameter=discovery.mssql.jobs,powershell.exe -noprofile -executionpolicy bypass -File D:\zabbix\scripts\discovery.mssql.server.ps1 JSONJOB
UserParameter=discovery.mssql.data[*],powershell.exe -noprofile -executionpolicy bypass -File D:\zabbix\scripts\discovery.mssql.server.ps1 $1 "$2"

powershell脚本文件
D:\zabbix\scripts\discovery.mssql.server.ps1
内容:

# parameter
Param(
  [string]$select,
  [string]$2
)

# Login SQLSERVER
$username = "username"
$password   = "password"

# JSONDB
if ( $select -eq 'JSONDB' ) 
{
$database = sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;SELECT name FROM master..sysdatabases"
$idx = 1
write-host "{"
write-host " `"data`":[`n"
foreach ($db in $database)
{
    if ($idx -lt $database.Count)
    {
        $line= "{ `"{#MSSQLDBNAME}`" : `"" + $db + "`" },"
        write-host $line
    }
    elseif ($idx -ge $database.Count)
    {
    $line= "{ `"{#MSSQLDBNAME}`" : `"" + $db + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"
} 

# STATUS
if ( $select -eq 'STATUS' )
{
sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;SELECT coalesce(max(state),7) from sys.databases where name = '$2'"
}

# CONN
if ( $select -eq 'CONN' )
{
sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)
INSERT INTO @AllConnections EXEC sp_who2
SELECT count(*) FROM @AllConnections WHERE DBName = '$2'"
}

# JSONJOB
if ( $select -eq 'JSONJOB' )
{
$jobname = sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;SELECT [name] FROM msdb.dbo.sysjobs"
$idx = 1
write-host "{"
write-host " `"data`":[`n"
foreach ($job in $jobname)
{
    if ($idx -lt $jobname.Count)
    {
        $line= "{ `"{#MSSQLJOBNAME}`" : `"" + $job + "`" },"
        write-host $line
    }
    elseif ($idx -ge $jobname.Count)
    {
    $line= "{ `"{#MSSQLJOBNAME}`" : `"" + $job + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"
}

# JOBSTATUS
if ( $select -eq 'JOBSTATUS' )
{
sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;WITH last_hist_rec AS
(
SELECT ROW_NUMBER() OVER
(PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS [RowNum]
, job_id
, run_date AS [last_run_date]
, run_time AS [last_run_time]
, CASE run_status
WHEN 0 THEN '0'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
END AS [status]
FROM msdb.dbo.sysjobhistory
)
SELECT jobs.name AS [job_name]
, hist.status
FROM msdb.dbo.sysjobs jobs
LEFT JOIN last_hist_rec hist ON hist.job_id = jobs.job_id
AND hist.RowNum = 1
WHERE jobs.name = '$2'" | % {$_.substring($_.length-1) -replace ''} | ForEach-Object {$_ -Replace "N", "5"}
}

# VERSION
if ( $select -eq 'VERSION' )
{
sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;SELECT
   SERVERPROPERTY ( 'ProductVersion' ),
   SERVERPROPERTY ( 'Edition' ),
   SERVERPROPERTY ( 'ProductLevel' )"
}

注意
需要替换脚本中SQL Server的用户和密码;
用zabbix运行用户确认脚本运行正常(手动模拟zabbix运行);

模板xml文件(zabbix3.2版本)
Template Windows LLD MSSQL.xml
内容:



    3.2
    2018-02-11T06:11:01Z
    
        
            Templates
        
    
    
        
    
    
        
            {Template Windows LLD MSSQL:net.tcp.port[,{$MSSQLPORT}].last(0)}=0
            0
            
            MSSQL ({ITEM.LASTVALUE}) is not running on {HOST.NAME}
            0
            
            
            0
            2
            MSSQL Port is down
            0
            1
            
            
        
        
            {Template Windows LLD MSSQL:service.info[{$MSSQLAGENT}].count(#3,0,gt)}=3
            0
            
            SQL Server Agent {$MSSQLAGENT} ({ITEM.LASTVALUE}) is not running on {HOST.NAME}
            0
            
            
            0
            2
            Service: SQLSERVERAGENT

Name: SQL Server Agent ({$ MSSQLAGENT})

Description: Performs tasks, monitors SQL Server, triggers alerts, and allows the automation of some administrative tasks.
            0
            1
            
            
        
        
            {Template Windows LLD MSSQL:service.info[MsDtsServer100].count(#3,0,gt)}=3 and {Template Windows LLD MSSQL:service.info[MsDtsServer100].last()}<>255
            0
            
            SQL Server Integration Services 10.0 ({ITEM.LASTVALUE}) is not runnig on {HOST.NAME}
            0
            
            
            0
            2
            Service: SQLSERVERAGENT

Name: SQL Server Agent ({$ MSSQLAGENT})

Description: Performs tasks, monitors SQL Server, triggers alerts, and allows the automation of some administrative tasks.
            0
            1
            
            
        
        
            {Template Windows LLD MSSQL:service.info[{$MSSQLSERVER}].count(#3,0,gt)}=3
            0
            
            SQL Server {$MSSQLSERVER} ({ITEM.LASTVALUE}) is not runnig on {HOST.NAME}
            0
            
            
            0
            2
            Service: MSSQLSERVER

Name: SQL Server ({$ MSSQLSERVER})

Description: Offers storage, processing, and controlled access to data and fast transaction processing.
            0
            1
            
            
        
    
    
        
            MSSQL Memory Usage
            900
            200
            0.0000
            100.0000
            1
            1
            0
            1
            0
            0.0000
            0.0000
            0
            0
            0
            0
            
                
                    0
                    2
                    FC6EA3
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\Process(sqlservr)\Private Bytes]
                    
                
                
                    1
                    2
                    A54F10
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Memory Manager\SQL Cache Memory (KB)]
                    
                
                
                    2
                    2
                    2774A4
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Buffer cache hit ratio]
                    
                
                
                    3
                    2
                    6C59DC
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Memory Manager\Memory Grants Pending]
                    
                
                
                    4
                    2
                    AC8C14
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Memory Manager\Target Server Memory (KB)]
                    
                
            
        
        
            MSSQL Statistics
            900
            200
            0.0000
            100.0000
            1
            1
            0
            1
            0
            0.0000
            0.0000
            0
            0
            0
            0
            
                
                    0
                    0
                    6C59DC
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Checkpoint pages/sec]
                    
                
                
                    1
                    0
                    AC8C14
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Database pages]
                    
                
                
                    2
                    0
                    611F27
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:SQL Errors(_Total)\Errors/sec]
                    
                
                
                    3
                    0
                    F230E0
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Lazy writes/sec]
                    
                
                
                    4
                    0
                    5CCD18
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Locks(_Total)\Lock Waits/sec]
                    
                
                
                    5
                    0
                    BB2A02
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Page life expectancy]
                    
                
                
                    6
                    0
                    5A2B57
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\Process(sqlservr)\% Processor Time]
                    
                
                
                    7
                    0
                    89ABF8
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:SQL Statistics\SQL Compilations/sec]
                    
                
                
                    8
                    0
                    7EC25C
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Buffer Manager\Target pages]
                    
                
                
                    9
                    0
                    274482
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:General Statistics\User Connections]
                    
                
            
        
        
            MSSQL Total Size of Databases and Logs
            900
            200
            0.0000
            100.0000
            1
            1
            0
            1
            0
            0.0000
            0.0000
            0
            0
            0
            0
            
                
                    0
                    5
                    00CC00
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Databases(_Total)\Data File(s) Size (KB)]
                    
                
                
                    1
                    5
                    DD0000
                    1
                    7
                    0
                    
                        Template Windows LLD MSSQL
                        perf_counter[\{$MSSQLINST}:Databases(_Total)\Log File(s) Size (KB)]
                    
                
            
        
    
    
        
            MSSQL Databases Status
            
                
                    0
                    online
                
                
                    1
                    restoration
                
                
                    2
                    recovering
                
                
                    3
                    pending recovery
                
                
                    4
                    suspect
                
                
                    5
                    emergency
                
                
                    6
                    offline
                
                
                    7
                    not exist
                
            
        
        
            MSSQL Jobs Status
            
                
                    0
                    failure
                
                
                    1
                    Sucess
                
                
                    2
                    Repeat
                
                
                    3
                    Canceled
                
                
                    4
                    In progress
                
                
                    5
                    Never performed
                
            
        
        
            MSSQL Service
            
                
                    0
                    Initiated
                
                
                    1
                    Paused
                
                
                    2
                    Start Pending
                
                
                    3
                    Pause Pending
                
                
                    4
                    Continue Pending
                
                
                    5
                    Stop Pending
                
                
                    6
                    Stopped
                
                
                    7
                    Unknown
                
                
                    255
                    Unknown
                
            
        
        
            Service state
            
                
                    0
                    Down
                
                
                    1
                    Up
                
            
        
    

注意
将xml内容保存为xml文件导入zabbix模板中;
链接到主机后验证和调试直至数据产生;

2 条评论
  • heyc

    2021年9月15日 10:55

    不能用

    1. ygqygq2

      2022年1月19日 22:37

      版本很重要,文章较久,方法原理是一样的,你可以自己找下相应版本的模板。

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据