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 PowerShell
powershell.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 belowJOBSTATUSThe 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.