Link to home
Start Free TrialLog in
Avatar of jskfan
jskfanFlag for Cyprus

asked on

SCCM Collection Query.

SCCM Collection Query.

I have 2 domain controllers [DC1 and DC2 ]Discovered in SCCM and with SCCM client installed on both of them.
User generated imageI would like to create a query based collections for Domain Controllers. I used this query:
select
*
from
SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Roles like "%Domain_Controller%"

Open in new window


however when I click Update Membership on the collection I created and named DC, it shows only one Domain Controller which is DC1, it does not show DC2.

User generated image
Any help will be very much appreciated.

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Hello There
Hello There

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jskfan

ASKER

followed this link...And it worked : https://blog.thesysadmins.co.uk/sccm-2012-creating-device-collections.html

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 SMS_R_System.SystemOUName = "TEST.LOCAL/DOMAIN CONTROLLERS"
Avatar of jskfan

ASKER

Thank you !
Avatar of Hello There
Hello There

Great!
Hi,

I see you have the answer already. One extra tip I will make: Avoid the both like and %, e.g. like "%Domain_Controller%"

why? 1) SQL has to work much harder*, meaning it is slower, meaning you get to sit there watching the hourglass more. You don't want that.
2) The %DC% is a double wildcard and is not even necessary. The more % wildcards you use, the slower things are
3) That query relies on the target successfully getting hardware inventory (HWINV) and returning it. You will find two failures spoil that - machines with no agent at all and machines where HWINV is delayed or broken. Either way I like all the other Expert's answers. All work and it's down to taste which you use!

*it's only seconds vs milliseconds but 100 collections in you will be sat at the console waiting. and waiting. Avoid the pain and tune collections.
This is a good read: https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sccm-housekeeping-collections/ba-p/1227039 (and the original post).