Recently installed Applications on all machines

Leal
New Contributor III

Hi There,

We wrote a SQL query that will show the recently installed applications on all machines using the latest two inventory days to compare. Hopefully it helps someone here who can't afford to pay JAMF for their services.

SELECT * FROM (SELECT applications.application_name, applications.application_version, applications.application_path, FROM_UNIXTIME(r.date_entered_epoch/1000) AS firstdate, computers_denormalized.computer_name
FROM computers_denormalized INNER JOIN reports r ON computers_denormalized.computer_id = r.computer_id INNER JOIN applications ON r.report_id = applications.report_id
Where r.date_entered_epoch = ( Select Max(r1.date_entered_epoch) from reports r1 where r1. computer_id = computers_denormalized.computer_id )) AS ONE

LEFT OUTER JOIN (SELECT applications.application_name as application_name_second, applications.application_version as application_version_second, applications.application_path as application_path_second, FROM_UNIXTIME(r.date_entered_epoch/1000) AS secondlatest, computers_denormalized.computer_name as computer_name_second
FROM computers_denormalized INNER JOIN reports r ON computers_denormalized.computer_id = r.computer_id INNER JOIN applications ON r.report_id = applications.report_id
Where r.date_entered_epoch = ( Select Max(r1.date_entered_epoch) from reports r1 where r1. computer_id = computers_denormalized.computer_id and r1.date_entered_epoch < (Select Max(r2.date_entered_epoch) from reports r2 where r2. computer_id = computers_denormalized.computer_id ) )) AS TWO

ON TWO.application_name_second=ONE.application_name AND TWO.application_path_second=ONE.application_path AND TWO.computer_name_second =ONE.computer_name AND TWO.application_version_second=ONE.application_version

WHERE TWO.application_name_second is NULL AND DATEDIFF (CURDATE(), ONE.firstdate) <=2

2 REPLIES 2

Snickasaurus
Contributor

Bookmarking this until I figure out how to use it. Was hoping/looking for something that reports this info. Thanks @Leal for your hard work!!

Leal
New Contributor III

If you need help let me know @Snickasaurus