Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Populating groups from a SQL server CMDB – step by step

Populating groups from a SQL server CMDB – step by step

  • Comments 28
  • Likes

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:

image

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

image

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!

image

 

 

I am attaching my working sample MP below

Attachment: SQLBasedGroupDemo.zip
Comments
  • I used something similar to this to create a "Critical Servers List". But I did not use a DB but a text file sitting on the RMS.

    This allowed me to monitor our whole environment but only be alerted in the middle of the night for critical servers.

  • Fantastic post.  We needed this about a year ago.

  • nice. But I would rather prefer see such "manual" using Autoring console :)

  • I agree.  I plan to document just that.... would be nice - huh?

  • HI

    I try create group which contain agent with heartbeat failures.

    But have problem. Could you view  my MP and may be you see wrong code ?

    ...

    <TypeDefinitions>

       <EntityTypes>

         <ClassTypes>

           <ClassType ID="GroupPopulation.Team1Group" 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>SC!Microsoft.SystemCenter.AgentWatcher</Target>

           </RelationshipType>

         </RelationshipTypes>

       </EntityTypes>

     </TypeDefinitions>

     <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>120</IntervalSeconds>

             <SyncTime />

             <ScriptName>Team1GroupDiscoverynew.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 = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OperationsManager;Data Source=."

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.Open sConnectString

    Set oRS = CreateObject("ADODB.Recordset")

    oRS.Open "SELECT ManagedEntityGenericView.Id FROM ManagedEntityGenericView INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id WHERE (ManagedEntityGenericView.IsAvailable = 'false') AND (ManagedTypeView.Name = 'Microsoft.SystemCenter.Agent') ORDER BY ManagedEntityGenericView.AvailabilityLastModified ", objConnection

    Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='GroupPopulation.Team1Group']$")

    While Not oRS.EOF

    Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")

    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",oRS.Fields("Id")

    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","Microsoft.SystemCenter.AgentWatchersGroup"

    Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.Team1GroupContainsWindowsComputers']$")

    relationshipInstance.Source = groupInstance

    relationshipInstance.Target = serverInstance

    'Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SC!Microsoft.SystemCenter.AgentWatcher']$")

    'serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",oRS.Fields("Id")

    'serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatchersGroup']/WatcherGroupName$","{8dd68103-6ee8-2654-2c32-bb9339a8e53b}"

    '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>

       </Discoveries>

    .....

    The group is empety, and I have error 33333 and 10801 (about  Invalid relationship target specified in the discovery data item.)

    Thanks

  • oops

    'oDiscoveryData.AddInstance relationshipInstance

    without '

    but problem not resolved yet.

  • How to avoid  error: 'The group can not be modified becouse trere is no group population rule' on OM Console/Authoring/Groups/Properies?

  • I am having the same problem with 'The group can not be modified because there is no group population rule' when attempting to view the properties of my group.  Additionally, no group members are created.

  • This is very cool but I would like to read in from a text or csv. What would be the syntax to read in from a csv file?

  • That would simply be a modification to the VBscript - instead of querying SQL for your recordset - you parse the text file.  There are lots of examples on the web for this.

  • This is very nice.  However, I'm getting a "ADODB.Connection: Provider is not specified and there is no designated default provider." error.  This is likely a 64-bit RMS issue.  Anyone have a solution?

  • Problem solved:  Used this:

    sConnectString = "Provider=SQLOLEDB;User ID=<username>;Password=<Password>;Initial Catalog=<database>;Data Source=<server\Instance>;Network Library=DBMSSOCN;"

  • I've managed to do this for windows computer objects without a problem, but I'm having trouble adding healthservicewatcher objects instead. I've converted the script to this but nothing shows up in the group (debugging and workflow simulators show it should work):

    Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='SystemCenter!Microsoft.SystemCenter.HealthServiceWatcher']$")

    serverInstance.AddProperty "$MPElement[Name='SystemLibrary6062780!System.Entity']/DisplayName$",ServerName

    Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='GroupPopulation.Tier3ServersGroupContainsHealthWatchers']$")

    relationshipInstance.Source = groupInstance

    relationshipInstance.Target = serverInstance

  • Sorry if this is a dumb question but how would you go about setting up a way to check that each computer in the external source is in SCOM?

    If they arent i seem to be getting a discovery error which stops the discovery completely

  • Hi Kevin,

    I encountered the same problem as others saying "The group can not be modified because there is no population rule defined for this group". There is not much info on the internet discussing this error. Do you have any idea?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs