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 # # Author: Kevin Holman # v1.2 #================================================================================= Param($SourceId,$ManagedEntityId) # Manual Testing section - put stuff here for manually testing script - typically parameters: #================================================================================= # $SourceId = '{00000000-0000-0000-0000-000000000000}' # $ManagedEntityId = '{00000000-0000-0000-0000-000000000000}' #================================================================================= # Constants section - modify stuff here: #================================================================================= # Assign script name variable for use in event logging. $ScriptName = "DemoCMDB.Windows.Computer.Extended.Class.Discovery.ps1" $EventID = "8888" $SQLServer = "SQL2A.opsmgr.net" $SQLDBName = "CMDB" $SqlQuery = "SELECT FQDN, TIER, GROUPID, OWNER FROM [dbo].[ServerList] ORDER BY FQDN" #================================================================================= # Starting Script section - All scripts get this #================================================================================= # Gather the start time of the script $StartTime = Get-Date #Set variable to be used in logging events $whoami = whoami # Load MOMScript API $momapi = New-Object -comObject MOM.ScriptAPI #Log script event that we are starting task $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script is starting. `n Running as ($whoami).") #================================================================================= # Discovery Script section - Discovery scripts get this #================================================================================= # Load SCOM Discovery module $DiscoveryData = $momapi.CreateDiscoveryData(0, $SourceId, $ManagedEntityId) #================================================================================= # Connect to local SCOM Management Group Section - If required #================================================================================= # I have found this to be the most reliable method to load SCOM modules for scripts running on Management Servers # Clear any previous errors $Error.Clear() # Import the OperationsManager module and connect to the management group $SCOMPowerShellKey = "HKLM:\SOFTWARE\Microsoft\System Center Operations Manager\12\Setup\Powershell\V2" $SCOMModulePath = Join-Path (Get-ItemProperty $SCOMPowerShellKey).InstallDirectory "OperationsManager" Import-module $SCOMModulePath New-DefaultManagementGroupConnection "localhost" IF ($Error) { $momapi.LogScriptEvent($ScriptName,$EventID,1,"`n FATAL ERROR: Unable to load OperationsManager module or unable to connect to Management Server. `n Terminating script. `n Error is: ($Error).") EXIT } #================================================================================= # Begin MAIN script section #================================================================================= # Get all instances of a existing Health Service class - this can take a long time. # We need this list of SCOM agents, so we can only submit discovery data for service in SCOM otherwise the discovery will reject the data, and this will clean up deleted Windows Computer objects that will remain until the next discovery runs $HS = Get-SCOMClass -Name "Microsoft.SystemCenter.Healthservice" | Get-SCOMClassInstance $HSNames = $HS.DisplayName $HSCount = $HSNames.count #Log event $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Get all Health Service Objects has completed. `n Returned ($HSCount) Health Service objects.") # 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() #Check and see if the $FQDN value contains a computer that exists as a HealthService in SCOM IF ($FQDN -in $HSNames) { $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) } } $CMDBMatchComputerCount = $j $CMDBRowCount = $i $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n CMDB returned ($CMDBRowCount) computers. `n SCOM returned ($HSCount) Computers. `n Discovery returned ($CMDBMatchComputerCount) matching computers from the CMDB and SCOM.") #================================================================================= # End MAIN script section # Discovery Script section - Discovery scripts get this #================================================================================= # Return Discovery Items Normally $DiscoveryData # Return Discovery Bag to the command line for testing (does not work from ISE) # $momapi.Return($DiscoveryData) #================================================================================= # End of script section #================================================================================= #Log an event for script ending and total execution time. $EndTime = Get-Date $ScriptTime = ($EndTime - $StartTime).TotalSeconds $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script Completed. `n Script Runtime: ($ScriptTime) seconds.") #================================================================================= # End of script

 

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

 

 

WARNING:  Whenever you “extend” a class that is normally hosted by the agent (Like Windows Computer) but you are running the discovery on a management server such as in this case, there is an unintended consequence.  If you delete the agent, you will notice the Extended class instances and Windows Computer objects are not deleted.  This is because they are still “discovered” but your extending discovery.  This discovery needs to run again before the objects will be “undiscovered” and marked as deleted.  These objects will be orphans, and hosted by the management servers until they are deleted.  Therefore, you might need to run the discoveries more often, such as every hour, or every 4 hours, to ensure they get deleted in a timely fashion and don’t impact your environment.


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