Why not? What's the problem?
----- Original Message ---- From: "Roberts, James" <James.Roberts at hants.gov.uk> To: hobbit at hswn.dk 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:dns1407 at yahoo.com] Sent: 09 July 2008 14:36 To: hobbit at hswn.dk 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" <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
Perhaps this will be helpful. I finally got around to giving this script a try today and made three changes:
- I had to run "winmgmt /resyncperf" to get the relevant perf counters registered with WMI.
- 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")
- 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.
From: DNS [mailto:dns1407 at yahoo.com] Sent: Thursday, July 10, 2008 8:32 AM To: hobbit at hswn.dk Subject: Re: [hobbit] MS SQL hobbit monitoring
Why not? What's the problem?
----- Original Message ---- From: "Roberts, James" <James.Roberts at hants.gov.uk> To: hobbit at hswn.dk 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:dns1407 at yahoo.com] Sent: 09 July 2008 14:36 To: hobbit at hswn.dk Subject: Re: [hobbit] MS SQL hobbit monitoring Yep, with the following vbs-script... ====Begin====== <long vbs snipped> ===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
participants (2)
-
dddugan＠iastate.edu
-
dns1407＠yahoo.com