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.name, va.vpp_admin_account_id, vala.name, 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):
vpp_assignment_id,adam_id,name,vpp_admin_account_id,name,used_license_count,license_count
8,361285480,"Teacher Apps",3,Keynote,2590,2700
24,361285480,All-Sites-PowerApps-NoHS,3,Keynote,2590,2700
