SCCM 2007 Report - Coelesce Two Line Disk Output onto One Line

SELECT DISTINCT 
	SYS.Name0 AS [Computer Name], 
	SYS.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 
	[Operating System] = CASE
		 WHEN (OS.Caption0 = 'Microsoft(R) Windows(R) Server 2003, Standard Edition' AND OS.Version0 = '5.2.3790') 
		 THEN 'Microsoft Windows Server 2003 R2 Standard' 
		 WHEN (OS.Caption0 = 'Microsoft(R) Windows(R) Server 2003, Enterprise Edition' AND OS.Version0 = '5.2.3790') 
		 THEN 'Microsoft Windows Server 2003 R2 Enterprise'
		 ELSE OS.Caption0
	END,
	OS.CSDVersion0 AS [Service Pack], 
	[Serial Number] = CASE
		WHEN (ENCL.SerialNumber0 = 'None')
		THEN 'VM'
		ELSE ENCL.SerialNumber0
	END,
	COMP.Manufacturer0 AS [Manufacturer], 
	COMP.Model0 AS [Model], 
	MEM.TotalPhysicalMemory0 / 1000000 As [Physical Memory (GB)], 
	CPU.Name0 AS [Processor Name], 
	[Processor Type] = CASE
		WHEN CPU.ADDRESSWIDTH0 = '32' 
		THEN '32-Bit' 
		ELSE '64-Bit' 
	END,

	[64 Bit Supported] = CASE
		WHEN CPU.IS64BIT0 = '1' 
		THEN 'Yes' 
		ELSE 'No' 
	END,       

	ISNULL(LDSK_C.Name0, LDSK_E.Name0) AS [Drive],
	ISNULL(LDSK_C.Size0, LDSK_E.Size0) / 1024 AS [Size (GB)],
	ISNULL(LDSK_C.FreeSpace0, LDSK_E.FreeSpace0) / 1024 AS [FreeSpace (GB)],
	(ISNULL(LDSK_C.Size0, LDSK_E.Size0) / 1024) - (ISNULL(LDSK_C.FreeSpace0, LDSK_E.FreeSpace0) / 1024) AS [Used (GB)]
	    
FROM v_FullCollectionMembership AS FCM
INNER JOIN v_R_System SYS
	ON FCM.ResourceID = SYS.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM OS
	ON FCM.ResourceID = OS.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE ENCL
	ON FCM.ResourceID = ENCL.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM COMP
	ON FCM.ResourceID = COMP.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY MEM
	ON FCM.ResourceID = MEM.ResourceID
INNER JOIN v_GS_PROCESSOR CPU
	ON FCM.ResourceID = CPU.ResourceID
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_C
	ON FCM.ResourceID = LDSK_C.ResourceID
	AND LDSK_C.DriveType0 = 3
	AND LDSK_C.Name0 LIKE 'C%'
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_E
	ON FCM.ResourceID = LDSK_E.ResourceID
	AND LDSK_E.DriveType0 = 3
	AND LDSK_E.Name0 LIKE 'E%'
ORDER BY 
	SYS.Name0

Select allOpen in new window

