摘要:本文主要向大家介绍了SQLServer数据库之Zabbix监控 Windows SQL Server,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了SQLServer数据库之Zabbix监控 Windows SQL Server,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
Zabbix监控 Windows SQL Server 以上就介绍了SQL Server的相关知识,希望对SQL Server有兴趣的朋友有所帮助。了解更多内容,请关注职坐标数据库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内容:
Template Windows LLD MSSQL
# Monitoramento Windows SQLServer
perf_counter[\Process(sqlservr)\Private Bytes]
perf_counter[\{$MSSQLINST}:Databases(_Total)\Data File(s) Size (KB)]
perf_counter[\{$MSSQLINST}:Databases(_Total)\Log File(s) Size (KB)]
perf_counter[\{$MSSQLINST}:General Statistics\User Connections]
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号