Configuration Manager Query Collection List

Published: February 18, 2024

13 Min Read

Configuration Manager Query Collection List. All queries were tested.

Contents

Configuration Manager Clients Collections Clients not approved Clients requiring a reboot All SCCM client computers with less than 5GB free disk space on C:All computer objects with the client installed in the specific site “21”All computers without an SCCM client Active Directory Collections Based on OU Based on many nested OU Based on domain membership.Workstations Collections All Workstations All Windows 10 All Windows 10 November 2019 Update 19H2 (1909)All Windows 10 May 2019 Update 19H1 (1903)All Windows 10 October 2018 Update (1809)All Windows 10 April 2018 Update (1803)All Windows 10 Fall Creators Update (1709)All Windows 10 Creators Update (1703)All Windows 10 Aniversary Update (1607)All Windows 10 November Update (1511)All Windows 10 RTM (1507)All Windows 8.1 All Windows 7 All Windows Vista All Linux All Mac OS Based on the computer manufacturer model.Server Collections All Domain Controllers All Servers All Windows 2019 Servers All Windows 2016 Servers All Windows 2012 R2 Servers All Windows 2012 Servers All Windows 2008 R2 Servers All Windows 2008 Servers SQL Server Collections All SQL Servers All SQL Server Management Studio installed All SQL Server Management Studio v17 installed All SQL Servers 2017 All SQL Servers 2016 All SQL Servers 2014 (x64)All SQL Servers 2012 (x64)Network Collections Based on IP subnet Based on IP range All Physical Systems All Virtual Systems Installed Software Collections All Office 365 Pro Plus All Office 365 Pro Plus 2016 (Office 365 Pro Plus)All Office 365 Pro Plus 2013 Primary user on device List all IIS websites Only active devices with hostnames ending in “0” or “1”Clients state messages have not been reported more than 10 days Workstations that are offline for more than 30 days Specific update kb installed or not installed Computers that have a specific Microsoft Store-based application installed

Configuration Manager Clients Collections

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= "0"

Clients requiring a 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 join sms_combineddeviceresources on
 sms_combineddeviceresources.resourceid = sms_r_system.resourceid
 where sms_combineddeviceresources.clientstate != 0

All SCCM client computers with less than 5GB free disk space on C:

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.DeviceID = "C:" and SMS_G_System_LOGICAL_DISK.FreeSpace <= 5000

All computer objects with the client installed in the specific site “21”

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.ADSiteName = "21" and SMS_R_System.Client = 1

All computers without an SCCM client

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.Client = "0"

Active Directory Collections

Based on OU

Replace “domain.local/OU/OU” with your own domain name and OU that you need a collection of.

select * from  SMS_R_System where LOWER(SMS_R_System.SystemOUName) = "domain.local/OU/OU"

Based on many nested OU

In case you have many repeated OU structure (one OU name exist in many other OU, for example Workstations). Replace “domain.local/%/Workstations” with your own domain name and OU that you need a collection of.

select *  from  SMS_R_System where SMS_R_System.SystemOUName like "domain.local/%/Workstations"

Based on domain membership.

Replace “domain” with the NetBIOS name of your domain.

select * from  SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup = "domain"

Workstations Collections

All Workstations

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
 SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
 from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%"

All Windows 10

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 10.0%"

All Windows 10 November 2019 Update 19H2 (1909)

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.BuildNumber = "18363"

All Windows 10 May 2019 Update 19H1 (1903)

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.BuildNumber = "18362"

All Windows 10 October 2018 Update (1809)

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.BuildNumber = "17763"

All Windows 10 April 2018 Update (1803)

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.BuildNumber = "17134"

All Windows 10 Fall Creators Update (1709)

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.BuildNumber = "16299"

All Windows 10 Creators Update (1703)

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.BuildNumber = "15063"

All Windows 10 Aniversary Update (1607)

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.BuildNumber = "14393"

All Windows 10 November Update (1511)

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.BuildNumber = "10586"

