Posted on 10-20-2014 01:57 AM
Any one have a good way to get a list of all installed software in my environment?
We would like to generate a report on all the installed software we have on all our managed macs, don't care who has it, just need a list.
I thought the jss summary could do something like that but it only seems to make the licensed softwares.
Solved! Go to Solution.
Posted on 10-20-2014 11:29 AM
I just do a * wildcard search for Applications. Returns them all but unfortunately no way to export from within the JSS webapp.
Posted on 10-20-2014 11:29 AM
I just do a * wildcard search for Applications. Returns them all but unfortunately no way to export from within the JSS webapp.
Posted on 11-12-2014 12:44 AM
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 ;)
Posted on 04-21-2016 11:04 AM
@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.
Posted on 04-22-2016 01:32 AM
No sorry, it lost focus at the office, ended up doing cut and paste from the webui of the search results :(
Posted on 04-22-2016 07:55 AM
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).
Posted on 05-18-2016 02:35 PM
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
Posted on 05-20-2016 01:25 AM
umm....
If you go to th eJSS summary, You can choose this???
or am I missing something???
Posted on 05-20-2016 01:27 AM
Then you can export to Excel..???
Posted on 05-20-2016 04:15 AM
Yes @kerouak that works but it doesn't give you counts for licensing count purposes that can be exported out.
Posted on 05-20-2016 04:58 AM
@kerouak Doesn't that just show Mac App Store apps? That is what I am seeing when I run it.
Posted on 05-20-2016 07:58 PM
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;")
Posted on 02-17-2017 03:12 PM
@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
Posted on 02-20-2017 04:32 AM
@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;
Posted on 02-23-2017 09:39 AM
@powellbc That worked perfectly! Thanks so much!
Posted on 03-01-2017 09:40 PM
@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;
Posted on 05-19-2017 03:03 AM
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.
Posted on 05-19-2017 06:20 AM
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.
Posted on 05-19-2017 06:50 AM
Thanks.
Posted on 05-28-2019 07:58 AM
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;
Posted on 09-04-2019 05:11 AM
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.