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