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

I am new to SQL statements.  Here is the one I am currently using:

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,       

      LDSK.Name0 AS [Drive],
      LDSK.Size0/1024 AS [Size (GB)],
      LDSK.FreeSpace0/1024 AS [FreeSpace (GB)],
      (LDSK.Size0/1024-LDSK.FreeSpace0/1024) AS [Used (GB)]
            
FROM
      v_FullCollectionMembership FCM
      JOIN v_R_System SYS ON FCM.ResourceID = SYS.ResourceID
      JOIN v_GS_OPERATING_SYSTEM OS ON FCM.ResourceID = OS.ResourceID
      JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE ENCL ON FCM.ResourceID = ENCL.ResourceID
      JOIN v_GS_COMPUTER_SYSTEM COMP ON FCM.ResourceID = COMP.ResourceID
      JOIN v_GS_X86_PC_MEMORY MEM ON FCM.ResourceID = MEM.ResourceID
      JOIN v_GS_PROCESSOR CPU ON FCM.ResourceID = CPU.ResourceID
      JOIN v_GS_LOGICAL_DISK LDSK ON FCM.ResourceID = LDSK.ResourceID

WHERE

      LDSK.DriveType0 = 3 AND SYS.ResourceID = LDSK.ResourceID AND LDSK.Name0 LIKE 'C%' OR LDSK.Name0 LIKE 'E%'

ORDER BY

      SYS.Name0

It works great, except, it puts the C: and E: output onto different lines, causing duplicate entries.

Is there any way to cause the statement to process C: and then E: in order and then place the output on the same line in the report?

Thank you.
LVL 3
isd503Asked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
Are the C drive and E drive data mutually exclusive or is it possible to have both.  If both are possible then try this:

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,       

	LDSK_C.Name0 AS DriveCName,
	LDSK_C.Size0 AS [Size C: (GB)],
	LDSK_C.FreeSpace0 / 1024 AS [FreeSpace C: (GB)],
	(LDSK_C.Size0 / 1024) - (LDSK_C.FreeSpace0 / 1024) AS [Used C: (GB)],
	LDSK_E.Name0 AS DriveEName,
	LDSK_E.Size0 AS [Size E: (GB)],
	LDSK_E.FreeSpace0 / 1024 AS [FreeSpace E: (GB)],
	(LDSK_E.Size0 / 1024) - (LDSK_E.FreeSpace0 / 1024) AS [Used E: (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

Open in new window

0
 
Brian CroweDatabase AdministratorCommented:
You need to create separate joins to LDSK for "C" and "E" and then coalesce the values in your select.  I will have some code for you shortly unless someone beats me to it :-)


Could a moderator please place the code in the users post in a code block?
0
 
Brian CroweDatabase AdministratorCommented:
Hopefully I didn't fatfinger anything...

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

Open in new window

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
isd503Author Commented:
I am getting the following error.  I apologize, I do not know how to troubleshoot it:

[4200][102][Microsoft]]ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.
0
 
isd503Author Commented:
OK, I copied the whole thing from above and pasted it into the report.  It runs, but does not show the e: drive data.
0
 
isd503Author Commented:
We ended up getting to the final resolution using the initial statement posted.
0
 
isd503Author Commented:
Here is the final statement I used:

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 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.