Lista de coleta de consultas do Configuration Manager

Published April 25, 2024

13 Leitura mínima

Lista de coleta de consultas do Configuration Manager. All queries were tested.

Conteúdo

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 Poderia 2019 Update 19H1 (1903)All Windows 10 outubro 2018 Atualizar (1809)All Windows 10 abril 2018 Atualizar (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 Servidores All Windows 2016 Servidores All Windows 2012 R2 Servers All Windows 2012 Servidores All Windows 2008 R2 Servers All Windows 2008 Servidores 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 (Escritório 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 dias Workstations that are offline for more than 30 dias 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, por exemplo 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 Poderia 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 outubro 2018 Atualizar (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 abril 2018 Atualizar (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.

Neste exemplo, we get all Dell workstations.

select distinct SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model
from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Model = "Dell"

Server Collections

All Domain Controllers

This query requires that the SCCM client is installed and hardware inventory is turned on.

select *  from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Roles like "%Domain_Controller%"

All Servers

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

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.SystemRole = "Server"

All Windows 2019 Servidores

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 Servidores

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 Servidores

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 Servidores

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 (Escritório 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 dias

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 dias

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.

Artigo Anterior

Office Insider 2205 para Windows

Próximo artigo

PowerShell extract strings from a file

42 Comentários 42 Comentários

Marc Papillon diz:

Janeiro 29, 2020 no 6:54 sou

Thank, very useful !

Responder
Max diz:

Fevereiro 3, 2020 no 6:46 sou

Perfect!

Responder
TankCR diz:

Fevereiro 19, 2020 no 9:28 sou

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?

Responder
- Andrej Prijmak diz:
  
  Fevereiro 20, 2020 no 12:50 sou
  
  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
  
  Responder
Mark Zerach diz:

abril 8, 2020 no 8:11 tarde

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

Responder
Greg diz:

Poderia 19, 2020 no 8:50 tarde

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?

Responder
- Andrej Prijmak diz:
  
  Poderia 20, 2020 no 11:35 tarde
  
  Oi. 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.
  
  Responder
Gerardo Strazzella diz:

Junho 3, 2020 no 7:27 sou

Top! Obrigado

Responder
anil diz:

Junho 15, 2020 no 10:18 sou

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

Responder
- Andrej Prijmak diz:
  
  Junho 16, 2020 no 11:19 tarde
  
  Oi, 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.
  
  Responder
Zafer Balkan diz:

July 30, 2020 no 4:20 sou

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

Responder
Asterix diz:

Agosto 12, 2020 no 6:25 sou

Thanks for your explain, very useful

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

Responder
Mo boul diz:

December 2, 2020 no 2:07 tarde
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.
Responder
NADIM BADRUDDIN CHOGLE diz:

December 14, 2020 no 11:27 tarde

Olá,

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

Responder
Alex Menard diz:

Marchar 19, 2021 no 10:52 sou

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?

Responder
Matthew diz:

Marchar 24, 2021 no 2:57 tarde

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

Responder
Ivan Zdrelko diz:

abril 13, 2021 no 5:35 sou

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

Responder
- Andrej Prijmak diz:
  
  abril 14, 2021 no 10:52 tarde
  
  Oi. 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”
  
  Responder
Jean-Sébastien COISSER diz:

Poderia 7, 2021 no 5:57 sou

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

Responder
- Andrej Prijmak diz:
  
  Poderia 8, 2021 no 11:13 sou
  
  Oi. Answer to your question can be finded on TechNet forums:
  “Add a DWORD Value called ‘ValueLimit’ no
  
  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, não 20,000. If so, just modify the number of rows you want returned. No entanto, 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
  
  Responder
Dan diz:

Poderia 10, 2021 no 6:45 sou

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

Responder
bala diz:

Poderia 24, 2021 no 8:18 sou

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

Responder
- Andrej Prijmak diz:
  
  Poderia 28, 2021 no 9:29 sou
  
  Oi. 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%
  
  Responder
rishi diz:

Agosto 11, 2021 no 11:36 tarde

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

Responder
- Andrej Prijmak diz:
  
  Agosto 12, 2021 no 1:32 sou
  
  Oi. There is no simple way to do that. Use Hardware Inventory and extending Quick Fix Engineering solution to achieve this
  
  Responder
Eugen diz:

Agosto 13, 2021 no 12:56 sou

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

Responder
- Andrej Prijmak diz:
  
  Agosto 13, 2021 no 1:13 sou
  
  Hallo. Nur wenn Sie über eine solche OU oder ein eindeutiges Computerattribut in Active Directory verfügen
  
  Responder
GUY diz:

Setembro 2, 2021 no 2:52 sou

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

Responder
- Andrej Prijmak diz:
  
  Setembro 4, 2021 no 11:21 tarde
  
  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(dia, 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(dia, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = “Heartbeat Discovery”))
  
  Responder
ROb diz:

outubro 4, 2021 no 12:48 tarde

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

Responder
- Andrej Prijmak diz:
  
  outubro 9, 2021 no 8:15 sou
  
  Oi. It’s possible, what query do you use?
  
  Responder
Sourav Datta diz:

December 14, 2021 no 11:52 sou

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

Responder
Rubben diz:

Fevereiro 24, 2022 no 8:25 sou

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

Responder
C MASTHAN SAHEB diz:

Fevereiro 28, 2022 no 9:32 tarde

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

Responder
yvorys diz:

Marchar 22, 2022 no 10:55 sou

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

Responder
Jak diz:

Agosto 22, 2022 no 2:00 sou

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.

Responder
- Andrej Prijmak diz:
  
  Agosto 23, 2022 no 12:43 sou
  
  Olá, 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.
  
  Responder
Franklin Davila diz:

outubro 13, 2022 no 1:20 tarde

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

Saludos.

Responder
MSoft diz:

outubro 26, 2022 no 5:25 sou

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

Responder
mickael cogerino diz:

December 15, 2022 no 5:37 sou

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 !

Responder
Patrick diz:

Janeiro 12, 2023 no 2:04 sou

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!

Responder
Jilberto diz:

Janeiro 19, 2023 no 1:56 tarde

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.

Responder