How to create a SCOM group from a SQL CMDB Query


 

imageimage

 

I wrote an example of this a long time ago.  I was never happy with it, as it was VBScript based, hard to troubleshoot, and required lots of editing each time you wanted to reuse it.  It was also error prone, and didn’t work if the SQL query results contained computers that didn’t exist in SCOM, as SCOM will reject the entire discovery data payload in that case.

 

If you too were looking for a reliable and easy way to do this, well, look no further!  I have created an MP Fragment in my fragment library for this: 

https://gallery.technet.microsoft.com/SCOM-Management-Pack-VSAE-2c506737

 

This MP Fragment will make creating SCOM groups of Windows Computers from a SQL query super easy!  This is a nice way to “delegate” the ability for end users to control what servers will appear in their scopes, as they often have the ability to easily add and remove computers from a database or CMDB, but they do not have access to SCOM Group memberships.

 

I am going to demonstrate using Silect MP Author Pro to reuse this Fragment, and you can also easily use Visual Studio with VSAE.  If you’d like to read more on either of those, see:

https://blogs.technet.microsoft.com/kevinholman/2016/06/04/authoring-management-packs-the-fast-and-easy-way-using-visual-studio/

https://blogs.technet.microsoft.com/kevinholman/2017/03/22/management-pack-authoring-the-really-fast-and-easy-way-using-silect-mp-author-and-fragments/

 

In Silect MP Author Pro – create a new, empty management pack, and select “Import Fragment”

image

 

Browse the fragment and choose:  Class.Group.SQLQueryBasedGroupWindowsComputers.mpx

 

image

 

We need to simply input the values here, such as:

 

image

 

Click “Import

Silect MP Author Pro will automagically handle the references for you, so just say “Yes” on the popup:

 

image

 

 

That’s IT!   Surprised smile

 

Save it, and deploy it!

 

image

 

If you look in SCOM after a few minutes – you should see your group:

 

image

 

The rule to populate it runs once a day by default, but it will run immediately upon import.  Look for event ID 7500 in the OpsMgr event log on the Management Server that hosts your All Management Servers Resource Pool object

 

image

 

Once you see these events and no errors in them – you can view group membership in SCOM:

 

image

 

So easy.  And you don’t have to know anything about XML, or even Management Packs to do it!

 

Using Visual Studio with VSAE works exactly the same way – you simply have to do a manual Find/Replace for each item.  See the VSAE method in the link above.

 

Want to dig deeper into how this is put together?  Read on:

The MP we generate is very basic.  There is a Class (the Group definition) a Relationship (the Group contains Windows Computers) and a discovery (queries SQL and discovers the relationship to the existing Windows Computers in SCOM)

 

image

 

The script is below:

We basically connect to SQL, return a list of FQDN’s from the query, look the results up and see if they exist in SCOM, if they do, add them to the group.

We will log events along the way to help in troubleshooting if anything doesn’t work, and record the completion and total script runtime, like all my SCOM scripts.

 

