Hi,

 

I am trying to get a report sorted in our K1000 that lists computer names, with the name and version number of MS Office they are running, sorted by computer name. I can get the report to list the workstations name, and the title of the MS Office app, but I can work out how to get the version number to list as seperate column. Below is a copy of the report, can anyone help?

 

Thanks

Scott

St John Ambulance

SELECT MACHINE.NAME AS SYSTEM_NAME,GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED FROM MACHINE  LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) WHERE ((SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Plus 2007%') OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Edition 2003%') OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office 2000 Professional%') OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Plus 2010%'))  GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME

 

 

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

You need to add a line to the select portion to include DISPLAY_VERSION

 SELECT MACHINE.NAME AS SYSTEM_NAME,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR ',') AS SOFTWARE_DISPLAY_VERSION_GROUPED

FROM MACHINE  
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 

WHERE 
((SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Plus 2007%') 
    OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Edition 2003%') 
    OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office 2000 Professional%') 
    OR (SOFTWARE.DISPLAY_NAME like '%Microsoft Office Professional Plus 2010%'))  

GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME

I used a group_concat similar to yours for the display name.

Answered 10/03/2012 by: chucksteel
Red Belt

  • Perfect - thank you. I knew it had to be easy!
Please log in to comment
Answer this question or Comment on this question for clarity

Share