Useful ConfigMgr Collection Queries


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:

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 = "Workstation"

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:

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.PrimaryGroupID = "516"

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):

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.SystemGroupName = "Domain\\ADSecurityGroup1"

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 (13)

  1. Dan says:

    I was looking for a query to find all domain controllers. Your query works perfect! Thanks!

  2. Bruce says:

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

  3. Bruce says:

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

  4. rich redden says:

    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…..

  5. simonia says:

    I am looking for a WQL query to find Models of all devices in my organization. Does anyone have a query for this?

  6. Brad says:

    @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

  7. Todd says:

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

  8. Sudhanshu says:

    I am looking for a query to get primary & secondary DNS server names on my clients, does anyonehave the query??

  9. Vinoth says:

    I am look for a query to pull the device which has a specific file and version.

  10. Vinoth says:

    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"

  11. Lakshmi Narayana says:

    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?

  12. Molley says:

    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.

  13. 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