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
