成功最有效的方法就是向有经验的人学习!

使用zabbix监控MSSQL

Zabbix应用模板:https://share.zabbix.com/databases/microsoft-sql-server/template-for-microsoft-sql-server

选择一款,下载地址:https://github.com/MantasTumenas/Zabbix-template-for-Microsoft-SQL-Server

User parameters文件放到zabbix-agent安装目录下的zabbix-agent.d目录。
然后将Scripts目录放到zabbix-agent安装目录下
需要修改User parameters中的脚本路径,又你实际安装路径为准

自动发现模板中的脚本修改,将Discovery.mssql.databasename.ps1文件的第六行改为:
uid为你SQL的用户,password为你SQL的密码,否则不能获取数据库名,进而不能创建与库相关的监控项

$connectionString = "Server = $fullInstanceName; uid=zabbix; password=Zz@111111;"

针对非自动发现模板修改脚本如下:

# Desenvolvido por Diego Cavalcante - 06/12/2017
# Monitoramento Windows SQLServer
# Versco: 1.1.0
# Criaeco = Versco 1.0.0 29/08/2017 (Script Bisico).
# Update  = Versco 1.1.0 02/01/2018 (Obrigado @bernardolankheet, JOBSTATUS Retornava N = 5 Nunca Executado).
# Update  = by Oleg D. and Mantas T. Translated to EN, added SQL Insance name.

# Parameters. Change Line 14 $SQLInstanceName="InstanceName" to correct instance name

Param(

[Parameter(Mandatory = $true, Position = 0)]  [string]$select,
  [Parameter(Mandatory = $false, Position = 1)][string]$2,
  [Parameter(Mandatory = $false, Position = 2)]$SQLInstanceName="127.0.0.1\"

)

#Login SQLInstanceName  以下两行改为你实际的SQL验证帐户
$uid = "zabbix"
$pwd = "Zz@111111"

# Construct JSON from queried databases names.
if ( $select -eq 'JSONDBNAME' ) 
{
$database = sqlcmd -U $uid -P $pwd -S $SQLInstanceName -d master -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= "{ `"{#DBNAME}`" : `"" + $db + "`" },"
        write-host $line
    }
    elseif ($idx -ge $database.Count)
    {
    $line= "{ `"{#DBNAME}`" : `"" + $db + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"
} 

# Query database status.
if ( $select -eq 'DBSTATUS' )
{
sqlcmd -U $uid -P $pwd -S $SQLInstanceName -d master -h -1 -W -Q "set nocount on;SELECT coalesce(max(state),7) from sys.databases where name = '$2'"
}

# Query connections to the database.
if ( $select -eq 'DBCONN' )
{
sqlcmd -U $uid -P $pwd -S $SQLInstanceName -d master -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'"
}

# Construct JSON from queried jobs names.
if ( $select -eq 'JSONJOBNAME' )
{
$jobname = sqlcmd -U $uid -P $pwd -S $SQLInstanceName -d msdb -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= "{ `"{#JOBNAME}`" : `"" + $job + "`" },"
        write-host $line
    }
    elseif ($idx -ge $jobname.Count)
    {
    $line= "{ `"{#JOBNAME}`" : `"" + $job + "`" }"
    write-host $line
    }
    $idx++;
}
write-host
write-host " ]"
write-host "}"
}

# Query jobstatus.
if ( $select -eq 'JOBSTATUS' )
{
sqlcmd -U $uid -P $pwd -S $SQLInstanceName -d msdb -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"}
}

重启ZABBIX-AGENT后

配置zabbix用户后台
导入Templates目录下文件:


导入参数Zabbix Value Mapping目录下文件:

至此,完成了客户端和服务端的配置,此时只在将SQL主机上应用刚刚导入的模板,稍等片刻就可又获取到数据

file

file

赞(1) 打赏
未经允许不得转载:陈桂林博客 » 使用zabbix监控MSSQL
分享到

大佬们的评论 3

  1. #2

    出现中文乱码

    kingen3年前 (2020-10-27)回复
    • 从Window服务器找到相应的字休复制到zabbix Server服务器上:
      控制面板–>字体–>选择一种中文字库例如“楷体”(simkai.ttf)
      将我们选择的字体文件上传到zabbix web服务,/usr/share/zabbix/fonts目录下(rpm安装目录)

      [root@Node3 fonts]# pwd
      /usr/share/zabbix/fonts
      [root@Node3 fonts]# ls
      graphfont.ttf simkai.ttf

      3、修改此/usr/share/zabbix/include/defines.inc.php文件中字体的配置,将里面关于字体设置从graphfont替换成simkai
      再次打开刚开的界面,发现乱码已经解决了

      admin3年前 (2020-10-28)回复
  2. #1

    你好 配置出现乱码

    kingen3年前 (2020-10-27)回复

全新“一站式”建站,高质量、高售后的一条龙服务

微信 抖音 支付宝 百度 头条 快手全平台打通信息流

橙子建站.极速智能建站8折购买虚拟主机

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

登录

找回密码

注册