Populating groups from a SQL server CMDB – step by step

Boris wrote a cool article HERE on how to populate a group of computers in OpsMgr, from an external source…. such as active directory.  In his published example – you run an LDAP query to AD, to return a recordset list if computers, in order to populate them into a group. 

This post will extend that, by showing how to do the same thing – but using a SQL database as the CMDB source for populating groups, instead of AD.  I had a customer who wanted to do this – to dynamically create groups for the purpose of scoping console views and notifications, to the teams that “owned” the different servers.  The CMDB contained this data, but it changes often, so manually controlling this proved to be a pain.

Here is a very simple example of the CMDB, which contains the ServerName, and the team that owns the server, in the “ServerList” table:

As you can see… I can easily write a SQL query to show ONLY servers owned by TEAM 1:

Let’s use this data source… to populate three groups.  Team 1 Group, Team 2 Group, and Team 3 group.

First – I will post my finished XML example at the bottom – go grab that and open it – it will help you follow along with the XML requirements.

In the XML… we basically need 4 components:

1.  We need to define the name of the MP, and add references to other MP’s we will depend on. (<Manifest> Section)

2.  We need to define the groups themselves, and then define the relationships (stating that they will contain Windows Computer Objects) (<TypeDefinitions> section)

3.  We need to run a discovery to populate the groups… this will be a script based discovery that runs only on the RMS, queries the CMDB, matches on the servername FQDN, and populates the group with a windows computer object. (<Monitoring> section)

4.  We need to modify the display strings in the XML MP – in order to show friendly display names for each of the above, in the UI. (<LanguagePacks> Section)

You can simply take the XML posted below, and just modify each section with your custom group names… or add new groups by adding a new class, relationship, discovery/script, and presentation section to each.

Here we go:

Section 1:  <Manifest>

Simply modify the <ID>, <Version>, and <Name> sections based on your custom MP naming standard.

<ManagementPack ContentReadable="true" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Manifest>
    <Identity>
      <ID>SQLBasedGroupDemo</ID>
      <Version>1.0.0.0</Version>
    </Identity>
    <Name>SQLBasedGroupDemo</Name>
    <References>
      <Reference Alias="SC">
        <ID>Microsoft.SystemCenter.Library</ID>
        <Version>6.0.6278.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="Windows">
        <ID>Microsoft.Windows.Library</ID>
        <Version>6.0.6278.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="Health">
        <ID>System.Health.Library</ID>
        <Version>6.0.6278.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="System">
        <ID>System.Library</ID>
        <Version>6.0.6278.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
    </References>
  </Manifest>

Section 2:  <TypeDefinitions>

