千家信息网

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: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 添加大数据库快捷键 河南上位机软件开发 基于数据什么特性数据库得以发展 网络安全都需要学习什么 数据库简体换成繁体 美记软件开发者 昆仑通态实时数据库名称如何删除 计算机网络安全的防范总数 软件开发模型建立举例 莆田伟创网络技术有限公司
0