This object discovery detects all types of Microsoft Data Warehouse Appliances v2 and related servers.
Knowledge Base article:
Summary
This object discovery detects Microsoft Data Warehouse Appliances v2 and related servers. It collects information about the Microsoft Data Warehouse Appliance v2 type, name, and other common properties.
Sub HandleError(ByVal customMessage)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
If Not USE_ERROR_LOG Then
Wscript.Quit 0
Else
Wscript.Quit 1
End If
End If
End Sub
Sub HandleErrorInMonitoring(ByVal customMessage, ByRef oBag, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
oAPI.Return(oBag)
Wscript.Quit 0
End If
End Sub
Sub HandleErrorInDiscovery(ByVal customMessage, ByRef oDiscoveryData, ByRef oAPI)
Dim logger
If Err.number <> 0 Then
Set logger = new ScriptLogger
logger.LogError(customMessage)
oDiscoveryData.IsSnapshot = false
HandleDebugMessage("Discovery: IsSnapshot = false")
oAPI.Return(oDiscoveryData)
Wscript.Quit 0
End If
End Sub
Sub HandleDebugMessage(ByVal debugMessage)
Dim logger
Set logger = new ScriptLogger
logger.LogDebug(debugMessage)
End Sub
Private Sub Class_Initialize()
sourceLogEvent = "Managegement Group: " + ManagementGroupName + ". Script: " + WScript.ScriptName
Set oAPI = CreateObject("MOM.ScriptAPI")
End Sub
Private Function LogEvent(ByVal message, ByVal eventType, ByVal scriptEventID)
On Error Resume Next
Call oAPI.LogScriptEvent(sourceLogEvent, scriptEventID, eventType, message)
End Function
Public Function LogDebug(ByVal message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
Call oAPI.LogScriptEvent(sourceLogEvent, DEBUG_SCRIPT_EVENT_ID, INFO_EVENT_TYPE, message)
End If
End Function
Public Function LogWarning(ByVal message, ByVal eventID)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
LogEvent message, WARNING_EVENT_TYPE, eventID
End Function
Public Function LogError(ByVal customMessage)
Dim message
If Err.number <> 0 Then
message = Replace(" Error Number: #P1# " & VbCrLf & " Description: #P2# ", "#P1#", CStr(Err.number and 65535) )
message = Replace(message, "#P2#", Err.Description )
message = customMessage & VbCrLf & message & VbCrLf
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
If USE_EVENT_LOG Then
LogEvent message, ERROR_EVENT_TYPE, SCRIPT_EVENT_ID
End If
If USE_ERROR_LOG Then
WScript.StdErr.WriteLine message
End If
End If
End Function
End Class
Function IsStringEmpty(ByVal sValue)
If sValue = EMPTY Or Trim(sValue) = "" Then
IsStringEmpty = true
Else
IsStringEmpty = false
End If
End Function
' ##### ..\Scripts\Common\AdoDB.vbs
'This class is wrapper for ADODB typical operations (Connect and Run SQL query)
Class SQLQuery
PRIVATE adodbConnection
Private Sub Class_Initialize()
On Error Resume Next
Set adodbConnection = CreateObject("ADODB.Connection")
Call HandleError("Cannot create ADODB.Connection")
End Sub
Private Sub Class_Terminate()
On Error Resume Next
'adodbConnection.State = 0 - Closed
If adodbConnection.State > 0 Then
Call adodbConnection.Close()
End If
Call HandleError("Cannot close ADODB.Connection")
End Sub
'This method create and open connection to sql Server
'<connectionString> - name of the server and instance, e.g. "server1.mydomain.com\Instance1";
'use only server name to connect to default instance
Public Sub Connect(ByVal connectionString, ByVal databaseName)
On Error Resume Next
Dim providerString
providerString = "Provider=SQLOLEDB;Server=" & EscapeConnStringValue(connectionString) & ";Database="& EscapeConnStringValue(databaseName) &";Trusted_Connection=yes"
Call adodbConnection.Open(providerString)
End Sub
'This function execute <query> with <parameters> and returns ADODB.RecordSet
'<parameters> must be array, and must be with the same order as sql query <?> paremetrs
Public Function ExecuteQueryToRecordSet(ByVal query, ByVal parameters)
Dim adodbCommand, oResults
Set adodbCommand = CreateObject("ADODB.Command")
Call HandleError("Cannot create ADODB.Command")
adodbCommand.ActiveConnection = adodbConnection
adodbCommand.CommandText = query
Dim i, dataType, dataSize, parameter, length
length = UBound(parameters)
'Put parameters in to ADODB.Command
For i=0 To length
parameter = parameters(i)
dataType = GetDataTypeEnum(parameter)
dataSize = GetDataTypeSize(parameter)
adodbCommand.Parameters.Append(adodbCommand.CreateParameter("p"&i, dataType, 1, dataSize, parameter)) '1 - this is adParamInput in ADODB. see http://www.w3schools.com/ado/met_comm_createparameter.asp#parameterdirenum
Next
Set ExecuteQueryToRecordSet = adodbCommand.Execute()
End Function
'returns size of string or 0 for others types
Private Function GetDataTypeSize(ByVal vValue)
If TypeName(vValue)="String" Then
GetDataTypeSize = Len(vValue)
Else
GetDataTypeSize = 0
End If
End Function
'this function returns int value of some DataTypeEnum
'values of adodb types see on http://www.w3schools.com/ado/met_comm_createparameter.asp#datatypeenum
Private Function GetDataTypeEnum(ByVal vValue)
If TypeName(vValue) = "Byte" Then
GetDataTypeEnum = 2 ' adSmallInt=2
End If
If TypeName(vValue)="Integer" or TypeName(vValue)="Long" Then
GetDataTypeEnum = 3 ' adInteger=3
End If
If TypeName(vValue)="Double" Then
GetDataTypeEnum = 5 ' adDouble=5
End If
If TypeName(vValue)="String" Then
GetDataTypeEnum = 8 ' adBSTR = 8
End If
End Function
'This function escapes string value for connection string
Private Function EscapeConnStringValue (ByVal strValue)
On Error Resume Next
EscapeConnStringValue = "{" + Replace(strValue, "}", "}}") + "}"
End Function
End Class
' ##### ..\Scripts\Common\WMIRegestry.vbs
'' Provides Regestry access via WMI
''
'' Important : This vbs module requires Common.vbs!!!
'This class can connect to wmi and call StdRegProv methods
Class Registry
'Standard Hives
Public HKEY_CLASSES_ROOT
Public HKEY_CURRENT_USER
Public HKEY_LOCAL_MACHINE
Public HKEY_USERS
Public HKEY_CURRENT_CONFIG
'Results of calling some StdRegProv methods
Public ERROR_ACCESS_DENIED
Public ERROR_KEY_NOT_FOUND
Public ERROR_VALUE_NOT_FOUND
Public SUCCESS
'connect to WMI
Public Sub Connect(ByVal sHostName)
On Error Resume Next
Set oReg = GetObject("winmgmts://" & sHostName & "/root/default:StdRegProv")
Call HandleError("Cannot connect to WMI on " & sHostName)
End Sub
'Getter of Hive property
' Hive is root folder in Regestry (for example HKLM\)
Public Property Get Hive()
Hive = m_lHive
End Property
'Setter Hive
Public Property Let Hive(ByVal lHive)
m_lHive = lHive
End Property
'This method reads all subkeys and returns it
Public Function EnumKey(ByVal sKeyPath, ByRef lResult)
Dim sNames
lResult = oReg.EnumKey(m_lHive, sKeyPath, sNames)
EnumKey = sNames
End Function
'This method reads all key values and returns it
Public Function EnumValues(ByVal sKeyPath, ByRef lResult)
Dim aNames
Dim aTypes
lResult = oReg.EnumValues(m_lHive, sKeyPath, aNames, aTypes)
EnumValues = aNames
End Function
Public Function ReadStringValue(ByVal sKeyPath, ByVal sValueName, ByRef lResult)
Dim sValue
lResult = oReg.GetStringValue(m_lHive, sKeyPath, sValueName, sValue)
ReadStringValue = sValue
End Function
Public Function WriteStringValue(ByVal sKeyPath, ByVal sValueName, ByVal sValue)
WriteStringValue = oReg.SetStringValue(m_lHive, sKeyPath, sValueName, sValue)
End Function
'This method cheks what regestry key with [sKeyPath] is exists
Public Function KeyExists(ByVal sKeyPath)
On Error Resume Next
Dim aSubfolders
Dim result
result = oReg.EnumKey(m_lHive, sKeyPath, aSubfolders)
if result = SUCCESS Then
KeyExists = true
Else
KeyExists = false
End If
End Function
End Class
' ##### Scripts\MDWApplianceDiscovery.vbs
On Error Resume Next
Dim SQL_SERVER_APPLIANCE_PATH
SQL_SERVER_APPLIANCE_PATH = SQL_SERVER_APPLIANCE & BACKSLASH
Dim APPLIANCE_PATH
APPLIANCE_PATH = "Software\Microsoft\" & SQL_SERVER_APPLIANCE_PATH
Const MSG_MISSED_OR_EMPTY_KEY = "Registry entry doesn't exist or empty: "
Const MSG_CANNOT_READ_FROM_REG_KEY = "Cannot read value from registry key"
Const QUOTE = """"
Dim SourceID
Dim ManagedEntityId
Dim PrincipalName
Dim oShell
Dim oRegistry
Dim oAPI
Dim logger
Set oShell = CreateObject("WScript.Shell")
Call HandleError("Cannot create WScript.Shell object")
Set oAPI = CreateObject("MOM.ScriptAPI")
Call HandleError("Cannot create MOM.ScriptAPI object")
Set oRegistry = new Registry
Set logger = new ScriptLogger
Dim oDiscoveryData
Call Main()
'This function try read key from registry
Function TryReadValue(ByVal key)
On Error Resume Next
Dim lResult
TryReadValue = oRegistry.ReadStringValue(APPLIANCE_PATH, key, lResult)
If lResult = oRegistry.ERROR_KEY_NOT_FOUND or lResult = oRegistry.ERROR_VALUE_NOT_FOUND Then
TryReadValue = empty
End If
If lResult = oRegistry.ERROR_ACCESS_DENIED Then
Call HandleErrorInDiscovery("Access Denied to WMI on " & PrincipalName, oDiscoveryData, oAPI)
Else
Call HandleErrorInDiscovery(MSG_CANNOT_READ_FROM_REG_KEY, oDiscoveryData, oAPI)
End If
End Function
'Detect if computer is Appliance Server by registry Path
'Return True if path is found, False if not
Function IsAppliance()
On Error Resume Next
IsAppliance = oRegistry.KeyExists(APPLIANCE_PATH)
Call HandleErrorInDiscovery("Cannot access appliance registry key", oDiscoveryData, oAPI)
End Function
'Set specific discovery property in MOM regKey for exlude this Server form GenericDiscovery
Sub SetSpecificDiscovered()
On Error Resume Next
Dim registryKey, specificDiscovery
registryKey = oAPI.GetScriptStateKeyPath(SQL_SERVER_APPLIANCE)' & BACKSLASH & SPECIFIC_DISCOVERY_KEY
specificDiscovery = oRegistry.WriteStringValue(registryKey, SPECIFIC_DISCOVERY_KEY, "true")
Call HandleErrorInDiscovery("Cannot write in registry Specific Discovered key", oDiscoveryData, oAPI)
End Sub
'Main method
Sub Main()
On Error Resume Next
Dim oParams
Set oParams = WScript.Arguments
SourceID = oParams(0)
Call HandleError("Cannot read SourceID from params")
ManagedEntityId = oParams(1)
Call HandleError("Cannot read ManagedEntityId from params")
PrincipalName = oParams(2)
Call HandleError("Cannot read PrincipalName from params")
Set oParams = Nothing
oRegistry.Connect(PrincipalName)'Connect to WMI service
Call DoServiceDiscovery()
End Sub
'Method for discovery data
Sub DoServiceDiscovery()
On Error Resume Next
Call HandleDebugMessage("Begin discovery")
Set oDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
Call HandleError("Cannot create Discovery Data object")
'If this machine is appliance and not need special discovery
If IsAppliance() Then
Call DiscoverApplianceServer(oDiscoveryData)
Call HandleErrorInDiscovery("Cannot discover Appliance", oDiscoveryData, oAPI)
End If
Call oAPI.Return(oDiscoveryData)
Call HandleDebugMessage("Discovery successfully complete")
End Sub
Function GetSQLMemoryMaxSize(ConnectionString)
On Error Resume Next
Const EnableAdvancedOptions = "EXEC sp_configure 'show advanced options', 1;RECONFIGURE"
Const GetMemoryMaxSize = "EXEC sp_configure 'max server memory (MB)'"
Const DisableAdvancedOptions = "EXEC sp_configure 'show advanced options', 0;RECONFIGURE"
Dim sql : Set sql = new SQLQuery
Call sql.Connect(ConnectionString, "master")
Call HandleErrorInDiscovery("Cannot connect to SQL Server with connection string: " & ConnectionString, oDiscoveryData, oAPI)
Call sql.ExecuteQueryToRecordSet(EnableAdvancedOptions, Array())
Dim recordSet : Set recordSet = sql.ExecuteQueryToRecordSet(GetMemoryMaxSize, Array())
Call sql.ExecuteQueryToRecordSet(DisableAdvancedOptions, Array())
Call HandleErrorInDiscovery("Cannot get memory max value for " & ConnectionString, oDiscoveryData, oAPI)
GetSQLMemoryMaxSize = recordSet(4)
End Function
'Method discovered appliance and servers
Sub DiscoverApplianceServer(ByRef oDiscoveryData)
Dim applianceID, applianceType, applianceName, applianceVersion
Dim computerRole, applianceManufacturer, applianceModel
Dim path
applianceType = TryReadValue(TYPE_KEY)
'If Type is not defined in Registry - this is not correctly configuration
' and we fire event in event log and finish discovery
If IsStringEmpty(applianceType) Then
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & TYPE_KEY & QUOTE, PROPERTY_MISSED_OR_EMPTY_EVENT_ID)
oDiscoveryData.IsSnapshot = false
Exit Sub
End If
'If type of Server where we reag from registry is not MDW appliance, just exit from this disco
If applianceType <> MDW_TYPE Then
Exit Sub
End If
applianceVersion = TryReadValue(VERSION_KEY)
If IsStringEmpty(applianceVersion) Then
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & VERSION_KEY & QUOTE, PROPERTY_MISSED_OR_EMPTY_EVENT_ID)
oDiscoveryData.IsSnapshot = false
Exit Sub
End If
'If Minor and Majour version of MDW Appliance is not equal 1.0. or 2.0.
'we do not discover this appliance
Dim minorMajorVersion
minorMajorVersion = Mid(applianceVersion, 1, 4)
If minorMajorVersion <> "2.0." Then
Call HandleDebugMessage("Version of MDW is not 2.0")
Exit Sub
End If
applianceID = TryReadValue(ID_KEY)
If IsStringEmpty(applianceID) Then
oDiscoveryData.IsSnapshot = false
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & ID_KEY & QUOTE, ID_MISSED_EVENT_ID)
Exit Sub
End If
applianceName = PrincipalName
computerRole = TryReadValue(ROLE_KEY)
If IsStringEmpty(computerRole) Then
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & ROLE_KEY & QUOTE, PROPERTY_MISSED_OR_EMPTY_EVENT_ID)
End If
applianceManufacturer = TryReadValue(MANUFACTURER_KEY)
If IsStringEmpty(applianceManufacturer) Then
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & MANUFACTURER_KEY & QUOTE, PROPERTY_MISSED_OR_EMPTY_EVENT_ID)
End If
applianceModel = TryReadValue(MODEL_KEY)
If IsStringEmpty(applianceModel) Then
Call logger.LogWarning(MSG_MISSED_OR_EMPTY_KEY & QUOTE & MODEL_KEY & QUOTE, PROPERTY_MISSED_OR_EMPTY_EVENT_ID)
End If
Dim oAppliance
Dim oRelationship
Dim sqlMemoryMaxSize : sqlMemoryMaxSize = GetSQLMemoryMaxSize(applianceName)
Dim oServer
Set oServer = oDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServerAppliance.MDW2.Server']$")
Set oAppliance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServerAppliance.MDW2.Appliance']$")
Home