<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE type=text/css>DIV {
MARGIN: 0px
}
</STYLE>
<META content="MSHTML 6.00.3790.3123" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=013304812-10072008><FONT face=Arial
color=#0000ff size=2>this didnt work for me...has anyone else got any
ideas?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=013304812-10072008></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> DNS [mailto:dns1407@yahoo.com]
<BR><B>Sent:</B> 09 July 2008 14:36<BR><B>To:</B>
hobbit@hswn.dk<BR><B>Subject:</B> Re: [hobbit] MS SQL hobbit
monitoring<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">
<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>