All Windows 10 RTM (1507)

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.BuildNumber = "10240"

All Windows 8.1

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 "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 6.3%"

All Windows 7

This query needs to be limited to the All Workstations collection to work.

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 6.1%"

All Windows Vista

This query needs to be limited to the All Workstations collection to work

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 6.0%"

All Linux

Select SMS_R_System.ClientEdition from SMS_R_System where SMS_R_System.ClientEdition = 13

All Mac OS

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 "Mac%"

Based on the computer manufacturer model.

In this example, we get all Dell workstations.

select distinct SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model
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.Model = "Dell"

Server Collections

All Domain Controllers

This query requires that the SCCM 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 Servers

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server%"

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 on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.SystemRole = "Server"

All Windows 2019 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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Name like "Microsoft Windows Server 2019 %"

All Windows 2016 Servers

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 10.0%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 10.0%"

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.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.3%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.3%"

All Windows 2012 Servers

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.2%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.2%"

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.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.1%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.1%"

All Windows 2008 Servers

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_System.OperatingSystemNameandVersion,
SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.0%"
OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.0%"

SQL Server Collections

All SQL Servers

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 "%SQLServer%"

All SQL Server Management Studio installed

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.ProductName like "Microsoft SQL Server Management Studio %"

All SQL Server Management Studio v17 installed

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.ProductName like "Microsoft SQL Server Management Studio - 17%"

All SQL Servers 2017

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.ProductName = "Microsoft SQL Server 2017"

All SQL Servers 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.ProductName like "Microsoft SQL Server 2016"

All SQL Servers 2014 (x64)

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.ProductName like "Microsoft SQL Server 2014 (64-%"

All SQL Servers 2012 (x64)

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.ProductName like "Microsoft SQL Server 2012 (64-%"

Network Collections

Based on IP subnet

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.IPSubnets LIKE '192.168.1[1-9].0'

Based on IP range

select *
from SMS_R_System
where SMS_R_System.IPAddresses like "10.10.10.2[1-9]"

All Physical Systems

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System where SMS_R_System.IsVirtualMachine like "False"

All Virtual Systems

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System where SMS_R_System.IsVirtualMachine like "True"

Installed Software Collections

All Office 365 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.ProductName like "Microsoft Office 365 ProPlus %"

