[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [hobbit] Monitoring Sql Databases

To: hobbit (at) hswn.dk
Subject: Re: [hobbit] Monitoring Sql Databases
From: DNS <dns1407 (at) yahoo.com>
Date: Tue, 17 Jun 2008 22:41:25 -0700 (PDT)
Domainkey-signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=KPZronvuwyF/OHVJsFl8A8mjEZPo4/WA+MIeCyiMu2Xp+BTPhJXaOnuTVB5ZrALQqk9HVXQ+5JX3y4sUDIXvHJyv8vuZl81NkKzLR7t1t3JnX75YolQCcW7GEHvbhmtV0T+MXj3Jpd2lkGMyGjF+GXrTU6BWsqgVxevJHym5S18=;
Hi Nikesh,
I use the following vbscript to monitor MS SQL Servers....
====Begin======
On Error Resume Next
strAlarmState = "green"
strTestName = "sqlserver"
'Physical Disk Settings
    '% Disk Read Time
    iPerDiskReadTimeWarn=5
    iPerDiskReadTimeAlarm=10
    '% Disk Write Time
    iPerDiskWriteTimeWarn=5
    iPerDiskWriteTimeAlarm=10
    'Current Disk Queue Length
    iCurDiskQueueLengthWarn=10
    iCurDiskQueueLengthAlarm=20
'SQL Buffer Cache Hit Ratio
    iBufferCacheHitRatioWarn=90
    iBufferCacheHitRatioAlarm=85
'SQL Lock Timeouts /sec
    iLockTimeoutsWarn=50
    iLockTimeoutsAlarm=100   
'SQL Dead Locks /sec
    iDeadLocksWarn=10000
    iDeadLocksAlarm=100000
    
'User Connections
 iUserConnectionsWarn=150
 iUserConnectionsAlarm=300
    
' Master Database Settings
Set aMasterDatabaseSetting = CreateObject("scripting.dictionary")
aMasterDatabaseSetting.add "DataFileWarn", 150000
aMasterDatabaseSetting.add "DataFileAlarm", 200000
aMasterDatabaseSetting.add "LogFileWarn", 20000
aMasterDatabaseSetting.add "LogFileAlarm", 40000
' Model Database Settings
Set aModelDatabaseSetting = CreateObject("scripting.dictionary")
aModelDatabaseSetting.add "DataFileWarn", 150000
aModelDatabaseSetting.add "DataFileAlarm", 200000
aModelDatabaseSetting.add "LogFileWarn", 20000
aModelDatabaseSetting.add "LogFileAlarm", 40000
'Main Array
SET aSQLDataFileSettings = CreateObject("scripting.dictionary")
aSQLDataFileSettings.add "master", aMasterDatabaseSetting
aSQLDataFileSettings.add "model", aModelDatabaseSetting
strOutput   = ""
Set ws = WScript.CreateObject("WScript.Shell")
extPath = ws.RegRead("HKLM\SOFTWARE\BBWin\Output\")

' ========================================
' Main Code Starts Here
'Physical Disk
Set colDisk = GetObject("winmgmts:").InstancesOf("Win32_PerfRawData_PerfDisk_PhysicalDisk")
strOutput = strOutput & vbcrlf &"Physical Disk Information:" & vbcrlf
For each DiskInstance in ColDisk
    '% Disk Read Time
    strOutput = strOutput & CheckValue(GetWMIPercent("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","PercentDiskReadTime","PercentDiskReadTime_Base"),DiskInstance.name & " % Disk Read Time",iPerDiskReadTimeWarn,iPerDiskReadTimeAlarm)   
    '% Disk Write Time
    strOutput = strOutput & CheckValue(GetWMIPercent("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","PercentDiskWriteTime","PercentDiskWriteTime_Base"),DiskInstance.name & " % Disk Write Time",iPerDiskWriteTimeWarn,iPerDiskWriteTimeAlarm)
    'Current Disk Queue Length
    strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","CurrentDiskQueueLength"),DiskInstance.name & " Current Disk Queue Length",iCurDiskQueueLengthWarn,iCurDiskQueueLengthAlarm)
Next
' SQL Server
strOutput = strOutput & vbcrlf & "SQL Server Information:" & vbcrlf
    'Buffer Cache Hit Ration
strOutput = strOutput & CheckReverseValue(GetWMIPercent("Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager","@","Buffercachehitratio","Buffercachehitratio_Base"),"Buffer Cache Hit Ratio",iBufferCacheHitRatioWarn, iBufferCacheHitRatioAlarm)
    'Lock Information
' strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","LockTimeoutsPersec"),"Locks Timeouts/sec",iLockTimeoutsWarn, iLockTimeoutsAlarm)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","NumberofDeadlocksPersec"),"Number of Deadlocks/sec",iDeadLocksWarn, iDeadLocksAlarm)
    'User Connections
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics","@","UserConnections"),"Number of User Connections",iUserConnectionsWarn, iUserConnectionsAlarm)

'SQL Data and Log Files
Set colDatabases = GetObject("winmgmts:").InstancesOf("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases")
strOutput = strOutput & vbcrlf & "SQL Server Data and Log File Information:" & vbcrlf
For each DatabaseInstance in colDatabases
    If aSQLDataFileSettings.exists(DatabaseInstance.name) then
        'Check Values
        strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases","""" & DatabaseInstance.name & """","DataFilesSizeKB"),DatabaseInstance.name & vbTab & "Data File Size(KB)",aSQLDataFileSettings.item(DatabaseInstance.name).item("DataFileWarn"), aSQLDataFileSettings.item(DatabaseInstance.name).item("DataFileAlarm"))
        strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases","""" & DatabaseInstance.name & """","LogFilesSizeKB"),DatabaseInstance.name & vbTab & "Log File Size(KB)",aSQLDataFileSettings.item(DatabaseInstance.name).item("LogFileWarn"), aSQLDataFileSettings.item(DatabaseInstance.name).item("LogFileAlarm"))
        strOutput = strOutput & vbcrlf
    elseif DatabaseInstance.Name = "_Total" then
        ' Skipping Display of Totals at this stage. May re-add later
        
        ' strOutput = strOutput & vbcrlf & "Total Data File Size:" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","DataFilesSizeKB") & vbcrlf
        ' strOutput = strOutput & vbcrlf & "Total Log File Size:" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","LogFilesSizeKB") & vbcrlf
    else
        ' Write Data Information Directly Out without running the Value Check
        ' Datafile
        strOutput = strOutput & "&clear" & " " & DatabaseInstance.name & vbTab & "Data File Size(KB)" & ":" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","DataFilesSizeKB") & vbcrlf
        ' Logfile
        strOutput = strOutput & "&clear" & " " & DatabaseInstance.name & vbTab & "Log File Size(KB)" & ":" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","LogFilesSizeKB") & vbcrlf
        strOutput = strOutput & vbcrlf
    end if
Next

' Write the file for BB
WriteFile extPath, strTestName, strAlarmState, strOutput
'===========================================================
' FUNCTIONS and SUBS start here
' This is used to get a percentage value from WMI. It requires the value and the base objects.
' It then returns the percentage
FUNCTION GetWMIPercent(strCollection,strInstance,strObject,strBaseObject)
    SET counterCollection = GETOBJECT("winmgmts:" & strCollection & "=" & strInstance)
    FOR EACH cntproperty IN counterCollection.properties_
        IF cntproperty.name = strObject THEN
            iObjectValue = cntproperty
        ELSEIF cntproperty.name = strBaseObject THEN
            iObjectBaseValue = cntproperty
        END IF
    NEXT
    GetWMIPercent = ROUND(CDBL(iObjectValue) / CDBL(iObjectBaseValue) * CDBL(100),0)
END FUNCTION
' This is used to pull a value from WMI.
FUNCTION GetWMIValue(strCollection,strInstance,strObject)
    Set counterCollection = GetObject("winmgmts:" & strCollection & "=" & strInstance)
    FOR EACH cntproperty IN counterCollection.properties_
        IF cntproperty.name = strObject THEN
            iObjectValue = cntproperty
        END IF
    NEXT
    GetWMIValue = iObjectValue
END FUNCTION
' This is used to check the actual value against the warning and alarm.
FUNCTION CheckValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue) 
    IF iWarnValue > iAlarmValue THEN
        CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"
        IF strAlarmState <> "red" THEN
            strAlarmState = "red"
        END IF
    ELSE
        IF iObjectValue > iWarnValue THEN
            IF iObjectValue > iAlarmValue THEN
                CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
                SetAlarmStatus  "red"
            ELSE
                CheckValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
                SetAlarmStatus "yellow"
            END IF
        ELSE
            CheckValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
        END IF
    END IF
END FUNCTION
' This is used to check the actual value against the warning and alarm.
' This one the alarm will be a lower value than the warning. (Values Decrease rather than increase)
FUNCTION CheckReverseValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue) 
    IF iWarnValue < iAlarmValue THEN
        CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"
        IF strAlarmState <> "red" THEN
            strAlarmState = "red"
        END IF
    ELSE
        IF iObjectValue < iWarnValue THEN
            IF iObjectValue < iAlarmValue THEN
                CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
                SetAlarmStatus  "red"
            ELSE
                CheckReverseValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
                SetAlarmStatus "yellow"
            END IF
        ELSE
            CheckReverseValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf
        END IF
    END IF
