Discovery of Operations Manager Database and DataWarehouse

Microsoft.SystemCenter.OM.Database.Discovery (Discovery)

This discovers the deployed versions of Operations Manager Database and DataWarehouse.

Element properties:

Member Modules:

Source Code:

<Discovery ID="Microsoft.SystemCenter.OM.Database.Discovery" Target="SCLibrary!Microsoft.SystemCenter.RootManagementServer" Enabled="true" ConfirmDelivery="false" Remotable="false" Priority="Normal">

  <Category>Discovery</Category>

  <DiscoveryTypes/>

  <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedPowerShell.DiscoveryProvider" RunAs="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.ActionAccount">

    <IntervalSeconds>86400</IntervalSeconds>

    <SyncTime/>

    <ScriptName>SCOMInstalledDatabase</ScriptName>

    <ScriptBody><Script>

              function Invoke-SQLCommand {

                param(

                  [string] $ServerInstance = $(throw "Please specify ServerInstance."),

                  [string] $DBName = $(throw "Please specify Database."),

                  [string] $Query = $(throw "Please specify a query.")

                )



                $connectionString = "Data Source=$ServerInstance; " + "Integrated Security=SSPI; " + "Initial Catalog=$DBName"

            

                $connection = new-object system.data.SqlClient.SQLConnection($connectionString)

                $command = new-object system.data.sqlclient.sqlcommand($Query,$connection)

                $connection.Open()

            

                $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

                $dsinstance = New-Object System.Data.DataSet

                $adapter.Fill($dsinstance) | Out-Null

            

                $connection.Close()

                $dsinstance.Tables[0].Rows[0][0]

              }

                        

              # Create a new discovery data packet

              $oAPI = new-object -comObject "MOM.ScriptAPI"

              $oDisc = $oAPI.CreateDiscoveryData(0, "$MPElement$", "$Target/Id$")

              $oDisc.IsSnapshot = $True

              $SCOMPowerShellKey = "HKLM:\SOFTWARE\Microsoft\System Center Operations Manager\12\Setup\Powershell\V2"

              $SCOMModulePath = Join-Path (Get-ItemProperty $SCOMPowerShellKey).InstallDirectory &#x201C;OperationsManager&#x201D;

              Import-module $SCOMModulePath

              $DBInfo = Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\" | Select-Object DatabaseServerName, DataWarehouseDBServerName, DatabaseName, DataWarehouseDBName

            

              #Database Discovery

              if($DBInfo.DatabaseServerName.Contains("\")){

                 $computerName = [System.Net.Dns]::GetHostEntry($DBInfo.DatabaseServerName.Split('\')[0]).HostName

              }else {

                 $computerName = [System.Net.Dns]::GetHostEntry($DBInfo.DatabaseServerName.Split(',')[0]).HostName

              }

              #Get database instance name in case of custom port.

               if($DBInfo.DatabaseServerName.Contains(",")){

                 $instanceName = $DBInfo.DatabaseServerName.Split(',')[0]

              }else {

                 $instanceName = $DBInfo.DatabaseServerName

              }

              $dbVersion = Invoke-SQLCommand -ServerInstance $DBInfo.DatabaseServerName -DBName $DBInfo.DatabaseName -Query "select Top(1) Value from SqlPatchVersion WHERE State = 'COMPLETED' order by cast('/' + replace(Value , '.', '/') + '/' as hierarchyid) desc"

              $oInst1 = $oDisc.CreateClassInstance("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']$");

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/InstanceName$",  $instanceName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/ProductDisplayName$", "Operations Manager Database")

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/DatabaseName$", $DBInfo.DatabaseName)

              $oInst1.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $computerName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/ComputerName$", $computerName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/Version$", $dbVersion)

              $oDisc.AddInstance($oInst1)

            

              #Data Warehouse Discovery

             if($DBInfo.DataWarehouseDBServerName.Contains("\")){

                $computerName = [System.Net.Dns]::GetHostEntry($DBInfo.DataWarehouseDBServerName.Split('\')[0]).HostName

              }else{

                $computerName = [System.Net.Dns]::GetHostEntry($DBInfo.DataWarehouseDBServerName.Split(',')[0]).HostName

              }

               #Get datawarehouse instance name in case of custom port.

               if( $DBInfo.DataWarehouseDBServerName.Contains(",")){

                 $instanceName = $DBInfo.DataWarehouseDBServerName.Split(',')[0]

              }else {

                 $instanceName = $DBInfo.DataWarehouseDBServerName

              }

              $dwVersion = Invoke-SQLCommand -ServerInstance $DBInfo.DataWarehouseDBServerName -DBName $DBInfo.DataWarehouseDBName -Query "select Top(1) Value from SqlPatchVersion WHERE State = 'COMPLETED' order by cast('/' + replace(Value , '.', '/') + '/' as hierarchyid) desc"

              $oInst1 = $oDisc.CreateClassInstance("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']$");

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/InstanceName$", $instanceName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/ProductDisplayName$", "Operations Manager Data Warehouse")

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/DatabaseName$", $DBInfo.DataWarehouseDBName)

              $oInst1.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $computerName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/ComputerName$", $computerName)

              $oInst1.AddProperty("$MPElement[Name='Microsoft.SystemCenter.Installed.Database']/Version$", $dwVersion)

              $oDisc.AddInstance($oInst1)

              $oDisc

            </Script></ScriptBody>

    <TimeoutSeconds>900</TimeoutSeconds>

  </DataSource>

</Discovery>