The Missing SCCM Database View

For most interfaces I've worked on over the past ten years, which involve poking a stick at System Center Configuration Manager 2007 or 2012, this is one database view (or stored proc) that I'm usually building for myself.  It basically tosses about eight built-in Views and Tables into a blender, with a dash or two of stupid sauce and provides a fairly good picture of each computer in your site database.

It spews out the computer name, client info, site code, manufacturer, model, memory, Windows flavor, chassis type (I didn't cross-join that to produce names, but you can do that, I'm too lazy right now), BIOS info and serial number, CPU, AD stuff, install date, and of course, my personal favorite: the SCCM distribution point reference.  Note that I've provided a fall-back DP name for those which are not explicitly mapped via a site boundary, so they come back home to an MP usually (not always).  I used "DP0001" but you should stuff your own name in place of that.

[begincode]

SELECT DISTINCT
v_R_System.Name0,
v_R_System.ResourceID,
v_R_System.Client0 AS ClientInstalled,
v_R_System.Client_Version0 AS ClientVersion,
v_R_System.Creation_Date0 AS ClientDate,
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS SiteCode,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
v_GS_COMPUTER_SYSTEM.Model0 AS ModelName,
v_GS_COMPUTER_SYSTEM.SystemType0 AS SystemType,
v_GS_OPERATING_SYSTEM.Caption0 AS OSName,
v_GS_OPERATING_SYSTEM.CSDVersion0 AS ServicePack,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS Memory,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 AS ChassisType,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS SerialNumber,
v_GS_PC_BIOS.Manufacturer0 AS BIOSvendor,
v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS BIOSversion,
v_GS_PC_BIOS.ReleaseDate0 AS BIOSdate,
v_GS_PROCESSOR.Name0 AS CPU,
v_R_System.AD_Site_Name0 AS ADSiteName,
v_GS_COMPUTER_SYSTEM.UserName0 AS ADUserName,
v_GS_COMPUTER_SYSTEM.Domain0 AS ADDomain,
v_GS_OPERATING_SYSTEM.InstallDate0 AS OSInstallDate,
v_BoundaryInfo.BoundaryID,
COALESCE (ProtectedSiteSystem_ARR.ServerName, 'DP0001') AS DPServer
FROM dbo.ProtectedSiteSystem_ARR AS ProtectedSiteSystem_ARR
INNER JOIN
dbo.v_BoundaryInfo AS v_BoundaryInfo
ON ProtectedSiteSystem_ARR.BoundaryID = v_BoundaryInfo.BoundaryID
RIGHT OUTER JOIN
dbo.v_R_System AS v_R_System INNER JOIN
dbo.v_GS_PROCESSOR AS v_GS_PROCESSOR
ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM AS v_GS_COMPUTER_SYSTEM
ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_SYSTEM_ENCLOSURE AS v_GS_SYSTEM_ENCLOSURE
ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
INNER JOIN
dbo.v_GS_X86_PC_MEMORY AS v_GS_X86_PC_MEMORY
ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM AS v_GS_OPERATING_SYSTEM
ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_PC_BIOS AS v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
INNER JOIN
dbo.v_RA_System_SMSAssignedSites AS v_RA_System_SMSAssignedSites
ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID
ON v_BoundaryInfo.Value = v_R_System.AD_Site_Name0

ORDER BY v_R_System.Name0

[endcode]

To test this out, open your SQL Management Studio.  Connect to your site database server and site database instance.  Then click "New Query" and paste the code in and press F5 to run it.  Add a splash of your favorite alcoholic beverage, some Barry White music, dim the lights, and enjoy the vibe.  Peace.

Random SCCM Database Thoughts

I ran these on a SCCM 2007 environment, but most of them should work in 2012 R2 as well.

Crack open your SSMS console, swallow your entire Espresso, crack your knuckles, inhale deep and slow, and let it out deep and slow.  Then scream something stupid and look serious.  Now, let's get started...

List the computers in a particular AD Site, and identify their makes, models, and BIOS serial numbers...

