SCCM collections query

I think most SCCM 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.

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

• Prev
• Next