Доброго времени суток!
Исходные данные: есть скрипт на vbs, который собирает статистические данные SQLServer (через WMI), но по умолчанию, статистика собирается и выводится в файл для основного экземпляра SQLServer a mssql-01, но статистика необходима для дополнительного экземпляра на том же сервере, который указывается mssql-01\SPS. Может кто-нибудбь подскажет, как модифицировать скрипт, чтобы выдавалась скриптом статистические данные для mssql-01\SPS.

Скрипт:

'==========================================================================
'
' VBScript Source File
'
' NAME: bb-sqlserver.vbs
'
' AUTHOR: Oliver Bassett , gen-i
' DATE : 20/09/2003
'
' COMMENT: This script will check a range or parameters in SQL server
' via it's windows performance counters using WMI. It will then Warn
' or Alarm as configured and report the information back to the
' configured Big Brother Server. You require the Big Brother client
' to be already installed.
'
' NOTES: You may need to change the extPath setting which currently tries
' to read the Professional Edition Client Reg Key.
'
' LICENSE:
' Copyright (c) 2003, Gen-i Limited
' All rights reserved.
'
' Redistribution and use in source and binary forms, with or without
' modification, are permitted provided that the following conditions are met:
'
' - Redistributions of source code must retain the above copyright notice,
' this list of conditions and the following disclaimer.
' - Redistributions in binary form must reproduce the above copyright notice,
' this list of conditions and the following disclaimer in the documentation
' and/or other materials provided with the distribution.
' - Neither the name of Gen-i Limited nor the names of its contributors
' may be used to endorse or promote products derived from this software without
' specific prior written permission.
'
' THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
' EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
' OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT
' SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
' SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
' OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
' HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
' TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
' EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
'
' VERSION HISTORY:
' 0.1 - Initial Release
' 0.2 - Fixed UserConnections which were using the settings from
' Deadlocks rather than their own Warn/Alarm values
' Thanks to Erik Damsgaard, CSC Managed Web Services
' For pointing this out
'==========================================================================


strAlarmState = "green"
strTestName = "MSSQL"
'Physical Disk Settings
'% Disk Read Time
iPerDiskReadTimeWarn=5
iPerDiskReadTimeAlarm=10
'% Disk Write Time
iPerDiskWriteTimeWarn=5
iPerDiskWriteTimeAlarm=10
'Current Disk Queue Length
iCurDiskQueueLengthWarn=300
iCurDiskQueueLengthAlarm=400

'SQL Databases size
iDataFilesWarn= 5242880000 '5000G
iDataFilesAlarm= 6291456000 '6000G
iLogFilesWarn= 3674904174089560 '52428800 '50G
iLogFilesAlarm= 3674904174179560 '62914560 '60G

'SQL Buffer Cache Hit Ratio
iBufferCacheHitRatioWarn=40 'Запарило уже, периодически падает, никто не жалуется
iBufferCacheHitRatioAlarm=20
'SQL Lock Timeouts /sec
iLockTimeoutsWarn=9000000
iLockTimeoutsAlarm=9000000
'SQL Dead Locks /sec
iDeadLocksWarn=9000000
iDeadLocksAlarm=9000000

'User Connections
iUserConnectionsWarn=9000
iUserConnectionsAlarm=10500

'SQL Data and Log Files (KB)
'These settings are stored in an associative array per database and then all these
'associative arrays are stored in a Main one.
'To add a new set of database logs to be monitored:
'1. Create an Associative Array for it: Set a[DatabaseName]DatabaseSetting = CreateObject("scripting.dictionary")
'2. Add the warning and alarm levels for the monitoring: a[DatabaseName]DatabaseSetting.add "DataFileWarn", 10000
' The above will add a warning for the Data file and set it at 10000KB.
' You will need to add warnings and alarms for both the data file and the log file.
'3. Next you need to add this to the main array.
' The format is: aSQLDataFileSettings.add "[DatabaseName]", a[DatabaseName]DatabaseSetting
'
' Thats it, it should just work now.