• Join v_R_System with v_GS_Computer_System and v_GS_System_Enclosure on ResourceID (using LEFT joins to avoid dropping those which don't report inventory yet).  Then group by the AD_Site_Name0 field.

• Step 1, filter on the following view-joins to see the general scope of data...

SELECT DISTINCT
   dbo.v_R_System.ResourceID, dbo.v_R_System.AD_Site_Name0, 
   dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 
   dbo.v_GS_COMPUTER_SYSTEM.Model0, 
   dbo.v_GS_COMPUTER_SYSTEM.SystemType0, 
   dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM dbo.v_R_System LEFT OUTER JOIN
   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID =
      dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID =
      dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

• Step 2, hone it down...

SELECT DISTINCT

   dbo.v_R_System.ResourceID, dbo.v_R_System.AD_Site_Name0, 

   dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, 

   dbo.v_GS_COMPUTER_SYSTEM.Model0, 

   dbo.v_GS_COMPUTER_SYSTEM.SystemType0, 

   dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0

FROM dbo.v_R_System LEFT OUTER JOIN

   dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID =

      dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
   dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID =

      dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

WHERE dbo.v_R_System.AD_Site_Name0 = 'DOUBLE_HEADED_DONG_FACTORY'

Find all clients which are assigned to a particular IPv4 gateway...

• Step 1, just for fun, filter and browse the results of round 1, using v_Network_Data_Serialized

SELECT DISTINCT 
   DNSHostName0, ResourceID, IPSubnet0, MACAddress0, 
   IPAddress0, DHCPEnabled0, DHCPServer0, DNSDomain0, DefaultIPGateway0
FROM dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL)
   AND (DHCPEnabled0 = 1)
   AND (IPAddress0 NOT LIKE 'f%')

• Step 2, go in for the kill.  Find all that are using gateway 192.168.2.11...

SELECT DISTINCT 

   DNSHostName0, ResourceID, IPSubnet0, MACAddress0, 

   IPAddress0, DHCPEnabled0, DHCPServer0, DNSDomain0, 

   DefaultIPGateway0
FROM dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL) 

   AND (DHCPEnabled0 = 1) 

   AND (DefaultIPDGateway0='192.168.2.11')

ORDER BY DNSHostName0

List the unique AD Site Names for all computers in a given Collection...

• Join v_R_System with a sub-query on the desired Collection "ABC12345".

SELECT DISTINCT AD_Site_Name0 dbo.v_R_System
WHERE dbo.v_R_System.ResourceID IN
   (SELECT ResourceID FROM dbo.v_CM_RES_COLL_ABC12345)

List all of the Distribution Point Servers in site "ABC"...

• Filter on View named v_SystemResourceList...

SELECT SiteCode,ServerName
FROM dbo.v_SystemResourceList
WHERE SiteCode='ABC' AND RoleName='SMS Distribution Point'
ORDER BY ServerName

List distinct Site Server Role type/names in the database, along with counts of servers for each role (keep in mind that servers can provide multiple roles, so don't sum the totals and think that's an accurate count of total site servers)

• Filter on View named v_SystemResourceList...

SELECT DISTINCT RoleName, COUNT(*) AS ServerCount
FROM dbo.v_SystemResourceList
GROUP BY RoleName
ORDER BY RoleName

List User Account status values and counts for each.

• Start with a basic SQL query to identify the unique values for column User_Account_Control0 from view named v_R_User

SELECT DISTINCT User_Account_Control0, COUNT(*) AS UserCount
FROM dbo.v_R_User
GROUP BY User_Account_Control0

