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.

Microsoft's System Center Configuration Manager (SCCM) 2007 is such a useful utility for IT environments, it's well worth spending a little more time talking about how to use it to its best advantage. My column last month examined some of the world's finest SCCM queries. This month, we'll serve up some of your submitted favorites.

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 
TEM.LastBootUpTime from  
SMS_R_System inner join 
ING_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, 
ORWorkgroup, SMS_R_SYSTEM.Client from 
SMS_R_System where Client = 1 
and ClientType = 1 and ResourceId 
not in (select ResourceID from 

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, 
ROGRAMS.DisplayName from  
SMS_R_System inner join 
ROGRAMS.ResourceID = 
SMS_R_System.ResourceId where 
ROGRAMS.DisplayName like 
"Microsoft Office 2000 Standard" or 
ROGRAMS.DisplayName = 
"Microsoft Office 2000 SR-1 Profes 
sional" or 
ROGRAMS.DisplayName = 
"Microsoft Office 2000 SR-1 Stan 
dard" or 
ROGRAMS.DisplayName = 
"Microsoft Office Professional Plus 
2007" or 
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,  
tifier, SMS_R_SYSTEM.Resource 
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 
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.

About the Author

Greg Shields is Author Evangelist with PluralSight, and is a globally-recognized expert on systems management, virtualization, and cloud technologies. A multiple-year recipient of the Microsoft MVP, VMware vExpert, and Citrix CTP awards, Greg is a contributing editor for Redmond Magazine and Virtualization Review Magazine, and is a frequent speaker at IT conferences worldwide. Reach him on Twitter at @concentratedgreg.


comments powered by Disqus