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 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.
Windows 10 (1511)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 = N’5.00.8325.1000′
Windows 10 (1602)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 = N’5.00.8355.1000′
Windows 10 (1610)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8458.100%’
Windows 10 (1702)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8498.100%’
Windows 10 (1706)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8540.100%’
Windows 10 (1710)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8577.100%’
Windows 10 (1810)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8740.10%’
Windows 10 (1906)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8853.10%’
Windows 10 (1910)
select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Client_Version0 like N’5.00.8913.10%’
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_Systemwhere 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_Systemwhere SMS_R_System.OperatingSystemNameandVersion like “Microsoft Windows NT Workstation 6.3%” |
Collection of all Windows 8 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.2%” |
Collection of all Windows 7 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_Systemwhere SMS_R_System.OperatingSystemNameandVersion like “Microsoft Windows NT Workstation 6.1%” |
Server 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.Clientfrom 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 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.Clientfrom 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.Clientfrom 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.Clientfrom 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.Clientfrom 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 Domain Controllers.
| select * from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceIdwhere 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 onSMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceIdwhere SMS_G_System_SERVICE.Name like “Microsoft Exchange Transport%” |
SQL Server Collections
Collection of All SQL Server 2016
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 2016’)
Collection of All SQL Server 2014
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 2014’)
Collection of All SQL Server 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
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 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_Systemwhere SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceIDfrom SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = ‘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_Systemwhere SMS_R_System.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMSwhere SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = ‘Microsoft SQL Server 2005’) |
Computer Model Collections
All Dell Systems
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.Manufacturer like “Dell%”
All Hewlet-Packard 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Manufacturer like “Hewlett-Packard%” or SMS_G_System_COMPUTER_SYSTEM.Manufacturer like “HP%”
All Lenovo 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Manufacturer like “Lenovo%”
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
Select SMS_R_System.ClientEdition from SMS_R_System where SMS_R_System.ClientEdition = 13
Mac OS X Collection 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.OperatingSystemNameandVersion like “Mac%
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_Systeminner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceIdwhere 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_systeminner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceidwhere 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_systeminner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 3 |
All SCCM client computers with less than 10GB 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.ResourceIdwhere SMS_G_System_LOGICAL_DISK.DeviceID = “C:” and SMS_G_System_LOGICAL_DISK.FreeSpace <= 100000 |
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.Clientfrom SMS_R_System where SMS_R_System.Client is null |