Using SCOM AD integration – but with a CMDB instead of LDAP wizards
AD integration has been around since SCOM 2007 first shipped. The concept is simple – the ability to deploy agents as part of a build process to the OS, but with the SCOM agent left un-configured. Then the SCOM agent checks with Active Directory in its local domain, and received management group and management server assignment from there.
Historically, there were two challenges using AD integration:
First, we only provided a very simple UI, that allowed you to use LDAP queries to try and assign agents based on some criteria in AD. While this worked for some people, in large enterprise environments, this rarely provided a good level of granularity or a good method of load balancing the agent numbers, without constant adjustments.
Second, it wasn’t always reliable. The UI wizards wrote these rules to the Default Management Pack, and when you made a change, it would delete these rules, and write new rules, and sometimes this process broke, leaving your AD publishing in a broken state. The risk was high, because a mistake or a hiccup might cause all your agents to drop out of monitoring (worst case).
Because of these challenges, a lot of customers stopped using AD integration over the years.
What if I told you – you don’t have to use the built in AD LDAP wizards to configure AD integration? Instead, you could use a CMDB datasource, to give you the potential for more granular control, and easier load balancing control of management servers.
I didn’t come up with this idea. One of my customers actually did, a long time ago. They have been using it for years to manage their agents with great success. I have simply re-written the design using PowerShell and simplified it to demonstrate here.
At the end of the day – AD integration is controlled simply by one or more rules in a management pack. The rule is made up of two components:
TheDatasourceis the custom script, that will query the CMDB, and return output necessary for the write action. For our datasource, we will use a simple scheduler, and the Microsoft.Windows.PowerShellPropertyBagProbe DS. The output will be propertybags for each computer we return from the CMDB.
TheWrite Actionis very specific – Microsoft.SystemCenter.ADWriter – this is the “worker bee” in the equation – it takes inputs for DNSHostName and DistinguishedName of each server, and writes that to the specialized container and security groups in AD. So if we are going to use a custom datasource with this write action – we simply need to pass these important items as propertybags to the Write Action.
Here is an example datasource script:
# Get Server list from CMDB for SCOM ADIntegration
# Output FQDN and DistinguishedName into PropertyBags
# Kevin Holman
# v 1.2
# Manual Testing section - put stuff here for manually testing script - typically parameters:
# $RuleId = "Demo.CMDB.ADIntegration.MGNAME.MSNAME.Rule"
# $SQLQuery = "WITH OrderedServers AS
# SELECT SERVERNAME, ROW_NUMBER() OVER(ORDER BY SERVERNAME) AS RowNumber
# FROM serverlist
# WHERE MG = 'PROD'
#WHERE RowNumber BETWEEN 1 and 2"
#=================================================================================# Constants section - modify stuff here:
# Assign script name variable for use in event logging$ScriptName="Demo.CMDB.ADIntegration.PsCMDBQuery.DS.ps1"$EventID=9500$SQLServer="SQLSERVERNAME\INSTANCENAME"$SQLDBName="CMDB"#=================================================================================# 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).")
#=================================================================================# PropertyBag Script section - Monitoring scripts get this
# Load SCOM PropertyBag function$bag=$momapi.CreatePropertyBag()
#=================================================================================# Begin MAIN script section
# Log an event for the parameters $momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Rule: ($RuleId) `n SQL Server: ($SQLServer) `n SQLDBName: ($SQLDBName) `n SQLQuery: ($SQLQuery)")
#Clear any previous errors$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
#Check for errors connecting to SQLIF($Error)
$momapi.LogScriptEvent($ScriptName,$EventID,1,"`n FATAL ERROR connecting to SQL server. `n Rule: ($RuleId). `n SQLServer: ($SQLServer). `n SQLDBName: ($SQLDBName). `n SQLQuery: ($SqlQuery). `n Error is: ($error).")
#Loop through each row of the SQL query output$i=0;
#Increment our counter to get number of computers returned from query$i=$i+1#Get the FQDN from the SQL data$FQDN=$row.ToString().Trim()
#Get the domain from the FQDN$Domain=$FQDN.Substring($FQDN.IndexOf(".") +1)
#Get the HostName and DomainComputer Account Name for DN use below$FQDNSplit=$FQDN.Split(".")
#Get the HostName$HostName=$FQDNSplit
#Get the DomainComputerAccount$DomainComputerAccountName=$FQDNSplit +"\"+$HostName+"$"#Get the Distinguished Name$ADS_NAME_INITTYPE_DOMAIN=1$ADS_NAME_TYPE_NT4=3$ADS_NAME_TYPE_1779=1$NameTranslate= New-Object -ComObject "NameTranslate"#Clear any previous errors$Error.Clear()
#Connect to Active directory$NameTranslate.GetType().InvokeMember("Init", "InvokeMethod", $NULL, $NameTranslate, ($ADS_NAME_INITTYPE_DOMAIN, $Domain)) | Out-Null
#We need to check for an error at this point because this is where we connect to a domain and this might fail if we dont have rights or are firewalledIF($Error)
$momapi.LogScriptEvent($ScriptName,$EventID,1, "`n FATAL ERROR connecting to Active Directory. `n Terminating script. `n Rule: ($RuleId) `n Domain: ($Domain) `n Error is: ($error).")
#Connect to AD and look up computer object from CMDB$NameTranslate.GetType().InvokeMember("Set", "InvokeMethod", $NULL, $NameTranslate, ($ADS_NAME_TYPE_NT4, $DomainComputerAccountName)) | Out-Null
$DN=$NameTranslate.GetType().InvokeMember("Get", "InvokeMethod", $NULL, $NameTranslate, $ADS_NAME_TYPE_1779)
#We need to check for an error at this point because this is where we find the computer in AD and it might not existIF($Error)
$momapi.LogScriptEvent($ScriptName,$EventID,2, "`n NON FATAL WARNING connecting to Active Directory to find computer from CMDB. `n This usually mean that a computer exists in the CMDB bues does not exist in AD or the CMDB record is bad. `n Rule: ($RuleId) `n Domain: ($Domain). `n ComputerName = ($DomainComputerAccountName). `n Error is: ($error).")
# Assume no errors so we will continue#Increment our counter to get number of computers returned from AD$j=$j+1# Debugging:#Write-Host "Servername: $FQDN"#Write-Host "HostName: $HostName"#Write-Host "Domain Name: $Domain"#Write-Host "Domain Computer Name: $DomainComputerAccountName"#Write-Host "DN: $DN"#$momapi.LogScriptEvent($ScriptName,$EventID,0, "`n Debug: `n Rule: ($RuleId) `n FQDN: ($FQDN) `n HostName: ($HostName). `n DomainName: ($Domain). `, Domain Computer Account Name: ($DomainComputerAccountName). `n DN: ($DN)")#Create a propertybag for each computer$bag=$momapi.CreatePropertyBag()
#Put the hostname and DN in the bag. #Includes a value for the folder name so that we can tell which folder the data is from.$bag.AddValue("distinguishedName",$DN)
#Return each property bag as we create and populate it.$bag
$QueryComputerCount=$i$ADComputerCount=$j$momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Rule: ($RuleId). `n CMDB query looped through ($QueryComputerCount) computers. `n AD query found ($ADComputerCount) matching computers.")
# End MAIN script section# End of script section
#Log an event for script ending and total execution time.$EndTime= Get-Date
$momapi.LogScriptEvent($ScriptName,$EventID,0,"`n Script Completed. `n Script Runtime: ($ScriptTime) seconds.")
# End of script
Each rule will pass in a custom SQL query, and a RuleID (the RuleID is used only for logging to know which rule is running the shared DS.)
The script will run the SQL query, loop through each servername we get in a result, and query AD, to get the DNSHostname and DN of the object (if it exists). Those items will be placed into propertybags to be consumed by the Write Action.
The rule has two parameters configured:
<Parameters><Parameter><Name>SQLQuery</Name><!-- Use ANY query that returns only fully qualified domain names --><Value>
WITH OrderedServers AS
SELECT SERVERNAME, ROW_NUMBER() OVER(ORDER BY SERVERNAME) AS RowNumber
WHERE MG = 'PROD'
WHERE RowNumber BETWEEN 1 and 3
</Value></Parameter><Parameter><Name>RuleId</Name><Value>Demo.CMDB.ADIntegration.DOMAIN.MS1.Rule</Value><!-- We use this to help identify the rule calling the script for troubleshooting --></Parameter></Parameters>
In the above SQL query – I am using numbers of results between 1 and 3 for the first management server. If you had thousands of agents, you could use a method like this to assign 2000 agents to each management server, just using the result number as a separator for each rule.
Next – the Write Action:
<WriteAction ID="WA" RunAs="SC!Microsoft.SystemCenter.ADWriterAccount" TypeID="SC!Microsoft.SystemCenter.ADWriter"><ManagementServerName>d46bb8b5-48b2-c607-4890-33efd9416450</ManagementServerName><!-- This needs to be changed to the GUID of the Windows Computer object for your management server --><Domain>DOMAIN.net</Domain><!-- This needs to be changed to your domain name you want to publish to --><UserAndDomain /><Password /><SecureReferenceId /><dNSXPath>DataItem/Property[@Name='dNSHostName']</dNSXPath><distinguishedNameXPath>DataItem/Property[@Name='distinguishedName']</distinguishedNameXPath></WriteAction>
We will need a distinct rule with a unique write action for each management server we want assignments to. The write action needs to contain the Management Server’s GUID in the <ManagementServerName> XML tag. To get a list of the correct GUIDS:
SELECT bme.DisplayName, bme.BaseManagedEntityId FROM BaseManagedEntity bme JOIN MTV_HealthService mtvhs ON bme.DisplayName = mtvhs.DisplayName WHERE bme.Fullname like ‘Microsoft.Windows.Computer:%’ AND mtvhs.IsManagementServer = 1 ORDER BY bme.fullname
Next, provide the domain we are publishing to.
Now – you can publish to any domain, using one rule for each management server. You can also assign agents to your gateways, using the same process.
I have published an example MP you can use as a template here: