/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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   [ - ] Hide Comments

Comments

Please log in to comment

Community Chosen Answer


Answers

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
Answer this question or Comment on this question for clarity

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
Red Belt

Please log in to comment