Getting Reporting (Vendor - Applications)

@raghdasi We were using the following SQL queries before our ServiceNow integration more than two years ago; they most likely need to be updated for schema changes (for which you should reach out to Jamf Support).

Applications by User

SELECT computers_denormalized.computer_name, computers_denormalized.computer_id, computers_denormalized.serial_number, computers_denormalized.email, application_details.version
FROM computers_denormalized
JOIN applications ON computers_denormalized.last_report_id = applications.report_id
JOIN application_details ON applications.application_details_id = application_details.id
ORDER BY application_details.version
INTO OUTFILE '/var/lib/mysql-files/JamfProProductionApplications-`date '+%Y-%m-%d-%H%M%S'`.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
';

Adobe Applications by User by Department

SELECT DISTINCT a.computer_name AS 'Computer Name', a.username AS 'User Name', a.department_name AS 'Department', c.application_name AS 'Application Name', c.application_version AS 'Version'
INTO OUTFILE ''/var/lib/mysql-files/Adobe_Apps-`date '+%Y-%m-%d-%H%M%S'`.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '
'
FROM computers_denormalized a INNER JOIN reports b ON a.computer_id = b.computer_id
INNER JOIN applications c ON b.report_id = c.report_id
WHERE c.application_name LIKE '%Adobe%'
ORDER BY a.computer_name, c.application_name, c.application_version
#LIMIT 20
;