Get all installed applications

Works for me @tron_jones

Will cut and paste into spreadsheet for now. I'm sure if I could use the API there might be a fancier way ;)

@charliwest Did you find a "fancier" way? I'm currently struggling with mucking about in the MySQL database (test environment only) and fumbling through APIs, but so far I'm not really finding anyway to get exportable reports for Application usage/version/etc for licensing true-up purposes.

No sorry, it lost focus at the office, ended up doing cut and paste from the webui of the search results :(

I have some MySQL queries that might help.

Installed applications and versions only:

SELECT DISTINCT application_name AS 'Application Name', application_version AS 'Version'
FROM applications;

Applications, versions, and each computer they are installed on. If you have a lot of computers this query will return a significant amount of data (270,050 in our environment of around 2300 computers) so be wary when running it. An Excel pivot table will help manage this data for viewing.

SELECT DISTINCT a.computer_name AS 'Computer Name', a.username AS 'User Name', a.operating_system_version AS 'Operating System Version ', c.application_name AS 'Application Name', c.application_version AS 'Version'
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 '%SPSS%'
ORDER BY c.application_version, a.computer_name;

I commented out a line that allows you to specify a single app application or name pattern to look for (in this example SPSS).

Thanks powellbc, your queries gave me a good stating place. For mobile devices you can use:

SELECT DISTINCT a.display_name AS 'iPad Name', a.username AS 'User Name', a.realname AS 'Real Name', a.room AS 'Room', c.application_name AS 'Application Name'
From mobile_devices_denormalized a INNER JOIN reports b ON a.mobile_device_id = b.mobile_device_id
INNER JOIN mobile_device_installed_applications c ON b.report_id = c.report_id
ORDER BY a.room, a.realname, c.application_name;

I have removed the version number because it seemed to report every version that was ever installed.

If you have extension attributes in your environments then they are likely important so to included them too
SELECT DISTINCT a.display_name AS 'iPad Name', a.username AS 'User Name', a.realname AS 'Real Name', d.value_on_client AS 'Room', c.application_name AS 'Application Name'
FROM mobile_devices_denormalized a INNER JOIN reports b ON a.mobile_device_id = b.mobile_device_id
INNER JOIN mobile_device_installed_applications c ON b.report_id = c.report_id
INNER JOIN mobile_device_extension_attribute_values d ON b.report_id = d.report_id
WHERE d.mobile_device_extension_attribute_id LIKE '3'
ORDER BY d.value_on_client, a.realname, c.application_name;

Regards
Graeme

umm....
If you go to th eJSS summary, You can choose this???
or am I missing something???

Then you can export to Excel..???

Yes @kerouak that works but it doesn't give you counts for licensing count purposes that can be exported out.

@kerouak Doesn't that just show Mac App Store apps? That is what I am seeing when I run it.

Our teaching staff wanted a way to audit the 1:1 iPads without having to go to each device. I use the query in a script to create a separate csv file for each classroom/homegroup and place them in a share for teacher access. I run the script each night so that on the (often adhoc) occasions that staff want it there is an up to date list available.

Regards
Graeme

EDIT
I found that the date of the report the data was taken from may not be the latest. To fix that I have changed to using the following query which skips the database table called "reports" and my extension attribute id is "3":

SELECT DISTINCT a.display_name, a.username, a.realname, d.value_on_client, c.application_name FROM mobile_devices_denormalized a

INNER JOIN mobile_device_installed_applications c ON a.last_report_id = c.report_id

INNER JOIN mobile_device_extension_attribute_values d ON a.last_report_id = d.report_id

WHERE d.mobile_device_extension_attribute_id LIKE '3'

ORDER BY d.value_on_client, a.display_name, a.realname, c.application_name;")

@powellbc Can you tailor the MySQL query to only search a static group of computers? Eg - all installed apps for Static Group X. My MySQL skills aren't great, and I'm not sure where to insert the group stuff. Here's where I am stuck so far:

SELECT DISTINCT a.computer_name AS 'Computer Name', c.application_name AS 'Application Name', c.application_version AS 'Version', a.username AS 'Username'
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 '%%'
INNER JOIN computer_group_members d ON a.computer_id = d.computer_id
WHERE d.computer_group_id LIKE '529'
INNER JOIN computer_groups d ON = c.report_id
ORDER BY c.application_version, a.computer_name

Thanks,

-tep

@tep Sure, try this. The WHERE clause includes a group name field you can customize with any smart or static group:

SELECT DISTINCT a.computer_name AS 'Computer Name', c.application_name AS 'Application Name', c.application_version AS 'Version', a.username AS 'User Name'
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
INNER JOIN computers d ON a.computer_id = d.computer_id
INNER JOIN computer_group_memberships e ON d.computer_id = e.computer_id
INNER JOIN computer_groups f ON e.computer_group_id = f.computer_group_id
WHERE c.application_name LIKE '%' AND f.computer_group_name LIKE "<Group Name>"
ORDER BY c.application_version, a.computer_name;

@powellbc That worked perfectly! Thanks so much!

@powellbc Thanks, Bryan … great starting-off point.

I elected to export my query to a .CSV via INTO OUTFILE and also used LIMIT while I was testing:

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/tmp/jss_apps.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 c.application_version, a.computer_name
LIMIT 20;

Would the above work to get a list of what apps users have installed from the web to their ipads? and if so how would i go about running the script? where does it go and how t orun it?

Thanks.

This query is only for macOS clients. IT is run directly against your Jamf Pro MySQL database. You may want to look into using the API instead, as that is the "Jamf way" to do it.

Thanks.

Here is our SQL-Statement for the current Jamf Pro Version (10.12), because the one above refers to the old database scheme:

SELECT DISTINCT
    a.computer_name AS 'Computer Name',
    a.username AS 'User Name',
    a.operating_system_version AS 'Operating System Version ',
    c.name AS 'Application Name',
    c.version AS 'Version'
FROM
    computers_denormalized a
    INNER JOIN reports b ON a.computer_id = b.computer_id
    INNER JOIN applications d ON b.report_id = d.report_id
    INNER JOIN application_details c ON c.id = d.application_details_id
WHERE
    c.name LIKE '%APP_NAME%' AND
    c.version LIKE 'NUMBER'
ORDER BY
    a.computer_name;

I use a Report under the Search Inventory Category for that. There you can pick several reports for different inventory items, eg. devices, Applications etc.