All Office 365 Pro Plus 2016 (Office 365 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.ProductName like "Microsoft Office 365 ProPlus - %" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion like "16.%"

All Office 365 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.ProductName like "Microsoft Office 365 ProPlus - %" and SMS_G_System_INSTALLED_SOFTWARE.ProductVersion like "15.0.%"

Primary user on device

SELECT SMS_R_System.name, SMS_R_User.UniqueUserName
  FROM SMS_R_System
  INNER JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
  JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
  WHERE
   SMS_UserMachineRelationship.Types = 1

List all IIS websites

SELECT SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, IISWebServer.SiteName FROM SMS_R_System INNER JOIN IISWebServer ON SMS_R_System.ResourceID = IISWebServer.ResourceID

Only active devices with hostnames ending in “0” or “1”

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.Name like '%0' OR SMS_R_System.Name like '%1' AND SMS_R_System.Active = 1

Clients state messages have not been reported more than 10 days

SELECT DISTINCT SYS.Name0, MAX(SM.RecordTime) as 'Last State Message Time' FROM v_StateMessage SM JOIN v_R_System SYS on SYS.ResourceID = SM.MachineID WHERE SM.RecordTime < DATEADD(day, -10, GETDATE()) GROUP BY SYS.Name0 ORDER BY SYS.Name0

Workstations that are offline for more than 30 days

SELECT Name0, SMS_G_System_SYSTEM.Name, LastHWScan FROM SMS_R_System JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID WHERE LastHWScan < DATEADD(day, -30, GETDATE()) AND SMS_R_System.OperatingSystemNameAndVersion LIKE '%Workstation%' AND SMS_R_System.Client = 1 AND SMS_R_System.Active = 1

Specific update kb installed or not installed

SELECT DISTINCT SYS.Name0, UA.Status FROM v_Update_ComplianceStatus AS UC JOIN v_UpdateInfo AS UI ON UC.CI_ID = UI.CI_ID JOIN v_R_System AS SYS ON SYS.ResourceID = UC.ResourceID JOIN v_GS_UpdateAssignment AS UA ON UA.AssignmentID = UC.AssignmentID WHERE UI.ArticleID = '<update KB>' AND SYS.Operating_System_Name_and0 LIKE '%Workstation%'

Computers that have a specific Microsoft Store-based application installed

SELECT DISTINCT SYS.Name0, APPM.DisplayName0 FROM v_R_System SYS JOIN v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID JOIN v_AppMan_Application APPM ON ARP.DisplayName0 = APPM.DisplayName0 WHERE APPM.DisplayName0 = '<application name>'

PLEASE NOTE: I don’t work with SCCM anymore, I don’t update this page, and I can’t help you with requests. Use ChatGPT or leave comment, maybe someone can help you.

Office Insider 2205 for Windows

PowerShell extract strings from a file

42 Comments 42 Comments

Marc Papillon says:

January 29, 2020 at 6:54 am

Thank, very useful !

Reply
Max says:

February 3, 2020 at 6:46 am

Perfect!

Reply
TankCR says:

February 19, 2020 at 9:28 am

I am trying to find a SQL query to return primary user and machine, but so far everything I can find is for 2012 and the queries don’t work for current branch. Got anything like that?

Reply
- Andrej Prijmak says:
  
  February 20, 2020 at 12:50 am
  
  Use this query. Tested on latest CB ConfigMgr 1910:
  
  SELECT SMS_R_System.name, SMS_R_User.UniqueUserName
  FROM SMS_R_System
  INNER JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
  JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
  WHERE
  SMS_UserMachineRelationship.Types = 1
  
  Reply
Mark Zerach says:

April 8, 2020 at 8:11 pm

Excellent list, would like to have a query that combines user device affinity using display name, make and model of the hardware, and includes the last policy cycle of the device. I can find the separately but, unable to combine the queries to run in sccm query.

full name login Hostname make/model date
Jane Doe jdoe Finance12 Dell Lat 5290 4/2/2020

Reply
Greg says:

May 19, 2020 at 8:50 pm

I am trying to find a SQL query to check for a missing application in a OU collection and then install the missing application. What is the process create the first collection and then advertise the application to the first collection?

Reply
- Andrej Prijmak says:
  
  May 20, 2020 at 11:35 pm
  
  Hi. Sorry, but you can’t achieve this with the SQL query itself, you must use Configuration Manager PowerShell to do that. Something like that:
  1. Get applications list;
  2. Get collection info;
  3. Then you can use {if … else} to check every application in your collection.
  
  There may be a better solution or a better PowerShell process.
  
  Reply
Gerardo Strazzella says:

June 3, 2020 at 7:27 am

Top! Thanks

Reply
anil says:

June 15, 2020 at 10:18 am

Kindly help me with a SQL query which can list PC names, OS Build where it should be limited to a specific collection.

Reply
- Andrej Prijmak says:
  
  June 16, 2020 at 11:19 pm
  
  Hi, this is the query to get names and builds:
  select SMS_R_System.NetbiosName, SMS_G_System_OPERATING_SYSTEM.BuildNumber from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId
  If you need to run it from sql, then you must run it from collection table, or limit it to table.
  
  Reply
Zafer Balkan says:

July 30, 2020 at 4:20 am

It seems like the list upgraded but it would be great if the archived/legacy versions (SQL 2008, Server 2003) are added. And also, Office versions are important, like 2010. 2013. 2016 and 2019. It would be helpful if those are included too.

Reply
Asterix says:

August 12, 2020 at 6:25 am

Thanks for your explain, very useful

An input for the Collection “All Servers”
Server 2019 not are including on this collection

Reply
Mo boul says:

December 2, 2020 at 2:07 pm
how to list all devices in OU=x, with computername like ‘ABC-%’ are using Remote-connection Method D?
for example User a using in Laptop ABC-1 “secure Pulse connection Methhod”
User B – Laptop ABC-2 is using Cisco-Anyconnect ect.
- User Device connection lastlogin
- User A ABC-1 Secure-Pulse 11/22/2020
- User B ABC-2 Cisco Anyconnect 12/01/2020
- User C ABC-10 RDS-Connection 12/02/2020
etc.
Reply
NADIM BADRUDDIN CHOGLE says:

December 14, 2020 at 11:27 pm

Hello,

Excellent List, but I am looking for one which I have not got answer to anywhere. I am looking for a query which will list the computers which do not have a specific software installed.
For example, I would like to find out the list of computers which do not have WINZIP installed on them.

Reply
Alex Menard says:

March 19, 2021 at 10:52 am

If possible, how can I query a collection for the users, dates and times of who logged on to the devices in the collection between Sept 1, 2020 and June 30, 2021?

Reply
Matthew says:

March 24, 2021 at 2:57 pm

How about a query that looks for a specific software like Adobe?

Reply
Ivan Zdrelko says:

April 13, 2021 at 5:35 am

Hi! I am trying to list all VMs with Office 2007 installed. Clients are Windows 10. Have tried a lot og queries from web. But nothing works. Maybe you have an idea? THX)

