MS SQL hobbit monitoring

5 messages in this thread

list James Roberts · Wed, 9 Jul 2008 11:26:12 +0100 ·

Has anyone had any success from using hobbit to monitor ms sql server?

Thanks

list Dns · Wed, 9 Jul 2008 06:35:41 -0700 (PDT) ·

Yep, with the following vbs-script...
====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"

▸ quoted from James Roberts


 


----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Wednesday, July 9, 2008 12:26:12 PM
Subject: [hobbit] MS SQL hobbit monitoring


Has anyone had any success from using hobbit to monitor ms sql server? 
Thanks

list James Roberts · Thu, 10 Jul 2008 13:48:51 +0100 ·

this didnt work for me...has anyone else got any ideas?

▸ quoted from Dns

 

From: DNS [mailto:user-08e747311acb@xymon.invalid] 
Sent: 09 July 2008 14:36
To: user-ae9b8668bcde@xymon.invalid
Subject: Re: [hobbit] MS SQL hobbit monitoring


Yep, with the following vbs-script...

 
====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_PhysicalD
isk")

▸ quoted from Dns

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


<http://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.na
me & " % Disk Write Time",iPerDiskWriteTimeWarn,iPerDiskWriteTimeAlarm)

▸ quoted from Dns

    '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_SQLServer
BufferManager","@","Buffercachehitratio","Buffercachehitratio_Base"),"Bu
ffer Cache Hit Ratio",iBufferCacheHitRatioWarn,
iBufferCacheHitRatioAlarm)
    'Lock Information
' strOutput = strOutput &
CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Nam
e","""_Total""","LockTimeoutsPersec"),"Locks
Timeouts/sec",iLockTimeoutsWarn, iLockTimeoutsAlarm)
strOutput = strOutput &
CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Nam
e","""_Total""","NumberofDeadlocksPersec"),"Number of
Deadlocks/sec",iDeadLocksWarn, iDeadLocksAlarm)
    'User Connections
strOutput = strOutput &
CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralSt
atistics","@","UserConnections"),"Number of User
Connections",iUserConnectionsWarn, iUserConnectionsAlarm)

'SQL Data and Log Files
Set colDatabases =
GetObject("winmgmts:").InstancesOf("Win32_PerfRawData_MSSQLSERVER_SQLSer
verDatabases")

▸ quoted from Dns

strOutput = strOutput & vbcrlf & "SQL Server Data and Log File
Information:" & vbcrlf
For each DatabaseInstance in colDatabases
    If aSQLDataFileSettings.exists(DatabaseInstance.name


<http://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("DataFil
eWarn"),
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("LogFile
Warn"),

▸ quoted from Dns

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 <http://cntproperty.name/>;  = strObject THEN

▸ quoted from Dns

            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"


----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Wednesday, July 9, 2008 12:26:12 PM
Subject: [hobbit] MS SQL hobbit monitoring


Has anyone had any success from using hobbit to monitor ms sql server? 

Thanks

list Dns · Thu, 10 Jul 2008 06:31:52 -0700 (PDT) ·

Why not? What's the problem?

▸ quoted from James Roberts



----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Thursday, July 10, 2008 2:48:51 PM
Subject: RE: [hobbit] MS SQL hobbit monitoring


this didnt work for me...has anyone else got any ideas?
 


From: DNS [mailto:user-08e747311acb@xymon.invalid] 
Sent: 09 July 2008 14:36
To: user-ae9b8668bcde@xymon.invalid
Subject: Re: [hobbit] MS SQL hobbit monitoring


Yep, with the following vbs-script...
 
====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"

 
 


----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Wednesday, July 9, 2008 12:26:12 PM
Subject: [hobbit] MS SQL hobbit monitoring


Has anyone had any success from using hobbit to monitor ms sql server? 
Thanks

list Darin D [eit] Dugan · Tue, 15 Jul 2008 15:49:09 -0500 ·

Perhaps this will be helpful. I finally got around to giving this script a try today and made three changes:

1. I had to run "winmgmt /resyncperf" to get the relevant perf counters registered with WMI.
2. Instead of adding an additional registry key for the output I changed the line to read BBWin's tmppath key:
	extPath = ws.RegRead("HKLM\SOFTWARE\BBWin\tmppath")
3. I put in explicit casts on iObjectValue, iWarnValue and iAlarmValue in CheckValue and CheckReverseValue. Prior to doing this some of my counters went red even though they were well below (or above) the warn threshold. Digging for a while I found the comparisons were failing until I used "Int(iObjectValue)", etc.

With some additional graphing I think this would be a great addition to the shire. I see an older version is posted at deadcat.
Cheers.

▸ quoted from Dns

From: DNS [mailto:user-08e747311acb@xymon.invalid] 
Sent: Thursday, July 10, 2008 8:32 AM
To: user-ae9b8668bcde@xymon.invalid
Subject: Re: [hobbit] MS SQL hobbit monitoring

Why not? What's the problem? 

----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Thursday, July 10, 2008 2:48:51 PM
Subject: RE: [hobbit] MS SQL hobbit monitoring
this didnt work for me...has anyone else got any ideas?

From: DNS [mailto:user-08e747311acb@xymon.invalid] 
Sent: 09 July 2008 14:36
To: user-ae9b8668bcde@xymon.invalid
Subject: Re: [hobbit] MS SQL hobbit monitoring
Yep, with the following vbs-script...

====Begin======


<long vbs snipped>

▸ quoted from Dns

===End====

In the registry of the Windows host you have to add:
[HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\Output]
@="C:\\Program Files\\BBWin\\tmp"
 
 
----- Original Message ----
From: "Roberts, James" <user-d7df29bbad83@xymon.invalid>
To: user-ae9b8668bcde@xymon.invalid
Sent: Wednesday, July 9, 2008 12:26:12 PM
Subject: [hobbit] MS SQL hobbit monitoring
Has anyone had any success from using hobbit to monitor ms sql server? 
Thanks

MS SQL hobbit monitoring 🔗 link

MS SQL hobbit monitoring