In the process of monitoring SQL Server with zabbix, a good template was found and shared as follows:
https://share.zabbix.com/data…
github:
https://github.com/MantasTume…
The results are as follows:
Contains two
With SQL instance discovery for multi-instance SQL Server monitoring, not tested
Without SQL instance discovery is suitable for single instance SQL Server monitoring. The template used in 2008 R2 is not supported by some monitoring items.
This template author has updates, pay attention to others’README and Documentation content. (foreigners do things very carefully)
Template Backup Address:
http://t.cn/EKlYbiF
https://www.gueizu.com/file-5…
Explain some of the problems that have been found at present:
Discovery.mssql.server.ps1:
$SQLInstanceName
The $SQLInstanceName needs to be modified, either as described in the script or directly as a dynamic assignment:
# Parameters. Change Line 14 $SQLInstanceName=”InstanceName” to correct instance name
or
if( $SQLInstanceName -eq "EnterInstanceName" )
{
$SQLInstanceName = $(hostname.exe)
}
Random code
Under the Chinese Windows Environment:
Execute in PowerShellpowershell.exe -File "C:\Program Files\Zabbix Agent\scripts\Discovery.mssql.server.ps1" JSONJOBNAME
The result is not scrambled, but the value obtained with zabbix_get in ZABBIX server returns scrambled code
Terms of settlement:
Add conversion method:
# This function converts from one encoding to another.
function convertto-encoding ([string]$from, [string]$to){
begin{
$encfrom = [system.text.encoding]::getencoding($from)
$encto = [system.text.encoding]::getencoding($to)
}
process{
$bytes = $encto.getbytes($_)
$bytes = [system.text.encoding]::convert($encfrom, $encto, $bytes)
$encto.getstring($bytes)
}
}
Amendment:
foreach ($job in $jobname)
{
if ($idx -lt $jobname.Count)
{
$line= "{ `"{#JOBNAME}`" : `"" + $job + "`" },"
write-host $line
}
....
For:
foreach ($job in $jobname)
{
$job = $job | convertto-encoding "gbk" "utf-8"
if ($idx -lt $jobname.Count)
{
$line= "{ `"{#JOBNAME}`" : `"" + $job + "`" },"
write-host $line
}
Be careful:
$job = $job | convertto-encoding “gbk” “utf-8”
Chinese garbled in other places can also be solved by this method, but when PowerShell executes, it returns the result of garbled.
Legacy Questions: Some Chinese (special words) are still scrambled and will be displayed as?, I don’t know what the problem is.
Get timed task list, insufficient SQL Server 2014
This problem is currently only found in SQL Server 2014, but not in the database of 2008 r2.
When ZABBIX server uses zabbix_get to get the timed task list, it mistakenly rejects SELECT permissions on the object’sysjobs'(database’msdb’, architecture’dbo’), but it can return results by using power shell execution.
The phenomena are as follows:
The simplest way is:
Add a username password, modify the following, and remove the comments
Amendment:$jobname = sqlcmd -S $SQLInstanceName -d msdb -h -1 -W -Q "set nocount on;SELECT [name] FROM msdb.dbo.sysjobs"
by$jobname = sqlcmd -S $SQLInstanceName -U $uid -P $pwd -d msdb -h -1 -W -Q "set nocount on;SELECT [name] FROM msdb.dbo.sysjobs"
Get belowJOBSTATUS
The user name password parameter is also added.
Possible reasons:
Comparing permissions, the number of users in 2014 is much smaller, and there is no SYSTEM user. It is uncertain whether it is the installation problem or the problem of 2014 itself. Ultimately, the user name password is used to solve the problem.