SCCM WQL Queries - Quick Reference

This article has a list of queries that System Center Configuration Manager (SCCM) Administrators should know about. These queries are helpful when system administrators need values from Windows Management Instrumentation (WMI) objects. WMI provides its own query language that allows you to query managed objects as data providers. The queries help with quick results rather than reports for troubleshooting purposes.

Creating query reports in SCCM

• Open the SCCM console, go-to the monitoring tab and right click on queries
• Select Create Query to open the "Create Query Wizard" as shown below

Give the query an appropriate name and then click the "Edit Query Statement" button (Highlighted in the above screenshot) 

A "Query Statement Properties" dialogue box will pop up. Click "Show Query Language" as shown below.

Copy the required query from the list below, and click OK, and then Next and Finish. 

Double-click the newly created query to get results.

Also, we can run these queries on collections. While creating a query, in the first screenshot, there are 3 options. "Not collection limited", "Limited collection", and "Prompt for collection". 

If the query needs to run on a specific collection, choose "Limited collection" and browse to the required collection. If we need a prompt before running a collection, choose "Prompt". if we want a query on all, choose "Not limited to"

The below queries are from the production environment. I couldn't find enough information on the Internet about writing WQL queries, so I came up with this article to help others with the queries I am creating, or using daily in my environment. I will update the list when I write a new query.

All PCs Heartbeat DDR Not Current within 23 Days

select SMS_R_System.ResourceId, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceType, SMS_R_System.Client from SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())<23)

All 32 Bit Clients

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "X86-based PC"

All 64 bit clients

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = "X64-based PC"

All Active Desktops

select SMS_R_System.ResourceId, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceType, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("3","4","5","6","7","15","16") and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Client = "1"

All Active Laptops

select SMS_R_System.ResourceId, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceType, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "8" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes =
"9" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "10" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes =
"11" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "14" and SMS_R_System.Obsolete = "0" and
SMS_R_System.Active = "1" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Client = "1"

All computers with particular application (replace application name with required name)

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%application name%"

All pull DP's

select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets from  SMS_R_System where SMS_R_System.ResourceNames in (Select ServerName FROM SMS_DistributionPointInfo where SMS_DistributionPointInfo.IsPullDP = '1')

All applications installed on systems in collection excluding updates (add more not like as needed)

select distinct SMS_R_System.NetbiosName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate, SMS_R_System.LastLogonUserName, SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%Security Update%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%Update For Windows%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%Hotfix%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%update for microsoft%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%update%"

Device Model

select distinct SMS_G_System_COMPUTER_SYSTEM.Name, 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

Machines with duplicate hostnames

select r.ResourceId, r.ResourceType, r.Name, r.SMSUniqueIdentifier, r.ResourceDomainORWorkgroup, r.Client from  SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId

Machines with Windows defender

select distinct SMS_R_System.Name, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserName, SMS_R_System.ResourceId, SMS_R_System.NetbiosName, SMS_G_System_SoftwareFile.FilePath 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.FileName = "msascui.exe" and SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 6.0"

Missing SCCM client on machines

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.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1) and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation %" and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in ("SMS_AD_SYSTEM_DISCOVERY_AGENT") and AgentTime >= DateAdd(dd, -7, getdate()))

No reboot in more than 30 days

select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.ResourceType, SMS_R_System.ResourceId, SMS_R_System.Client, SMS_R_System.SMSUniqueIdentifier from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where DATEPART(DD, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime) > 30

Operating system-based collection query

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

Query compare 2 or more collections

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.ResourceId in (select ResourceID   from SMS_FullCollectionMembership   where CollectionID = "") and SMS_R_System.ResourceId not in (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE collectionid IN (''))

Workstations with failed Windows updates

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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CI_ComplianceState.ComplianceStateName = "11"

Machines with failed CCM Eval

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.ResourceId in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result in (3,4,5) and SMS_CH_EvalResult.EvalTime >= DateAdd(dd, -7, getdate())) and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation %"

Clients with no recent activity

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.ResourceId in ( select ResourceID from SMS_R_System where SMS_R_System.AgentName = 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and SMS_R_System.AgentTime <= DateAdd(dd, -7, getdate())) and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation %"

Disk space in C drive

select SMS_R_System.Name, SMS_G_System_LOGICAL_DISK.FreeSpace, SMS_G_System_LOGICAL_DISK.Size, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.CSDVersion from  SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_LOGICAL_DISK.FreeSpace <= 10000 

and SMS_G_System_LOGICAL_DISK.DeviceID = "C:"

CCMEval - Failed Status

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 resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result in (3,4,5) and SMS_CH_EvalResult.EvalTime >= DateAdd(dd, -7, getdate()))

CCMEval -No status

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 resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result = 1 and SMS_CH_EvalResult.EvalTime >= DateAdd(dd, -7, getdate()))

I hope you find this collection of queries useful. 

If anyone comes across any new queries, please feel to share in the comments sections below and I will update the article. By doing so it will be helpful for many admins like me.