July 2019 – 1 Giant Nerd

Today im going to leave you with a list of SCCM Device Collection queries that I use on every SCCM environment that I manage or build.

To add one of the queries below, create the new Device Collection as per normal in SCCM:

On the Membership Rules tab, click Add Rule then Query Rule.

SCCMDeviceCollections#1

In the name field, specify the OS or application, then click Edit Query Statement.

SCCMDeviceCollections#2

Click Show Query Language.

SCCMDeviceCollections#3

4. Copy and paste the below query into the window and click Ok then Ok.

Device Collection Queries:

Windows Server 2003:

Windows Server 2008:

Windows Server 2008 R2:

Windows Server 2012:

Windows Server 2012 R2:

Windows Server 2016:

Windows 7:

Windows 10:

Low Disk Space < 10GB:

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.ResourceID where SMS_G_SYSTEM_LOGICAL_DISK.FreeSpace < 10240 and SMS_G_SYSTEM_LOGICAL_DISK.Name = “C:” and SMS_R_System.Client is not null

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 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 = “%SQL Server 2008 R2%”

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 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 = “%SQL Server 2012%”

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 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 = “%SQL Server 2014%”

Pending 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_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid
where c.LastEnforcementMessageID = 9

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_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
where SMS_CM_RES_COLL_SMS00001.IsApproved= “2”

Clients with Office Pro Plus:

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName LIKE “Microsoft Office Professional Plus%”

Clients with Office Pro Plus 2013:

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_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = “Microsoft Office Professional Plus 2013”

Clients with Office Pro Plus 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 inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = “Microsoft Office Professional Plus 2016”

Computers by Manufacturer Brand:

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 “Dell%“

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 “WYSE%“

Servers that are physical:

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 not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.IsVirtualMachine = ‘True’) and SMS_R_System.OperatingSystemNameandVersion like ‘Microsoft Windows NT%Server%’

Servers that are virtual:

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.IsVirtualMachine = ‘True’ and SMS_R_System.OperatingSystemNameandVersion like ‘Microsoft Windows NT%Server%’

Find SCCM Distribution Point Servers:

32Bit Operating 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.SystemType = “X86-based PC“

64Bit Operating 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.SystemType = “x64-based PC“

Update 16-Jan-20: Fixed typo in WSUS Export Script.

Recently had to perform the exact steps to get an environment patched that did not have an internet connection or could connect to an upstream WSUS server.

The following process will describe downloading and upload patches to a disconnected domain:

1. WSUS server that has internet connection. For the one I recent did, I created a VM with WSUS and WID database to keep the footprint small.
2. Let WSUS sync overnight with all the classifications.
3. Once synced, go into WSUS administration.
4. Click Options.
5. Click Update Files and Languages.
6. Confirm that on the Update Files tab, that only update files will download when they are approved. If you click download express installation files this will take forever to complete and download everything.
7. Click on the Update Languages tab.
8. Select Download updates only in these languages, and select the language that you wish, in my case English.
9. The easiest way is to get into the Critical and Security updates under updates. Click Any Except Declined and click Refresh.
10. Once the results come back, right click on the column bar and select SuperSedence.
11. Now sort by the supersedence column, by clicking on the column.
12. Updates that have a blue box at the top mean that this update replaces other updates.
13. These ones mean that they are not superseded, but another update replaces them.
14. Any updates that have been superseded, select them. You can select many by shift+a on the
15. Right click on these updates and select Decline.
16. Approve the remaining updates.
17. WSUS will then begin to download the physical files.
18. Wait till all the downloads have been completed and the download status is idle. This will take sometime to download, i.e 2 days and depending on the number of classifications selected.
19. Once completed, copy the entire contents of E:\WSUSContent to a harddrive. You will need at least 1TB of space.
  The best way to copy the contents is by using ROBOCOPY.
  Example command:
  robocopy /MIR
  The /MIR switch will copy all the sub folders. The important thing is to copy the files exactly as they are from the source, as the WSUS database has pointers to each of the files.
20. If your copying to an existing WSUS content on a harddrive then you will want to run an incremental Robocopy, which basically means that any files that have been modified compared to the file in the destination are copied.
  Command:
  robocopy E:\WSUSContent F:\WSUSContent /E /MIR /R:1 /W:1
21. While the copying is happening, open a Command prompt as an administrator on the source WSUS server.
22. Navigate to C:\Program Files\Update Services\Tools
23. Enter the following command (this will export the metadata of the updates):
  exe export D:\wsusexport_20190601.xml.gz D:\wsusexport_20190601.log
  NP if you have a large number of updates, you will need to run the .xml.gz extension as the standard WSUS command wont work.
24. Copy the export files to the harddrive.
25. Robocopy the WSUS Content from the harddrive to the WSUS server using the differential robocopy command:
  robocopy D:\WSUSContent\WSUSContent \\E$\WSUSContent\WSUSContent /E /MIR /R:1 /W:1
26. Verify that the Robocopy has finished.
27. Run the following command to import WSUS metadata.
28. Navigate to C:\Program Files\Update Services\Tools
29. Enter the following command (this will import the metadata of the updates):
  wsusutil.exe import
  E:\wsusexport_20190601.xml.gz E:\wsusexport_20190601.log
30. Confirm the metadata is being imported. Process will take ages depending on the size of the xml.gz file.
31. Confirm that metadata is imported.
32. Open the SCCM Console.
33. Go to Software Library tab.
34. Expand Software Updates.
35. Right click on All Software Updates and click Synchronize.
36. This process will take some time.
37. To confirm that the updates have been sync’d with SCCM, go to the All Software Updates page.
38. Set the criteria to Date Released is on or after last 1 month.
39. Select all the updates that in the Required column is more than 1.
40. Now your good to go.