- v_GS_INSTALLED_SOFTWARE_CATEGORIZED (phew! Long name!)
...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.