monitors:bbsql-server

no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


—monitors:bbsql-server [2010/09/14 19:11] (current) – created - external edit 127.0.0.1
Line 1: Line 1:
 +====== monitor script for Microsoft SQL server 2000 and up ======
 +
 +^ Author | [[ bart.gillis@cegeka.be| Bart Gillis]] |
 +^ Compatibility | Xymon 4.2 |
 +^ Requirements | BBWin 0.12  |
 +^ Download | None |
 +^ Last Update | 2010-03-25|
 +
 +===== Description =====
 + 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
 +
 +===== Installation =====
 +=== 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)
 +==== bbsql-server.vbs ====
 +<hidden onHidden="Show Code ?" onVisible="Hide Code ?">
 +<code vb>
 +' 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
 +</code>
 +</hidden>
 +
 +===== Known  Bugs and Issues =====
 +
 +===== To Do =====
 +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
 +
 +===== Credits =====
 +
 +===== Changelog =====
 +
 +  * **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