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?

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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
Answered 10/23/2013 by: dugullett
Red Belt

  • 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.
Please log in to comment

Answers

0

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.

 

Answered 10/23/2013 by: rockhead44
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity