monitors:bbsql-server

Error loading plugin struct
ParseError: syntax error, unexpected 'fn' (T_STRING), expecting :: (T_PAAMAYIM_NEKUDOTAYIM)
More info is available in the error log.

monitor script for Microsoft SQL server 2000 and up

Author Bart Gillis
Compatibility Xymon 4.2
Requirements BBWin 0.12
Download None
Last Update 2010-03-25

This is a port of the bbsql-server script that was written 03/2002 by Jason Benassi (jbenassi at futurecasting.com) Used for monitoring MS SQL Server

  • Blocking Locks → This test will look at a 20 minute window for database jobs
  • Statistics on “Latest Backup date”
  • Statistics on the Size and usage of the transaction logs

Client side

  • Install BBWin 0.12 on the MSSQL server
  • Place this script anywhere on your SQL-Server 7, 2000, 2005 or 2008 server.
  • Configure the BBWin client to execute this script as an external script.
  • Alternatively you can configure BBWin in central mode and schedule this script in the Windows “Scheduled Tasks” scheduler.

Server side

(none)

Show Code ?

Hide Code ?

' Script for Xymon (Big Brother) Monitoring Tool
' Modified 02/2010 By Bart Gillis
' email: Bart.Gillis@cegeka.be
' This is a port of the bbsql-server script that was written 03/2002 by Jason Benassi
'           jbenassi@futurecasting.com
' Used for monitoring SQL Server Blocking Locks
' And database jobs
' This test will look at a 20 minute window for database jobs
' 
'           Feel free to modify this script (just send me the updates so I can use them) :)
 
' Modification History:
' bbsql-server-v2.0
'           - Changed the SQL connection string. Authentication to a trusted server -> No password information required in this script.
'           - 32 bit/64 bit processor architecture check.
 
' bbsql-server-v3.0
'           - Check the latest finished Database Backup date and time
'           - Check Log Size and Log space used of the transaction log.
 
' bbsql-server-v3.0.1
'           - Minor big fix around line 122 (Thank you Richard Finegold)
'             the script only opened the file if it exists, then deletes immediately after
'             reading instead of after the variable assignment.
'           - changed <td> to <th> in table headers (Thank you Richard Finegold)
 
'           Install Instructions:
'           Place this script anywhere on your SQL-Server 7, 2000, 2005 or 2008 server.
'           Configure the BBWin client to execute this script as an external scrip.
'           Alternatively you can configure BBWin in central mode and schedule this script in the Windows "Scheduled Tasks" scheduler. 
'           Next configure the tesname variables below.
 
Dim version, ws, extPath, testName, testString, gbFailed, outputfilename, tempfilename, Proc
 
'************* Configure these parameters ***********************
 
testName = "mssql" 'This is what the bb test will be called
version = "V3.0.1"
 
'***************************************************************
outputfilename = replace(testName,".","")
tempfilename = outputfilename & ".tmp"
Const adStateClosed = 0
 
Set ws = WScript.CreateObject("WScript.Shell")
ProcessorCheck
            if Proc = "x86" then
'            extPath = "c:\"
           extPath = ws.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\tmppath") 'Registry key that stores the ext script path on 32bit Architecture
            WScript.Echo Proc
            else
'            extPath = "c:\"  
           extPath = ws.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BBWin\tmppath") 'Registry key that stores the ext script path on 64bit Architecture
            WScript.Echo Proc
            end if
gbFailed = false
CheckJobStats extPath
CheckLocks
CheckBackup
CheckTransactionlog
WriteFile extPath
 
