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
Answer Summary:
SUBSTRING_INDEX(SUBSTRING_INDEX(MD.NAME, ' ', 2), ' ', -1) AS 'DRIVE_LETTER'
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

Try this - it uses MACHINE_DISKS.NAME, but will only return the letter (i.e. C:):

SUBSTRING_INDEX(SUBSTRING_INDEX(MD.NAME, ' ', 2), ' ', -1) AS 'DRIVE_LETTER'

John

Answered 08/11/2012 by: jverbosk
Red Belt

  • That works... Some of these machines have 4-6 drives. Determining which one is the main narrows it down. Thanks John.
Please log in to comment

Answers

0
Answered 08/08/2012 by: SMal.tmcc
Red Belt

  • or this
    http://msdn.microsoft.com/en-us/library/windows/desktop/aa394592(v=vs.85).aspx
  • That's for vbscript. This is a SQL query.
  • oops pulled a Homer
    • at least now I can say the drugs are legal
  • do not know much on sql language, but will look for links as my images do there stuff
    yea or nay?
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158859
  • http://www.sqlservercentral.com/Forums/Topic440435-5-1.aspx
    end of day, Off to check out Hot August Nights Car show. good luck
Please log in to comment
Answer this question or Comment on this question for clarity