Extending Windows Computer class from a SQL CMDB in SCOM


 

Years ago – I wrote a post on customizing the “Windows Computer” class, showing how to use registry keys to add properties to the “Windows Computer” class, to make creating custom groups much simpler.  You can read about the details of how and why here:  https://blogs.technet.microsoft.com/kevinholman/2009/06/10/creating-custom-dynamic-computer-groups-based-on-registry-keys-on-agents/

I later updated that sample MP here:  https://blogs.technet.microsoft.com/kevinholman/2016/12/04/extending-windows-computer-class-from-registry-keys-in-scom/

I also provided a sample of doing the same thing from a CSV file:  https://blogs.technet.microsoft.com/kevinholman/2016/12/04/extending-windows-computer-class-from-a-csv-file-in-scom/

 

This post will demonstrate how to extend the Windows Computer class using a SQL database (CMDB) as the source for the class properties.  This is incredibly useful if you have an authoritative record of all servers, and important properties that you would like to use for grouping in SCOM.

 

Here is an example of my test CMDB:

image

 

We can write an extended class of Windows Computer, and a script based discovery to read in these tables by sending a query to a SQL DB, and add each returned column as a class property in SCOM.

Here is the class definition:

<TypeDefinitions> <EntityTypes> <ClassTypes> <ClassType ID="DemoCMDB.Windows.Computer.Extended.Class" Accessibility="Public" Abstract="false" Base="Windows!Microsoft.Windows.Computer" Hosted="false" Singleton="false" Extension="false"> <Property ID="TIER" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" /> <Property ID="GROUPID" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" /> <Property ID="OWNER" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" /> </ClassType> </ClassTypes> </EntityTypes> </TypeDefinitions>

 

The discovery will target the “All Management Servers Resource Pool” class.  This class is hosted by ONE of the management servers at any given time, and by doing this we will have high availability for the discovery workflow.

The script will read the SQL DB via query, get the FQDN of each row in the database, then compare that to a list of all computers in SCOM.  If the computer exists in SCOM, it will add the properties to the discovery.  There is a “constants” section in the script for you to change relevant information:

#======================================================
# Constants section - modify stuff here:
$SQLServer = "SQL2A.opsmgr.net"
$SQLDBName =  "CMDB"
$SqlQuery = "SELECT FQDN, TIER, GROUPID, OWNER FROM [dbo].[ServerList] ORDER BY FQDN"

 

Here is the script:

