Ryan Anderson's ConfigMgr Blog

Premier Field Engineer for System Center Configuration Manager

Useful ConfigMgr Collection Queries

Useful ConfigMgr Collection Queries

  • Comments 4
  • Likes

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
  • I was looking for a query to find all domain controllers. Your query works perfect! Thanks!

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

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

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment