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:
I later updated that sample MP here:
https://kevinholman.com/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://kevinholman.com/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:
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:
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://github.com/thekevinholman/ExtendWCfromCMDB
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.
This is great! Thanks for providing this, Kevin. I’m a big fan of yours! I’ve implemented this in my Dev environment, but I’m having a little trouble with one of the properties. It’s not getting the entire content from the CSV file. A couple examples below:
CSV = Active Directory – Production;Active Directory Federation Services (ADFS) – Production
SCOM = Active Directory
CSV = Lawson – Portal – Production
SCOM = Lawson – Portal
If I do Get-Content or Import-CSV in PowerShell, I can see the entire column content. Not sure why it’s cutting off some of it when it adds it to the instance properties in SCOM.
You commented on a blog about CMDB, but reference a CSV. Did you mean to comment on the VSC blog article? I assume that’s what you are using?
Class Properties should generally be somewhat short. The default is MaxLength=”256″ which you can see in the class definition.
I don’t think that’s what happening here…. however, it looks like it is truncating it for some other reason. I’d probably need a copy of your CSV and your Management pack to be able to troubleshoot it.
Thank you for your quick response. Sorry about that. I meant to comment on this one: https://kevinholman.com/2016/12/04/extending-windows-computer-class-from-a-csv-file-in-scom/. I’ve been looking at both the CSV and SQL query options for extending the windows computer class. If you’d be willing to take a look, I’d greatly appreciate it. I’ll go ahead and contact you through the Contact link.
Actually, I just figured it out. Sorry to have bothered you with this. I had forgotten I had changed the path to the CSV I was using, so there were two different CSVs involved here, which explains the discrepancy I was seeing.
Hey Kevin,
I am testing this in dev now and it is way more reliable than pushing out reg keys. What happens if say.. the cmdb is down when it queries one night? Or even 3 nights in a row (just for fun). Will discovery data and group memberships be lost?
Thanks
While this is more reliable – reg keys actually work better. The reg key attribute is discovered ON the agent, so you get a lot less risk of orphans and issues when deleting agents this way. While CMDB discovery of extended computer properties does work, it can cause some issues when you delete the agents so care must be taken. As far as the DB being down – I write into my scripts failsafes like “if I dont get “x” records from SQL – stop and throw an error. You will not impact property population….. you just wont get anything new/changed/deleted. Before I had these failsafes I depopulated massive numbers of groups at a customer when there was a script/DB connectivity issue. Oops.
Ah thankyou so much for that info. Gunna write a failsafe in now.