Reply
- Andrej Prijmak says:
  
  April 14, 2021 at 10:52 pm
  
  Hi. Try this:
  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 or SMS_G_System_INSTALLED_SOFTWARE.ProductName = “Microsoft Office Professional Plus 2007”
  
  Reply
Jean-Sébastien COISSER says:

May 7, 2021 at 5:57 am

Bonjour,

Tout d’abord merci l’ensemble de vos requêtes sont vraiment géniaux.

J’aurais une difficulté a créer une requête actuellement….
J’aimerais savoir est-ce possible d’avoir une query qui me remonte uniquement 100 postes finissant par %0 ?

Merci d’avance pour votre aide

Cdt

Coisser Jean-Sébastien

Reply
- Andrej Prijmak says:
  
  May 8, 2021 at 11:13 am
  
  Hi. Answer to your question can be finded on TechNet forums:
  “Add a DWORD Value called ‘ValueLimit’ at
  
  HKEY_Local_Machine\SOFTWARE\Microsoft\ConfigMgr\AdminUI\QueryProcessors
  
  and set to the # of rows you want to display.
  
  Note that you may already have that value added, as they say the default is 2,000, not 20,000. If so, just modify the number of rows you want returned. However, I believe there is a memory limit here, so you still may not get the entire list, depending on how many values you have.”
  
  Other solution – use smaller collections
  
  Reply
Dan says:

May 10, 2021 at 6:45 am

Is there a SQL query for listing computers that have a specific Microsoft Store based application installed?

Reply
bala says:

May 24, 2021 at 8:18 am

select distinct
SMS_R_System.Name,
SMS_G_System_SoftwareFile.FileName,
SMS_G_System_SoftwareFile.FilePath,
SMS_G_System_SoftwareFile.FileSize,
SMS_G_System_SoftwareFile.ModifiedDate
from
SMS_R_System
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SoftwareFile.FilePath like “c:\\users\\%roaming%”

Please help with me a query to check if the folder is available in the path

Reply
- Andrej Prijmak says:
  
  May 28, 2021 at 9:29 am
  
  Hi. Try to use c:\users\*\%appdata%, %appdata%, *\%appdata%. What you try to get? As I understod you try to chek that every user has roaming folder? There is no %roaming% use %appdata%
  
  Reply
rishi says:

August 11, 2021 at 11:36 pm

can we create a collection based on specific update kb installed or not installed …
thankyou

