Thursday, June 26, 2014

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?
                    Post a Comment