Skip to main content
Question

Finding which VPP Assignments contains a specific app

  • February 17, 2015
  • 0 replies
  • 0 views

Forum|alt.badge.img+12

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 @admin11_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

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings