SMS 2003: How to create a query based collection based on Security or Distribution Group Membership

One question that seems to pop up every now and then is how to create a query based collection based on Security or Distribution Group Membership. The bottom line is you need to use the domain name followed by double backslashes then the group name: DomainName\\Groupname.  Note that the double backslashes not a typo but a requirement.

So basically if you create a new Collection and import this query statement it should work fine regardless of security or distribution group. Alter the below to match your domain and group name.

If you right click the computer that you want to add in All Systems you can see the values for SystemGroupName that are available, they are defined by SystemGroupName(0) through (WhateverNumber) but you do not need to add the (#) in the query.

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where SystemGroupName = "buzdo2k3\\DISTGRP1"

Below is the same for Users Distribution or Security Group:

select SMS_R_User.ResourceID,SMS_R_User.ResourceType,SMS_R_User.Name,SMS_R_User.UniqueUserName,SMS_R_User.WindowsNTDomain from SMS_R_User where UserGroupName = "buzdo2k3\\distgrp1"

More Information regarding AD System Group Discovery:

Active Directory System Group Discovery: The Active Directory System Group Discovery method polls an Active Directory domain controller to discover system groups for computer systems that are discovered by other discovery methods and
assigned to the SMS site. In this way, Active Directory System Group Discovery enhances the discovery data of other discovery methods. If a resource is not assigned to an SMS site, Active Directory System Group Discovery does not discover
system group information for that resource.  The Active Directory System Group Discovery method gathers discovery
information about:

  • Organizational unit
  • Global groups
  • Universal groups
  • Nested groups
  • Other non-security groups such as Windows distribution groups

The Active Directory domain can be in mixed mode or native mode. You specify the containers to be polled (such as specific domains, organizational units, or user groups), and SMS routinely polls the containers (and, optionally, their child containers) for the system groups. You can also adjust the schedule of the polling. SMS must have Read access to the containers that you specify for Active Directory System Group Discovery by using the SMS Service account or the site server computer account, depending on the security mode in which SMS is running.

Hope this helps,

Buz Brodin | Support Escalation Engineer