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?