<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><P>Yep, with the following vbs-script...</P>
<P> </P>
<P>====Begin======<BR>On Error Resume Next<BR>strAlarmState = "green"<BR>strTestName = "sqlserver"<BR>'Physical Disk Settings<BR> '% Disk Read Time<BR> iPerDiskReadTimeWarn=5<BR> iPerDiskReadTimeAlarm=10<BR> '% Disk Write Time<BR> iPerDiskWriteTimeWarn=5<BR> iPerDiskWriteTimeAlarm=10<BR> 'Current Disk Queue Length<BR> iCurDiskQueueLengthWarn=10<BR> iCurDiskQueueLengthAlarm=20<BR>'SQL Buffer Cache Hit Ratio<BR> iBufferCacheHitRatioWarn=90<BR> iBufferCacheHitRatioAlarm=85<BR>'SQL Lock Timeouts /sec<BR> iLockTimeoutsWarn=50<BR> iLockTimeoutsAlarm=100 <BR>'SQL Dead Locks /sec<BR> iDeadLocksWarn=10000<BR> iDeadLocksAlarm=100000<BR> <BR>'User
Connections<BR> iUserConnectionsWarn=150<BR> iUserConnectionsAlarm=300<BR> <BR>' Master Database Settings<BR>Set aMasterDatabaseSetting = CreateObject("scripting.dictionary")<BR>aMasterDatabaseSetting.add "DataFileWarn", 150000<BR>aMasterDatabaseSetting.add "DataFileAlarm", 200000<BR>aMasterDatabaseSetting.add "LogFileWarn", 20000<BR>aMasterDatabaseSetting.add "LogFileAlarm", 40000<BR>' Model Database Settings<BR>Set aModelDatabaseSetting = CreateObject("scripting.dictionary")<BR>aModelDatabaseSetting.add "DataFileWarn", 150000<BR>aModelDatabaseSetting.add "DataFileAlarm", 200000<BR>aModelDatabaseSetting.add "LogFileWarn", 20000<BR>aModelDatabaseSetting.add "LogFileAlarm", 40000<BR>'Main Array<BR>SET aSQLDataFileSettings = CreateObject("scripting.dictionary")<BR>aSQLDataFileSettings.add "master", aMasterDatabaseSetting<BR>aSQLDataFileSettings.add "model", aModelDatabaseSetting<BR>strOutput = ""<BR>Set ws =
WScript.CreateObject("WScript.Shell")<BR>extPath = ws.RegRead("HKLM\SOFTWARE\BBWin\Output\")<BR><BR>' ========================================<BR>' Main Code Starts Here<BR>'Physical Disk<BR>Set colDisk = GetObject("winmgmts:").InstancesOf("Win32_PerfRawData_PerfDisk_PhysicalDisk")<BR>strOutput = strOutput & vbcrlf &"Physical Disk Information:" & vbcrlf<BR>For each DiskInstance in ColDisk<BR> '% Disk Read Time<BR> strOutput = strOutput & CheckValue(GetWMIPercent("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","PercentDiskReadTime","PercentDiskReadTime_Base"),<A href="http://diskinstance.name/" target=_blank><SPAN class=yshortcuts id=lw_1215610463_3><FONT color=#0000ff>DiskInstance.name</FONT></SPAN></A> & " % Disk Read Time",iPerDiskReadTimeWarn,iPerDiskReadTimeAlarm) <BR> '% Disk Write Time<BR> strOutput =
strOutput & CheckValue(GetWMIPercent("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","PercentDiskWriteTime","PercentDiskWriteTime_Base"),DiskInstance.name & " % Disk Write Time",iPerDiskWriteTimeWarn,iPerDiskWriteTimeAlarm)<BR> 'Current Disk Queue Length<BR> strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_PerfDisk_PhysicalDisk.Name","""" & DiskInstance.Name & """","CurrentDiskQueueLength"),DiskInstance.name & " Current Disk Queue Length",iCurDiskQueueLengthWarn,iCurDiskQueueLengthAlarm)<BR>Next<BR>' SQL Server<BR>strOutput = strOutput & vbcrlf & "SQL Server Information:" & vbcrlf<BR> 'Buffer Cache Hit Ration<BR>strOutput = strOutput & CheckReverseValue(GetWMIPercent("Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager","@","Buffercachehitratio","Buffercachehitratio_Base"),"Buffer Cache Hit
Ratio",iBufferCacheHitRatioWarn, iBufferCacheHitRatioAlarm)<BR> 'Lock Information<BR>' strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","LockTimeoutsPersec"),"Locks Timeouts/sec",iLockTimeoutsWarn, iLockTimeoutsAlarm)<BR>strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","NumberofDeadlocksPersec"),"Number of Deadlocks/sec",iDeadLocksWarn, iDeadLocksAlarm)<BR> 'User Connections<BR>strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics","@","UserConnections"),"Number of User Connections",iUserConnectionsWarn, iUserConnectionsAlarm)<BR><BR>'SQL Data and Log Files<BR>Set colDatabases = GetObject("winmgmts:").InstancesOf("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases")<BR>strOutput = strOutput & vbcrlf & "SQL Server Data and Log
File Information:" & vbcrlf<BR>For each DatabaseInstance in colDatabases<BR> If aSQLDataFileSettings.exists(<A href="http://databaseinstance.name/" target=_blank><SPAN class=yshortcuts id=lw_1215610463_4><FONT color=#0000ff>DatabaseInstance.name</FONT></SPAN></A>) then<BR> 'Check Values<BR> 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"))<BR> 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"))<BR> strOutput = strOutput & vbcrlf<BR> elseif DatabaseInstance.Name = "_Total" then<BR> ' Skipping Display of Totals at this stage. May re-add later<BR> <BR> ' strOutput = strOutput & vbcrlf & "Total Data File Size:" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","DataFilesSizeKB") & vbcrlf<BR> ' strOutput = strOutput & vbcrlf & "Total Log File Size:" & vbTab &
GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","LogFilesSizeKB") & vbcrlf<BR> else<BR> ' Write Data Information Directly Out without running the Value Check<BR> ' Datafile<BR> strOutput = strOutput & "&clear" & " " & DatabaseInstance.name & vbTab & "Data File Size(KB)" & ":" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","DataFilesSizeKB") & vbcrlf<BR> ' Logfile<BR> strOutput = strOutput & "&clear" & " " & DatabaseInstance.name & vbTab & "Log File Size(KB)" & ":" & vbTab &
GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","LogFilesSizeKB") & vbcrlf<BR> strOutput = strOutput & vbcrlf<BR> end if<BR>Next<BR><BR>' Write the file for BB<BR>WriteFile extPath, strTestName, strAlarmState, strOutput<BR>'===========================================================<BR>' FUNCTIONS and SUBS start here<BR>' This is used to get a percentage value from WMI. It requires the value and the base objects.<BR>' It then returns the percentage<BR>FUNCTION GetWMIPercent(strCollection,strInstance,strObject,strBaseObject)<BR> SET counterCollection = GETOBJECT("winmgmts:" & strCollection & "=" & strInstance)<BR> FOR EACH cntproperty IN counterCollection.properties_<BR> IF <A href="http://cntproperty.name/" target=_blank><SPAN
class=yshortcuts id=lw_1215610463_5><FONT color=#0000ff>cntproperty.name</FONT></SPAN></A> = strObject THEN<BR> iObjectValue = cntproperty<BR> ELSEIF cntproperty.name = strBaseObject THEN<BR> iObjectBaseValue = cntproperty<BR> END IF<BR> NEXT<BR> GetWMIPercent = ROUND(CDBL(iObjectValue) / CDBL(iObjectBaseValue) * CDBL(100),0)<BR>END FUNCTION<BR>' This is used to pull a value from WMI.<BR>FUNCTION GetWMIValue(strCollection,strInstance,strObject)<BR> Set counterCollection = GetObject("winmgmts:" & strCollection & "=" & strInstance)<BR> FOR EACH cntproperty IN counterCollection.properties_<BR> IF cntproperty.name =
strObject THEN<BR> iObjectValue = cntproperty<BR> END IF<BR> NEXT<BR> GetWMIValue = iObjectValue<BR>END FUNCTION<BR>' This is used to check the actual value against the warning and alarm.<BR>FUNCTION CheckValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue) <BR> IF iWarnValue > iAlarmValue THEN<BR> CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"<BR> IF strAlarmState <> "red" THEN<BR> strAlarmState = "red"<BR> END IF<BR> ELSE<BR> IF iObjectValue > iWarnValue
THEN<BR> IF iObjectValue > iAlarmValue THEN<BR> CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf<BR> SetAlarmStatus "red"<BR> ELSE<BR> CheckValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf<BR> SetAlarmStatus "yellow"<BR> END IF<BR>
ELSE<BR> CheckValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf<BR> END IF<BR> END IF<BR>END FUNCTION<BR>' This is used to check the actual value against the warning and alarm.<BR>' This one the alarm will be a lower value than the warning. (Values Decrease rather than increase)<BR>FUNCTION CheckReverseValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue) <BR> IF iWarnValue < iAlarmValue THEN<BR> CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"<BR> IF strAlarmState <> "red" THEN<BR> strAlarmState =
"red"<BR> END IF<BR> ELSE<BR> IF iObjectValue < iWarnValue THEN<BR> IF iObjectValue < iAlarmValue THEN<BR> CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf<BR> SetAlarmStatus "red"<BR> ELSE<BR> CheckReverseValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue &
vbcrlf<BR> SetAlarmStatus "yellow"<BR> END IF<BR> ELSE<BR> CheckReverseValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbcrlf<BR> END IF<BR> END IF<BR>END FUNCTION<BR><BR>' This is called to set the overall alarm status.<BR>SUB SetAlarmStatus(strnewAlarmState)<BR> IF strnewAlarmState = "red" THEN<BR> strAlarmState = strnewAlarmState<BR> ELSEIF strnewAlarmState = "yellow" THEN<BR> IF strAlarmState <> "red"
THEN<BR> strAlarmState = strnewAlarmState<BR> END IF<BR> END IF<BR>END SUB<BR><BR>' This SUB is used for outputting the file to the external's directory in bb<BR>SUB WriteFile(strExtPath, strTestName, strAlarmState, strOutput)<BR> Set fso = CreateObject("Scripting.FileSystemObject") <BR> strOutput = strAlarmState & " " & Date & " " & Time & vbcrlf & vbcrlf & strOutput & vbcrlf<BR> Set f = fso.OpenTextFile(strExtPath & "\" & strTestName , 8 , TRUE)<BR> f.Write strOutput<BR> f.Close<BR> Set fso = Nothing<BR>END SUB<BR>===End====<BR><BR>In the registry of the Windows host you have to add:<BR>[HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\Output]<BR>@="C:\\Program Files\\BBWin\\tmp"<BR></P>
<P> </P>
<P> </P>
<DIV><BR></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"><BR>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">----- Original Message ----<BR>From: "Roberts, James" <James.Roberts@hants.gov.uk><BR>To: hobbit@hswn.dk<BR>Sent: Wednesday, July 9, 2008 12:26:12 PM<BR>Subject: [hobbit] MS SQL hobbit monitoring<BR><BR>
<P><FONT face=Arial size=2>Has anyone had any success from using hobbit to monitor ms sql server?</FONT> </P>
<P><FONT face=Arial size=2>Thanks</FONT> </P></DIV></DIV></div><br>
</body></html>