I need a list of all installed applications without versions in my environment.   This will be an ongoing report to bring to our security team to see if these software applicantions need to be removed.  It's very cumbersome to present a list of 20,000 applications because its shows many versions of the same title installed.  I would like to programmatically filter this list down to add just the title to the list.  I don't need to see App version 1.x, 2.x, 3.x etc...  Is this possible?  I've been working on this a few days and can't get anywhere.

I'm using K1000 v. 7.0.121306                                                
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
You can group by display name, but that won't entirely fix the problem because some software titles include the version.
SELECT DISPLAY_NAME, COUNT(DISPLAY_VERSION) as "Versions", COUNT(MACHINE_SOFTWARE_JT.MACHINE_ID) as "Machines"FROM ORG1.SOFTWAREJOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.IDGROUP BY DISPLAY_NAME
This shows how many different versions are installed for demonstration purposes. Since this is an ongoing effort you may want to show just applications that were added in the past 30 days:
SELECT DISPLAY_NAME, COUNT(DISPLAY_VERSION) as "Versions", COUNT(MACHINE_SOFTWARE_JT.MACHINE_ID) as "Machines"
FROM ORG1.SOFTWARE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DISPLAY_NAME

Answered 09/28/2017 by: chucksteel
Red Belt

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