Sub WriteFile (strFilePath)
    Set fso = CreateObject("Scripting.FileSystemObject")
                        If gbFailed = False Then
                                   testString = "green " & Date & " " & Time & vbcrlf & testString & vbcrlf
                        Else
                                   testString = "red " & Date & " " & Time & vbcrlf & testString & vbcrlf       
                        End If
            Set f = fso.OpenTextFile(strFilePath & "\" & outputfilename , 8 , TRUE)
                        f.Write testString
                        f.Close
            Set fso = Nothing
End Sub
 
Sub CheckLocks 
            Dim cn, rs, sqlStr, strResult, htmlcolorcode
    Set cn = CreateObject ("ADODB.Connection")
            cn.Open "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=master" & ";Integrated Security=SSPI"
            Set rs = CreateObject ("ADODB.Recordset")
            htmlcolorcode="##00ff00"
 
            sqlStr = _
                        "Select DISTINCT CONVERT (smallint, l1.req_spid) AS spid, " & _
                        "Left(db_name(l1.rsc_dbid), 10) AS dbName, " & _
                        "Left(object_name(l1.rsc_objid), 20) AS ObjName, " & _
                        "l1.rsc_indid AS IndId, " & _
                        "substring (v.name, 1, 4) AS Type, " & _
                        "substring (l1.rsc_text, 1, 16) AS Resource, " & _
                        "substring (u.name, 1, 8) AS Mode, " & _
                        "substring (x.name, 1, 5) AS Status " & _
                        "FROM master.dbo.syslockinfo l1, " & _
                        "master.dbo.syslockinfo l2, " & _
                        "master.dbo.spt_values v, " & _
                        "master.dbo.spt_values x, " & _
                        "master.dbo.spt_values u " & _
                        "WHERE l1.rsc_type = v.number " & _
                        "AND v.type = 'LR' " & _
                        "AND l1.req_status = x.number " & _
                        "AND x.type = 'LS' " & _
                        "AND l1.req_mode + 1 = u.number " & _
                        "AND u.type = 'L' " & _
                        "AND l1.rsc_type <>2 /* NOT a DB lock */ " & _
                        "AND l1.rsc_dbid = l2.rsc_dbid " & _
                        "AND l1.rsc_bin = l2.rsc_bin " & _
                        "AND l1.rsc_objid = l2.rsc_objid " & _ 
                        "AND l1.rsc_indid = l2.rsc_indid " & _ 
                        "AND l1.req_spid <> l2.req_spid " & _
                        "AND l1.req_status <> l2.req_status " & _
                        "ORDER BY substring (l1.rsc_text, 1, 16), substring (x.name, 1, 5) "
            rs.Open sqlStr, cn , 3 ,3 
 
        If Not rs.EOF Then
 
            Do While Not rs.EOF
                    htmlcolorcode="#ff0000"
            gbFailed = True
            strResult = strResult & "            Block Found -- Details: " & vbcrlf & _
                "                                              SPID= " & rs.Fields("spid") & _
                "                                              DBNAME= " & rs.Fields("dbName") & _
                "                                              OJBNAME= " & rs.Fields("ObjName") & _
                "                                              INDID = " & rs.Fields("IndId") & _
                "                                              TYPE = " & rs.Fields("Type") & _
                "                                              RESOURCE = " & rs.Fields("Resource") & _
                "                                              MODE = " & rs.Fields("Mode") & _
                "                                              STATUS = " & rs.Fields("Status") & vbcrlf & vbcrlf
 
                wscript.echo "Blocking Lock SPID = " & rs.Fields("spid")
            rs.MoveNext
            Loop
        Else
            strResult = strResult & vbcrlf & "              No Blocking Locks." & vbcrlf
                                   wscript.echo "No Blocking Locks"
                        End If
            testString = testString & "<br><b>	<font color=" & htmlcolorcode & ">-------------------- SQL SERVER BLOCKING LOCKS -------------------</font></b><br><br>"
            testString = testString & strResult
 
            Set rs = Nothing                                   
            Set cn = Nothing
 
End Sub
 
Sub CheckJobStats (strFilePath)
            Dim cn, rs, cmd, sqlStr, fso, f, htmlStr1, htmlStr2, rColor, strStatus, tmpStr, bFailed, htmlcolorcode 
    Set cn = CreateObject("ADODB.Connection")
            cn.Open "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=msdb" & ";Integrated Security=SSPI"
 
            sqlStr = "DECLARE all_jobs_cursor CURSOR " & _
                    "FOR " & _
                    "SELECT job_id FROM sysjobhistory GROUP BY job_id " & _
                    "OPEN all_jobs_cursor " & _
                    "DECLARE @current_id uniqueidentifier " & _
                    "FETCH NEXT FROM all_jobs_cursor " & _
                    "INTO @current_id " & _
                    "WHILE @@FETCH_STATUS = 0 " & _
                    "BEGIN " & _
                    "SELECT s.*, v.name FROM sysjobhistory s, sysjobs_view v " & _
                    "WHERE s.job_id = v.job_id and s.job_id =  @current_id " & _
                    "and cast(cast(run_date as varchar)+' '+cast(run_time/10000 as varchar)+':'+cast(run_time/100%100 as varchar)+':'+cast(run_time%100 as varchar) as datetime)>getdate()-1.0/24" & _
                    "ORDER BY instance_id " & _
                    "FETCH NEXT FROM all_jobs_cursor " & _
                    "INTO @current_id " & _
                    "END " & _
                    "CLOSE all_jobs_cursor " & _
                    "DEALLOCATE all_jobs_cursor"
    Set rs = cn.Execute(sqlStr)
 
        bFailed = False
        strStatus = "green"
            htmlcolorcode= "#00ff00"
 
            Set fso = CreateObject("Scripting.FileSystemObject")
            Do Until rs Is Nothing
                        if NOT rs.State = adStateClosed then
                        Set f = fso.OpenTextFile(strFilePath & "\" & tempfilename , 8, True )
            rCnt = 1
    If Not rs.EOF Then
        Do While Not rs.EOF
        bFailed = False
			If rs.Fields("run_status") <> 1 Then
				strStatus = "red"
			End If
 
			If rs.Fields("run_status") = 0 Then
				bFailed = True
				rColor = "&red"
				htmlcolorcode = "#ff0000"
            Else
                bFailed = False
                rColor = "&green"
                htmlcolorcode = "#00ff00"
            End If
 
            If Not rCnt > 1 Then       
                f.Write "            Job Name: <b>" & rs.Fields("name") & "</b>" & vbcrlf
            End If
		msg = replace(rs.Fields("message"),vbCRLF,vbCRLF & "             ")
        str =     "                      Step ID: " & rs.Fields("step_id") & "   Step Name: " & rs.Fields("step_name") &  _
                  "   Run Duration: " & rs.Fields("run_duration") & "   Run Status: " & rs.Fields("run_status") &  _
                  "   Retries Attempted: " & rs.Fields("retries_attempted") & "   Run Date: " & rs.Fields("run_date") & "            Run Time: " & rs.Fields("run_time") & vbcrlf & _
                  "                      Message: " & vbcrlf & "             " & msg & vbcrlf & _
                  "                      -----------------------------------------------------------------------------------------------------------------" & vbcrlf
        f.Write str
        wscript.echo str
 
        rCnt = rCnt + 1
        rs.MoveNext
    Loop
        f.Write vbcrlf & vbcrlf
        End If
        If bFailed = True Then
          gbFailed = True
        End If                                       
    f.Close  
    rCnt = 1
    End If
Set rs = rs.NextRecordSet
 
Loop
if fso.FileExists(strFilePath & "\" & tempfilename) then
       Set f = fso.OpenTextFile(strFilePath & "\" & tempfilename ,1 )
               If Not f.AtEndOfLine Then
                       tmpStr = f.ReadAll
               Else
                       tmpStr = "              No Job History. " & vbcrlf
               End If
       f.Close
       fso.DeleteFile strFilePath & "\" & tempfilename
else
       tmpStr = "              No Job History. " & vbcrlf
end if
 
            tmpStr = "<br>Version:" & version & "<br><b>	<font color=" & htmlcolorcode & ">----------------------- SQL SERVER JOBS --------------------------</font></b><br><br>" & tmpStr
 
            testString = testString & tmpStr & vbcrlf & vbcrlf
 
            Set fso = Nothing
            Set cn = Nothing
            Set rs = Nothing
End Sub
 
Sub ProcessorCheck ()
Dim  WshShell, WshSysEnv
 
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("SYSTEM")
Proc = WshSysEnv("PROCESSOR_ARCHITECTURE")
End Sub
 
Sub CheckBackup ()
Dim cn, rs, sqlStr, strStatus, htmlcolorcode ,strResult
    Set cn = CreateObject ("ADODB.Connection")
            cn.Open "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=master" & ";Integrated Security=SSPI"
            Set rs = CreateObject ("ADODB.Recordset")
            htmlcolorcode = "#00ff00"
            strResult = vbcrlf & "<table border=1 cellpadding=2 cellspacing=2><tbody><tr><th>" &_
                                               "DBASE NAME" & vbTab & "</th><th>" & vbTab &_
                                               "BACKUP FINISHED"  & "</th></tr>" & vbcrlf
            sqlStr = _
                        "SELECT db.name, ISNULL(MAX(backup_finish_date), '1900-01-01') as backup_finish_date " & _
                        "FROM master.dbo.sysdatabases db " & _
                        "LEFT JOIN msdb..backupset bu ON ( db.name = bu.database_name ) " & _
                        "WHERE db.name != 'tempdb'" &_
                        "group by db.name"
        Set rs = cn.Execute(sqlStr)
'                       rs.Open sqlStr, cn , 3 ,3
                        Do While Not rs.EOF
                    strResult = strResult &_
                                                                       "<td>" & rs.Fields("name") & vbTab & "</td><td>" & vbTab &_
                                               rs.Fields("backup_finish_date") & "</td></tr>" & vbcrlf
                    rs.MoveNext
                        Loop
                        strResult = strResult & "</tbody></table>"
            testString = testString & vbcrlf & "<b>        <font color=" & htmlcolorcode & ">------------------- SQL SERVER LAST BACKUP DATES -----------------</font></b>"
            Wscript.Echo strStatus & strResult
            testString = testString & strResult
End Sub
 
Sub CheckTransactionlog ()
Dim cn, rs, sqlStr, strStatus, htmlcolorcode ,strResult
    Set cn = CreateObject ("ADODB.Connection")
            cn.Open "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=master" & ";Integrated Security=SSPI"
            Set rs = CreateObject ("ADODB.Recordset")
            htmlcolorcode = "#00ff00"
            strResult = vbcrlf & "<table border=1 cellpadding=2 cellspacing=2><tbody><tr><th>" &_
                                               "DBASE NAME" & vbTab & "</th><th>" & vbTab &_
                                               "TRANSACTION LOG Size (MB)"  & vbTab & "</th><th>" & vbTab &_
                                               "TRANSACTION LOG Usage (%)"  & "</th></tr>" & vbcrlf
            sqlStr = _
                        "DBCC SQLPERF (logspace)"
        Set rs = cn.Execute(sqlStr)
'                       rs.Open sqlStr, cn , 3 ,3
                        Do While Not rs.EOF
                    strResult = strResult &_
                                                                       "<td>" & rs.Fields("Database Name") & vbTab & "</td><td>" & vbTab &_
                                               rs.Fields("Log Size (MB)") & vbTab & "</td><td>" & vbTab &_
                                               rs.Fields("Log Space Used (%)")& "</td></tr>" & vbcrlf
                    rs.MoveNext
                        Loop
                        strResult = strResult & "</tbody></table>"
            testString = testString & vbcrlf & "<b>        <font color=" & htmlcolorcode & ">----------------- SQL SERVER TRANSACTION LOG INFO ----------------</font></b>"
            Wscript.Echo strStatus & strResult
            testString = testString & strResult
End Sub

I still have to implement some alerting on

  • the latest finished Database Backup date and time check
  • “Log Size” and “Log space used” of the transaction log check
  • 2003-11-24
    • Initial release: Downloaded from deadcat.net
  • 2006-05-20 bbsql-server-v2.0
    • Changed the SQL connection string. Authentication to a trusted server → No password information required in this script.
    • 32 bit/64 bit processor architecture check.
  • 2010-03-25 bbsql-server-v3.0
    • Check the latest finished Database Backup date and time
    • Check “Log Size” and “Log space used” of the transaction log.
  • 2010-04-16 bbsql-server-v3.0.1
    • Minor bug fix around line 122 (Thank you Richard Finegold) the script only opened the file if it exists, then deletes immediately after reading instead of after the variable assignment.
    • changed <td> to <th> in table headers (Thank you Richard Finegold)
  • monitors/bbsql-server.txt
  • Last modified: 2010/09/14 19:11
  • by 127.0.0.1