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.


  • How To Configure Windows 10 for Intel Optane Memory

    Intel's Optane memory technology can significantly improve the performance of your Windows 10 system -- provided you enable it correctly. A single mistake can render the system unbootable. Here's how to do it the right way.

  • Microsoft and SAP Enhance Partnership with Teams Integration

    Microsoft and SAP this week described continuing partnership efforts on Microsoft Azure, while also planning a Microsoft Teams integration with SAP's enterprise resource planning product and other solutions.

  • Blue Squares Graphic

    Microsoft Previews Azure IoT Edge for Linux on Windows

    Microsoft announced a preview of Azure IoT Edge for Linux on Windows, which lets organizations tap Linux virtual machine processes that also work with Windows- and Azure-based processes and services.

  • How To Automate Tasks in Azure SQL Database

    Knowing how to automate tasks in the cloud will make you a more productive DBA. Here are the key concepts to understand about cloud scripting and a rundown of the best tools for automating code in Azure.

comments powered by Disqus