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