Перейти к основному содержанию
 

Zabbix шаблон для мониторинга Jobs у Microsoft SQL Server 2014

Microsoft SQL Server

Заморочился и написал. Делюсь полезным шаблоном для мониторинга джобов Microsoft SQL Server 2014. Ось - Windows Server 2012 R2.

В шаблоне 16 прототипов элементов данных и 8 прототипов триггеров.

Скачать шаблон: zabbix шаблон Jobs MSSQL 2014.

Ранее выкладывал Zabbix шаблон для мониторинга Microsoft SQL Server 2014. Можно объединить эти шаблоны в один, у меня так и сделано. Для вашего удобства выделил джобы отдельно.

16 прототипов элементов данных:

sql

8 прототипов триггеров:

sql

Немного про триггеры

Система расписания в SQL Server написана индусами и работает по-индусьи. Поэтому насписать универсальное решение для всех вариантов расписаний у меня не поднялась рука. Я написал триггеры для своих конкретных случаев.

  • Job {#JOBNAME} Is NOT Enabled
    Триггер срабатывает если джоб DISABLED.
  • Job {#JOBNAME} Is NOT Scheduled
    Триггер срабатывает если у джоба нет расписания.
  • Job {#JOBNAME} Last Run Status NOT Succeeded
    Самый полезный триггер, срабатывает когда джоб не выполнился. Есть дополнение, если отредактировать Maintenance Plans, то соответствующие джобы пересоздадутся и триггер сработает, потому как если джоб ни разу не запускался, то и последний запуск у него не succeeded.
  • Job {#JOBNAME} Last Run was more than a 1 HOUR ago
    Для джобов, которые запускаются точно раз в час.
  • Job {#JOBNAME} Last Run was more than a 2 HOURS ago
    Для джобов, которые запускаются точно раз в два часа.
  • Job {#JOBNAME} Last Run was more than a MONTH ago
    Для джобов, которые запускаются точно раз в месяц. Работает также если стоит настройка типа "в последний/первый день месяца". Триггер сработает если джоб не выполнялся 31 день.
  • Job {#JOBNAME} Last Run was more than a WEEK ago
    Для джобов, которые запускаются точно раз в неделю.
  • Job {#JOBNAME} Last Run was more than ONE DAY ago
    Для джобов, которые запускаются точно раз в день.

Для себя вы можете запилить свои триггеры.

Дополнительно вам понадобится:

Value mapping

SQL Server Last Run Status:

0 ⇒ Failed
1 ⇒ Succeeded
2 ⇒ Retry
3 ⇒ Canceled
5 ⇒ Unknown

SQL Server Schedule Type:

0 ⇒ Not scheduled
1 ⇒ One Time
4 ⇒ Daily
8 ⇒ Weekly
16 ⇒ Monthly
32 ⇒ Monthly - Relative to Frequency Interval
64 ⇒ Start automatically when SQL Server Agent starts
128 ⇒ Start whenever the CPUs become idle

sql

Скрипт Powershell

Скрипт Powershell для получения данных о джобах:

#функция для приведения к формату который понимает zabbix / the function is to bring to the format understands zabbix
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)
    }
}

#Задаем переменные для подключение к MSSQL. $uid и $pwd нужны для проверки подлинности windows / We define the variables for connecting to MS SQL. $uid и $pwd need to authenticate windows
$SQLServer = $(hostname.exe)
$uid = "ЛОГИН_ПОЛЬЗОВАТЕЛЯ_SQL" 
$pwd = "ЗДЕСЬ_ПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_SQL"

#Создаем подключение к MSSQL / Create a connection to MSSQL

#Если проверка подлинности windows / If windows authentication
$connectionString = "Server = $SQLServer; User ID = $uid; Password = $pwd;"

#Если Интегрированная проверка подлинности / If integrated authentication
#$connectionString = "Server = $SQLServer; Integrated Security = True;"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

#Создаем запрос непосредственно к MSSQL / Create a request directly to MSSQL
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  

if ([string]::IsNullOrEmpty($args[0]) -and [string]::IsNullOrEmpty($args[1])) {
    $SqlCmd.CommandText = "
    SELECT 
	    [sSVR].[name] + '.' +[sJOB].[name] AS [JobName]
    FROM
	    [msdb].[dbo].[sysjobs] AS [sJOB] WITH (NOLOCK)
	    INNER JOIN [msdb].[sys].[servers] AS [sSVR] WITH (NOLOCK)
		    ON [sJOB].[originating_server_id] = [sSVR].[server_id]
	    INNER JOIN [msdb].[dbo].[syscategories] AS [sCAT] WITH (NOLOCK)
		    ON [sJOB].[category_id] = [sCAT].[category_id] AND [sCAT].[name] = N'Database Maintenance'
	    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] WITH (NOLOCK)
		    ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id]
	    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] WITH (NOLOCK)
		    ON [sJOB].[job_id] = [sJOBSCH].[job_id]
	    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] WITH (NOLOCK)
		    ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
	    LEFT JOIN (
		    SELECT job_id, instance_id = MAX(instance_id), MAX(run_duration) AS run_duration
                FROM [msdb].[dbo].sysjobhistory WITH (NOLOCK)
                GROUP BY job_id
		    ) AS l
		    ON sJOB.job_id = l.job_id
	    LEFT JOIN [msdb].[dbo].sysjobhistory AS h WITH (NOLOCK)
		    ON h.job_id = l.job_id AND h.instance_id = l.instance_id
    ORDER BY [JobName]
    "
} else {
    $SqlCmd.CommandText = "
    SELECT 
        CASE UPPER(@JobValue)
            WHEN 'SERVERNAME' THEN [sSVR].[name]
            WHEN 'CREATED' THEN CONVERT(VARCHAR, [sJOB].[date_created], 121)
            WHEN 'MODIFIED' THEN ISNULL(CONVERT(VARCHAR, [sJOB].[date_modified], 121), '')
            WHEN 'IS_ENABLED' THEN CASE [sJOB].[enabled] WHEN 1 THEN '1' ELSE '0' END
            WHEN 'IS_SCHEDULED' THEN CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '0' ELSE '1' END
            WHEN 'SCHEDULE_TYPE' THEN ISNULL(LTRIM(RTRIM(STR([freq_type]))), '0')
			WHEN 'SCHEDULE_INTERVAL' THEN ISNULL(LTRIM(RTRIM(STR([freq_interval]))), '0')
			WHEN 'SCHEDULE_RELATIVE_INTERVAL' THEN ISNULL(LTRIM(RTRIM(STR([freq_relative_interval]))), '')
			WHEN 'SCHEDULE_SUBDAY_TYPE' THEN ISNULL(LTRIM(RTRIM(STR([freq_subday_type]))), '0')
			WHEN 'SCHEDULE_SUBDAY_INTERVAL' THEN ISNULL(LTRIM(RTRIM(STR([freq_subday_interval]))), '0')
			WHEN 'SCHEDULE_RECURRENCE_FACTOR' THEN ISNULL(LTRIM(RTRIM(STR([freq_recurrence_factor]))), '0')
            WHEN 'LAST_RUN' THEN ISNULL(CONVERT(VARCHAR, CONVERT(DATETIME, RTRIM(run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),' ','0'),3,0,':'),6,0,':')), 121),'')
			WHEN 'LAST_RUN_TIMESTAMP' THEN ISNULL(CONVERT(VARCHAR, DATEDIFF(SECOND, {d '1970-01-01'}, CONVERT(DATETIME, RTRIM(run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),' ','0'),3,0,':'),6,0,':')))), '0')
            WHEN 'LAST_RUN_STATUS' THEN ISNULL(LTRIM(RTRIM(STR([sJSTP].Last_run_outcome))), '')
            WHEN 'LAST_RUN_DURATION' THEN ISNULL(STUFF(STUFF(REPLACE(STR([sJSTP].last_run_duration,7,0),' ','0'),4,0,':'),7,0,':'), '')
            WHEN 'NEXT_RUN' THEN ISNULL(CONVERT(VARCHAR,  CONVERT(DATETIME, RTRIM(NULLIF([sJOBSCH].next_run_date, 0)) +' '+ STUFF(STUFF(REPLACE(STR(RTRIM([sJOBSCH].next_run_time),6,0),' ','0'),3,0,':'),6,0,':')), 121), '')
            WHEN 'MESSAGE' THEN ISNULL(h.Message, '')
            ELSE [sSVR].[name] + '.' +[sJOB].[name]
	    END AS [JobValue]
    FROM
	    [msdb].[dbo].[sysjobs] AS [sJOB] WITH (NOLOCK)
	    INNER JOIN [msdb].[sys].[servers] AS [sSVR] WITH (NOLOCK)
		    ON [sJOB].[originating_server_id] = [sSVR].[server_id]
	    INNER JOIN [msdb].[dbo].[syscategories] AS [sCAT] WITH (NOLOCK)
		    ON [sJOB].[category_id] = [sCAT].[category_id] AND [sCAT].[name] = N'Database Maintenance'
	    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] WITH (NOLOCK)
		    ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id]
	    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] WITH (NOLOCK)
		    ON [sJOB].[job_id] = [sJOBSCH].[job_id]
	    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] WITH (NOLOCK)
		    ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
	    LEFT JOIN (
		    SELECT job_id, instance_id = MAX(instance_id), MAX(run_duration) AS run_duration
                FROM [msdb].[dbo].sysjobhistory WITH (NOLOCK)
                GROUP BY job_id
		    ) AS l
		    ON sJOB.job_id = l.job_id
	    LEFT JOIN [msdb].[dbo].sysjobhistory AS h WITH (NOLOCK)
		    ON h.job_id = l.job_id AND h.instance_id = l.instance_id
    WHERE [sSVR].[name] + '.' +[sJOB].[name] = @JobName
    "
    $SqlCmd.Parameters.AddWithValue("@JobName", $args[0].ToString()) | Out-Null;
    $SqlCmd.Parameters.AddWithValue("@JobValue", $args[1].ToString()) | Out-Null;
}

