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

How to Deploy a Windows 10 Servicing update as a Application

By -
Image
 When a little after windows 10 build updates started coming out Microsoft released a new feature in SCCM called Windows 10 Serving. This feature is used to deploy windows 10 build to devices, However the deployment status and some of the reporting are not always the most accurate. So I will show you away that you can deploy the update using a standard application deployment. You will need to enable the windows 10 serving feature in your SCCM environment or have access to a SCCM setup that has windows 10 serving enabled.  If you dont have it enabled here is a good way to do so  Download the Windows 10 Serving Update, to do so find the update and right click Download  Once you have the update downloaded navigate to the path you downloaded the update to and copy the content of the GUID folder to a folder in your content library source. You should see a esd file and a WindowsUpdateBox.exe file in the folder. In the folder you copied the content to create a bat file with the following sta
Read more