Sccm Collection Queries

By -

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 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 "%SOFTWARENAME%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "%VERSIONUMBER%") 
or (SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%SOFTWARENAME%" and 
SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version < "%VERSIONUMBER%")

Popular posts from this blog

SCCM Task Sequence GUI - How to set up the TS to work with a GUI

By -
Image
Before I have posted how you can create a TS for windows 10 , add a GUI to your TS and run a script to configure your windows 10 install . However all of this has become out dated and so I wanted to update all of that. This how to will walk you through how to create a TS that will allow you to choose a windows 10 or windows 7 image, name the computer, add the computer description to AD, Choose form a list what applications you want to install, Choose to enable BitLocker and set the PIN as well as create a local account. Prerequisites: MDT integrated to SCCM A boot image with the following components added Windows Powershell(WinPE-DismCmdlest) HTML(WinPE-HTA) Microsoft .NET (WinPE-NetFx) Windows Powershell (WinPE-Powershell) A windows 10 and windows 7 image  1. Download the package I have put together containing the scripts you will need https://github.com/sccmtst/SCCM-Management-Scripts/tree/master/TSGUI 2. If you have not already done so download and in...
Read more

SCCM Applications vs. SCCM Packages: Understanding the Key Differences

By -
Microsoft System Center Configuration Manager (SCCM) is a versatile and powerful tool for managing devices, applications, and updates in an organization's IT environment. Among its many features, SCCM allows you to deploy software to your end-users in two primary ways: Applications and Packages. Although both methods have a similar goal, they have some key differences that IT administrators should understand when choosing the appropriate deployment method. In this blog post, we will explore these differences and help you decide which option is best for your organization. Definition and Deployment Scenarios SCCM Applications: An SCCM Application is a high-level, user-centric deployment method that focuses on providing the desired user experience. Applications are designed to be state-based, meaning they can detect if the application is already installed and only perform the necessary actions if the application is not present or if a newer version is available. Applications are suita...
Read more

Faster PXE boot times in SCCM 1606 and later

By -
Image
A new feature introduced with SCCM 1606 was being able to modify the boot times for PXE. This is done by modifying the TFTP block and window size of the boot image RamDisk. This was originally implemented to help a admin over come network requirement. This allows us to make the PXE boot times much faster for my self this meant taking a 10 minute boot time all the way down to 30 second boot time. In order to make this change you need to create 2 registry keys on your PXE enabled DP. Location : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP Name: RamDiskTFTPWindowSize Type : REG_DWORD Value : <customized window size> The default value is 1 (1 data block fills the window)  Location : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP Name: RamDiskTFTPBlockSize Type : REG_DWORD Value : <customized block size> The default value is 4096 (4k).  once you have added the registry keys you will need to restart  the Windows Deployment Service for the ch...
Read more