Windows Insider

Best Configuration Manager Queries

Readers share their tried-and-true SCCM queries.

• By Greg Shields
• 07/01/2008

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 MyITForum.com. 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 = 
UD.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', 
SD.Resource_Domain_OR_Workgr0 
'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, 
SAS.SMS_Assigned_Sites0 
'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 
 Join v_GS_COMPUTER_SYSTEM CS on 
 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 
SYS.Netbios_Name0, 
PRT.Name0, PRT.ShareName0, 
PRT.DriverName0, PRT.DeviceID0, 
PRT.PortName0 FROM v_R_System 
SYS JOIN v_GS_PRINTER_DEVICE PRT
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.