Useful ConfigMgr Collection Queries

Article
01/27/2014

I think most ConfigMgr administrators have a handful of WQL queries that they hang onto for frequently used collection queries. I thought it might be useful to share out a few of my most commonly used queries. Please add your favorites to the comments and we can all benefit!

All Servers:

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

All Workstations:

All Branch Distribution Points (SCCM 2007):

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_distributionpointinfo on SMS_r_system.name = SMS_distributionpointinfo.servername where ispeerdp = 1 and SMS_R_SYSTEM.Active=1

Is Service1 present and running:

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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "Service1" and SMS_G_System_SERVICE.State = "Running"

All Domain Controllers:

Software Updates Last Scan Completion Greater than 30

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_SUP_SCAN_TOOL on SMS_G_System_SUP_SCAN_TOOL.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SUP_SCAN_TOOL.LastCompletionTime <= DATEADD(dd,-30,GetDate())

6/14/2014: Adding Collection by AD Security Group:

Members of ADSecurityGroup1 (remember to update both domain the domain name, and the security group name):

Clients from a particular hardware manufacturer. The following would pull Dell systems.

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.Manufacturer like "Dell%"

You can create a report to see what values you need to consider in your criteria with the following SQL Query:

SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'

FROM dbo.v_GS_COMPUTER_SYSTEM

GROUP BY Manufacturer0,Model0

ORDER BY Model0

You can run this in SQL Management Studio or create a report using the following post:

Creating a ConfigMgr Report from a SQL Query

For example, HP also uses Hewlett-Packard:

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.Manufacturer like "HP%" or SMS_G_System_COMPUTER_SYSTEM.Manufacturer like "Hewlett-Packard%"

7/10/2014: Adding OU Queries:

Specific OU:

SELECT
ResourceId,
SystemOUName,
ResourceType,
Name,
SMSUniqueIdentifier,
ResourceDomainORWorkgroup,
Client
FROM
SMS_R_System
WHERE SystemOUName = "DOMAIN.COM/LEVEL1OU"

Specific OU Excluding SubOU:

SELECT
ResourceId,
SystemOUName,
ResourceType,
Name,
SMSUniqueIdentifier,
ResourceDomainORWorkgroup,
Client
FROM
SMS_R_System
WHERE SystemOUName = "DOMAIN.COM/LEVEL1OU"
AND ResourceId NOT IN (SELECT
resourceID
FROM
SMS_R_System
WHERE
SystemOUName LIKE "DOMAIN.COM/LEVEL1OU/%")

I’ll post more as I find them. I seem to keep finding notes all over the place. Might as well keep a copy here!

Enjoy!

Ryan

Comments

Anonymous
April 28, 2014
I was looking for a query to find all domain controllers. Your query works perfect! Thanks!
Anonymous
April 16, 2015
I want to modify an existing collection I have that tells me who has not rebooted in 7 days, but exclude an OU. I cannot figure out how to do this. Any ideas?

existing:

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.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7)

And this snippet is what I want to add in but no matter how I do it I get a syntax error:

SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System.SystemOUName = "DOMAIN.PVT/XYZ/WORKSTATIONS/MEETING ROOMS")
Anonymous
April 20, 2015
I got something to work for what I want. Again looking to have a collection of PC's that have not rebooted in a week excluding Pc's we have in a OU for meeting rooms. Those PC's rarely reboot except for scheduled patch time.

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.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7) and (ResourceId NOT IN (SELECT resourceID FROM SMS_R_System WHERE SystemOUName = "DOMAIN.PVT/OU1/OU2/MEETING ROOMS"))
Anonymous
April 24, 2015
what a coincidence... I came across your site Ryan when I googled a query I am trying to create to find a list of computers not running X service.....
Anonymous
September 14, 2015
I am looking for a WQL query to find Models of all devices in my organization. Does anyone have a query for this?
Anonymous
November 12, 2015
@simonia:
select distinct 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_R_System.Client = "1" order by SMS_G_System_COMPUTER_SYSTEM.Model
Anonymous
December 21, 2015
Here is one for IP address range based on prompted Value ... Example 10.%.%.%

select
SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like ##PRM:SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress##
Anonymous
December 30, 2015
The comment has been removed
Anonymous
January 13, 2016
I am look for a query to pull the device which has a specific file and version.
Anonymous
January 13, 2016
I tried the below query and still not getting the results.

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "File Name" and SMS_G_System_SoftwareFile.FileVersion != "Version"
Anonymous
January 21, 2016
i have one doubt regarding sccm client installation. how to install sccm client on discovered computer.?
Is there any process to deploy clients through remote push silently?
Anonymous
February 26, 2016
I need to have a query which returns Clients Collection Name, Client Computer Name, Client Mac address, and any deployment failures for that collection per client .I also need to subscribe for emailing this query result weekly to my email. I appreciate any help.
Anonymous
March 10, 2016
Hi ! Thank you all for your SCCM queries, they really help. I am looking for three main queries, I believe they are hard to build, at least for me who have just a little knowledge on SQL. The three are related to Hardware changes.

1) Memory Change - A report that would tell me which computer had a memory change in (MB). Also mentioning columns with RAM added and RAM Removed (With the specific amount).

2) Video Card Change - Also it would tell me when a video card was added or removed. If possible, mentioning the Make, Model and Video Card capacity in GB.

3) Monitor Change - Most of my users have two or three monitors. I would like to know which computer have removed or added a new monitor based on any specific reference (Serial Number) for example.

I believe that is a very hard thing to achieve, but I just wanted to say thank you in advance to all of you who will try to help me out on this.

Regards,

Eden Oliveira
Anonymous
March 09, 2017
I am looking for a query to create a collection which grab all non compliant clients due to missing software updates or pending updates , does any one have the query??

Share via

Useful ConfigMgr Collection Queries

Comments

Additional resources