END FUNCTION

' This is called to set the overall alarm status.
SUB SetAlarmStatus(strnewAlarmState)
    IF strnewAlarmState = "red" THEN
        strAlarmState = strnewAlarmState
    ELSEIF strnewAlarmState = "yellow" THEN
        IF strAlarmState <> "red" THEN
            strAlarmState = strnewAlarmState
        END IF
    END IF
END SUB

' This SUB is used for outputting the file to the external's directory in bb
SUB WriteFile(strExtPath, strTestName, strAlarmState, strOutput)
    Set fso = CreateObject("Scripting.FileSystemObject") 
    strOutput = strAlarmState & " " & Date & " " & Time & vbcrlf & vbcrlf & strOutput & vbcrlf
    Set f = fso.OpenTextFile(strExtPath & "\" & strTestName , 8 , TRUE)
    f.Write strOutput
    f.Close
    Set fso = Nothing
END SUB
===End====

In the registry of the Windows host you have to add:
[HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\Output]
@="C:\\Program Files\\BBWin\\tmp"
Success....
Kind regards,
DNS
----- Original Message ----
From: Nikesh Maharaj <NMaharaj (at) tcta.co.za>
To: hobbit (at) hswn.dk
Sent: Tuesday, June 17, 2008 11:29:35 AM
Subject: RE: [hobbit] Monitoring Sql Databases

Hi, 

I just want to monitor sql2000 and sql2005 databases for sizes , jobs
etc. will this script help ?

-----Original Message-----
From: Buchan Milne [mailto:bgmilne (at) staff.telkomsa.net] 
Sent: 17 June 2008 11:08 AM
To: hobbit (at) hswn.dk
Cc: Nikesh Maharaj
Subject: Re: [hobbit] Monitoring Sql Databases

On Tuesday 17 June 2008 08:52:27 Nikesh Maharaj wrote:
> Hi Guys,
>
> I am aware that Hobbit can monitor and report on Sql Databases. Please
> if I may ask, can someone who has this working already, please assist
me
> or point me in the direction I can get this implemented on my Hobbit
> monitor ?

You may want to be more specific on the database in question.

We use dbcheck from http://sourceforge.net/projects/hobbit-perl-cl/ to
monitor 
Oracle and MySQL.

Regards,
Buchan



To unsubscribe from the hobbit list, send an e-mail to
hobbit-unsubscribe (at) hswn.dk
Follow-Ups:
- Service smtp on xxxxxx is not OK : Unexpected service response
  - From: Andrew Clarke
Prev by Date: Hobbit Graphing Question:
Next by Date: cont test && badTest
Previous by thread: Hobbit Graphing Question:
Next by thread: Service smtp on xxxxxx is not OK : Unexpected service response
Index(es):
- Date
- Thread