' Master Database Settings
Set aMasterDatabaseSetting = CreateObject("scripting.dictionary")
aMasterDatabaseSetting.add "DataFileWarn", 200000
aMasterDatabaseSetting.add "DataFileAlarm", 250000
aMasterDatabaseSetting.add "LogFileWarn", 30000
aMasterDatabaseSetting.add "LogFileAlarm", 50000
aMasterDatabaseSetting.add "LogUsedWarn", 100
aMasterDatabaseSetting.add "LogUsedAlarm", 101

' Model Database Settings
Set aModelDatabaseSetting = CreateObject("scripting.dictionary")
aModelDatabaseSetting.add "DataFileWarn", 150000
aModelDatabaseSetting.add "DataFileAlarm", 200000
aModelDatabaseSetting.add "LogFileWarn", 25000
aModelDatabaseSetting.add "LogFileAlarm", 40000
aModelDatabaseSetting.add "LogUsedWarn", 100
aModelDatabaseSetting.add "LogUsedAlarm", 101


' Tempdb Database Settings
Set aTempDBDatabaseSetting = CreateObject("scripting.dictionary")
aTempDBDatabaseSetting.add "DataFileWarn", 23582912 '17G
aTempDBDatabaseSetting.add "DataFileAlarm", 27777216 '20G
aTempDBDatabaseSetting.add "LogFileWarn", 20480000 '20000M
aTempDBDatabaseSetting.add "LogFileAlarm", 40960000 '40000M
aTempDBDatabaseSetting.add "LogUsedWarn", 100
aTempDBDatabaseSetting.add "LogUsedAlarm", 110

' Msdb Database Settings
Set aMsDBDatabaseSetting = CreateObject("scripting.dictionary")
aMsDBDatabaseSetting.add "DataFileWarn", 2228800 '2.2G
aMsDBDatabaseSetting.add "DataFileAlarm", 2843200 '2.8
aMsDBDatabaseSetting.add "LogFileWarn", 409600 '400M
aMsDBDatabaseSetting.add "LogFileAlarm", 614400 '600M
aMsDBDatabaseSetting.add "LogUsedWarn", 100
aMsDBDatabaseSetting.add "LogUsedAlarm", 101


'' Pubs Database Settings
'Set aPubsDatabaseSetting = CreateObject("scripting.dictionary")
'aPubsDatabaseSetting.add "DataFileWarn", 150000
'aPubsDatabaseSetting.add "DataFileAlarm", 200000
'aPubsDatabaseSetting.add "LogFileWarn", 40000
'aPubsDatabaseSetting.add "LogFileAlarm", 80000
'aPubsDatabaseSetting.add "LogUsedWarn", 100
'aPubsDatabaseSetting.add "LogUsedAlarm", 101


'' MSSQLSystemResource
'Set aMSSQLSystemResourceDatabaseSetting = CreateObject("scripting.dictionary")
'aMSSQLSystemResourceDatabaseSetting.add "DataFileWarn", 150000
'aMSSQLSystemResourceDatabaseSetting.add "DataFileAlarm", 200000
'aMSSQLSystemResourceDatabaseSetting.add "LogFileWarn", 40000
'aMSSQLSystemResourceDatabaseSetting.add "LogFileAlarm", 80000
'aMSSQLSystemResourceDatabaseSetting.add "LogUsedWarn", 100
'aMSSQLSystemResourceDatabaseSetting.add "LogUsedAlarm", 101


'' Expert Database Settings
'Set aExpertdatabaseSetting = CreateObject("scripting.dictionary")
'aExpertDatabaseSetting.add "DataFileWarn", 31457280 '30G
'aExpertDatabaseSetting.add "DataFileAlarm", 41943040 '40G
'aExpertDatabaseSetting.add "LogFileWarn", 15728640 '15G
'aExpertDatabaseSetting.add "LogFileAlarm", 31457280 '30G
'aExpertDatabaseSetting.add "LogUsedWarn", 100
'aExpertDatabaseSetting.add "LogUsedAlarm", 101


