The difference in researching using the UI, and exploring the database directly, is that the UI often only presents a small extract of what is stored in the database - in a way thats a little more appealing and useful for the consumer to look at!
For example, app usage trends would show as Siri Suggestions, but these suggestions are derived from the raw data held in these databases.
I would like to firstly give a big thank youuu to @iamevltwin. Her articles and projects are absolutely amazing and definitely inspired me to research some of these areas myself. This article is, in a sense, a non technical version of Sarah’s article.
As this article has been written for non-technical users, I won’t include many technical details although i’ll probably write a follow-up! If you’d like the technical details straight up, please do check out the Mac4N6 blog post -> https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage
Let’s get started
The KnowledgeC Database itself can be found in the following location ony your iOS Device -> /private/var/mobile/Library/CoreDuet/Knowledge/KnowledgeC.db
You can use SCP or SFTP to transfer this file straight from your jailbroken device to your PC for analysis. If you need a hand with that process just leave a comment and i’ll add details to this article!
If you’re not too familiar with Databases and their layouts, imagine it as a massive Excel Spreadsheet, with data sorted in rows and columns, making use of multiple sheets for different types of information.
Tables. Records. JOIN?
So lets start with the ZOBJECT Table. A Table will appear in almost the same format as your average Excel sheet. Although data thats referenced, such as Titles, may be pulled from another Table. It’s just a way of organising the data! In that case we’d have to ‘JOIN’ the tables for the data to be accessible and show correctly for the correct record.
As Sarah mentions in here article, there are a couple of other Tables which can be associated with the records in ZOBJECT:
• ZSOURCE – Source of the ZOBJECT records.
• ZSTRUCTUREDMETADATA – Additional metadata associated with ZOBJECT records.
Metadata is the extra information associated with a file or record. For example, if I take a photograph there will likely be the Location stored in the photograph metadata.
When we are working with databases, instead of just opening the Excel Spreadsheet, we have to ‘execute’ a query where if the query matches the conditions you have presented in your query, those particular records will be returned!
In real life terms, a database query is sort of like going into a candy store and asking “I’d like a sweet that tastes like X, has Y texture but is also organically sourced”. There could be a ‘sweet’ table that would have a unique ID for each type of sweet, the texture and taste. There could be another table holding the sources/recipe for the sweets. Using the unique id of each type of sweet, we can check from this other table to evaluate if the sweet truly is organically sourced!
The sqlite prompt…
So let’s open up the macOS Terminal, type ‘cd Desktop’ if that’s where you’ve saved your KnowledgeC.db!
Now that our working directory is set correctly, type ‘sqlite3 KnowledgeC.db’. You should now see a wonderful, feature-full, and really quite engaging prompt that looks a little like the following:
This essentially signals that we are interfacing with the database and can run/execute Queries.
If you’ve got a good few years to check out all the contents of the db, go ahead and execute the query ‘select *;’.
The art here is condensing the amount of returned data, in order to match our goal / what we’re trying to actually learn about the user. We may not even understand fully what our goal is, and could just be exploring to find new artefacts!
To begin, execute the following query which will return the ‘types’ of records held in the database:
‘SELECT DISTINCT ZOBJECT.ZSTREAMNAME
The ‘StreamName’ represents the type of record here. This is not only useful to start our investigation of KnowledgeC by understanding roughly what we’ll find, but we could also re-run this same query across devices on multiple iOS Versions to understand changes in the data stored over time. It’s worth noting that the ‘type of record’ reference, which in this case is ZSTREAMNAME, could be different in other iOS Databases and isn’t necessarily transferable.
Some examples of the returned ‘types’ you should see as the result of the query are as follows:
We can probably guess from the types what exactly is being referenced in a specific record using just this!
For example, Let’s say we retrieve record with a Bundle Identifier of com.apple.calculator along with a timestamp, and the ‘/app/install’ type. We can pretty easily assume what’s going on using the data from this record!
Using more complex ‘queries’ we can piece together data from different table so the results are much more relevant to the real questions we’re asking, for example “When was X application installed, and how long did the user ACTUALLY use the application for?”.
In fact, Sarah has a great example on her blog of how this will look in a SQL Database. I’ve been a little stuck since updating to the iOS 14 Beta as I can’t pull my own KnowledgeC.db to show you!
Anyway, this article was intended to spark a little interest in KnowledgeC for non technical users and hopefully drive interested users to check it out, and possibly put together some of your own queries!