Ryan Anderson's ConfigMgr Blog

Premier Field Engineer for System Center Configuration Manager

Useful ConfigMgr Collection Queries

Useful ConfigMgr Collection Queries

  • Comments 1
  • 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!

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