K1000: Report on Machines with anything less than Java 7 Update 45
I am trying to get a list of all machines that have anything less than the Java 7 Update 45 installed. Using the ASSET_HISTORY table makes it tricky because everything in inventory had a different version installed prior to last week when this update came out. When I try to run a search or create a label in Inventory it takes forever, because everything has java and it's pulling thousands of entries. Here's the sql query I am starting with to narrow it down:
SELECT NAME, TIME, VALUE1 as Software, VALUE2 as Version
FROM ASSET_HISTORY A
WHERE FIELD_NAME = 'SOFTWARE'
AND CHANGE_TYPE = 'DETECTED'
AND TIME BETWEEN '2013-10-16' and '2013-10-23'
AND Value1 like '%Java%'
AND Value1 not like '%Java 7 Update 45%'
AND Value1 not like '%Java Auto Updater%'
ORDER BY TIME asc
This gives me 21 machines which is much more manageable, but I know there are more out there based on software numbers in Kace. Any help?
Answers (2)
Would something like this work?
SELECT S.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 MSJT ON MSJT.SOFTWARE_ID = S.ID LEFT JOIN MACHINE M ON M.ID = MSJT.MACHINE_ID WHERE (S.DISPLAY_NAME LIKE 'JAVA%' AND S.DISPLAY_VERSION < '7.0.450' AND S.DISPLAY_NAME LIKE '%UPDATE%') AND S.DISPLAY_NAME NOT RLIKE 'UPDATE CHECKER|UPDATER|DEPLOYMENT KIT|DEVELOPMENT KIT' GROUP BY S.DISPLAY_NAME ORDER BY S.DISPLAY_NAME
Comments:
-
This works beautifully. I might change it to list by machine name and order by version or something to make the list prettier, but this is perfect. - lmland 10 years ago
I created a report that shows me all Java versions by count with the machines. I am uploading the pictures showing my steps. Hopefully it's viewable.
