SCCM Collection queries

This post provides various WQL queries to generate custom SCCM collections (07/12) to group the machines in a collection.

All of the queries from this post have tested in my lab. If you see any errors in a query please post a comment so I can fix them.

This page will be updated periodically.

Please note; when you are copying a query from a web browser you need to make sure that the browser is not adding any extra characters.
SCCM Collection query for various models in one  collection:
The following WQL query will populate a collection with  all the dell Latitude and XPS models. Change the models names as required.
******************************************************************************
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_G_System_COMPUTER_SYSTEM.Model like "Latitude%" or SMS_G_System_COMPUTER_SYSTEM.Model like "XPS%"
*******************************************************************************
SCCM Collection query for all workstations:
The following WQL query for SCCM will poplulate all the workstaions into one collection.
This query will be useful when
*******************************************************************************
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.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%"
 *******************************************************************************

SCCM Collection query to get 32 bit and 64 bit machines:

This WQL query will create a SCCM collection for all the 64 bit Workstations in the organization.

To create a collection for 32-bit workstations, replace the “x64” with “X32”.

*********************************************************************************

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

*********************************************************************************

SCCM Collection query for a PC model:

This WQL query will create a SCCM collection for all the VMWare Workstations in the organization.

To create a collection for specific model workstation, replace the “VMware virtual Platform” with a model name of the machine you want to create a collection.

*********************************************************************************

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_G_System_COMPUTER_SYSTEM.Model like "VMware Virtual Platform"

*********************************************************************************

SCCM Collection query for Windows 7 machines in multiple domains:

This query makes a collection of all Windows 7 machines on different domains.

You can rename DOMAIN1 to DOMAIN5 as you needed.

************************************************************

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.ResourceDomainORWorkgroup like "DOMAIN1" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN2" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN3" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN4" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN5") and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 6.1"

************************************************************

SCCM Collection for Windows 7 machines from a OU and multiple domains:
This WQL Query will create a collection for all Windows 7 machines with combination of OU and domains.

*********************************************************
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 like "AD.YOURDOMAIN.COM/OU" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN1" or SMS_R_System.ResourceDomainORWorkgroup like "DOMAIN2")and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation 6.1"

*********************************************************

SCCM Collection for Laptops:

Below WQL query creates a collection for all the laptops in the environment based on the System Enclosure attribute and Chassis type.

*********************************************************

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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes like "8 , 9, 10, 11, 12, 14, 18, 21"

**********************************************************

SCCM Collection for Desktops:

Below WQL query creates a collection for all the desktops in the environment based on the System Enclosure attribute and Chassis type.

***********************************************************

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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes like "('3','4','5','6','7','15','16')"

************************************************************

SCCM Collection query for inactive workstations:

Below WQL query creates a collection with all inactive machines for 90 days in the environment. The 90 days can be modified as required by editing the last line of 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 SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=90) and AgentName = "Heartbeat Discovery"))

************************************************************* 

SCCM Collection query for machines not rebooted in last30 days:

This is an easy WQL query to get the machine details which hasn’t been re-booted for 30 days.

We can change the 30 days at end of the statement to adjust desired days.

This query is targeted only for workstations such as XP, Vista and Windows 7.

*********************************************************

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >30)

********************************************************** 

SCCM Collection query for no client machines:

The following SCCM Collection query is useful when you have many machines without SCCM Client on the client machines.

Just create a new collection using the below query;

***********************************************************

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId =   r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name   = s2.Name and s1.ResourceId != s2.ResourceId  and R.Client = null

***********************************************************

SCCM Collection Query to get Duplicate Hostnames in SCCM console:

The following WQL query will add all the machines with duplicate machine name.

***********************************************************

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId =   r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name   = s2.Name and s1.ResourceId != s2.ResourceId  and R.Client = null

****************************************************************
SCCM Collection query computer name:

The following WQL query will add the machines to a collection based on their naming conventions. This query is useful if we want to only some machines to a collection using a query with starting names (ABC-XXXX) or machines named using business unit (HR-XXXXXX).

Change the values ( ABC and DEF ) as your requirements.

*************************************************************************

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.Name like "%HR%" or SMS_R_System.Name like "%ABC%" or SMS_R_System.Name like "%DEF%"

**************************************************************************