Windows Insider

Best Configuration Manager Queries

Readers share their tried-and-true SCCM queries.

I often tell inquisitive admins that there's both an art and a science to getting the most out of tools like Microsoft's System Center Configuration Manager (SCCM) 2007. In many ways the "science" is the easy part. You can learn the science of what you need to know by reading the documentation, taking a couple of classes and simply just knowing the intricacies of its rich interface.

The "art" is another story entirely. With systems-management tools like SCCM, just knowing how to use the interface isn't enough. SCCM's client agents are constantly pulling data from targeted machines and depositing that useful information into SCCM's SQL database. Getting just the right information back out of that database and into a useful form is an art form unto itself.

Reader Responses
I was thinking about this problem the other day with my own SCCM infrastructure. There are many outlets for learning the science, but few for developing your artistic skills. So I sent out a call to readers asking for their very best SCCM queries and collection definitions. I figured that by publishing some new and exciting definitions that other admins swear by and will attach their name to, everyone would benefit.

From my call for queries, I received seven query definitions from four highly artistic admins. Some arrived in SQL format, while others arrived in SCCM console format. Each provides a different perspective of the information that can be useful for managing a Windows environment. What's best about this exercise is that it can give you -- the SCCM admin -- an artistic view of how others paint their own SCCM masterpieces.

The first set of three queries in SQL format comes from Don Hite. Hite is a well-known blogger who writes for the popular Web site Hite kindly handed over his top three most exciting queries. The first enumerates critical information about the data that makes up your SCCM Web reports:

Select VR.ReportID, VR.Name, 
VR.Category, VR.Comment, 
VR.SQLQuery From v_Report VR 
Join v_ReportParameter RP 
on VR.ReportID = RP.ReportID

You can use Hite's second SQL-format query to retrieve the full name associated with a particular username to be entered in place of {Username}. This query aggregates information gathered through computer and user discovery:

Select SD.Name0 
'Machine Name', SD.User_Name0 
'Logon Name', UD.Full_User_Name0 
'Full Name' From v_R_System SD 
Join v_R_User UD on SD.User_Name0 = 
Where SD.User_Name0 = '{Username}'

Also using both user and computer discovery, Hite's third query can be used to return detailed user and machine information from a specified collection. Replace {CollectionName} with the name of the collection to query against:

Select SD.Name0 'Machine Name', 
'Resource Domain', 
SD.User_Name0 'Login ID', 
SD.User_Domain0 'Account 
Domain', USR.Full_User_Name0 
'Full Name', PCB.SerialNumber0 
'Serial Number', CS.Manufacturer0 
Manufacturer, CS.Model0 Model, 
'Assigned Site Code' 
From v_R_System SD Join v_FullCollectionMembership
 FCM on SD.ResourceID = FCM.ResourceID 
 Join v_Collection COL on 
 FCM.CollectionID = COL.CollectionID 
 Join v_R_User USR on SD.User_Name0 = 
 USR.User_Name0 Join v_GS_PC_BIOS PCB 
 on SD.ResourceID = PCB.ResourceID 
 SD.ResourceID = CS.ResourceID 
 Join v_RA_System_SMSAssignedSites 
 SAS on SD.ResourceID = SAS.ResourceID 
 Where COL.Name = '{CollectionName}'

David Mullis is a network admin out of Indianapolis who uses his SQL-formatted query to list all locally installed printers on every machine. He finds this query useful for tracking down rogue printers. Mullis notes that, for this query to work, you'll need to enable the "Printers" section within your sms_def.mof file:

SELECT Distinct 
PRT.Name0, PRT.ShareName0, 
PRT.DriverName0, PRT.DeviceID0, 
PRT.PortName0 FROM v_R_System 
on SYS.ResourceID = PRT.ResourceID
WHERE PRT.PortName0 like 'ip%' or 
PRT.PortName0 like 'com%' or 
PRT.PortName0 like 'lpt%' or 
PRT.PortName0 like 'usb%' ORDER 
BY SYS.Netbios_Name0

Next month, I'll share the SCCM queries submitted by readers Joseph Corey and Steve Oravetz.

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