Finding which VPP Assignments contains a specific app

Contributor II

I got tired of going through VPP Assignments just to find out which one contained the specific app to unassign the app so I came up with the following:

use jamfsoftware;
set @admin_id = "3";
select distinct val.vpp_assignment_id, val.adam_id,, va.vpp_admin_account_id,, vala.used_license_count, vala.license_count
from vpp_assignment_licenses val
join vpp_assignment va on va.vpp_assignment_id = val.vpp_assignment_id and va.vpp_admin_account_id = @admin_id
join vpp_mobile_device_app_license_app vala on vala.adam_id = val.adam_id and vala.vpp_admin_account_id = @admin_id
where val.adam_id in (select distinct adam_id from vpp_mobile_device_app_license_app where name like "%Keynote%")
and va.vpp_admin_account_id = @admin_id
order by val.vpp_assignment_id

The @admin_id is 3 because that is what the id for the VPP Account that was having issues is. That can be found with the following MySQL statement:

use jamfsoftware;
select *
from vpp_admin_accounts

val.adam_id is set dynamically for whatever application you want it to be. Just change where "%Keynote%" is to something like "%Pages%". With the "like" statement, if you don't know how the name is stored you can type part of it such as "%ynot%" would get you Keynote and "%age%" would get you Pages, and if in the JSS "Age of Empires".

That will spit out something like this (csv format):

8,361285480,"Teacher Apps",3,Keynote,2590,2700