#================================================================================= # Group Population script based on SQL Query # Your query should return a list of FQDN names only # # Kevin Holman # v1.1 #================================================================================= 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}' # $SQLServer = "FOO" # $SQLDBName = "CMDB" # $SQLQuery = "SELECT SERVERNAME from serverlist" #================================================================================= # Constants section - modify stuff here: #================================================================================= # Assign script name variable for use in event logging $ScriptName = "FAB.MyApp.SCOMComputerGroupFromSQL.SQLBased.Group.Discovery.ps1" $EventID = "7501" $SQLServer = "SQL2A" $SQLDBName = "CMDB" $SQLQuery = "select SERVERNAME from serverlist" #================================================================================= # Starting Script section #================================================================================= # Gather the start time of the script $StartTime = Get-Date # Load MOMScript API $momapi = New-Object -comObject MOM.ScriptAPI # Load SCOM Discovery module $DiscoveryData = $momapi.CreateDiscoveryData(0, $SourceId, $ManagedEntityId) #Set variables to be used in logging events $whoami = whoami #Log script event that we are starting task $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script is starting. `n Running as ($whoami).") #================================================================================= # Connect to local SCOM Management Group Section #================================================================================= # 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: Failure loading OperationsManager module or unable to connect to the management server. `n Terminating script. `n Error is: ($Error).") EXIT } #================================================================================= # Begin MAIN script section #================================================================================= #Log event for captured parameters $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n SQLServer: ($SQLServer) `n SQLDatabase: ($SQLDBName). `n SQL Query: ($SQLQuery).") # Health Service class section # We need this list of SCOM agents, so we can only submit discovery data for a Healthservice in SCOM otherwise SCOM will reject the discovery data, and this will clean up deleted stale Windows Computer objects that will remain until the next discovery # Clear any previous errors $Error.Clear() # Get all instances of a existing Health Service class $HS = Get-SCOMClass -Name "Microsoft.SystemCenter.Healthservice" | Get-SCOMClassInstance $HSNames = $HS.DisplayName $HSCount = $HSNames.count IF($Error) { $momapi.LogScriptEvent($ScriptName,$EventID,1, "`n FATAL ERROR: Unable to gather Healthservice instances from SCOM. `n Error is: $Error") EXIT } ELSE { $momapi.LogScriptEvent($ScriptName,$EventID,0, "`n Get all Health Service Objects has completed. `n Returned ($HSCount) Health Service Objects from SCOM.") } # END Health Service class section # Connect to and then Query the database $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) | Out-Null $SqlConnection.Close() # Check for errors connecting to SQL IF ($Error) { $momapi.LogScriptEvent($ScriptName,$EventID,1,"`n FATAL ERROR: There was an attempting to connect to and query SQL. `n Terminating script. `n Error is: ($Error).") EXIT } # Set the output to a variable [array]$SQLNames = $ds.Tables[0] $SQLNamesCount = $SQLNames.Count IF ($SQLNamesCount -ge 1) { $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Successfully collected ($SQLNamesCount) records from the SQL query.") } ELSE { $momapi.LogScriptEvent($ScriptName,$EventID,1,"`n FATAL ERROR: There was an error getting records from SQL or no records were returned. `n Number of objects returned: ($SQLNamesCount). `n Terminating script.") EXIT } # Set namelist array to empty [array]$NameList = @() # Loop through each Name from SQL and build an array of FQDN hostnames FOREACH ($SQLName in $SQLNames) { #Get the Hostname property from SQL [string]$DNSComputerName = $SQLName[0] $NameList += $DNSComputerName } $NameListCount = $NameList.Count #Discovery Section #Set the group instance we will discover members of $GroupInstance = $DiscoveryData.CreateClassInstance("$MPElement[Name='FAB.MyApp.SCOMComputerGroupFromSQL.SQLBased.Group']$") # Loop through each SCOM computer and add a group membership containment relationship to the discovery data $i=0; FOREACH ($Name in $NameList) { #Check to make sure the name we got from AD exists as a Healthservice in this Management Group IF ($Name -in $HSNames) { $i = $i+1 $ServerInstance = $DiscoveryData.CreateClassInstance("$MPElement[Name='Windows!Microsoft.Windows.Computer']$") $ServerInstance.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $Name) $RelationshipInstance = $DiscoveryData.CreateRelationshipInstance("$MPElement[Name='FAB.MyApp.SCOMComputerGroupFromSQL.SQLBased.Group.Contains.Windows.Computers']$") $RelationshipInstance.Source = $GroupInstance $RelationshipInstance.Target = $ServerInstance $DiscoveryData.AddInstance($RelationshipInstance) } } IF ($i -ge 1) { $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Successfully found ($i) Computers in SCOM from the original ($NameListCount) DNS names from the query.") } ELSE { $momapi.LogScriptEvent($ScriptName,$EventID,1,"`n FATAL ERROR: No computers in SCOM were found matching the ($NameListCount) DNS names from the query. `n Terminating script.") EXIT } # Return Discovery Items Normally $DiscoveryData # Return Discovery Bag to the command line for testing (does not work from ISE) # $momapi.Return($DiscoveryData) #================================================================================= # End MAIN script section # 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 Ending. `n Script Runtime: ($ScriptTime) seconds.") #================================================================================= #End Script

 

 

 

Key recommendations:

1.  Don’t run your frequency <intervalseconds> too often.  If updating the group once a day is ok, leave it at the default.  If you need it more frequent, that’s fine, just remember it’s a script, and all scripts running on the management servers have an impact on the overall load, plus we are submitting discovery data about relationships each time, and searching through SQL and SCOM via the SDK.

2.  The default timeout is set to 5 minutes.  If you cannot complete this in less, something is WRONG.  If that is true for you, you need to find out where it is taking too long.

3.  If you have a lot of SQL based SCOM groups, consider adding a staggered sync time to each discovery, so they don’t all run at the same time, or on the same interval.


Comments (5)

    1. Sam Boutros says:

      nvm, resolved:
      $MS=Get-SCOMGatewayManagementServer -Name $FQDN
      $MS.ProxyingEnabled=$true
      $MS.ApplyChanges()

  1. Noah says:

    Hi Kevin, this is great!

    We’re in the initial steps of going with a system generated naming convention in attempts to get rid of naming convention drift that has become more problematic over the years so we’ve been exploring how to query our CMDB as the authoritative source.

    The question I have is would you do this on a group by group basis by having a separate MP (with a different script for each one) or would you do all your group population based off this example?

    For example we could have one SQL query to get all the server information then build each group inside your script and populate the groups 1 by 1. Also what about UNIX/Linux servers, do you have an example of that in your script?

    Thanks!

    1. Kevin Holman says:

      Noah –

      That probably depends on how many groups we are talking. I am not a huge fan of “mega workflows” that do tons of discoveries, but that must be balanced with the number of individual workflows. If we are talking 500 groups or more, then I’d have to do some serious testing and would consider consolidation. Querying SQL is actually REALLY fast. What is slow is accessing the SDK. That would be the bottleneck for lots of small workflows. For unix/linux – you’d just change the class for the relationships, it would be the group contains Unix Computer.

      1. Noah says:

        Thanks for the reply, we’ve got a large number custom groups today but I’d say the number that I need focus on initially would be much smaller to start with. But you’ve covered the question I was more or less trying to get at with the bottleneck being on the SDK, not on the SQL side so we can figure that out.

        Today we’ve created attributes that are looking in WMI where we then determine the following support groups – OS, APP, DB, etc… and then build a dynamic group based on that. But we’re trying to get out of that model of having that information locally on each server and have SCOM populate it’s groups based on the CMDB so this is great to see an example of this out in the wild.

        Thanks again!

Skip to main content