Help finding a field in JSS DB

[lehmanp00]

Any idea what field in the JSS dB this is? We are looking to generate a report based users who have this set to False or Pending.

[sdagley]

@lehmanp00 I can't answer what table in the JSS you'll find that in, but I can point you to an app a Jamf trainer recommended for investigating the database: Sequel Pro (no affiliation, just a happy user)

[chris_kemp]

Perhaps you can find this through the API? We don't have VPP set up here, so I can't give a specific example but there are several ways to access either device data or vpp specifics through the API, which may be easier than sifting through the mysql tables.

[tthurman]

There are two tables you will likely need.
mobile_devices and mobile_device_details

I think the field you're looking for is: itunes_store_account_is_active Should show up as a 1 or a 0.

You will probably need to pull in the VPP tables as well. But I don't use VPP. :(

This is mostly guessing - but it's worth looking at.

Regards,
TJ

[lehmanp00]

@tthurman was mostly right.

We found the itunes_store_account_is_active field in 2 tables:
mobile_device_details
vpp_user_accounts

What we found was that the itunes_store_account_is_active hash (which is all you see in the tables) was different between those 2 tables. If it was different than the Users's we looked at had False. If they were the same than they had "True".

That actually opened up a whole other can o' worms!

The user that we were looking at this for, WAS invited to that VPP account and was seeing the correct apps in Self-Service. However, she was still set to 'False' Why??

We are a K-12 and last year each user created their own Apple ID with their District email address. This year, we moved to Apple School Manager (ASM) and Managed Apple IDs. In ASM the Managed Apple ID is username@district.us. Their email address field in ASM is username@temp.domain. This was because Apple already had an Apple ID for their correct email address.

So now we integrate ASM with JSS. JSS will only look at the username part of an email address for the user ID. So everything after the @ is dropped. So the JSS sees 2 accounts, 1 pre-existing in JSS from last year and 1 from ASM. It will attach the ASM account to the JSS account. What we think is happening, is the JSS sees last years account as 'True' and sends out the apps. BUT the user is logged into the iPad with this years' Managed Apple ID so the 2 fields mismatch and the JSS displays 'False'.

All we had to do was re-do the VPP invite, per user, and the setting was reset to 'True'.

[cdenesha]

@lehmanp00 You probably have figured this out, but I'll answer the Why?

Your user saw the correct apps in Self Service because they had accepted the VPP for it. Once accepted, they'll still see the apps in Self Service no matter what iTunes account they sign in with, or even if they do not sign into iTunes. The apps are in scope for the user assigned to the iPad.

Since they now have the new Managed Apple ID (MID) they have logged into iTunes on the iPad with it. But it is different than the Apple ID that accepted the VPP Invitation last year, so the setting displays False. It is a simple compare of the hashes of 'accepted' vs 'signed in with'.

Your solution was the correct one to get the setting to display as True. Since you are using Self Service you probably didn't need to go through the process - it really matters when users are trying to access their User assigned VPP apps in the App Store.