Configuration Manager Query Collection List
ConfigMgr

Configuration Manager Query Collection List

13 Min Read

Configuration Manager Query Collection List. All queries were tested.

Contents
Configuration Manager Clients CollectionsClients not approvedClients requiring a rebootAll 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 clientActive Directory CollectionsBased on OUBased on many nested OUBased on domain membership.Workstations CollectionsAll WorkstationsAll Windows 10All 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.1All Windows 7All Windows VistaAll LinuxAll Mac OSBased on the computer manufacturer model.Server CollectionsAll Domain ControllersAll ServersAll Windows 2019 ServersAll Windows 2016 ServersAll Windows 2012 R2 ServersAll Windows 2012 ServersAll Windows 2008 R2 ServersAll Windows 2008 ServersSQL Server CollectionsAll SQL ServersAll SQL Server Management Studio installedAll SQL Server Management Studio v17 installedAll SQL Servers 2017All SQL Servers 2016All SQL Servers 2014 (x64)All SQL Servers 2012 (x64)Network CollectionsBased on IP subnetBased on IP range All Physical Systems All Virtual SystemsInstalled Software CollectionsAll Office 365 Pro PlusAll Office 365 Pro Plus 2016 (Office 365 Pro Plus)All Office 365 Pro Plus 2013Primary user on deviceList all IIS websitesOnly active devices with hostnames ending in “0” or “1”Clients state messages have not been reported more than 10 daysWorkstations that are offline for more than 30 daysSpecific update kb installed or not installedComputers 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%"

or

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.

TAGGED:
Previous Article Office Insider 2205 for Windows
Next Article PowerShell extract strings from a file
42 Comments

  • 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

    • 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

  • 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

  • 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

    • 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

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

    Reply

    • 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

  • 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

  • Thanks for your explain, very useful

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

    Reply

  • 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

  • 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

  • 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

  • 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

    • 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

  • 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

    • 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

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

    Reply

  • 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

    • 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

    • 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

  • 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

  • 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

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

  • 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

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

    Saludos.
    Reply

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

    Reply

  • 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

  • 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

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Exit mobile version