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 (8)

  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.

  2. Sebastian says:

    Hi Kevin,

    thanks for this awesome article. I have built an automation process into SCOM based on the windows computer class extension based on your MP. After the class is extended and filled with our data from the CMDB there are some write actions which trigger PowerShell scripts by a schedule. For example one of the script creates SCOM groups based on the data from the CMDB and stores them into the same MP which contains the class extension and the write actions. Everything is stored into one unsealed MP. If you like some kind of self-updating MP.

    After importing the MP into SCOM everything works fine and the MP makes what it is designed for. 🙂 But after some time I have some errors in the “Operations Manager” Event Log:

    OpsMgr Management Configuration Service failed to execute ‘DeltaSynchronization’ engine work item due to the following exception
    Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.ConfigManager.ConfigurationChangeException: Configuration change occurred during delta synchronization. Delta will be restarted.

    The Error occurs regularly until I remove the MP. So I can pretty sure say that the error comes from the MP.

    Now my Question:
    Have SCOM a problem with this self-changing MP? A MP that saves objects in itself.

    THX Sebastian

  3. Michael says:

    Hi Kevin,

    thank you very much for the Script. Maybe you can help me with an error i get when i execute your script.
    The line:
    $DiscoveryData = $momapi.CreateDiscoveryData(0, $SourceId, $ManagedEntityId)

    throws following error:
    Exception calling “CreateDiscoveryData” with “3” argument(s): “Invalid class string (Exception from HRESULT: 0x800401F3 (CO_E_CLASSSTRING))”
    At line:45 char:1
    + $DiscoveryData = $momapi.CreateDiscoveryData(0, $SourceId, $ManagedEntityId)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

    and i can´t find why. You have any idea?

    Kind Regards,

    Michael

    1. Michael says:

      We found the error,
      but in the next step the Data is not populated to the discovered object.

      1. Michael says:

        Hello Kevin,
        when i import the management pack into scom 2012r2 the discovery works fine now. Te discovery finds objects in a SharePoint view and in SCOM, also the compare works and shows the right count of systems. In the variables for FQDN, TIER…. the expected values are stored.
        But after the script ends without an error, i cant find the extends on the server objects in the SCOM console.
        Do you have any hint for me where to look or how i can fix the problem?

        Kind regards,
        Michael

        1. Sebastian says:

          Hello Micheal,

          have you taken a look at the Discovered Invertory View at the Monitoring Pane. Choose your class extension and the values should be there.

          Hope it helps.

          Regards Sebastian

          1. Michael says:

            Hi Sebastian,
            i already did, but non of the matching servers come up.
            When i look under authoring -> management pack objects -> object discoveries i find the discovery that i had imported with the management pack. And in the discovery, i find the values i definite. So right now i don´t know exactly what my problem is.
            I pull my objects from the SharePoint…. checked
            With the object i get the values i to publish to the server objects in SCOM…. checked
            The script takes the server objects from SCOM and match it against the objects from SharePoint…. checked

            How can i find the error? Can i push the matching data into a file (Excel/csv) to check if the mapping and variables work?

            Thank you,

            Michael

            1. Sebastian Pabst says:

              Hi Michael,

              maybe you have your problem solved but in the case that not:

              Have you checked the eventlog of your Management Server? Kevin have implemented logging into the Operations Manager Log at the SCOM Management Server.

              This is the interesting line (EventID 8888):
              $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.”)

              The $CMDBMatchComputerCount variable should give you a number of Systems which will be inserted into you Class Extention.

              You can also try to use this logging PowerShell method to write out every system to the log. You should use the $FQDN variable for that. In this case you can see which system pass the PS script and is writen to the SCOM Property Bag.

              Regards Sebastian

Skip to main content