Windows Insider

The World's Best Configuration Manager Queries: Part II

Readers submit more of their prized queries for everything from checking maintenance windows to deploying software.

• By Greg Shields
• 08/01/2008

Joseph Corey submitted two of his favorite queries in SCCM console format. His first helps you by reporting on the last time a client machine was rebooted. Joseph finds this query useful for validating that his maintenance windows are functioning as they should:

select distinct 
SMS_R_System.Name, 
SMS_G_System_OPERATING_SYS
TEM.LastBootUpTime from  
SMS_R_System inner join 
SMS_G_System_OPERATING_SYS
TEM on SMS_G_System_OPERAT 
ING_SYSTEM.ResourceID = 
SMS_R_System.ResourceId

His second query, also in SCCM console format, is handy for populating what he calls "exclusionary collections." He uses this one to create a collection of machines that aren't part of another collection. In this query, replace {collectionID} with the collection ID you're interested in verifying against:

Select SMS_R_SYSTEM.ResourceID, 
SMS_R_SYSTEM.ResourceType, 
SMS_R_SYSTEM.Name,SMS_R_SY
STEM.SMSUniqueIdentifier, 
SMS_R_SYSTEM.ResourceDomain 
ORWorkgroup, SMS_R_SYSTEM.Client from 
SMS_R_System where Client = 1 
and ClientType = 1 and ResourceId 
not in (select ResourceID from 
SMS_CM_RES_COLL_{collectionID})

Steve Oravetz, a network specialist from Wisconsin, submitted a SCCM console-formatted query to retrieve systems on his network that have an installed version of Microsoft Office:

select SMS_R_System.Name, 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName from  
SMS_R_System inner join 
SMS_G_System_ADD_REMOVE_P
ROGRAMS on 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.ResourceID = 
SMS_R_System.ResourceId where 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName like 
"Microsoft Office 2000 Standard" or 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName = 
"Microsoft Office 2000 SR-1 Profes 
sional" or 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName = 
"Microsoft Office 2000 SR-1 Stan 
dard" or 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName = 
"Microsoft Office Professional Plus 
2007" or 
SMS_G_System_ADD_REMOVE_P
ROGRAMS.DisplayName = 
"Microsoft Office Standard 2007"

Last, Eric Schmidt submitted a highly useful query for deploying software. SCCM's ability to create dynamic collections for deploying software is one of its greatest strengths. It helps you push software packages out to computers based on more than just their name or physical location.

However, determining which computers successfully receive and install the software using traditional collections is difficult. Instead, try pushing software to "inverse collections." These collections don't list the software computers have installed, but rather the software they haven't. Here's an example in SCCM console format that collects computers that are missing Microsoft Office 2007:

select SMS_R_SYSTEM.Resour 
ceID, SMS_R_SYSTEM.Resource 
Type, SMS_R_SYSTEM.Name,  
SMS_R_SYSTEM.SMSUniqueIden 
tifier, SMS_R_SYSTEM.Resource 
DomainORWorkgroup, 
SMS_R_SYSTEM.Client from 
SMS_R_System where 
SMS_R_System.Name not in 
(select SMS_R_SYSTEM.Name 
from SMS_R_System inner join 
SMS_G_System_SoftwareFile on 
SMS_G_System_SoftwareFile.Reso
urceId = SMS_R_System.Resour 
ceId where SMS_G_System_Soft 
wareFile.FileName = "winword.exe" 
and SMS_G_System_Software 
File.FileVersion >= "12.0")

Once you've created the list, simply refer any Office 2007 advertisements to this collection until it's empty.

There they are: The world's best SCCM queries. The structure of these queries may not be an exact fit for your IT environment's needs, but they're a good way to learn how others are painting the picture of data in their environments.