- ZAPPINSTALL: In AppleConfigurator.storedata keeps track of applications that have been deployed via Configurator. Each application has a ZAPPLICATION column, which is a numeric identifier for each application. The ZCODE is a human readable column that shows what kind of application was deployed and the ZDEVICERECORD indicates which device that Apple Configurator placed the application on. This table does not appear to be used in Users.storedata.
- ZMOBILEAPPLICATIONICON: In the AppleConfigurator.storedata, the Z_PK column in this table should match the ZDEVICERECORD column in ZAPPINSTALL. Other than that, thus far the columns appear to be the same for each row. This table does not appear to be used in the Users.storedata.
- ZDEVICEPROPERTIES: Not used in Users.storedata, the table in AppleConfigurator.storedata is used to track information about devices under supervision. The Z_PK column does not match the Z_PK column in the ZMOBILEAPPLICATIONICON but the ZECID, ZDISKCAPACITY, ZENABLEWIFICONNECTIONS, ZBLUETOOTHADDRESS, ZBUILDVERSION, ZDEVICECLASS, ZNAME, ZPRODUCTVERSION, ZSERIALNUMBER, ZTYPE and ZUDID match what their names imply if you de-prefix the Z from the column names.
- ZPROFILE – Each profile created is tracked in this table. Given that a profile is just a property list, the ZDATA column consists of text data that we’ll look at a little more in a bit. The ZPAYLOADUUID column is a unique identifier for the payload, which appears in the payload as well. The ZNAME column tracks the user-enterable name for each profile you create.
- Z_19MEMBERS: Not used in AppleConfigurator.storedata, this table is used to track manual group membership in the Users.storedata database.
- Z_15SUPERVISIONRECORDS: Table used to track supervision with manually created groups.
- ZDEVICERECORD: Each iOS device that has been docked into Apple Configurator is tracked using this table. Here, the name of the iOS device is trapped in the ZNAME column, the serial number in the ZSERIALNUMBER column and the UDID of the device in the ZUDID column.
- ZSUPERVISIONRECORD: AppleConfigurator.storedata uses this table to attach UUIDs for users (in the ZPOLICYUSERUUID column and ZUSERUUID columns) to devices (ZUDID and ZDEVICESERIALNUMBER columns)
- Z_METADATA: Used in both .storedata databases, appears to link a UUID to a property list.
- ZIPSWPACKAGE: Doesn’t appear to be used. Based on the column headers, I think this table is supposed to link the IPSW path to the build version and device types. This seems to be getting derived from elsewhere though (e.g. the metadata directly on IPSWs). Given that you can drop an IPSW into the directory and it will just use it I, I think that Configurator is just getting the data directly from the bundleID and skipping this altogether, which given the relatively small number of IPSWs that people will use doesn’t seem to be very costly in terms of runtime/efficiency… For me the jury is still out on this as I haven’t made it do anything yet.
- ZSUPERVISIONRECORDGROUP: Stores information about manually created device groups (as opposed to user groups). The ZNAME column is where the name you enter for the group is kept, so you can direct queries here to see the rest of the contents for each row. Used by AppleConfigurator.storedata, not Users.storedata.
- Z_PRIMARYKEY: The Z_NAME row in this table is the same in both Users.storedata and AppleConfigurator.storedata as is the Z_SUPER column. However, the Z_MAX is different, as it indicates the highest number of entries (in terms of IDs) in the column for the corresponding object (e.g. the highest Z_PK entry in the ZMOBILEAPPLICATION table).
- ZMANAGEDFILEOBJECT: The users.storedata table uses this directory to track data that has been assigned to each user. The ZURLSTRING column indicates the path to the file, the ZAPPLICATION IDENTIFIER indicates the application the file is bound for in the iOS filesystem and the ZNAME1 column indicates the name of the object. This table is also used in the AppleConfigurator.storedata database, but there it simply lists the instance of each application, the generated ID for the application and the location on the filesystem.
- ZUSER: Show’s the UUID of the user in the ZUUID column, which matches with the ZUSERUUID in column in the ZSUPERVISIONRECORD table, (assigned in Configurator), whether there is an image for the user from the directory service (ZIMAGEISFROMDIRECTORY column, boolean), the hash of the image if there is one (ZOPENDIRECTORYIMAGEHASH column), the device the user has checked out (ZDEVICESERIALNUMBER), the last device the user checked out (ZLASTDEVICESERIALNUMBER), the Open Directory GUID (ZOPENDIRECTORYGUID) for the user, the short name (ZOPENDIRECTORYSHORTNAME) and the directory domain (ZOPENDIRECTORYNODEPATH). This data is, as you can imagine, in Users.storedata and the AppleConfigurator.storedata for this table is empty.
- ZMOBILEAPPLICATION: Empty for Users.storedata, but keeps all the fun info on apps for AppleConfigurator.storedata. It keeps the name the app should have (ZNAME), the version of the app (ZVERSION), the identifier for the app (ZIDENTIFIER), the genre (ZGENRE), the author (ZAUTHOR) the price paid for the app (ZORIGINALPURCHASEPRICE), the app image (the ZMOBILEAPPLICATIONICON Z_PK column matches the ZDATA and ZIMAGES columns here), whether you can push content to the app (using the ZSUPPORTFILESHARING column), whether the app is an enterprise app (ZISENTERPRISEAPP). The app has told Apple Configurator all of this information (notice when you add an app you have to have an internet connection) and so if you try and change it the app won’t deploy.
- ZUSERGROUP: Empty for AppleConfigurator.storedata, lists the name (in the ZNAME column) of groups, the Open Directory GUID if it’s an Open Directory group (ZOPENDIRECTORYGUID). You could do an export from dscl and then import it here if you wanted to pop a whole lot of groups into Apple Configurator.
Note: Again, these are my initial ideas of what these are. I’ll change them if I discover they’re something different or link to official documentation of the table map if it’s released.Now that we know roughly what’s where, let’s put that to use. Let’s start with just getting some information on what profiles are installed in Apple Configurator. Here, we can run a SQL select to look at all the rows in the ZPROFILE table, by calling the sqlite3 command (in /usr/bin), using the SQL table as our first position and then putting our select query in quotes, which in this case is to select all (*) information from the ZPROFILE table:
sqlite3 ~/Library/Application Support/com.apple.configurator/AppleConfigurator.storedata 'SELECT * FROM ZPROFILE'To export a profile from Apple Configurator’s database using sqlite3 (which means you can do this from a centralized location), use the sqlite3 command, selecting the database and then run a select for the ZPROFILE table, selecting the ZNAME record called Pretendco Deployment and then send the output to a file called test.mobileconfig:
sqlite3 ~/Library/Application Support/com.apple.configurator/AppleConfigurator.storedata 'SELECT * FROM ZPROFILE WHERE ZNAME = "Pretendco Deployment"' > test.mobileconfigStrip the first line of that file out and you’ve got yourself a mobileconfig file. To see all of your applications:
sqlite3 ~/Library/Application Support/com.apple.configurator/AppleConfigurator.storedata 'SELECT * FROM ZMOBILEAPPLICATION'Or if that’s too much output (it is), then just look at the names of the apps:
sqlite3 ~/Library/Application Support/com.apple.configurator/AppleConfigurator.storedata 'SELECT ZNAME FROM ZMOBILEAPPLICATION'These are just a few light queries. You could easily expand on this to export all of the profiles as mobileconfigs, dump a list of each iOS device that has each app installed, a list of which users have which devices, which users have which apps, which devices are running low on disk space, etc. Overall, the sqlite queries are similar in nature (or can be) to what we were doing in the scripting iPhone Configuration Utility article I did awhile back; however, there are a lot more options here.