Need help creating a software report
I am trying to create a report with the following information and am having a difficult time getting it to work. Any help would be greatly appreciated.
Total # Windows OS Systems
Total # MS Office by office version…
Total # Project
Total # Vision
Exclude systems with these products that have not reported in the last 30 days.
Community Chosen Answer
This will probably require some tweaking, but is this something like what you are looking for? I wasn't for sure on the Vision part that will need to be adjusted so that you don't get everything with Vision in the title.
SELECT DISPLAY_NAME, DISPLAY_VERSION, GROUP_CONCAT(DISTINCT M.NAME ORDER BY M.NAME SEPARATOR '\n') AS MACHINE_NAME FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE (NOT S.IS_PATCH) AND M.OS_NAME LIKE '%WINDOWS%' AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY) AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE 2%' OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROJECT%' OR DISPLAY_NAME LIKE '%VISION%') GROUP BY S.DISPLAY_NAME ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION