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:


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

SQLBasedGroupDemo.zip

Comments (32)

  1. Kevin Holman says:

    Pretty sure thats normal – since the UI likely doesn't understand our custom group population discovery rule.

  2. Kevin Holman says:

    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.

  3. mschippr says:

    Here is an expansion of the post by MedeBay and Kevin to populate groups with health watcher agent items. i have incorporated some error handling to allow for CMDB databases which may or may not include servers that don’t have SCOM agents or have correct FQDN's in your CMDB, previously the script would just error and fail. This modified script is also case independent and uses the Datawarehouse's own FQDN instead of the one in your CMDB, as long as the NETBIOS name is correct it will pick it up.

    Thanks for this initial post it has helped me with a large scale deployment of SCOM which i look after. Previously we have had to manually keep our OpsMgr groups updated with our CMDB which has gotten more and more complicated as new customers have come on-board. Currently we have over 1000 agents deployed including windows and unix machines. I am currently modifying this script to also populate "Windows.Unix.Computer" items.

    ———–

    Option Explicit

    Dim objConnection

    Dim objConnection2

    Dim oRS

    Dim oRS2

    Dim sConnectString

    Dim sConnectString2

    Dim SourceId

    Dim ManagedEntityID

    Dim oAPI

    Dim oDiscoveryData

    Dim groupInstance

    Dim serverInstance

    Dim relationshipInstance

    Dim id

    SourceId = WScript.Arguments(0)

    ManagedEntityId = WScript.Arguments(1)

    Set oAPI = CreateObject("MOM.ScriptAPI")

    Set oDiscoveryData = oAPI.CreateDiscoveryData(0,SourceId,ManagedEntityId)

    Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Group.CAASLA5X16.HealthWatchers']$")

    oDiscoveryData.IsSnapshot = true

    sConnectString = "Provider=SQLOLEDB;User ID=***********;Password=**********;Initial Catalog=MDB;Data Source=**************;"

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.Open sConnectString

    Set oRS = CreateObject("ADODB.Recordset")

    oRS.Open("SELECT DISTINCT [resource_name] FROM [database] WHERE [inactive_flag] != '1' AND [service_type] LIKE '%16%'"), objConnection

    While Not oRS.EOF

    sConnectString2 = "Provider=SQLOLEDB.1;Data Source=*************;Initial Catalog=OperationsManager;User Id=**********;Password=*********;"

    Set objConnection2 = CreateObject("ADODB.Connection")

    objConnection2.Open sConnectString2

    Set oRS2 = CreateObject("ADODB.Recordset")

    oRS2.Open("SELECT DISTINCT [Id], [Path] FROM [OperationsManager].[dbo].[ManagedEntityGenericView] WHERE FullName LIKE '" + "Microsoft.SystemCenter.HealthService:" + LTRIM(RTRIM(oRS.Fields("resource_name"))) + "%" + "'"), objConnection2

    If Not oRS2.BOF Then

    oRS2.MoveFirst

    End If

    If Not oRS2.EOF Then

    If Not IsNull(oRS2.Fields("Id")) AND oRS2.Fields("Id") <> "" Then

    id = oRS2.Fields("Id")

    id = Left(Right(Id,37),36)

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

    Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='Group.CAASLA5X16.ContainsHealthWatchers']$")  

    serverInstance.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$",oRS2.Fields("Path")

    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",id

    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceName$",oRS2.Fields("Path")

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

    relationshipInstance.Source = groupInstance

    relationshipInstance.Target = serverInstance

    oDiscoveryData.AddInstance relationshipInstance

    End If

    End If

    oRS.MoveNext

    Wend

    objConnection2.Close

    objConnection.Close

    Set objConnection2 = nothing

    Set objConnection = nothing

    Call oAPI.Return(oDiscoveryData)

    1. John Austin says:

      Does this also remove nodes from the group when they are removed from the db?

  4. If you need a fully dynamic solution, you can set the base class to Microsoft.SystemCenter.ComptuerGroup and flag Singleton=false.  Then we can use variable notation to populate display names.  This way, we do not need to update the XML each time a group is added/removed from the CMDB.

  5. Anonymous says:

    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?

  6. Kevin Holman says:

    That is correct.  When the discovery data is submitted – if it submits ANY data for a computer that does not exist in SCOM – the discovery data is rejected and we log this event on the management server OpsMgr event log.

    Therefore – if you need error correction – your script will need to gather the data from your CMDB datasource, ensure that each object exists as an object in SCOM, then it will be submitted.  Essentially, you need to put error correction in your script.  This is the single biggest reason that people have challenges implementing this solution.

  7. MedeBay says:

    Health Watcher (Agent) group from CMDB

    For anyone interested here is Kevin's script modified to create a group of Health Watchers from a CMDB:

    Dim objConnection

    Dim oRS

    Dim oRS2

    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)

    Set groupInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name="CMDBGroupPopulation.HealthWatchers']$")

    oDiscoveryData.IsSnapshot = true

    sConnectString = "Driver={SQL Server};Server=<server>; Database=<database>;"

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.Open sConnectString

    Set oRS = CreateObject("ADODB.Recordset")

    oRS.Open "select distinct servername instancename from serverlist ", objConnection

    While Not oRS.EOF

    sConnectString2 = "Driver={SQL Server};Server=<Ops DB Server>; Database=OperationsManager;"

    Set objConnection2 = CreateObject("ADODB.Connection")

    objConnection2.Open sConnectString2

    Set oRS2 = CreateObject("ADODB.Recordset")

    oRS2.Open("SELECT DISTINCT [Id] FROM [OperationsManager].[dbo].[ManagedEntityGenericView] where path = '" + oRS.Fields("instancename") + "' and FullName like 'Microsoft.SystemCenter.HealthService:%'"), objConnection2

    id = oRS2.Fields("Id")

    id = Left(Right(Id,37),36)

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

    Set relationshipInstance = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='CMDBGroupPopulation.ContainsHealthWatchers']$")  

    serverInstance.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$",oRS.Fields("InstanceName")

    serverInstance.AddProperty "$MPElement[Name='SC!Microsoft.SystemCenter.HealthServiceWatcher']/HealthServiceId$",id

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

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

    relationshipInstance.Source = groupInstance

    relationshipInstance.Target = serverInstance

    oDiscoveryData.AddInstance relationshipInstance

    oRS.MoveNext

    Wend

    objConnection2.Close

    objConnection.Close

    Call oAPI.Return(oDiscoveryData)

  8. Kevin Holman says:

    I agree.  I plan to document just that…. would be nice – huh?

  9. SteveH says:

    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.

  10. nomad says:

    Fantastic post.  We needed this about a year ago.

  11. ziembor says:

    nice. But I would rather prefer see such "manual" using Autoring console 🙂

  12. alex says:

    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

  13. alex says:

    oops

    ‘oDiscoveryData.AddInstance relationshipInstance

    without ‘

    but problem not resolved yet.

  14. ziembor says:

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

  15. mccocha says:

    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.

  16. jg says:

    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?

  17. shall@troweprice.com says:

    Problem solved:  Used this:

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

  18. David says:

    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

  19. ML49448 says:

    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

  20. William says:

    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?

  21. William says:

    Forgot to mention, the error shows when trying to view the Property of the newly created groups. And ‘view group memberships’ shows empty.

  22. SCOMcat says:

    I managed to get the groups populated, but I also get the "no group population rule defined" when trying to open the properties of the group.

  23. Jeff Delaney says:

    I am getting that same error "The group can not be modified because there is no group population rule defined for this group".  Problem with that is I modified the script so it populates specific group for WSUS reboots based on AD OU.  If I try to add these groups to SCOM Scheduler to put them in Maintenance Mode, it doesn't work due to the above error.

    Is there a way around this?

  24. Brian Hansen says:

    Hey Kevin,

    I implimented this just fine except for one annoyance. The solution appears to be case sensative. The case of the computer name in the CMDB has to match the case of the Name in SCOM. Any thoughts on how to work around this?

    Thanks,

    Brian

  25. Chris Temple says:

    Kevin Holman, you said:

    "Therefore – if you need error correction – your script will need to gather the data from your CMDB datasource, ensure that each object exists as an object in SCOM, then it will be submitted.  Essentially, you need to put error correction in your script.  This is the single biggest reason that people have challenges implementing this solution."

    That is exactly where I am stuck.  I modified this script to pull from LDAP ADSI and get a list of group memberships that are basically PrincipalNames of workstations/laptops in AD.  What I am finding is that some of the names in AD are not for whatever reason in SCOM as a Windows Computer and the entire SCOM submission fails and I get an error in the event log.  I verified this by altering my script and making only valid entries become submitted.

    My question is, how in the world can I do 'error correction' or 'ensure that each object exists as an object in SCOM" ?  I don't see any SCOM scripting methods that allow me to query SCOM for status or data.  I would even consider exporting out the list of PrincipalNamed computers in SCOM and cross referencing that before I add them but I cant find anything for that either.  It appears when you call .Return on the SCOM scripting object, you are done, no more interaction.  

    Any ideas?

  26. Chris Temple says:

    Dim SourceId

             Dim objConnection

             Dim oRS

             Dim objCommand

             Dim sConnectString

             Dim ManagedEntityID

             Dim oAPI

       Dim iCounter

             Dim oDiscoveryData

             Const ADS_SCOPE_SUBTREE = 2

             SourceId                = WScript.Arguments(0)

             ManagedEntityId         = WScript.Arguments(1)

             Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile("d:log.txt",2,true)

             '– insert ad query

             Set objConnection = CreateObject("ADODB.Connection")

             Set objCommand =   CreateObject("ADODB.Command")

             objConnection.Provider = "ADsDSOObject"

             objConnection.Open "Active Directory Provider"

             Set objCommand.ActiveConnection = objConnection

             objCommand.CommandText ="SELECT sAMAccountName FROM 'LDAP://dc=domain,dc=com' WHERE memberOf='cn=Appsense.GPO.Computer,OU=Appsense Groups,OU=Software,dc=domain,dc=com"

             objCommand.Properties("Page Size") = 1000

             objCommand.Properties("Timeout") = 30

             objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

             objCommand.Properties("Cache Results") = False

             Set oRS = objCommand.Execute

             ' — end ad query

    Set oAPI                = CreateObject("MOM.ScriptAPI")

             Set oDiscoveryData      = oAPI.CreateDiscoveryData(0,SourceId,ManagedEntityId)

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

             oDiscoveryData.IsSnapshot = true

             objFileToWrite.WriteLine("Source ID: " + SourceID)

             objFileToWrite.WriteLine("ManagedEntityID: " + ManagedEntityId)

             objFileToWrite.WriteLine("Start of loop")

             While Not oRS.EOF

             strComputerName = replace(oRS("sAMAccountName").Value, "$", "")

             objFileToWrite.WriteLine(strComputerName + ".lgeenergy.int")

             Set serverInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Windows!Microsoft.Windows.Computer'

    serverInstance.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$",strComputerName + ".domain.com"

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

             relationshipInstance.Source = groupInstance

             relationshipInstance.Target = serverInstance

             oDiscoveryData.AddInstance relationshipInstance

             oRS.MoveNext

             Wend

                           Call oAPI.Return(oDiscoveryData)

       objFileToWrite.WriteLine("End of Loop")

                 Set oAPI = nothing

       Set oDiscoveryData = nothing

       Set groupInstance = nothing

             objConnection.Close

             objFileToWrite.Close

  27. pete says:

    Hello Kevin,

    I am new to SCOM, and have been using your posts to guide me along (thank you for your very informative posts about the data warehouse maintenance!). I’m more of a SQL Server guy, so i’ve been more concentrated on the SQL Server side of things, but now I am
    venturing into groups and dashboards.

    My new quest is to dynamically assign SQL instances to groups. I would like to use your example in this post, just modify it to put the SQL instances as group members. I am doing this because I have a need to differentiate multiple instances on the same server
    to different work groups. basically, I have a server with 5 instances, and each instance belongs to a different work group. I need a way to group and create views and dashboards on this information. I figured following this article would work great, if I can
    change it around to use instances as group members instead of windows computers.

    I Guess my questions are, does this sound do-able, and is it anything more than changing the target types in the XML? I have already set up the table the way I think it should be.

    Thank you.

  28. Phani says:

    Hi kevin, This is very interesting article. How long does it take for the discovery to populate groups?

    1. Kevin Holman says:

      depends on the interval of the workflow. You can track the progress on the management servers, looking at 31400-31411 events.