CM 12 - Summary of Computer specific details(Hardware) with IP Address and Subnet

Custom Query for Hardware details with IP Address and Subnet. Please type your collection name to view the reports.


SELECT DISTINCT
                      TOP (100) PERCENT dbo.v_R_System_Valid.Netbios_Name0 AS [Computer Name],
                      dbo.v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], dbo.v_Site.SiteName AS [SMS Site Name],
                      CASE WHEN (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 IS NULL OR
                      v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
                      THEN 'Unknown' ELSE v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 END AS [Top Console User],
                      dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
                      dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
                      dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                          (SELECT     SUM(dbo.v_GS_LOGICAL_DISK.Size0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK INNER JOIN
                                                   dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID
                            WHERE      (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System_Valid.ResourceID) AND
                                                   (dbo.v_FullCollectionMembership.CollectionID = @CollectionID)) AS [Disk Space (MB)],
                          (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                   dbo.v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                   v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                            WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = dbo.v_R_System_Valid.ResourceID) AND
                                                   (v_FullCollectionMembership_2.CollectionID = @CollectionID)) AS [Free Disk Space (MB)],
                      dbo.v_RA_System_IPAddresses.IP_Addresses0, dbo.v_RA_System_IPSubnets.IP_Subnets0
FROM         dbo.v_R_System_Valid INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE ON
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_FullCollectionMembership AS v_FullCollectionMembership_1 ON
                      v_FullCollectionMembership_1.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                      dbo.v_Site ON v_FullCollectionMembership_1.SiteCode = dbo.v_Site.SiteCode INNER JOIN
                      dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = dbo.v_R_System_Valid.ResourceID AND
                      v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) INNER JOIN
                      dbo.v_RA_System_IPSubnets ON dbo.v_R_System_Valid.ResourceID = dbo.v_RA_System_IPSubnets.ResourceID INNER JOIN
                      dbo.v_RA_System_IPAddresses ON dbo.v_R_System_Valid.ResourceID = dbo.v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN
                      dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
                      dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = dbo.v_R_System_Valid.ResourceID