Windows 7 Readiness Report
I have this report that works, but I'm trying to clean it up some. Basically trying to see what all machines cannot be upgraded to Win 7. My only problem is that I would like to display just the drive letter next to DISK_SIZE and DISK_FREE. I know I can add NAME, but it also gives info that I do not need. Does anyone know a better way to add just the drive letter to those fields?
SELECT M.NAME AS NAME, OS_NAME,PROCESSORS,RAM_TOTAL, GROUP_CONCAT(DISTINCT DISK_SIZE SEPARATOR '\n') AS 'DISK_SIZE(GB)', GROUP_CONCAT(DISTINCT MD.DISK_FREE SEPARATOR '\n') AS 'DISK_FREE(GB)', VIDEO_CONTROLLERS as VIDEO FROM MACHINE M LEFT JOIN MACHINE_DISKS MD ON (MD.ID = M.ID) where OS_NAME NOT LIKE '%Mac%' and OS_NAME not like '%Windows 7%' and OS_NAME not like '%ppc%' and OS_NAME not like '%server%' and OS_NAME not like '%linux%' and OS_NAME not like '%x86%' and OS_NAME not like '%cent%' and cs_model not like 'VM%' and cs_model not like 'Virtual%' AND (1 not in ( select 1 from MACHINE_DISKS where MD.ID = M.ID and if(M.OS_ARCH like '%x86%',MD.DISK_FREE >= '16',MD.DISK_FREE >= '20') ) OR (RAM_TOTAL < '1024' OR RAM_TOTAL < '2048') OR (M.PROCESSORS LIKE '%Mhz%')) group by M.NAME
SUBSTRING_INDEX(SUBSTRING_INDEX(MD.NAME, ' ', 2), ' ', -1) AS 'DRIVE_LETTER'