The example below defines 3 groups, and the relationships for those groups (contains windows computer objects).  Simply replace the bolded red “Team1Group” example with a short name for your custom groups.  (We will define the UI friendly name later, in the <Presentation> section.

<TypeDefinitions>
  <EntityTypes>
    <ClassTypes>
      <ClassType ID="GroupPopulation.Team1Group" Accessibility="Internal" Abstract="false" Base="System!System.Group" Hosted="false" Singleton="true" />
      <ClassType ID="GroupPopulation.Team2Group" Accessibility="Internal" Abstract="false" Base="System!System.Group" Hosted="false" Singleton="true" />
      <ClassType ID="GroupPopulation.Team3Group" Accessibility="Internal" Abstract="false" Base="System!System.Group" Hosted="false" Singleton="true" />
    </ClassTypes>
    <RelationshipTypes>
      <RelationshipType ID="GroupPopulation.Team1GroupContainsWindowsComputers" Accessibility="Internal" Abstract="false" Base="System!System.Containment">
        <Source>GroupPopulation.Team1Group</Source>
        <Target>Windows!Microsoft.Windows.Computer</Target>
      </RelationshipType>
      <RelationshipType ID="GroupPopulation.Team2GroupContainsWindowsComputers" Accessibility="Internal" Abstract="false" Base="System!System.Containment">
        <Source>GroupPopulation.Team2Group</Source>
        <Target>Windows!Microsoft.Windows.Computer</Target>
      </RelationshipType>
      <RelationshipType ID="GroupPopulation.Team3GroupContainsWindowsComputers" Accessibility="Internal" Abstract="false" Base="System!System.Containment">
        <Source>GroupPopulation.Team3Group</Source>
        <Target>Windows!Microsoft.Windows.Computer</Target>
      </RelationshipType>
    </RelationshipTypes>
  </EntityTypes>
</TypeDefinitions>

Section 3:  <Monitoring>

In this section – we define the discovery, and add the script to run.  In this example – I am running a VBscript with a SQL query to the CMDB.  You will need to modify the group name – just like you did above.  This is where we create a discovery to populate each group – so we will need one of these sections for each group in the MP.  Each of these sections will run a distinct script, with a different query, depending on which computers you want populated.

I bolded in RED the group name sections you will need to modify, just like we did above… and you will need to modify the SQL DB information, and the script name.

I set the discovery time to every 3600 seconds in this example…. you should probably set this to once or twice a day max…. no need to keep re-running it for groups that wont change that often.

  <Monitoring>
    <Discoveries>
      <Discovery ID="Team1Group.Discovery" Enabled="true" Target="SC!Microsoft.SystemCenter.RootManagementServer" ConfirmDelivery="true" Remotable="true" Priority="Normal">
        <Category>Discovery</Category>
        <DiscoveryTypes>
          <DiscoveryClass TypeID="GroupPopulation.Team1Group" />
        </DiscoveryTypes>
        <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedScript.DiscoveryProvider">
          <IntervalSeconds>3600</IntervalSeconds>
          <SyncTime />
          <ScriptName>Team1GroupDiscovery.vbs</ScriptName>
          <Arguments>$MPElement$ $Target/Id$</Arguments>
          <ScriptBody><![CDATA[Dim SourceId
Dim objConnection
Dim oRS
Dim sConnectString
Dim ManagedEntityID
Dim oAPI
Dim oDiscoveryData
SourceId                = WScript.Arguments(0)
ManagedEntityId         = WScript.Arguments(1)
Set oAPI                = CreateObject("MOM.ScriptAPI")
Set oDiscoveryData      = oAPI.CreateDiscoveryData(0,SourceId,ManagedEntityId)
sConnectString = "Driver={SQL Server}; Server=OMDW; Database=CMDB;"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open sConnectString
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "select ServerName from ServerList where MonitorGroup = 'Team 1'", objConnection
Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='GroupPopulation.Team1Group']$")
While Not oRS.EOF
Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Windows!Microsoft.Windows.Computer']$")
serverInstance.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$",oRS.Fields("ServerName")
Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.Team1GroupContainsWindowsComputers']$")
relationshipInstance.Source = groupInstance
relationshipInstance.Target = serverInstance
oDiscoveryData.AddInstance relationshipInstance
oRS.MoveNext
Wend
objConnection.Close
Call oAPI.Return(oDiscoveryData)
                     ]]></ScriptBody>
          <TimeoutSeconds>120</TimeoutSeconds>
        </DataSource>
      </Discovery>

Section 4:  <LanguagePacks>

Here – we will take ALL of the modifications we made in the prior three steps, and match them up with friendly names to show in the UI:

Essentially – just modify the MP name, group name, relationship name, and discovery name, to match what you did above…. and assign each a friendly name that you want to see in the UI.  I will bold in red the sections to modify, for the “Team 1 servers”.  You would continue this for as many groups as you used:

<LanguagePacks>
  <LanguagePack ID="ENU" IsDefault="true">
    <DisplayStrings>
      <DisplayString ElementID="SQLBasedGroupDemo">
        <Name>SQL Based Group Population Demo MP</Name>
      </DisplayString>

      <DisplayString ElementID="GroupPopulation.Team1Group">
        <Name>Team 1 Servers Group</Name>
      </DisplayString>
      <DisplayString ElementID="GroupPopulation.Team1GroupContainsWindowsComputers">
        <Name>Team 1 SQL Based Group Contains Windows Computers</Name>
      </DisplayString>
      <DisplayString ElementID="Team1Group.Discovery">
        <Name>Team 1 SQL Based Group Discovery</Name>
        <Description />
      </DisplayString>

      <DisplayString ElementID="GroupPopulation.Team2Group">
        <Name>Team 2 Servers Group</Name>
      </DisplayString>
      <DisplayString ElementID="GroupPopulation.Team2GroupContainsWindowsComputers">
        <Name>Team 2 SQL Based Group Contains Windows Computers</Name>
      </DisplayString>
      <DisplayString ElementID="Team2Group.Discovery">
        <Name>Team 2 SQL Based Group Discovery</Name>
        <Description />
      </DisplayString>

      <DisplayString ElementID="GroupPopulation.Team3Group">
        <Name>Team 3 Servers Group</Name>
      </DisplayString>
      <DisplayString ElementID="GroupPopulation.Team3GroupContainsWindowsComputers">
        <Name>Team 3 SQL Based Group Contains Windows Computers</Name>
      </DisplayString>
      <DisplayString ElementID="Team3Group.Discovery">
        <Name>Team 3 SQL Based Group Discovery</Name>
        <Description />
      </DisplayString>

    </DisplayStrings>
  </LanguagePack>
</LanguagePacks>

That’s it!  If you get errors trying to import – you most likely modified a definition incompletely…. the import error should help you figure out what's wrong.

Now I can go to my groups – find “Team 1 Group” and see if it is populated:  SUCCESS!

I am attaching my working sample MP below