Help with extracting a report from our K1000
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
Answers (1)
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.
Comments:
-
Perfect - thank you. I knew it had to be easy! - stjohn 11 years ago
