Lista kolekcji zapytań programu Menedżer konfiguracji

Published April 25, 2024

13 Min. odczyt

Lista kolekcji zapytań programu Menedżer konfiguracji. All queries were tested.

Zawartość

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 Listopad 2019 Update 19H2 (1909)All Windows 10 Móc 2019 Update 19H1 (1903)All Windows 10 Październik 2018 Aktualizacja (1809)All Windows 10 April 2018 Aktualizacja (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 Serwery All Windows 2016 Serwery All Windows 2012 R2 Servers All Windows 2012 Serwery All Windows 2008 R2 Servers All Windows 2008 Serwery 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 (Biuro 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 dni Workstations that are offline for more than 30 dni 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, Na przykład 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 Listopad 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 Móc 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 Październik 2018 Aktualizacja (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 Aktualizacja (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.

W tym przykładzie, 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%"

Lub

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 Serwery

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 Serwery

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 Serwery

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 Serwery

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 (Biuro 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 dni

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 dni

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.

Poprzedni artykuł

Nieznajomy biura 2205 dla Windowsa

Następny artykuł

PowerShell extract strings from a file

42 Uwagi 42 Uwagi

Marc Papillon mówi:

Styczeń 29, 2020 Na 6:54 jestem

Thank, very useful !

Odpowiedź
Max mówi:

Luty 3, 2020 Na 6:46 jestem

Doskonały!

Odpowiedź
TankCR mówi:

Luty 19, 2020 Na 9:28 jestem

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?

Odpowiedź
- Andreja Prijmaka mówi:
  
  Luty 20, 2020 Na 12:50 jestem
  
  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
  
  Odpowiedź
Mark Zerach mówi:

April 8, 2020 Na 8:11 po południu

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

Odpowiedź
Greg mówi:

Móc 19, 2020 Na 8:50 po południu

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?

Odpowiedź
- Andreja Prijmaka mówi:
  
  Móc 20, 2020 Na 11:35 po południu
  
  Cześć. 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.
  
  Odpowiedź
Gerardo Strazzella mówi:

Czerwiec 3, 2020 Na 7:27 jestem

Top! Dzięki

Odpowiedź
anil mówi:

Czerwiec 15, 2020 Na 10:18 jestem

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Czerwiec 16, 2020 Na 11:19 po południu
  
  Cześć, 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.
  
  Odpowiedź
Zafer Balkan mówi:

Lipiec 30, 2020 Na 4:20 jestem

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

Odpowiedź
Asterix mówi:

Sierpień 12, 2020 Na 6:25 jestem

Thanks for your explain, very useful

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

Odpowiedź
Mo boul mówi:

Grudzień 2, 2020 Na 2:07 po południu
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.
Odpowiedź
NADIM BADRUDDIN CHOGLE mówi:

Grudzień 14, 2020 Na 11:27 po południu

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

Odpowiedź
Alex Menard mówi:

marzec 19, 2021 Na 10:52 jestem

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?

Odpowiedź
Matthew mówi:

marzec 24, 2021 Na 2:57 po południu

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

Odpowiedź
Ivan Zdrelko mówi:

April 13, 2021 Na 5:35 jestem

Cześć! 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)

Odpowiedź
- Andreja Prijmaka mówi:
  
  April 14, 2021 Na 10:52 po południu
  
  Cześć. 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”
  
  Odpowiedź
Jean-Sébastien COISSER mówi:

Móc 7, 2021 Na 5:57 jestem

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Móc 8, 2021 Na 11:13 jestem
  
  Cześć. Answer to your question can be finded on TechNet forums:
  “Add a DWORD Value called ‘ValueLimit’ Na
  
  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, nie 20,000. If so, just modify the number of rows you want returned. Jednakże, 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
  
  Odpowiedź
Dan mówi:

Móc 10, 2021 Na 6:45 jestem

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

Odpowiedź
bala mówi:

Móc 24, 2021 Na 8:18 jestem

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Móc 28, 2021 Na 9:29 jestem
  
  Cześć. 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%
  
  Odpowiedź
rishi mówi:

Sierpień 11, 2021 Na 11:36 po południu

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Sierpień 12, 2021 Na 1:32 jestem
  
  Cześć. There is no simple way to do that. Use Hardware Inventory and extending Quick Fix Engineering solution to achieve this
  
  Odpowiedź
Eugen mówi:

Sierpień 13, 2021 Na 12:56 jestem

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Sierpień 13, 2021 Na 1:13 jestem
  
  Hallo. Nur wenn Sie über eine solche OU oder ein eindeutiges Computerattribut in Active Directory verfügen
  
  Odpowiedź
GUY mówi:

September 2, 2021 Na 2:52 jestem

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  September 4, 2021 Na 11:21 po południu
  
  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(dzień, 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(dzień, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = “Heartbeat Discovery”))
  
  Odpowiedź
ROb mówi:

Październik 4, 2021 Na 12:48 po południu

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

Odpowiedź
- Andreja Prijmaka mówi:
  
  Październik 9, 2021 Na 8:15 jestem
  
  Cześć. It’s possible, what query do you use?
  
  Odpowiedź
Sourav Datta mówi:

Grudzień 14, 2021 Na 11:52 jestem

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

Odpowiedź
Rubben mówi:

Luty 24, 2022 Na 8:25 jestem

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

Odpowiedź
C MASTHAN SAHEB mówi:

Luty 28, 2022 Na 9:32 po południu

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

Odpowiedź
yvorys mówi:

marzec 22, 2022 Na 10:55 jestem

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

Odpowiedź
Jak mówi:

Sierpień 22, 2022 Na 2:00 jestem

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.

Odpowiedź
- Andreja Prijmaka mówi:
  
  Sierpień 23, 2022 Na 12:43 jestem
  
  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.
  
  Odpowiedź
Franklin Davila mówi:

Październik 13, 2022 Na 1:20 po południu

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

Saludos.

Odpowiedź
MSoft mówi:

Październik 26, 2022 Na 5:25 jestem

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

Odpowiedź
mickael cogerino mówi:

Grudzień 15, 2022 Na 5:37 jestem

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 !

Odpowiedź
Patrick mówi:

Styczeń 12, 2023 Na 2:04 jestem

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!

Odpowiedź
Jilberto mówi:

Styczeń 19, 2023 Na 1:56 po południu

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.

Odpowiedź