As most of you know I NEVER use IP data within a report. due to the large number of duplicate rows. however….


Notice that E6510, ES-04, ES-06 are list twice.. Hence why I NEVER use IP address date within a report.

select  distinct
R.Netbios_Name0 AS ‘Computer Name’,
R.Resource_Domain_OR_Workgr0 AS ‘Domain/Workgroup’,
S.SiteName as ‘SMS Site Name’,
R.AD_Site_name0 as ‘AD Site’,
IP.IP_Subnets0 as ‘IP Subnet’,
‘Top Console User’ = CASE
  when (SCUM.TopConsoleUser0 is NULL or SCUM.TopConsoleUser0 = ‘-1’) then ‘Unknown’
  Else SCUM.TopConsoleUser0
OS.Caption0 AS ‘Operating System’,
OS.CSDVersion0 AS ‘Service Pack Level’,
SEU.SerialNumber0 AS ‘Serial Number’,
SEU.SMBIOSAssetTag0 AS ‘Asset Tag’,
CS.Manufacturer0 AS ‘Manufacturer’,
CS.Model0 AS ‘Model’,
RAM.TotalPhysicalMemory0/1024 AS ‘Memory (MB)’,
Pro.NormSpeed0 AS ‘Processor (GHz)’,
LD.Size0 as ‘Disk Space (MB) on C:\’,
LD.FreeSpace0 as ‘Free Disk Space (MB) on C:\’
v_R_System_Valid R
inner join v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID)
left join v_GS_SYSTEM_ENCLOSURE_UNIQUE SEU on (SEU.ResourceID = R.ResourceID)
inner join v_GS_COMPUTER_SYSTEM CS on (CS.ResourceID = R.ResourceID)
inner join v_GS_X86_PC_MEMORY RAM on (RAM.ResourceID = R.ResourceID)
inner join v_GS_PROCESSOR Pro on (Pro.ResourceID = R.ResourceID)
inner join v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID)
left  join v_Site s on (FCM.SiteCode = S.SiteCode)
inner join v_GS_LOGICAL_DISK LD on (LD.ResourceID =R.ResourceID) and LD.DeviceID0 = SUBSTRING(OS.WindowsDirectory0,1,2)
left outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on (SCUM.ResourceID = R.ResourceID)
join dbo.v_RA_System_IPSubnets IP  on (IP.ResourceID = R.ResourceID)
FCM.CollectionID = @CollectionID
Order by