Create a User Collection and a Device Collection that pulls its members from an active directory group
1. Make sure you have Discovery set up on your Active Directory or Specific OU containing groups. You can do this by clicking Administration > Discover Methods then right click on “Active Directory Group Discovery” and choose Properties. Click Add then configure appropriately.
2. From the Collection Properties window select “Membership Rules”
3. Select “Add Rule” then “Query Rule”
4. In the “Query Rule Properties” window Name your query rule and select “Edit Query Statement…”
5. In the “Query Statement Properties” Window select Criteria then the “Criterion Properties” Button (the star)
6. Choose Select
7. Make you’re query statement
User Collection based on Users in a specific group:
Criterion Value: Simple
Attribute Class: User Resource
Attribute: Security Group Name
Operator: is equal to
Value: <Group Name> ex: Domain\AccountingUsers
Device Collection based on Devices in a specific group:
Criterion Value: Simple
Attribute Class: System Resource
Attribute: System Group Name
Operator: is equal to
Value: <Group Name> ex: Domain\AccountingSystems
Looking in to it some more this may be more what you want.
SELECT * FROM SMS_R_SYSTEM
WHERE SMS_R_SYSTEM.LastLogonUserName
In (SELECT SMS_R_USER.UserName
FROM SMS_R_USER WHERE SMS_R_USER.UserGroupName = ‘Domain\\secgroup’)
possibly.
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 LEFT JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId
WHERE SMS_UserMachineRelationship.UniqueUserName IN (SELECT SMS_R_User.UniqueUserName FROM SMS_R_User WHERE SMS_R_User.SecurityGroupName = 'domain\\secgroup')
How about a device collection based on the users logging into devices being members of an AD group?