Configuration Manager: Exploring the Database Goodies, Part 1

I spend a lot of time crawling around in the tables and views of Configuration Manager site databases.  There are enough tables and views to spend a lifetime analyzing and discussing them.  There are quite a few that are very useful for custom reports, extensible applications development and good ole fashioned data mining.  Some of these are:

• v_R_SYSTEM
• v_GS_COMPUTER_SYSTEM
• v_GS_SYSTEM_ENCLOSURE
• v_GS_INSTALLED_SOFTWARE_CATEGORIZED (phew!  Long name!)
• v_GS_OPERATING_SYSTEM
• v_GS_X86_PC_MEMORY

...and dozens more.  The real power in these comes from judicious use of SQL "JOIN" operations, whereby you merge pertinent and relevant data from two or more tables or views (or tables and views) to get an aggregate result.

For example, the following query pulls all Laptop systems that have less than 2,048 MB of memory (2 GB's), but it does a little more.  You may notice another database schema being referenced (ABC_SCCM).  This is a separate database I created on the same SQL Server instance, where I have created a TABLE named ADUsers.  This is where a daily process queries the Active Directory environment, truncates and re-populates the table to keep it up to date with user accounts in the organization.  (Note, there are other ways to accomplish this, but this is just one way)...

SELECT dbo.v_GS_COMPUTER_SYSTEM.Name0 AS ComputerName, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
   dbo.v_R_System.User_Name0 AS UserID, 
   ABC_SCCM.dbo.ADUsers.Fname+' '+ABC_SCCM.dbo.ADUsers.Lname AS FullName, 
   ABC_SCCM.dbo.ADUsers.Dept AS Department, 
   dbo.v_R_System.AD_Site_Name0 AS SiteName,  
   dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS Memory 
FROM dbo.v_GS_COMPUTER_SYSTEM INNER JOIN 
   dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = 
dbo.v_R_System.ResourceID 
   INNER JOIN 
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = 
dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID 
   LEFT OUTER JOIN 
   dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = 
dbo.v_GS_X86_PC_MEMORY.ResourceID 
   LEFT OUTER JOIN 
   ABC_SCCM.dbo.ADUsers ON dbo.v_R_System.User_Name0 = 
ABC_SCCM.dbo.ADUsers.Userid 
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN 
(8, 9, 10, 11, 12, 14, 18, 21)) 
   AND (dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 < 2097152) 
ORDER BY dbo.v_GS_COMPUTER_SYSTEM.Name0

Now I can view the following attributes for each row in the results:

• ComputerName (NetBIOS name)
• Model Name
• UserID (sAMAccountName from AD account)
• User Full Name (concatenated from First and Last Name values)
• User Department
• AD Site Name
• Computer Memory (in Kilobytes)

I've been asked quite a few times what the difference between two of these nested VIEW objects: v_GS_COMPUTER_SYSTEM, and v_R_SYSTEM (or v_R_SYSTEM_VALID).

Basically, v_R_SYSTEM is populated by site discovery data, and v_GS_COMPUTER_SYSTEM is populated by client hardware inventory data.  So, from a sequential  or chronological aspect, v_R_SYSTEM is normally populated first, because client systems are typically discovered before they are installed and inventoried.  The net result is that during that gap in events, the resource (computer object within Configuration Manager) is available for management from a Collection perspective.  In other words, you can add the resource to a Collection before it's been inventoried, even before it's had a ConfigMgr client installed.

You obviously don't need to join VIEWs or TABLEs to get useful results. For example, you can find out the counts of computers by each manufacturer in your environment...

SELECT DISTINCT Manufacturer0 AS Manufacturer, 
  COUNT(*) AS QTY 
FROM dbo.v_GS_COMPUTER_SYSTEM 
GROUP BY Manufacturer0 
ORDER BY Manufacturer0

This report only uses v_GS_COMPUTER_SYSTEM, but the limitation is that it can only report from computers which have submitted hardware inventory data. That's always a bad thing, nor is it always a real limitation. It depends on what your needs are, and what your environment is like.

But when you need to pull more information, it often falls in separate VIEWs or TABLEs, such as finding all of the computers for a given user account.  In other words, find all the computers where a specific user account is shown as the "Primary User".  You can get that from one VIEW (v_R_SYSTEM) but if you also want to see the Model of those computers, you will need to get that from another VIEW (v_GS_COMPUTER_SYSTEM), for example...

SELECT DISTINCT dbo.v_R_System_Valid.ResourceID, 
   dbo.v_R_System_Valid.Netbios_Name0 AS ComputerName, 
   dbo.v_R_System_Valid.User_Name0 AS UserName, 
   dbo.v_R_System_Valid.User_Domain0 AS Domain, 
   dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model 
FROM dbo.v_R_System_Valid LEFT OUTER JOIN 
   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System_Valid.ResourceID = 
   dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
WHERE (dbo.v_R_System_Valid.User_Name0 LIKE '%johndoe%') 
ORDER BY ComputerName

You may be wondering what the difference is between v_R_SYSTEM and v_R_SYSTEM_VALID. Ok, besides the "VALID" part, the difference is really based on the IsObsolete and IsDecommissioned fields. If these two fields are not "True", then the resource is included in v_R_SYSTEM_VALID, making it a logical subset of what v_R_SYSTEM contains. For more information, check out this TechNet article.  This may seem trivial, but the more you work with these views, and the more you rely upon them, the more this small distinction will matter.

Conclusion

I will hopefully be posting more on this subject.  I have been so busy with my head shoved up the SQL ass of Configuration Manager for so long that I really just didn't think about sharing my experiences with it all until now.  I will try to balance the posts between "raw" T-SQL and query aspects, as well as the more discreet implications of using it within scripts and web applications.  In the meantime, post a reply/comment if you have any questions or suggestions for future posts?  Thank you!