Zabbix监控 Windows SQL Server
发表于:2025-01-31 作者:千家信息网编辑
千家信息网最后更新 2025年01月31日,Zabbix监控 Windows SQL Server[TOC]1. 模板来源此模板来自如下模板的修改和翻译。因为原模板为葡萄牙语。https://share.zabbix.com/databases
千家信息网最后更新 2025年01月31日Zabbix监控 Windows SQL Server
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 zabbixUserParameter=discovery.mssql.databases,powershell.exe -noprofile -executionpolicy bypass -File D:\zabbix\scripts\discovery.mssql.server.ps1 JSONDBUserParameter=discovery.mssql.jobs,powershell.exe -noprofile -executionpolicy bypass -File D:\zabbix\scripts\discovery.mssql.server.ps1 JSONJOBUserParameter=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
内容:
# parameterParam( [string]$select, [string]$2)# Login SQLSERVER$username = "username"$password = "password"# JSONDBif ( $select -eq 'JSONDB' ) {$database = sqlcmd -d Master -U $username -P $password -h -1 -W -Q "set nocount on;SELECT name FROM master..sysdatabases"$idx = 1write-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-hostwrite-host " ]"write-host "}"} # STATUSif ( $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'"}# CONNif ( $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_who2SELECT count(*) FROM @AllConnections WHERE DBName = '$2'"}# JSONJOBif ( $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 = 1write-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-hostwrite-host " ]"write-host "}"}# JOBSTATUSif ( $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_statusWHEN 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.statusFROM msdb.dbo.sysjobs jobsLEFT JOIN last_hist_rec hist ON hist.job_id = jobs.job_idAND hist.RowNum = 1WHERE jobs.name = '$2'" | % {$_.substring($_.length-1) -replace ''} | ForEach-Object {$_ -Replace "N", "5"}}# VERSIONif ( $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 Template Windows LLD MSSQL # Desenvolvido por Diego Cavalcante - 06/12/2017
# Monitoramento Windows SQLServer Templates MSSQL General MSSQL Jobs Status MSSQL Memory MSSQL Services MSSQL Statistics -
Version 7 0 discovery.mssql.data[version] 86400 15 0 0 1 0 0 0 0 1 0 0 Version of SQLServer. 0 MSSQL Statistics -
MSSQL is running 7 0 net.tcp.port[,{$MSSQLPORT}] 300 7 30 0 3 0 0 0 0 1 0 0 0 MSSQL General Service state -
Processor Time (%) 7 0 perf_counter[\Process(sqlservr)\% Processor Time] 300 7 30 0 0 % 0 0 0 0 1 0 0 perf_counter[\Process(sqlservr)\% Processor Time] 0 MSSQL Statistics -
Memory in Use 7 0 perf_counter[\Process(sqlservr)\Private Bytes] 300 7 30 0 0 B 0 0 0 0 1 0 0 Memory in Use
perf_counter[\Process(sqlservr)\Private Bytes] 0 MSSQL Memory -
Buffer Cache Hit Ratio (%) 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Buffer cache hit ratio] 300 7 30 0 0 % 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Buffer cache hit ratio] 0 MSSQL Memory -
Checkpoint Pages por (SEG) 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Checkpoint pages/sec] 300 7 30 0 0 p/sec 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Checkpoint pages/sec] 0 MSSQL Statistics -
Database Pages 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Database pages] 300 7 30 0 0 0 0 0 0 1 0 0 perf_counter[\SQLServer:Buffer Manager\Database pages] 0 MSSQL Statistics -
Lazy Writes por (SEG) 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Lazy writes/sec] 300 7 30 0 0 p/sec 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Lazy writes/sec] 0 MSSQL Statistics -
Page Life Expectancy 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Page life expectancy] 300 7 30 0 0 s 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Page life expectancy] 0 MSSQL Statistics -
Target Pages 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Target pages] 300 7 30 0 3 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Target pages] 0 MSSQL Statistics -
Total pages 7 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Total pages] 300 7 30 1 3 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Buffer Manager\Total pages] 0 MSSQL Statistics -
Total size of the data banks 7 1 perf_counter[\{$MSSQLINST}:Databases(_Total)\Data File(s) Size (KB)] 3600 15 90 0 0 B 0 0 0 0 1024 0 0 Total size of the data banks.
perf_counter[\{$MSSQLINST}:Databases(_Total)\Data File(s) Size (KB)] 0 MSSQL General -
Total size of logs 7 1 perf_counter[\{$MSSQLINST}:Databases(_Total)\Log File(s) Size (KB)] 3600 15 90 0 0 B 0 0 0 0 1024 0 0 日志的大小
perf_counter[\{$MSSQLINST}:Databases(_Total)\Log File(s) Size (KB)] 0 MSSQL General -
Total of connections in the databases 7 0 perf_counter[\{$MSSQLINST}:General Statistics\User Connections] 300 7 30 0 0 0 0 0 0 1 0 0 总的数据库连接
perf_counter[\{$MSSQLINST}:General Statistics\User Connections] 0 MSSQL Statistics -
Lock Waits por (SEG) 7 0 perf_counter[\{$MSSQLINST}:Locks(_Total)\Lock Waits/sec] 300 7 30 0 3 p/sec 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Locks(_Total)\Lock Waits/sec] 0 MSSQL Statistics -
Grants Pending Memory 7 0 perf_counter[\{$MSSQLINST}:Memory Manager\Memory Grants Pending] 300 7 30 0 3 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:Memory Manager\Memory Grants Pending] 0 MSSQL Memory -
Cache Memory 7 1 perf_counter[\{$MSSQLINST}:Memory Manager\SQL Cache Memory (KB)] 300 7 30 0 0 B 0 0 0 0 1024 0 0 Cache Memory
perf_counter[\{$MSSQLINST}:Memory Manager\SQL Cache Memory (KB)] 0 MSSQL Memory -
Memory Reserved 7 1 perf_counter[\{$MSSQLINST}:Memory Manager\Target Server Memory (KB)] 3600 7 30 0 3 B 0 0 0 0 1024 0 0 perf_counter[\{$MSSQLINST}:Memory Manager\Target Server Memory (KB)] 0 MSSQL Memory -
Erros por (SEG) 7 0 perf_counter[\{$MSSQLINST}:SQL Errors(_Total)\Errors/sec] 300 7 30 0 0 p/sec 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:SQL Errors(_Total)\Errors/sec] 0 MSSQL Statistics -
SQL Compilations por (SEG) 7 0 perf_counter[\{$MSSQLINST}:SQL Statistics\SQL Compilations/sec] 300 7 30 0 0 p/sec 0 0 0 0 1 0 0 perf_counter[\{$MSSQLINST}:SQL Statistics\SQL Compilations/sec] 0 MSSQL Statistics -
SQL Server Integration Services 10.0 7 0 service.info[MsDtsServer100] 300 7 30 0 3 0 0 0 0 1 0 0 Service: MsDtsServer100
Name: SQL Server Integration Services 10.0
Description: Provides management support for storing and running SSIS packages. 0 MSSQL Services MSSQL Service -
SQL Server Agent {$MSSQLAGENT} 7 0 service.info[{$MSSQLAGENT}] 300 7 30 0 3 0 0 0 0 1 0 0 Service: SQLSERVERAGENT
Name: SQL Server Agent ({$ MSSQLAGENT})
Description: Performs tasks, monitors SQL Server, triggers alerts, and allows the automation of some administrative tasks. 0 MSSQL Services MSSQL Service -
SQL Server {$MSSQLSERVER} 7 0 service.info[{$MSSQLSERVER}] 300 7 30 0 3 0 0 0 0 1 0 0 Service: MSSQLSERVER
Name: SQL Server ({$ MSSQLSERVER})
Description: Offers storage, processing, and controlled access to data and fast transaction processing. 0 MSSQL Services MSSQL Service MSSQL Databases 7 discovery.mssql.databases 3600 0 0 0 0 0 0 1 MSSQL数据库服务器发现 {#MSSQLDBNAME} Number of Connections 7 0 discovery.mssql.data[CONN,{#MSSQLDBNAME}] 600 7 30 0 3 0 0 0 0 1 0 0 Number of MSSQL Database Connections - {#MSSQLDBNAME} 0 MSSQL database info - {#MSSQLDBNAME} {#MSSQLDBNAME} Status 7 0 discovery.mssql.data[STATUS,{#MSSQLDBNAME}] 600 7 30 0 3 0 0 0 0 1 0 0 MONITOR o Status to DATE MINIMUM - {# MOUNT} 0 MSSQL Databases Status MSSQL database info - {#MSSQLDBNAME} {#MSSQLDBNAME} Database Size 7 1 perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Data File(s) Size (KB)] 3600 15 90 0 3 B 0 0 0 0 1024 0 0 Detects Total Bank Size.
perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Data File(s) Size (KB)] 0 MSSQL database info - {#MSSQLDBNAME} {#MSSQLDBNAME} Log Size 7 1 perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Log File(s) Size (KB)] 3600 15 90 0 3 B 0 0 0 0 1024 0 0 Total Log File Size.
perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Log File(s) Size (KB)] 0 MSSQL database info - {#MSSQLDBNAME} {Template Windows LLD MSSQL:discovery.mssql.data[STATUS,{#MSSQLDBNAME}].last(0)}>0 0 {#MSSQLDBNAME} ({ITEM.LASTVALUE}) is down on {HOST.NAME} 0 0 4 Sqlserver
The status of the database {# mssqldbname} is {item.lastvalue}
Status possible.
0 ⇒ online.
1 ⇒ catering
2 ⇒ recovering
3 ⇒ recovery pending
4 ⇒ suspect
5 ⇒Emergency
6 ⇒ offline
7 ⇒ not exist 0 0 MSSQL Database Size and Log in {#MSSQLDBNAME} 900 200 0.0000 100.0000 0 1 0 1 0 0.0000 0.0000 0 0 0 0 0 5 00C800 0 7 0 -
Template Windows LLD MSSQL perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Data File(s) Size (KB)] 1 5 0099CC 1 7 0 -
Template Windows LLD MSSQL perf_counter[\{$MSSQLINST}:Databases({#MSSQLDBNAME})\Log File(s) Size (KB)] MSSQL Number of Connections in {#MSSQLDBNAME} 900 200 0.0000 100.0000 0 1 0 1 0 0.0000 0.0000 0 0 0 0 0 5 0099CC 1 7 0 -
Template Windows LLD MSSQL discovery.mssql.data[CONN,{#MSSQLDBNAME}] MSSQL Jobs 7 discovery.mssql.jobs 3600 0 0 0 0 0 0 1 SQL Server Jobs Discovery Job {#MSSQLJOBNAME} Status 7 0 discovery.mssql.data[JOBSTATUS,{#MSSQLJOBNAME}] 3600 15 30 0 3 0 0 0 0 1 0 0 Monitora Status dos Jobs SQLSERVER. 0 MSSQL Jobs Status MSSQL Jobs Status {Template Windows LLD MSSQL:discovery.mssql.data[JOBSTATUS,{#MSSQLJOBNAME}].last(0)}=0 0 Job {#MSSQLJOBNAME} ({ITEM.LASTVALUE}) on {HOST.NAME} 0 0 4 SQLServer
Job Status {#MSSQLJOBNAME} is {ITEM.LASTVALUE}
Possible Status:
0 = Failure
1 = Success
2 = Repeat
3 = Canceled
4 = In Progress
5 = Never Run 0 0 {Template Windows LLD MSSQL:discovery.mssql.data[JOBSTATUS,{#MSSQLJOBNAME}].last(0)}=2 0 Job {#MSSQLJOBNAME} ({ITEM.LASTVALUE}) on {HOST.NAME} 0 0 1 0 0 {Template Windows LLD MSSQL:discovery.mssql.data[JOBSTATUS,{#MSSQLJOBNAME}].last(0)}=3 0 Job {#MSSQLJOBNAME} ({ITEM.LASTVALUE}) on {HOST.NAME} 0 0 2 0 0 {Template Windows LLD MSSQL:discovery.mssql.data[JOBSTATUS,{#MSSQLJOBNAME}].last(0)}=5 0 Job {#MSSQLJOBNAME} ({ITEM.LASTVALUE}) on {HOST.NAME} 0 0 3 0 0 {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模板中;
链接到主机后验证和调试直至数据产生;
模板
文件
内容
数据
脚本
运行
数据库
用户
配置
监控
主机
大小
密码
手动
日志
服务器
来源
版本
目录
葡萄
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
静态网站怎么做做数据库
网络安全顺口溜30字
欧迪办公网络技术有限公司电话
广州工业软件开发要多少钱
个人承接手机软件开发
如何租用阿里云服务器
通州软件开发
英雄联盟聊天服务器登不上
网络安全问题已然成为
数据库简体换成繁体
gb 网络安全事件分类定级标准
广州天河区网络安全备案地址
高智能移动软件开发专业
腾讯轻量服务器怎么远程连接
服务器固件更新
网站模板源文件和数据库
数据库技术面试
数据库具有三个特点是什么
福建华为服务器虚拟化设计服务器
浪潮服务器在bios做raid
添加大数据库快捷键
河南上位机软件开发
基于数据什么特性数据库得以发展
网络安全都需要学习什么
数据库简体换成繁体
美记软件开发者
昆仑通态实时数据库名称如何删除
计算机网络安全的防范总数
软件开发模型建立举例
莆田伟创网络技术有限公司