System Center Configuration Manager SCCM Collection Queries

Some of the useful queries we used and still being used;

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.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client

from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like

“%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 “%workstation 10.0%”

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 “%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 “%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 “%workstation 6.1%”

 

Collection for 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 where SMS_R_System.OperatingSystemNameandVersion like

“%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.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from

SMS_R_System

where SMS_R_System.OperatingSystemNameandVersion like “%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.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from

SMS_R_System

where SMS_R_System.OperatingSystemNameandVersion like “%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.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from

SMS_R_System

where SMS_R_System.OperatingSystemNameandVersion like “%Server 6.1%”

 

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

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 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”

 

SQL Server 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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Microsoft SQL Server 200%” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “Microsoft SQL Server 20%”

 

All Windows Workstations

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

 

All Systems with HyperV Role

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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_SERVICE.DisplayName like “Hyper-V Virtual Machine Management”

 

SCCM All Laptops 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_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” )