配置管理器查询集合列表

四月出版 25, 2024

13 最小阅读量

配置管理器 Query Collection List. All queries were tested.

内容

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 可能 2019 Update 19H1 (1903)All Windows 10 October 2018 更新 (1809)All Windows 10 April 2018 更新 (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 服务器 All Windows 2016 服务器 All Windows 2012 R2 Servers All Windows 2012 服务器 All Windows 2008 R2 Servers All Windows 2008 服务器 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 (办公室 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 领域 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 领域 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 领域.

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 可能 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 更新 (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 更新 (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 和 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 服务器

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 服务器

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 服务器

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 服务器

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 (办公室 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.

办公室内幕 2205 对于Windows

PowerShell extract strings from a file

42 评论 42 评论

Marc Papillon 说:

一月 29, 2020 在 6:54 是

Thank, very useful !

回复
Max 说:

二月 3, 2020 在 6:46 是

Perfect!

回复
TankCR 说:

二月 19, 2020 在 9:28 是

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?

回复
- 安德烈·普里马克 说:
  
  二月 20, 2020 在 12:50 是
  
  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
  
  回复
Mark Zerach 说:

April 8, 2020 在 8:11 下午

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

回复
Greg 说:

可能 19, 2020 在 8:50 下午

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?

回复
- 安德烈·普里马克 说:
  
  可能 20, 2020 在 11:35 下午
  
  你好. 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.
  
  回复
Gerardo Strazzella 说:

六月 3, 2020 在 7:27 是

Top! Thanks

回复
anil 说:

六月 15, 2020 在 10:18 是

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

回复
- 安德烈·普里马克 说:
  
  六月 16, 2020 在 11:19 下午
  
  你好, 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.
  
  回复
Zafer Balkan 说:

七月 30, 2020 在 4:20 是

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 和 2019. It would be helpful if those are included too.

回复
Asterix 说:

August 12, 2020 在 6:25 是

Thanks for your explain, very useful

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

回复
Mo boul 说:

十二月 2, 2020 在 2:07 下午
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.
回复
NADIM BADRUDDIN CHOGLE 说:

十二月 14, 2020 在 11:27 下午

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.
例如, I would like to find out the list of computers which do not have WINZIP installed on them.

回复
Alex Menard 说:

行进 19, 2021 在 10:52 是

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?

回复
Matthew 说:

行进 24, 2021 在 2:57 下午

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

回复
Ivan Zdrelko 说:

April 13, 2021 在 5:35 是

你好! 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)

回复
- 安德烈·普里马克 说:
  
  April 14, 2021 在 10:52 下午
  
  你好. 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”
  
  回复
Jean-Sébastien COISSER 说:

可能 7, 2021 在 5:57 是

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

回复
- 安德烈·普里马克 说:
  
  可能 8, 2021 在 11:13 是
  
  你好. Answer to your question can be finded on TechNet forums:
  “Add a DWORD Value called ‘ValueLimit’ 在
  
  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, 不是 20,000. If so, just modify the number of rows you want returned. 然而, 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
  
  回复
Dan 说:

可能 10, 2021 在 6:45 是

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

回复
bala 说:

可能 24, 2021 在 8:18 是

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

回复
- 安德烈·普里马克 说:
  
  可能 28, 2021 在 9:29 是
  
  你好. 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%
  
  回复
rishi 说:

August 11, 2021 在 11:36 下午

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

回复
- 安德烈·普里马克 说:
  
  August 12, 2021 在 1:32 是
  
  你好. There is no simple way to do that. Use Hardware Inventory and extending Quick Fix Engineering solution to achieve this
  
  回复
Eugen 说:

August 13, 2021 在 12:56 是

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

回复
- 安德烈·普里马克 说:
  
  August 13, 2021 在 1:13 是
  
  Hallo. Nur wenn Sie über eine solche OU oder ein eindeutiges Computerattribut in Active Directory verfügen
  
  回复
GUY 说:

September 2, 2021 在 2:52 是

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

回复
- 安德烈·普里马克 说:
  
  September 4, 2021 在 11:21 下午
  
  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”))
  
  回复
ROb 说:

October 4, 2021 在 12:48 下午

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

回复
- 安德烈·普里马克 说:
  
  October 9, 2021 在 8:15 是
  
  你好. It’s possible, what query do you use?
  
  回复
Sourav Datta 说:

十二月 14, 2021 在 11:52 是

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

回复
Rubben 说:

二月 24, 2022 在 8:25 是

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

回复
C MASTHAN SAHEB 说:

二月 28, 2022 在 9:32 下午

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

回复
yvorys 说:

行进 22, 2022 在 10:55 是

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

回复
Jak 说:

August 22, 2022 在 2:00 是

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.

回复
- 安德烈·普里马克 说:
  
  August 23, 2022 在 12:43 是
  
  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.
  
  回复
Franklin Davila 说:

October 13, 2022 在 1:20 下午

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

Saludos.

回复
MSoft 说:

October 26, 2022 在 5:25 是

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

回复
mickael cogerino 说:

十二月 15, 2022 在 5:37 是

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 !

回复
Patrick 说:

一月 12, 2023 在 2:04 是

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!

回复
Jilberto 说:

一月 19, 2023 在 1:56 下午

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.

回复