Useful SCCM 2012 Device Collection Queries

Below are some useful SCCM Device Collection Queries. Some of these may require the device collection to be Dynamic.

Device Collection based on OU

SELECT * from SMS_R_System WHERE SMS_R_System.SystemOUName = "YOURDOM.LOCAL/Computers/YourCustomOU"

Device Collection Laptops/Notebooks

SELECT * FROM SMS_R_System INNER JOIN SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID WHERE SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "9", "10", "14" ) 

64bit Devices

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.SystemType = "x64-based PC" 

Windows 8 Devices

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
INNER JOIN
SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
WHERE
SMS_R_System.OperatingSystemNameandVersion
LIKE
"%Workstation 6.2%" or SMS_R_System.OperatingSystemNameandVersion LIKE "%Windows 8%" 

All Servers

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 INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId WHERE SMS_G_System_SYSTEM.SystemRole = "Server"

Domain Controllers

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.PrimaryGroupID = "516"

Computer Name Contains…

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 INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE SMS_G_System_SYSTEM.Name LINK "%COMP%" ORDER BY SMS_R_System.Name