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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Brian CroweDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.