$SqlCmd.Connection = $Connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) > $null
$Connection.Close()

#Получили список джобов. Записываем в переменную. / We get a list of jobs. Write to the variable.
$jobs = $DataSet.Tables[0]

#Парсим и передаем список джобов в zabbix. В последней строке нужно вывести имя джоба без запятой в конце. / Parse and pass a list of jobs in zabbix. In the last line need to display the job name without a comma at the end.
$idx = 1

if ([string]::IsNullOrEmpty($args[0])) {

    write-host "{ `"data`" : [ "
    foreach ($name in $jobs)
    {
        $line= "{`"{#JOBNAME}`" : `"" + $name.JobName + "`"}"
        if ($idx -lt $jobs.Rows.Count) {$line= $line + ","}
        $line= $line | convertto-encoding "cp866" "utf-8"
        write-host $line
        $idx++;
    }
    write-host " ]}"
} else {
    foreach ($name in $jobs)
    {
        $line= $name.JobValue | convertto-encoding "cp866" "utf-8"
        write-host $name.JobValue
        $idx++;
    }
}

Сохраняем скрипт на SQL сервере как mssql_jobs.ps1 в C:\zabbix\scripts. Не забываем внести в код свой логин-пароль.

SQL запрос дёрнул отсюда и модифицировал+оптимизировал: MSSQL - получить список JOB с помощью SQL запроса.

Пользовательская переменная

Добавляем в C:\zabbix\conf\userparameter_mssql.conf:

UserParameter=mssql.job.discovery,powershell -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\mssql_jobs.ps1"
UserParameter=mssql.job.info[*],powershell -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\mssql_jobs.ps1" $1 $2

Убедимся что этот файл подключен в C:\zabbix\conf\zabbix_agentd.win.conf:

Include=C:\zabbix\conf\userparameter_mssql.conf

 

Теги