Report MS Office Installs for versions older than 2013
11/15/2017 875 views
I have been asked for a K1000 report for computers within a particular label (smart label based on domain) that have an installation of MS Office older than version 2013. I have been trying to modify various canned reports without luck... any SQL gurus out there willing to help out a greenhorn? Looking for it to return computer name, username, IP, and Office version.
Answer Chosen by the Author
Please log in to answer
Here you go:
SELECT MACHINE.NAME AS SYSTEM_NAME, LAST_SYNC,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED ,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_VERSION SEPARATOR '\n') AS SOFTWARE_DISPLAY_VERSION_GROUPED
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%'
and substring_index(SOFTWARE.DISPLAY_VERSION, ",", 1) < 15
GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
Answered 11/16/2017 by: chucksteel