One of the fundamental things you need to do with any SCCM installation and deployment, you need to get the basics right. Queries are those basic building blocks which everything else in SCCM is based on. Once you have all your custom queries setup, then you can setup collections based on the queries, once this is done, all other SCCM components is relatively strait forward.

A good way to target separate machines in queries and collections is by their Chassis Type.

Chassis Types

1	Other
2	Unknown
3	Desktop (Virtual machines as well)
4	Low Profile Desktop
5	Pizza Box
6	Mini Tower
7	Tower
8	Portable (Laptop or Notebook)
9	Laptop (Not as popular)
10	Notebook (Popular)
11	Hand Held
12	Docking Station (Laptop or Notebook)
13	All in One (Generally Windows on Apple hardware, Manufacturer attribute will say Apple Inc.)
14	Sub Notebook
15	Space-Saving
16	Lunch Box
17	Main System Chassis (Physical Server)
18	Expansion Chassis
19	Sub Chassis
20	Bus Expansion Chassis
21	Peripheral Chassis (Laptop or Notebook)
22	Storage Chassis
23	Rack Mount Chassis (Physical Server)
24	Sealed-Case PC

SQL Query

You should run the following SQL query against the SMS/SCCM database to spit out an entire list of machines on your network with their Chassis Type and Manufacturer. You need to know exactly what you have got in the field.

SELECT     v_GS_SYSTEM_ENCLOSURE.ChassisTypes0, v_R_System.Name0, v_R_System.Netbios_Name0, v_GS_SYSTEM_ENCLOSURE.Manufacturer0
FROM         v_GS_SYSTEM_ENCLOSURE INNER JOIN
                      v_R_System ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID

I have added in here the Manufacturer attribute from the Computer System attribute class because if you rely on only the Chassis Type of a machine only, then when querying for the desktops Chassis Type (3), this will also list some virtual machines which most likely could be servers – a little dangerous! So if you add in the Manufacturer then you can select only the Manufacturers that you have while leaving out virtual machines (Microsoft Corporation, Apple Inc. and VMware, Inc.).

SCCM queries

All virtual machines

select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName 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.Manufacturer in ("VMware, Inc.","Microsoft Corporation","Apple Inc.")

Virtual client machines (not servers)

select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Manufacturer in ("Apple Inc.","VMware, Inc.","Microsoft Corporation") and (SMS_G_System_OPERATING_SYSTEM.Name like "%Windows XP%" or SMS_G_System_OPERATING_SYSTEM.Name like "%Windows 7%")

All client laptops

select SMS_R_System.Name, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8","9","10","12","14","18","21") and (SMS_G_System_OPERATING_SYSTEM.Name like "%Windows XP%" or SMS_G_System_OPERATING_SYSTEM.Name like "%Windows 7%")

All client desktops or workstations

select SMS_R_System.Name, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","13","15","16") and (SMS_G_System_OPERATING_SYSTEM.Name like "%Windows XP%" or SMS_G_System_OPERATING_SYSTEM.Name like "%Windows 7%")

All physical servers

select SMS_R_System.Name, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("17","23")

All virtual servers

select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName 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.Manufacturer in ("Microsoft Corporation","VMware, Inc.") and SMS_G_System_COMPUTER_SYSTEM.DomainRole in (3,4,5)

All servers

select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName 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.DomainRole in (3,4,5)

Notice for the last two queries I have used SMS_G_System_COMPUTER_SYSTEM.DomainRole. The attribute class is Computer System and the attribute is Domain Role.

Computer System – Domain Role

0	Standalone Workstation
1	Member Workstation
2	Standalone Server
3	Member Server
4	Backup Domain Controller
5	Primary Domain Controller

Windows XP x64

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from sms_r_system inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%"

Windows Vista x86

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from sms_r_system inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Vista%"

Windows Vista x64

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from sms_r_system inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Vista%"

Windows 7 x86

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from sms_r_system inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows 7%"

Windows 7 x64

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from sms_r_system inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows 7%"

Windows XP x86 Laptops

select distinct Name, SMSAssignedSites, IPAddresses, IPSubnets, OperatingSystemNameandVersion, ResourceDomainORWorkgroup, LastLogonUserDomain, LastLogonUserName, SMSUniqueIdentifier, ResourceId, ResourceType, NetbiosName  from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8","9","10","12","14","18","21")

Windows x86 Desktops

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","13","15","16")

I won’t list the rest of the multiple possible combinations of queries, however you can see in RED, 3 sections (Chassis Type, OS and System type) to change to give you different results.

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","13","15","16")

The following query gives 5 combinations of results in RED. I am selecting IP subnet information from two areas to give more of a complete result. Windows XP x86 Laptops (Location based)

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_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like "10.2.30.%" or SMS_R_System.IPSubnets = "10.2.30.0") and SMS_G_System_COMPUTER_SYSTEM.SystemType = "X86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8","9","10","12","14","18","21")

Collection of machines where software is not installed

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 not in (select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Watchguard Authentication%")

Collection of machines where software is not installed (Member Workstations only)

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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Watchguard Authentication%") and SMS_G_System_COMPUTER_SYSTEM.DomainRole = 1