#================================================================================= # Extend Windows Computer class from CMDB #================================================================================= Param($SourceId,$ManagedEntityId) # For testing discovery manually in PowerShell: # $SourceId = '{00000000-0000-0000-0000-000000000000}' # $ManagedEntityId = '{00000000-0000-0000-0000-000000000000}' #================================================================================= # Constants section - modify stuff here: $SQLServer = "SQL2A.opsmgr.net" $SQLDBName = "CMDB" $SqlQuery = "SELECT FQDN, TIER, GROUPID, OWNER FROM [dbo].[ServerList] ORDER BY FQDN" # Assign script name variable for use in event logging $ScriptName = "DemoCMDB.Windows.Computer.Extended.Class.Discovery.Script.ps1" #================================================================================= #================================================================================= # function Is-ClassMember # Purpose: To ensure we only return discvoery data for computers that # already exist in SCOM, otherwise it will be rejected # Arguments: # -$InstanceDisplayName - The name of the object instance like 'servername.domain.com' #================================================================================== function Is-ClassMember { param($InstanceDisplayName) If ($InstanceDisplayName -in $ComputerNames) { $value = "True" } Else { $value = "False" } Return $value } # End of function Is-ClassMember # Gather script start time $StartTime = Get-Date $MServer = $env:COMPUTERNAME # Gather who the script is running as $WhoAmI = whoami # Load MOMScript API $momapi = New-Object -comObject MOM.ScriptAPI # Load SCOM Discovery module $DiscoveryData = $momapi.CreateDiscoveryData(0, $SourceId, $ManagedEntityId) # Log an event for the script starting $momapi.LogScriptEvent($ScriptName,8888,0, "Script is starting. Running, as $WhoAmI.") # Clear any previous errors if($Error) { $Error.Clear() } # Import the OperationsManager module and connect to the management group Try { $SCOMPowerShellKey = "HKLM:\SOFTWARE\Microsoft\System Center Operations Manager\12\Setup\Powershell\V2" $SCOMModulePath = Join-Path (Get-ItemProperty $SCOMPowerShellKey).InstallDirectory "OperationsManager" Import-module $SCOMModulePath } Catch { $momapi.LogScriptEvent($ScriptName,8889,2, "Unable to load the OperationsManager module, Error is: $error") } Try { New-DefaultManagementGroupConnection $MServer } Catch { $momapi.LogScriptEvent($ScriptName,8889,2, "Unable to connect to the management server: $MServer. Error when calling New-DefaultManagementGroupConnection. Error is: $error") } # Get all instances of a existing Windows Computer class # We need this to check and make sure each computer in the CMDB exists in SCOM or discovery data will be rejected $WindowsComputers = Get-SCOMClass -DisplayName "Windows Computer" | Get-SCOMClassInstance $ComputerNames = $WindowsComputers.DisplayName $ComputerCount = $ComputerNames.count # Log an event for command ending $momapi.LogScriptEvent($ScriptName,8888,0, "Get all Windows Computers has completed. Returned $ComputerCount Windows Computers.") # Clear any previous errors if($Error) { $Error.Clear() } # Query the CMDB database to get the servers and properties $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $ds = New-Object System.Data.DataSet $SqlAdapter.Fill($ds) $SqlConnection.Close() $i=0; $j=0; foreach ($row in $ds.Tables[0].Rows) { $i = $i+1 $FQDN = $row[0].ToString().Trim() $IsSCOMComputer = Is-ClassMember $FQDN If($IsSCOMComputer -eq "True") { $j=$j+1 $TIER = $row[1].ToString().Trim() $GROUPID = $row[2].ToString().Trim() $OWNER = $row[3].ToString().Trim() # Create discovery data for each computer that exists in both the CMDB and SCOM $Inst = $DiscoveryData.CreateClassInstance("$MPElement[Name='DemoCMDB.Windows.Computer.Extended.Class']$") $Inst.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $FQDN) $Inst.AddProperty("$MPElement[Name='DemoCMDB.Windows.Computer.Extended.Class']/TIER$", $TIER) $Inst.AddProperty("$MPElement[Name='DemoCMDB.Windows.Computer.Extended.Class']/GROUPID$", $GROUPID) $Inst.AddProperty("$MPElement[Name='DemoCMDB.Windows.Computer.Extended.Class']/OWNER$", $OWNER) $DiscoveryData.AddInstance($Inst) } #End If } #End foreach # Return Discovery Items $DiscoveryData # Return Discovery Bag to the command line for testing (does not work from ISE): # $momapi.Return($DiscoveryData) $CMDBMatchComputerCount = $j $CMDBRowCount = $i # End script and record total runtime $EndTime = Get-Date $ScriptTime = ($EndTime - $StartTime).TotalSeconds # Log an event for script ending and total execution time. $momapi.LogScriptEvent($ScriptName,8888,0, "Script has completed. CMDB returned $CMDBRowCount computers. SCOM returned $ComputerCount Computers. Discovery returned $CMDBMatchComputerCount matching computers from the CMDB and SCOM. Runtime was $ScriptTime seconds")

 

You will need to change the SQL server name, DB name, and query, along with adding/changing the properties you want in the relevant sections.

 

You can review the discovery data in discovered inventory:

image

 

 

I also added rich logging to the script to understand what is happening:

Log Name:      Operations Manager
Source:        Health Service Script
Date:          12/4/2016 3:00:30 PM
Event ID:      8888
Level:         Information
Computer:      SCOMA1.opsmgr.net
Description:
DemoCMDB.Windows.Computer.Extended.Class.Discovery.Script.ps1 : Script has completed.  CMDB returned 8 computers.  SCOM returned 26 Computers.  Discovery returned 6 matching computers from the CMDB and SCOM.  Runtime was 5.7812508 seconds

 

I am attaching the sample MP file, along with the sample CSV registry file, at the following location:

 

https://gallery.technet.microsoft.com/Extend-Windows-Computer-13486493


Comments (1)

  1. rob1974 says:

    CMDB (or whatever the source is) might change and that would require you to rewrite the mp. I create the extended windows computer class based on a registry value and I create scom task to set the registry key.

    An external script runs daily and reads from the source (CMDB, Excel, CSV, whatever). If there are new or changed properties the scom task(s) are called to set the registry with the new values. When the source changes all i need to do update the external script to grap its data from another source. And if for some reason the "source" is unavailable you can just manually set the keys.

Skip to main content