For a while now I’ve wanted to be able to report on how many iPads have a specific app installed. I want to know which apps are most popular, what staff are installing from the App Store vs Self Service, and which ignored apps can probably be removed our catalog altogether. Jamf Pro has functionality to create an advanced search or smart group to pull this information, but that would require one query per app. Similarly, the API could be used to programmatically create advanced searches, but that would be slow and add a large number of searches to have to sift through later.

The solution I came up with is to query the Jamf Pro database directly. I’m prefacing this with the disclaimer I’m far from a MySQL expert. This method works for me though there’s probably a better way to get it done. The secret sauce is the mobile_device_installed_applications table which is what’s referenced in a mobile device record’s installed app list. Running a test query shows data is stored by report_id instead of device ID. As a result, it’s not possible to ask mobile_device_installed_applications to return a list of every installed app on a specific mobile device or every mobile device with a specific app installed without referencing a report_id.

To make working with this data easier I created a view with the query below to return mobile device ID, app bundle identifier, app name, and management status using mobile device last_report_id table. Note I like to format queries this way to make them more digestible, but you could also compress it down to one line. As of writing this I’m running MySQL 5.7.22 on Ubuntu 16.04.

CREATE VIEW app_count AS
SELECT 
    mobiledevi0_.mobile_device_id as 'mobile_device_id',
    mobiledevi1_.identifier as 'bundle_id',
    mobiledevi1_.application_name as 'app_name',
    mobiledevi1_.management_status as 'management_status'
FROM 
    mobile_devices_denormalized mobiledevi0_
CROSS JOIN 
    mobile_device_installed_applications mobiledevi1_ 
WHERE 
    mobiledevi0_.last_report_id=mobiledevi1_.report_id
GROUP BY
    mobiledevi0_.mobile_device_id,
    mobiledevi1_.identifier,
    mobiledevi1_.application_name,
    mobiledevi1_.management_status;

Now that we’ve got our data set let’s look at an example query…

select * from app_count limit 5;
+------------------+------------------------------+-------------+-------------------+
| mobile_device_id | bundle_id                    | app_name    | management_status |
+------------------+------------------------------+-------------+-------------------+
|                4 | com.amazon.aiv.AIVApp        | Prime Video | Unmanaged         |
|                4 | com.appstreetsoft.ibooth     | Insta Booth | Unmanaged         |
|                4 | com.cardinalblue.PicCollage  | Pic Collage | Unmanaged         |
|                4 | com.evernote.iPhone.Evernote | Evernote    | Unmanaged         |
|                4 | com.gingerlabs.Notability    | Notability  | Managed           |
+------------------+------------------------------+-------------+-------------------+

Great, everything required to get an accurate count of installed apps by name.

select app_name,count(*) from app_count group by app_name order by count(*) desc;
+--------------+----------+
| app_name     | count(*) |
+--------------+----------+
| Self Service |     6249 |
| Drive        |     5265 |
| Book Creator |     5210 |
| NWEA Testing |     5196 |
| TestNav      |     4415 |
+--------------+----------+

A few obvious contenders for top spots. Self Service is set to auto-install, we’re a G Suite district so Drive is a no brainer, and testing season is perpetually in full swing. Let’s take it a step further to find out which apps are managed, and therefore most likely installed by Jamf Pro.

select app_name,count(*) from app_count where management_status="Managed" group by app_name order by count(*) desc;

Or find out how many staff have truly been using their iPad to its full potential by installing a certain entertainment app. Unmanaged in this case referes to apps not installed by MDM, or which at no point an MDM took over management.

select app_name,count(*) from app_count where management_status="Unmanaged" group by app_name order by count(*) desc;
+----------------+----------+
| app_name       | count(*) |
+----------------+----------+
| iTunes U       |     2247 |
| YouTube        |      164 |
| Netflix        |      146 |
| Chrome         |      128 |
| The Calculator |      105 |
+----------------+----------+

Now that summer’s in full swing I’m using this data to inform my choices about which apps are getting the axe as I clean up our catalog. I’m also able to share with our technology integration team which apps their teachers are adopting most widely, and which apps requested over the past few years have been a total bust. Hopefully you can find creative ways to utilize this to improve your own iPad program.