• Then add a dash of SQL "CASE" statement with some Oregano and Basil (for other values to match up, check out Rajnish's blog post here)...

SELECT DISTINCT 

User_Account_Control0, 

COUNT(*) AS UserCount, 

CASE User_Account_Control0 

WHEN 512 THEN 'Enabled' 

WHEN 514 THEN 'Disabled' 

WHEN 544 THEN 'Enabled Must Change Password' 

WHEN 66048 THEN 'Enabled Password Never Expires' 

ELSE 'You can code the others...' 

END AS UAC_Name 

FROM dbo.v_R_User 

GROUP BY User_Account_Control0

List computers a particular AD user has logged onto within the past 30 days...

• Find logins for user "doofus" on domain "contoso".  Join v_R_System with v_GS_SYSTEM_CONSOLE_USER on ResourceID and filter on the SystemConsoleUser0 column.  Then add a DateDiff() filter to restrict on logons within the last 30 days...

SELECT 
   dbo.v_R_System.Name0 AS ComputerName, 
   dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID,
   dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS LastLogon,
   dbo.v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS NumberLogons,
   dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS UserID,
   dbo.v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS LogonTotalTime
FROM dbo.v_GS_SYSTEM_CONSOLE_USER INNER JOIN
   dbo.v_R_System ON dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID =
      dbo.v_R_System.ResourceID
WHERE 
   (dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 = 'contoso\doofus')
   AND
   (DATEDIFF(dd, dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0, GETDATE()) < 30)

Need to identify Advertisements pointed at Direct-membership Collections?

• Join v_Advertisement to v_Package, and v_Collection, and sub-query against v_CollectionRuleDirect using CollectionID as the filtering column...

SELECT 
   dbo.v_Advertisement.AdvertisementID, 
   dbo.v_Advertisement.AdvertisementName, 
   dbo.v_Advertisement.PackageID, 
   dbo.v_Package.Name, 
   dbo.v_Advertisement.CollectionID,
   dbo.v_Collection.Name AS CollectionName
FROM dbo.v_Advertisement INNER JOIN
   dbo.v_Collection ON dbo.v_Advertisement.CollectionID =
      dbo.v_Collection.CollectionID INNER JOIN 
   dbo.v_Package ON dbo.v_Advertisement.PackageID =
      dbo.v_Package.PackageID
WHERE (dbo.v_Collection.CollectionID IN
   (SELECT DISTINCT CollectionID FROM dbo.v_CollectionRuleDirect)) 
ORDER BY 
   dbo.v_Advertisement.AdvertisementName

Need to computers with every version of Internet Explorer?

• Well, you might expect to query v_GS_Installed_Software_Categorized or the ARP tables, but remember that IE10 and 11 came out as KB updates for some platforms.  So best to query v_GS_Software_Product.  Note the some entries (ProductName0 LIKE 'Internet Explorer%') OR (ProductName0 LIKE 'Windows%Internet Explorer%') will produce the version within the product name, while others will only show "Internet Explorer" and the version in the ProductVersion0 column.  Drink plenty of coffee and enjoy that.  Don't forget to filter out the double counted items (yes. they are hiding there).  Don't be surprised if you need to crack open your dusty T-SQL book and brush up on the CASE statement.  I'll let you have fun with this one, and I'll post my take on it later.

If I get more coffee in me and feel motivated, I may post more.  Let me know if these are helpful?

Config Manager Queries: CPU Types

I probably should revive my old ScriptZilla blog for stuff like this, but the heck with it.  I'm just posting all this here from now on.  After all: It is the brain-skattering blogness that I'm kicking around, if that even makes any sense.

This is a simple SQL query to fetch all the unique CPU manufacturers and names within your inventoried ball of confusion...

SELECT DISTINCT Manufacturer, Name, COUNT(Name) AS QTY
FROM dbo.v_LU_CPU
GROUP BY Manufacturer, Name 
ORDER BY Manufacturer, Name

Here's an example using VBScript (example is using a DSN-less connection with an explicit SQL user account and password. You can obviously run this under SSPI or "trusted" context, or using a stored DSN)

dsn = "DRIVER=SQL Server;SERVER=DBServer1;database=SMS_ABC;UID=username;PWD=password;"

Set conn = CreateObject("ADODB.Connection")
Set cmd  = CreateObject("ADODB.Command")
Set rs   = CreateObject("ADODB.Recordset")

conn.Open dsn

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd

If Not(rs.BOF And rs.EOF) Then
    xrows = rs.RecordCount
    Do Until rs.EOF
        For i = 0 to rs.Fields.Count -1
            wscript.Echo rs.Fields(i).Name & vbTab & rs.Fields(i).Value
        Next
        rs.MoveNext
    Loop
Else
    wscript.echo "no records found, bummer."
End If

found = False
rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

I was going to post a PowerShell example, but going from v2 to v3 I'm finding all sorts of confusing recommendations about the "best way" to invoke a simple T-SQL "SELECT" query against a remote SQL Server that my head is already spinning. Even some that just recommend installing custom cmdlet extensions, and whatnot. If anyone wants to point me to a nice, simple, concise, example (i.e. equal or fewer lines of code than the VBScript example above) please post a reply. Gracias!

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!