Discovers properties of Microsoft System Center Data Warehouse Management Server
Knowledge Base article:
Summary
This discovery populates Service Manager DataWarehouse properties.Discovery runs immedietely after being imported followed by another run at 13:00, the sync time. Following this it runs at a 24 hour interval. Sync time, frequency as well as the timeout can be changed.Changing arguments is not recommended.
'*************************************************************************
' $ScriptName: "GetSqlDriverName" $
'
' Purpose: This script is used to get SQL Driver name.
'
' $File: GetSqlDriverName.vbs $
'*************************************************************************
Option Explicit
Function GetSqlDriverName
Dim oAPI1, oReg
Const HKEY_LOCAL_MACHINE = &H80000002
Dim strComputer, strDriverPath, strKeyPath, intValue, strValueName,arrSubKeys,oSubkey,strNewKeyPath,intDotIndex
strComputer = "."
strDriverPath="SQLOLEDB"
Set oReg=GetObject("winmgmts:\\" & _
strComputer & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\ODBC\ODBCINST.INI"
oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys
For Each oSubkey In arrSubKeys
intValue = InStr(oSubkey, "SQL Server Native Client")
if intValue <> 0 Then
strValueName = "Driver"
strNewKeyPath = strKeyPath&"\"&oSubkey
oReg.GetStringValue HKEY_LOCAL_MACHINE, strNewKeyPath, strValueName , strDriverPath
strDriverPath = Mid (strDriverPath, InStrRev(strDriverPath, "\")+1)
intDotIndex = InStr(strDriverPath, ".")
strDriverPath = Left(strDriverPath , intDotIndex-1)
end if
Next
GetSqlDriverName = strDriverPath
End Function
'*************************************************************************
' ScriptName: DwMgtServerPropDiscovery.vbs
'
' Purpose - Populate properties of DW Management Server
'
' Parameters - 0 - discovery id
' 1 - Target managed entity id
' 2 - Host Computer Principal Name
' 3 - Version
'*************************************************************************
SetLocale("en-us")
Dim sourceId,managedEntityId,oAPI,strKeyPath,principalName, version, oDiscoveryData
Set oAPI = CreateObject("MOM.ScriptAPI")
sourceId = WScript.Arguments(0)
managedEntityId = WScript.Arguments(1)
principalName = WScript.Arguments(2)
version = WScript.Arguments(3)
' Fetching Data Warehouse Management Group using WMI
Call oAPI.LogScriptEvent("DwMgtServerPropDiscovery.vbs", 3000, 4, "Starting discovery of Service Manager DataWarehouse Management Server.")
Set oDiscoveryData = oAPI.CreateDiscoveryData(0, sourceId, managedEntityId)
Dim oReg, iResult, arrSubKeys, managementGroupName
Set oReg = GetObject("winmgmts:\\" & principalName & "\root\default:StdRegProv")
If Err <> 0 Then
Call oAPI.LogScriptEvent("DwMgtServerPropDiscovery.vbs", 3000, 4, "Unable to bind to WMI registry provider on " & principalName & ". Terminating Discovery.")
Call oAPI.Return(oDiscoveryData)
WScript.Quit
End if
Const HKLM = &H80000002
If version = "2010" or version = "2012" then
iResult = oReg.Enumkey(HKLM, "SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Server Management Groups", arrSubKeys)
If iResult = 0 then
Call oAPI.LogScriptEvent("DwMgtServerPropDiscovery.vbs", 3000, 4, "Discovered SCSM " & version &" DataWarehouse Server Properties.")
Dim dwMgName,oMgtServerInst,oMgGroupInst,oRelMgGroupContainsMgServer,oSmAppInst,oRelSmContainsMg, arrRegValues, oRegDictionary, strKeyRoot, queryResult, allQueriesPassed, Value
dwMgName = arrSubKeys(0)
arrRegValues = Array("DataMartSQLInstance", "DataMartDatabaseName", "RepositorySQLInstance", "RepositoryDatabaseName", "DatabaseServerName", "DatabaseName")
Set oRegDictionary = CreateObject("Scripting.Dictionary")
strKeyRoot = "SOFTWARE\Microsoft\System Center\2010\Common\Database"
allQueriesPassed = True
For Each Value in arrRegValues
iResult = oReg.GetStringValue(HKLM, strKeyRoot, Value, queryResult)
If iResult = 0 then
oRegDictionary.item(Value) = queryResult
Else
allQueriesPassed = False
Exit For
End If
Next
If allQueriesPassed = True then
'Instantiate Data Warehouse Management Server instance
If version = "2010" then
Set oMgtServerInst = oDiscoveryData.CreateClassInstance("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.DwManagementServer.2010']$")
ElseIf version = "2012" then
Set oMgtServerInst = oDiscoveryData.CreateClassInstance("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.DwManagementServer.2012']$")
End If
Call oMgtServerInst.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", principalName)
Call oMgtServerInst.AddProperty("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.ManagementServer']/ManagementGroupName$", dwMgName)
Dim connectionString, cnADOConnection, oResults,oQuery,sourceName, strDriverName
strDriverName = GetSqlDriverName
connectionString = "Provider=" & strDriverName & ";Server=" & oRegDictionary.item("DatabaseServerName") & ";Integrated Security=SSPI;Initial Catalog=" & oRegDictionary.item("DatabaseName")
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Open connectionString
cnADOConnection.CommandTimeout = 300
oQuery = "select SourceName from etl.source where SourceTypeId ='2'"
oResults = CreateObject("ADODB.Recordset")
Set oResults = cnADOConnection.Execute(oQuery)
If oResults.State <> 0 Then
Do Until oResults.EOF
If oResults.Fields("SourceName") <> dwMgName Then
sourceName = sourceName & oResults.Fields("SourceName")& ","
End If
oResults.MoveNext
Loop
End If
If oResults.State <> 0 Then
oResults.Close
End If
cnADOConnection.Close
if(isEmpty(sourceName)) Then
sourceName = ""
Else
sourceName = Left(sourceName,(Len(sourceName)-1))
End If