Sunday, February 14, 2010

NSClient++ external vbs script to check MSSQL jobs

Edit: New fixed version

This script for NSClient++ checks if there is any failed jobs in MS SQL server. The database connection data is stored in a UDL file.

External script alias in nsclient:
check_mssql_jobs=cscript.exe //T:30 //NoLogo scripts\check_mssql_jobs.vbs /MaxWarn:0 /MaxCrit:1 /truncate:900

Nagios command:
define service{
use generic-service
host_name SEHQFVD03
service_description MSSQL jobs
check_command check_nrpe_check_mssql_jobs
high_flap_threshold 35.0
low_flap_threshold 30.0
notification_options w,u,c,r,f
}


Script:
'########################################################
'# Check MSSQL jobs over UDL TEST v0.0.0.4
'# VIKJON0 2010-02-13
'# VIKJON0 2010-03-02 Only look at latest time job was run
'#########################################################

Option Explicit
Dim strConnection, conn, rs, strSQL
Dim objConn
Dim connStr
Dim foundTXT, returnTXT, perfTXT
Dim errLevel, errLevelTXT
Dim MaxWarn, MaxCrit, numberOfRows, truncate
Dim wshArgs

'--Set working directory
Dim WshShell
Set WshShell = WScript.CreateObject("WScript.Shell")
'WScript.Echo WshShell.CurrentDirectory
WshShell.CurrentDirectory = "C:\Program Files\NSClient++\scripts"

'--Get command line arguments-- /MaxWarn:Y /MaxCrit:Z /truncate:x
Set wshArgs = wscript.arguments

if wshArgs.Named.exists("MaxWarn") then
MaxWarn = cint(wshArgs.named.item("MaxWarn"))
else
MaxWarn = 0
end if

if wshArgs.Named.exists("MaxCrit") then
MaxCrit = cint(wshArgs.named.item("MaxCrit"))
else
MaxCrit = 0
end if
if wshArgs.Named.exists("truncate") then
truncate = cint(wshArgs.named.item("truncate"))
else
truncate = 0
end if

'--Run db query-----------------------
strConnection = "File Name=myUDL.udl; "
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = CreateObject("ADODB.recordset")

'Check only category = 3 for maintence jobs
'------------------------------------------------------

strSQL = "SELECT name, message,category_id FROM msdb.dbo.sysjobs AS J LEFT OUTER JOIN msdb.dbo.sysjobhistory AS H ON J.job_id = H.job_id " &_
"WHERE enabled = 1 AND run_status != 1 AND step_id = 0 " &_
"AND (cast(run_date as varchar) + RIGHT('00' + cast(run_time as varchar),6)) = " &_
"(select max(cast(run_date as varchar) + RIGHT('00' + cast(run_time as varchar),6)) " &_
"FROM msdb.dbo.sysjobhistory AS H2 where H2.job_id = H.job_id AND H2.step_id = 0 " &_
")order by name"

'----------------------------------------------


rs.open strSQL, conn, 3,3
numberOfRows = rs.recordcount

foundTXT = ""
if numberOfRows = 0 then
foundTXT = "check_db OK"
else
rs.MoveFirst
WHILE NOT rs.EOF
foundTXT = foundTXT & rs("name") & "#"
rs.MoveNext
wend
end if


'--Ceck result and build return data
If truncate > 0 then
foundTXT = left(foundTXT,truncate)
end if

errLevel = 0
errLevelTXT = ""
if not MaxCrit = 0 AND numberOfRows >= MaxCrit then
errLevel = 2
errLevelTXT = ", found errors: " & numberOfRows & " > critical"
else
if not MaxWarn = 0 AND numberOfRows >= MaxWarn then
errLevel = 1
errLevelTXT = ", found errors: " & numberOfRows & " > warning"
end if
end if

perfTXT = "|'found errors'=" & numberOfRows &";" & MaxWarn & ";" & MaxCrit & ";"
returnTXT = foundTXT & errLevelTXT & perfTXT
'msgbox returnTXT

'--Close and exit

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Wscript.StdOut.WriteLine ReturnTXT
WScript.Quit(errLevel)

No comments:

Post a Comment