Reply
- Andrej Prijmak says:
  
  August 12, 2021 at 1:32 am
  
  Hi. There is no simple way to do that. Use Hardware Inventory and extending Quick Fix Engineering solution to achieve this
  
  Reply
Eugen says:

August 13, 2021 at 12:56 am

Ist es möglich eine Abfrage zu erstellen, die alle Windows 10 Geräte in einer bestimmten Abteilung anzeigt?

Reply
- Andrej Prijmak says:
  
  August 13, 2021 at 1:13 am
  
  Hallo. Nur wenn Sie über eine solche OU oder ein eindeutiges Computerattribut in Active Directory verfügen
  
  Reply
GUY says:

September 2, 2021 at 2:52 am

HELLO
I NEED QUERY FOW WORKSTATIONS THAT ARE OFFLINE MORE THAN 30 DAYS

Reply
- Andrej Prijmak says:
  
  September 4, 2021 at 11:21 pm
  
  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.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) ) and SMS_R_System.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = “Heartbeat Discovery”))
  
  Reply
ROb says:

October 4, 2021 at 12:48 pm

I’m trying to query 2 domains in order to create user collections with members from both. Is that possible ?

Reply
- Andrej Prijmak says:
  
  October 9, 2021 at 8:15 am
  
  Hi. It’s possible, what query do you use?
  
  Reply
Sourav Datta says:

December 14, 2021 at 11:52 am

Hi Andrej,
Thanks, very useful post…can you help me build a query that will show the membership rules of a specific collection ID.

Reply
Rubben says:

February 24, 2022 at 8:25 am

Boa Tarde, estou a tentar criar uma query que me devolva, as maquinas que têm o windows 10 enterprise instalado, não consigo…Pode me ajudar

Reply
C MASTHAN SAHEB says:

February 28, 2022 at 9:32 pm

I need collection query for clients state messages have not reported more than 10 days

Reply
yvorys says:

March 22, 2022 at 10:55 am

muy bueno, podrías ayudarme con una consulta que me puede dar los nombres equipos, usuarios y sistema operativo?

Reply
Jak says:

August 22, 2022 at 2:00 am

Bloß nicht meine Gedanke kommentieren aber auf eine Frage Antwort geben.
Warum eine OU-Abfrage, die genau Pfad enthält, Clients in eine übergeordnete OU zeigt.
Systemressource – System-Organisationseinheit ist gleich domain.ou1.ou2. Gezeigt werden auch Clients in der OU1.

Reply
- Andrej Prijmak says:
  
  August 23, 2022 at 12:43 am
  
  Hello, I don’t work with SCCM anymore so I can’t answer your question, but I suggest you try the “Based on many nested OUs” query with modifications for your needs.
  
  Reply
Franklin Davila says:

October 13, 2022 at 1:20 pm

Buenas tardes, quisiera un query donde saque el listado de los servidores y su ultimo parcheo, seria posible.

Saludos.

Reply
MSoft says:

October 26, 2022 at 5:25 am

Does anyone have an example of a query that shows ONLY active devices with hostnames ending in “0” or “1”? Much appreciated

Reply
mickael cogerino says:

December 15, 2022 at 5:37 am

Bonjour,

quel type de requete je dois taper pour obtenir les applications installées dans le appdata/local ? est ce que c’est possible seulement ?

Merci d’avance !

Reply
Patrick says:

January 12, 2023 at 2:04 am

Dankeschön!
Wie sieht denn eine Abfrage aus, die PCs enthalten, die keinen Client aktiv haben aber sich in der letzten Woche in der Domäne angemeldet haben?
Da gibt es teilweise Rechner bei mir die aus dem Raster fallen wenn Updates verteilt werden!
Vielen Dank!

Reply
Jilberto says:

January 19, 2023 at 1:56 pm

Can you tell me how to create a report in SCCM for all IIS Web Server and the web content?
I would like to have a list of all web site in SCCM report.

Reply