Displaying a list of all installed software without versions?
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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
Comments:
-
This definitely helps, thanks. I've now created a smart label that filters out some of the software we know is ok and do not want to report on. This is an export of the smart label.
SELECT DISPLAY_NAME, PUBLISHER, SOFTWARE.ID as TOPIC_ID FROM SOFTWARE WHERE ((DISPLAY_NAME not like '%update%') AND (DISPLAY_NAME not like '%kace%') AND (DISPLAY_NAME not like '%microsoft%') AND (DISPLAY_NAME not like '%mcafee%') AND (DISPLAY_NAME not like '%symantec%')
How do I combine this filtering with your sample SQL query? - randyintally 6 years ago
