Add IP address and Subnet to the "Hardware 01A – Summary of computers in a specific collection"

Summary:

Hardware 01A – Summary of computers in a specific collection report is Hardware Asset Intelligence reports contain a summary view of all of the computers in a collection.

It’s very useful to add two more columns, on for machine IP address and one for Subnet.

Report query:

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
WHERE     (v_FullCollectionMembership_1.CollectionID = @CollectionID)
ORDER BY [Computer Name]

Good Luck

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s