monitor script for Microsoft SQL server 2000 and up

' 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