SCCM Query Collection List.

Simply copy and paste these into the sccm query statement of the query rule. Let me know in the comments below if you need a specific query and I will try to add it to this list.

Note: Some of these collection queries require software or hardware inventory to be enabled. Also, not all query’s work in all SCCM versions.

SCCM Query Collections

Computers that have more than 30 days old hardware information.

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.Name  not in (select SMS_R_System.Name
from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >=  DateAdd(dd,-30,GetDate()))

 Collection of clients not approved.

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_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
where SMS_CM_RES_COLL_SMS00001.IsApproved= "2"

Collection of clients requiring a reboot.

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 AS sms_r_system
inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid
where c.LastEnforcementMessageID = 9

Collection of ConfigMgr clients waiting for another installation to finish.

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 AS sms_r_system
inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 3

All SCCM client computers with less than 5GB free disk space on C:

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_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_G_System_LOGICAL_DISK.FreeSpace <= 5000

 All computer objects with client installed in the specific site “PS1”

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.ADSiteName = "PS1" AND client=1

Collection with all computers without a ConfigMgr client.

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.Client is null

 

Active Directory Collections

Collection based on OU.

Replace “domain.local/OU/OU” with your own domain name and OU that you need a collection of.

select * from  SMS_R_System where LOWER(SMS_R_System.SystemOUName) = "domain.local/OU/OU"

Collection based on many OU.

In case you have many repeated OU structure (one OU name exist in many other OU). Replace “domain.local/%/OU” with your own domain name and OU that you need a collection of.

select *  from  SMS_R_System where SMS_R_System.SystemOUName like "domain.local/%/OU"

Collection based on domain membership.

Replace “domain” with the NETBIOS name of your domain.

select * from  SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup = "domain"

 

Client Collections

Collection for all Workstations.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%"

Collection of all Windows 10 clients.

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 10.0%"

Collection of All Windows 10 Creators Update (1703)

select * 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.BuildNumber = "15063"

Collection of All Windows 10 Aniversary Update (1607)

select * 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.BuildNumber = "14393"

Collection of All Windows 10 November Update (1511)

select * 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.BuildNumber = "10586"

Collection of All Windows 10 RTM (1507).

select *  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.BuildNumber = "10240"

Collection of all Windows 8.1 clients.

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 "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 6.3%"

Collection of all Windows 8 clients.

This query need to be limited to the All Workstations collection to work. If not it will also have Windows Server 2012 members as they share the same version number.

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 "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 6.2%"

Collection of all Windows 7 clients.

This query need to be limited to the All Workstations collection to work. If not it will also have Windows Server 2008 R2 members as they share the same version number.

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 6.1%"

Collection of all Windows Vista clients.

This query need to be limited to the All Workstations collection to work. If not it will also have Windows Server 2008 members as they share the same version number.

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 6.0%"

Collection of all Windows XP clients.

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 5.1%"

Collections based on computer manufacturer model.

Use this query to create collections for your various computer models.

select distinct SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model
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 = "Dell"

Collection of computers ending with odd numbers

Note: This query may be heavy on the SCCM server.

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 "%[13579]"

Collection of computers ending with even numbers

Note: This query may be heavy on the SCCM server.

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 "%[02468]"

Collection of computers with failing hard drive

This collection query requires hardware inventory settings:

  • Classes -> Disk Drives -> Status
  • Classes -> Disk Drives -> Status Info
  • Classes -> Logical Disk -> Status
  • Classes -> Logical Disk -> Status Info
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_DISK on SMS_G_System_DISK.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_DISK.Status = "Pred Fail" order by SMS_R_System.Name DESC

 

Server Roles Collections

Collection for all Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server%"

Collection of all Windows 2016 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 10.0%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 10.0%"

Collection of all Windows 2012 R2 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.3%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.3%"

Collection of all Windows 2012 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.2%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.2%"

Collection of all Windows 2008 R2 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.1%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.1%"

Collection of all Windows 2008 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.0%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.0%"

Collection of all Windows 2003 Servers.

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 5.2%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 5.2%"

Collection of all Domain Controllers.

This query requires that the config manager client is installed and hardware inventory is turned on.

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%"

 

Microsoft Exchange Server Collections

Query based collection for all Microsoft Exchange Servers

select *  from  SMS_R_System inner join SMS_G_System_SERVICE on
SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_SERVICE.Name like "Microsoft Exchange %"

 

SQL Server Collections

Collection of all SQL Servers

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 "%SQLServer%"

Collection of all SQL Servers 2016 (x64)

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft SQL Server 2016 (64-%"

Collection of all SQL Servers 2014 (x64)

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft SQL Server 2014 (64-%"

Collection of all SQL Servers 2012 (x64)

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft SQL Server 2012 (64-%"

Collection of all SQL Servers 2012.

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ProductName = "Microsoft SQL Server 2012"

Collection of all SQL Servers 2008.

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ProductName = "Microsoft SQL Server 2008"

Collection of all SQL Servers 2005.

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.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = 'Microsoft SQL Server 2005')

 

Network Collections

Query based collection based on IP subnet

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.IPSubnets LIKE '192.168.2[1-9].0'

Query based collection based on IP range

This query creates a collection for all devices between the IP range: 10.10.10.11 – 10.10.10.19

select *
from SMS_R_System
where SMS_R_System.IPAddresses like "10.10.10.1[1-9]"

 All Physical Systems

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 = "23" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "17"

 

Other Operating Systems

Linux Collection query

Linux / Unix clients are easy to query as they have SMS_R_System.ClientEdition property set to 13 in the database. This is hos a collection query for linux / unix computers look like in SCCM.

Select SMS_R_System.ClientEdition from SMS_R_System where SMS_R_System.ClientEdition = 13

Mac OS X Collection query

This is a collection query for a with all Mac computers as members of the 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 where SMS_R_System.OperatingSystemNameandVersion like "Mac%"