This works for me. :) You had zeroes on the column names where you shouldn't have had...
select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.user_name0 AS [User Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = 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, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] , v_GS_Chico640.Chico_AssetTag0 as [Reg Asset], v_GS_Chico640.Chico_BuildingCode0 as [Building], v_GS_Chico640.Chico_RoomCode0 as [Room], v_GS_Chico640.Chico_BusinessArea0 as [Bus Area], v_GS_Chico640.OSD_Base0 as [OSD Base], v_GS_Chico640.OSD_Deploy0 as [OSD Deploy], v_GS_Chico640.Role_Admin0 as [Role Admin], v_GS_Chico640.Roles0 as [Roles], v_GS_NETWORK_ADAPTER_CONFIGURATION.Description0 AS [NIC Description], v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 AS [IP Address], v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0 AS [Subnet Mask], v_GS_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway0 AS [Gateway], v_CH_ClientSummary.LastDDR as [Heartbeat Disc], v_CH_ClientSummary.LastHW as [HW Scan], v_CH_ClientSummary.LastSW as [SW Scan], v_CH_ClientSummary.LastPolicyRequest as [Policy Req], v_CH_ClientSummary.LastStatusMessage as [Status Msg], v_CH_ClientSummary.LastActiveTime as [Active] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_Chico640 on (v_GS_Chico640.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_NETWORK_ADAPTER_CONFIGURATION on (v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) left join v_CH_ClientSummary on v_R_System_Valid.ResourceID = v_CH_ClientSummary.ResourceID Where v_FullCollectionMembership.CollectionID = @CollectionID Order by v_R_System_Valid.Netbios_Name0 From: [email protected] [mailto:[email protected]] On Behalf Of Murray, Mike Sent: 21 March 2016 18:43 To: [email protected] Subject: [mssms] Report help? Hey SQL gurus, can someone assist? I want to add the below columns to an existing report. I've tried, but it's not working. Existing report SQL is listed further below. Thanks! New columns: v_CH_ClientSummary.LastDDR0 as [Heartbeat Disc], v_CH_ClientSummary.LastHW0 as [HW Scan], v_CH_ClientSummary.LastSW0 as [SW Scan], v_CH_ClientSummary.LastPolicyRequest0 as [Policy Req], v_CH_ClientSummary.LastStatusMessage0 as [Status Msg], v_CH_ClientSummary.LastActiveTime0 as [Active], Existing report: select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.user_name0 AS [User Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = 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, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] , v_GS_Chico640.Chico_AssetTag0 as [Reg Asset], v_GS_Chico640.Chico_BuildingCode0 as [Building], v_GS_Chico640.Chico_RoomCode0 as [Room], v_GS_Chico640.Chico_BusinessArea0 as [Bus Area], v_GS_Chico640.OSD_Base0 as [OSD Base], v_GS_Chico640.OSD_Deploy0 as [OSD Deploy], v_GS_Chico640.Role_Admin0 as [Role Admin], v_GS_Chico640.Roles0 as [Roles], v_GS_NETWORK_ADAPTER_CONFIGURATION.Description0 AS [NIC Description], v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 AS [IP Address], v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0 AS [Subnet Mask], v_GS_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway0 AS [Gateway] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_Chico640 on (v_GS_Chico640.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_NETWORK_ADAPTER_CONFIGURATION on (v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = @CollectionID Order by v_R_System_Valid.Netbios_Name0 Best Regards, Mike Murray Desktop Management Coordinator - IT Support Services California State University, Chico 530.898.4357 [email protected]<mailto:[email protected]>