'' TermAccess1DS
'Set aTermAccess1DSDatabaseSetting = CreateObject("scripting.dictionary")
'aTermAccess1DSDatabaseSetting.add "DataFileWarn", 150000
'aTermAccess1DSDatabaseSetting.add "DataFileAlarm", 200000
'aTermAccess1DSDatabaseSetting.add "LogFileWarn", 1000000
'aTermAccess1DSDatabaseSetting.add "LogFileAlarm",2000000
'aTermAccess1DSDatabaseSetting.add "LogUsedWarn", 100
'aTermAccess1DSDatabaseSetting.add "LogUsedAlarm", 101


'Main Array
SET aSQLDataFileSettings = CreateObject("scripting.dictionary")
aSQLDataFileSettings.add "master", aMasterDatabaseSetting
aSQLDataFileSettings.add "model", aModelDatabaseSetting
aSQLDataFileSettings.add "msdb", aMsDBDatabaseSetting
aSQLDataFileSettings.add "tempdb", aTempDBDatabaseSetting
aSQLDataFileSettings.add "pubs", aPubsDatabaseSetting
aSQLDataFileSettings.add "DIRECTUM", aDirectumDatabaseSetting
'aSQLDataFileSettings.add "mssqlsystemresource", aMSSQLSystemResourceDatabaseSetting





strOutput = ""
Set ws = WScript.CreateObject("WScript.Shell")
'extPath = ws.RegRead("HKLM\SOFTWARE\Quest Software\BigBrother\bbnt\ExternalPath\")
extPath = "C:\Program Files (x86)\BBWin\tmp"
'DatabaseInstance = "sadm-mssql-04\SPS"

' ========================================
' 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)
'MsgBox("OK")
'Lock Information
'strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","LockTimeoutsPersec"),"Locks Timeouts per 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)
'Size databases
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLSERVERDatabases.Name","""_Total""","DataFilesSizeKB"),"Total Data Files Size(KB)",iDataFilesWarn, iDataFilesAlarm)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLSERVERDatabases.Name","""_Total""","LogFilesSizeKB"),"Total Log Files Size(KB)",iLogFilesWarn, iLogFilesAlarm)



'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 & "<b><i>Database " & DatabaseInstance.name&"</b></i>" &vbcrlf
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 & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLSERVERDatabases","""" & DatabaseInstance.name & """","DataFilesSizeKB"),"Data File (KB)",aSQLDataFileSettings.item(DatabaseInstance.name).item("DataFileWarn"), aSQLDataFileSettings.item(DatabaseInstance.name).item("DataFileAlarm"))
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLSERVERDatabases","""" & DatabaseInstance.name & """","LogFilesSizeKB"),"Log File (KB)",aSQLDataFileSettings.item(DatabaseInstance.name).item("LogFileWarn"), aSQLDataFileSettings.item(DatabaseInstance.name).item("LogFileAlarm"))
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLSERVERDatabases","""" & DatabaseInstance.name & """","PercentLogUsed"),"Log used (%)",aSQLDataFileSettings.item(DatabaseInstance.name).item("LogUsedWarn"), aSQLDataFileSettings.item(DatabaseInstance.name).item("LogUsedAlarm"))

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 & "<b><i>Database " & DatabaseInstance.name&"</b></i>" &vbcrlf
strOutput = strOutput & "&clear" & " " & "Data File (KB)" & ":" & vbTab & GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases.Name","""" & DatabaseInstance.name & """","DataFilesSizeKB") & vbcrlf
strOutput = strOutput & "&clear" & " " & "Log File (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 Abs(iObjectValue) > iWarnValue THEN
IF Abs(iObjectValue) > iAlarmValue THEN
' MsgBox iObjectValue &">"& iAlarmValue
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 Abs(iObjectValue) < iWarnValue THEN
IF Abs(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