Are the key_tab table values the same for all iOS devices? In another phone, would ID 1 still stand for compatabilityInfo and ID 13 stand for _UninstallDate data? If every phone has different key_tab relationships there would not be a way to scale this analysis using a universal SQL query for it.I'm am happy to say the community came to my rescue. Among the great multiple responses I got the ones from the awesome Sarah Edwards stand out.
As seen in the screenshot above the ID to key relationship within the key_tab table can, and many times is, different on each iOS device. Also, as seen on the screenshot, her suggestion of identifying the ID by key name instead was totally on point. With that in mind I remade the query as seen here:
https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/INSTALLED-APPS
The fact that the uninstalledapp key is so random that it did not appear in many of the data sets from folks that reached out to me tells me that the query for unistalled apps is not useful at all, hence I nixed it.
As of tonight this is where I stand:
1) The applicationState.db is a good way of getting a current list of installed apps with their corresponding app directory locations and names. It is super useful for the purposes of validating a list of installed apps that a mobile forensic tool tells you. Any app in the applicationState.db list that is not in the installed app list from the tool requires a look at that app directory. Since it is a simple SQL query it is fast and simple to implement.
Sarah did not limit herself to that. She pointed me to another location rich in app installation data.
2) The MobileInstallation logs are fantastic! They have all that and more. Still, to pry a list of current installed apps and directory names out of these logs requires some work. There is no script that automates it. Time permitting I will try and take a crack at it. If the reader hasn't looked at those logs before then they (you!) definitely should.
Christopher Vance chimed in with another set of forensically relevant items.
Great info for future use.
3) Last but not least, Sarah provided a way to track uninstalled apps.
Good stuff all around.
Conclusions
My original requirement was to produce a list of installed apps and their corresponding app directory names and locations in order to identify any apps that would require parsing when a commercial tool misses them. As of today the applicationState.db method from my previous post seems to fulfill that requirement.
If a historical look at installed apps is required the MobileInstallation logs provide a full picture. Sadly until a script is made to parse it getting that information out will require some grepping/lots-of-eyeballing-the-logs to get what is needed.
For network usage Netusage and datausage analysis fills that gap.
UninstalledApplications.plist rounds out the picture as the name of the plist clearly indicates.
I can't thank enough all the folks who took time out of their weekends to respond and share their knowledge. Super grateful for all of you. I'm sure glad to be part of the digital forensic community.
As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.
