Monitoring Microsoft SQL Jobs with Zabbix

Posted: October 5, 2018 in Linux, Scripts

On SQL server create stored procedure for collecting Job status:

CREATE PROCEDURE [dbo].[usp_job_server_error] @job_name VARCHAR(100) AS
BEGIN
SET NOCOUNT ON
SELECT j.NAME AS '[JOB]',
CASE
WHEN jh.run_statusIN ( 0, 1, 2, 3, 4 ) THEN jh.run_status
ELSE ( CASE
WHEN ja.run_requested_dateIS NOT NULL
AND ja.stop_execution_dateIS NULL THEN 4
ELSE -1
END ) END  AS '[STATUS]',
ja.run_requested_date AS '[LAST_EXECUTION]'
WHERE  ja.session_id = (SELECT Max(session_id)
AND j.enabled = 1
AND j.name = ISNULL(@job_name, j.name)
--AND (j.name LIKE ISNULL(@identifier, 'HIGH')+'%' OR j.name LIKE ISNULL(@identifier, 'DISASTER')+'%')
SET NOCOUNT OFF
END;
GO

Script for getting data from stored procedure and sending it to Zabbix:

 

$data = $(foreach ($line in sqlcmd -Q "exec dbo.usp_job_server_error @job_name=sql job" -E -s ":") { $l=$line.split()[0]; $s=$line.split(":")[1] ; "$s"})
$result=$data[2..$data.length]
cd "C:\Program Files\Zabbix Agent\bin\win64"
.\zabbix_sender.exe-z zabbix_server -p 10051 -s zabbix_host -c "C:\Program Files\Zabbix Agent\conf\zabbix_agentd.win.conf" -k sql.job[myjob]-o $result -vv
Create Zabbix item:
5.PNG
And trigger:
{server:sql.job[myjob].last()}=0
Schedule above script with Task Scheduler:

Program/script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Add argument: -file “C:\File\Scripts\jobstatus.ps1”

Start In: C:\File\Scripts

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s