[hobbit] MS SQL hobbit monitoring
DNS
dns1407 at yahoo.com
Wed Jul 9 15:35:41 CEST 2008
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" <James.Roberts at hants.gov.uk>
To: hobbit at hswn.dk
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.xymon.com/pipermail/xymon/attachments/20080709/b1eed15a/attachment.html>
More information about the Xymon
mailing list