Discovers Data Warehouse databases and where they are hosted.
Knowledge Base article:
Summary
This discovery populates Data Warehouse databases.Discovery runs immedietely after being imported. Following this it runs at a 24 hour interval. Sync time,frequency as well as 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: Dw2012DatabaseDiscovery.VBS
'
' Purpose - Discover the Data Warehouse databases in the environment.
'
' Parameters - 0 - discovery id
' 1 - Target managed entity id
' 2 - Host Computer Principal Name
' 3 - Staging DB Server
' 4 - Staging DB Name
'*************************************************************************
SetLocale("en-us")
Dim sourceId,managedEntityId,oAPI,principalName, oDiscoveryData, StagingDBServer, StagingDBName
Call oAPI.LogScriptEvent("Dw2012DatabaseDiscovery.VBS", 3000, 4, "Starting discovery of Service Manager Data Warehouse Databases")
Set oDiscoveryData = oAPI.CreateDiscoveryData(0, sourceId, managedEntityId)
Dim connectionString, cnADOConnection, oResults,oQuery,oDwDatabaseInst, ServerName, DwDbName, strDriverName
strDriverName = GetSqlDriverName
connectionString = "Provider=" & strDriverName & ";Server=" & StagingDBServer & ";Integrated Security=SSPI;Initial Catalog=" & StagingDBName
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Open connectionString
cnADOConnection.CommandTimeout = 300
oQuery = "Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�ASResourceStore Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�CmdbResourceStore Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�SqlResourceStore where Name_188E1D94_94DB_6A6B_3897_D7C2F6DFDBB6 = 'Ral.SqlResourceStore.Ods' Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�SqlResourceStore where Name_188E1D94_94DB_6A6B_3897_D7C2F6DFDBB6 = 'Ral.SqlResourceStore.Repository' Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�SqlResourceStore where Name_188E1D94_94DB_6A6B_3897_D7C2F6DFDBB6 = 'Ral.SqlResourceStore.Datamart' Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�SqlResourceStore where Name_188E1D94_94DB_6A6B_3897_D7C2F6DFDBB6 like '%ExtractionSource' Union " &_
"Select Server_48B308F9_CF0E_0F74_83E1_0AEB1B58E2FA AS ServerName, DataService_98B2DDF9_D9FD_9297_85D3_FCF36F1D016B AS DbName FROM MT_Microsoft�SystemCenter�ResourceAccessLayer�SrsResourceStore Union " &_
"Select ServerName_3AAF92F8_EBD2_2235_C8E7_8E5DCEFED671 AS ServerName, DatabaseName_DB63F7A4_5275_0BE4_8592_2091384B4C68 AS DbName From MTV_Microsoft�SystemCenter�DataWarehouse�ConfigurationManager�DataSource"
oQuery = Replace(oQuery,"�","$")
oResults = CreateObject("ADODB.Recordset")
Set oResults = cnADOConnection.Execute(oQuery)
If oResults.State <> 0 Then
Do Until oResults.EOF
Call oAPI.LogScriptEvent("Dw2012DatabaseDiscovery.VBS", 3000, 4, "Discovered SCSM DataWarehouse Server Properties.")
Set oDwDatabaseInst = oDiscoveryData.CreateClassInstance("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.DwDatabase']$")
ServerName = oResults.Fields("ServerName")
DwDbName = oResults.Fields("DbName")
Call oDwDatabaseInst.AddProperty("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.DwDatabase']/ServerName$", ServerName)
Call oDwDatabaseInst.AddProperty("$MPElement[Name='SM!Microsoft.SystemCenter.ServiceManager.DwDatabase']/DatabaseName$", DwDbName)
Call oDwDatabaseInst.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", principalName)
Call oDwDatabaseInst.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", ServerName & "\" & DwDbName)
Call oDiscoveryData.AddInstance(oDwDatabaseInst)
oResults.MoveNext
Loop
End If
If oResults.State <> 0 Then
oResults.Close
End If
cnADOConnection.Close
' Submit the discovery data to the Operations Manager database.
Call oAPI.Return(oDiscoveryData)
Call oAPI.LogScriptEvent("Dw2012DatabaseDiscovery.VBS", 3000, 4, "Finishing Data Warehouse Database Discovery.")
WScript.Quit
Home
ENU