SELECT DISTINCT 
	SYS.Name0 AS [Computer Name], 

	[Environment] = CASE
		WHEN (SYS.Name0 LIKE '%DV%')
		THEN 'DEV'
		WHEN (SYS.Name0 LIKE '%UA%')
		THEN 'UAT'
		ELSE 'PROD'
	END,

	SYS.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 

	[Operating System] = CASE
		 WHEN (OS.Caption0 = 'Microsoft(R) Windows(R) Server 2003, Standard Edition' 
			AND OS.Version0 = '5.2.3790') 
		 THEN 'Microsoft Windows Server 2003 R2 Standard' 
		 WHEN (OS.Caption0 = 'Microsoft(R) Windows(R) Server 2003, Enterprise Edition' 
			AND OS.Version0 = '5.2.3790') 
		 THEN 'Microsoft Windows Server 2003 R2 Enterprise'
		 ELSE OS.Caption0
	END,

	OS.CSDVersion0 AS [Service Pack], 
	DATEDIFF (day, OS.LastBootUpTime0, WS.LastHWScan) AS [Uptime (in Days)], 
	CONVERT(varchar(20), OS.LastBootupTime0, 100) AS [Last Reboot Date/Time],
	CONVERT(varchar(20), WS.LastHWScan, 100) AS [Last Hardware Inventory],

	[Serial Number] = CASE
		WHEN (ENCL.SerialNumber0 = 'None')
		THEN 'VM'
		ELSE ENCL.SerialNumber0
	END,

	COMP.Manufacturer0 AS [Manufacturer], 
	COMP.Model0 AS [Model],

	[Time Zone] = CASE
		WHEN COMP.CurrentTimeZone0 = '0'
		THEN 'UTC'
		WHEN COMP.CurrentTimeZone0 = '-240'
		THEN 'Eastern'
		WHEN COMP.CurrentTimeZone0 = '-300'
		THEN 'Central'
		WHEN COMP.CurrentTimeZone0 = '-360'
		THEN 'Mountain'
		WHEN COMP.CurrentTimeZone0 = '-420'
		THEN 'Pacific'
		ELSE 'Unknown'
	END,		

	MEM.TotalPhysicalMemory0 / 1000000 As [Physical Memory (GB)], 
	CPU.Name0 AS [Processor Name], 
	CPU.NumberOfCores0 AS [Number of Cores],
	CPU.NumberOfLogicalProcessors0 As [Number of Logical CPUs],

	[Processor Type] = CASE
		WHEN CPU.ADDRESSWIDTH0 = '32' 
		THEN '32-Bit' 
		ELSE '64-Bit' 
	END,

	[64 Bit Supported] = CASE
		WHEN CPU.IS64BIT0 = '1' 
		THEN 'Yes' 
		ELSE 'No' 
	END,

	LDSK_C.Size0 / 1024 AS [C: Size (GB)],
	LDSK_C.FreeSpace0 / 1024 AS [C: FreeSpace (GB)],
	(LDSK_C.Size0 / 1024) - (LDSK_C.FreeSpace0 / 1024) AS [C: Used (GB)],

	LDSK_E.Size0 / 1024 AS [E: Size (GB)],
	LDSK_E.FreeSpace0 / 1024 AS [E: FreeSpace (GB)],
	(LDSK_E.Size0 / 1024) - (LDSK_E.FreeSpace0 / 1024) AS [E: Used (GB)]
	    
FROM v_R_System AS SYS

INNER JOIN v_GS_OPERATING_SYSTEM OS
	ON SYS.ResourceID = OS.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS WS
	ON SYS.ResourceID = WS.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE ENCL
	ON SYS.ResourceID = ENCL.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM COMP
	ON SYS.ResourceID = COMP.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY MEM
	ON SYS.ResourceID = MEM.ResourceID
INNER JOIN v_GS_PROCESSOR CPU
	ON SYS.ResourceID = CPU.ResourceID
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_C
	ON SYS.ResourceID = LDSK_C.ResourceID
	AND LDSK_C.DriveType0 = '3'
	AND LDSK_C.Name0 LIKE 'C%'
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_E
	ON SYS.ResourceID = LDSK_E.ResourceID
	AND LDSK_E.DriveType0 = '3'
	AND LDSK_E.Name0 LIKE 'E%'

WHERE (WS.LastHWScan <> 0) 
	AND SYS.canonicalName0 LIKE 'mydomain.com/Servers%' OR SYS.canonicalName0 LIKE 
	'mydomain.com/Domain Controllers%' OR SYS.Name0 IN ('MACHINE1',
	'MACHINE2','MACHINE3','MACHINE4','MACHINE5','MACHINE6',
	'MACHINE7','MACHINE8','MACHINE9','MACHINE10')

ORDER BY SYS.Name0 

Select allOpen in new window