Sccm Collection Queries


Queries that can be used for SCCM collections

All Windows 7 Computers

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

Windows 7 x86 Computers

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_PROCESSOR 
on SMS_G_System_PROCESSOR.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_PROCESSOR.AddressWidth = 
32 and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 7%"

Windows 7 x64 Computers

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_PROCESSOR 
on SMS_G_System_PROCESSOR.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_PROCESSOR.AddressWidth = 
64 and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 7%"

 All Windows 10 Computers

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 "Microsoft Windows 10%"

Windows 10 x86 Computers

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_PROCESSOR 
on SMS_G_System_PROCESSOR.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_PROCESSOR.AddressWidth = 
32 and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 10%"

Windows 10 x64 Computers

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_PROCESSOR 
on SMS_G_System_PROCESSOR.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_PROCESSOR.AddressWidth = 
64 and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 10%"

Computers in 1 or more AD OUs

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 = "Food.com/Computers/Desktops" or  
SMS_R_System.SystemOUName = "Food.com/Computers/Laptops"

Computers that have not been rebooted in 30 days

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 
DATEDIFF(DD, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GETDATE()) > 30

Inactive 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 inner join SMS_G_System_CH_ClientSummary 
on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where 
SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0

Computers with specific software version


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 where 
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%SOFTWARENAME%" and 
SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "%VERSIONUMBER%"

Comments

Popular posts from this blog

Office 365 Deployment - Office 365 Upgrade Task Sequence

SCCM Task Sequence with GUI - How to install more then one image with a single Task Sequence

Windows 10 Setup Script - V 2.0.0.0