KnowledgeC.db is a common database that until relatively recently hadn't been utilized by common mobile forensic tools. Although tools are now starting to include some of the data from this database, I wasn’t entirely happy about how they did so and decided to address it.

Although its existence isn’t a secret (and I’ve no doubt that it has been examined and written about numerous times before) I thought it was worthy of a closer look to try to understand exactly how the data within it should be interpreted. Pretty much all information contained here is the result of my own research and testing.

KnowledgeC is a relatively nice, decently sized subject to discuss for my first post and it ties in beautifully with the ArtEx software I’ve been working on lately. Note however that ArtEx is not limited to KnowledgeC and incorporates data found elsewhere on an phone extraction. These additional databases/files may or may not be covered by this blog post.

So what is KnowledgeC.db?

KnowledgeC.db is a SQLite file on recent iOS versions that tracks lots of different activity on the device ranging from Battery Level and Bluetooth connections to which speaker is in use and what it is playing at any given time. Because of the vast amount of data stored, the database is only stored for a couple of months before it appears to purge records on a first in/first out basis. It does appear that some records are kept for longer depending on the type of data being stored.
The database is located at /private/var/mobile/Library/CoreDuet/Knowledge/knowledgeC.db and is made up of 12 tables. I will only focus on the most important ones for this post.

ZOBJECT

ZOBJECT is the main table within the database and contains the most records on all of the devices I have checked. Typically 50,000 or so depending on usage.
It also has a high number of columns (over 40 of them) which is why I will again only be picking on the most important columns.
Let’s start with one of the most descriptive fields; ZSTREAMNAME.
When viewing ZSTREAMNAME it quickly gives you an idea of what kind of data the record is regarding. For example, values such as “/device/batteryPercentage”, “/display/isBacklit” or “/device/isPluggedIn” are descriptive enough to take an educated guess at.
Here, I will try to break down some of the ZSTREAMNAME record types some more and show you how the data can be important.

Note that all records have a ZSTARTDATE, ZENDDATE and ZCREATIONDATE that are all Apple Mac Absolute Time (number of seconds since Jan 1st 2001) and are shown in UTC.
In many cases, the record creation is recording a change of state, from plugged in to unplugged for example. In these cases, the ZENDDATE of the plugged in state will be the same as the ZSTARTDATE for the unplugged state.

MetaData

As alluded to in the table above, many records, including the ‘media/nowPlaying’ records have a '1' in the ZHASSTRUCTUREDMETADATA field.
If this is the case, then the important number you need to know is within the ZSTRUCTUREDMETADATA column. This value is a foreign key for table ZSTRUCTUREDMETADATA.

The 'One -to-Many' relationship between the ZOBJECT and ZSTRUCTUREDMETADATA tables.

The ZSTRUCTUREDMETADATA table is a huge table with many columns; most of which aren't in use on any given record.
This is because Apple have made the odd decision (in my opinion) of dedicating a column for every possible bit of meta data that could be saved instead of having just a few multi-use columns.
For example, there are columns in the table related to the currently playing song title, artist, album etc. which is fine if the meta data relates to music. But the same columns exists for every other, non music related record too.

There are way too many columns in this table to discuss in detail; but there are some interesting points I would like to make.

A record is created under the ZSTREAMNAME audio/outputRoute for any sound being emitted by the device such as music, videos, calls, alarm etc.
Information related to this record-type can include which speaker was being used (internal, ear piece, bluetooth etc.)

For Bluetooth output, the MAC address and device name is recorded along with the Audio Route Protocol which may be either "BluetoothHFP (Hand-Free Protocol)" used for voice calls or “BluetoothA2DPOutput” (Advanced Audio Distribution Profile) which is reserved for playing and controlling music via Bluetooth. (https://en.wikipedia.org/wiki/List_of_Bluetooth_profiles).

Intent

A super interesting column I was recently drawn to (Thanks Mike and Jeremy!) is the Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION column of he ZSTRUCTUREDMETADATA table.

For the purpose of this blog, I searched for all records where Z_DKINTENTMETADATAKEY__INTENTVERB was 'SendMessage', but there are other options too such as 'StartAudioCall', 'Show', 'Search' and 'CreateNote' to name a few.

SELECT Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION FROM ZSTRUCTUREDMETADATA WHERE Z_DKINTENTMETADATAKEY__INTENTVERB LIKE 'SendMessage'

This column contains a BLOB of data that may not be visible in your SQL viewer of choice. The BLOB itself is a binary PList* which can be copied out and saved as a standalone plist file.

*Binary PLists are covered in my next blog so I won't go into detail here. You just need to know that PLists are basically lists of values used by Apple devices.

Opening the PList in most PList viewers will show you that there is another BLOB of data within the PList. (My FREE tool 'Mushy' will parse embedded Plists too but more about that in the next blog post).

That BLOB is actually yet another PList file! Exporting it and opening it again results in one last BLOB of data that is unlike any of the previous BLOBs.

Viewing the BLOB as HEX, you will see lots of easily interpretable data such as message data and recipient/ sender information such as name, phone number email address etc.

Breaking this data down, I found that there was a consistent pattern to the data which is very similar to the structure of SQLite. There are several differences which I won't dwell on. Basically, what you need to know is that the data is basically in a form of [header][length][value].

An easy example is the [12][03] highlighted in red on the image below. From this I can say that an object with Header 12 has a length of 3. In this case, it was actually the full iMessage body.
The 3 byte message is followed in blue by header 42 with a length of 17 which in this case was the value "iMessage;-;+1403******1".

This is all well and good if the key or value is less than 255 characters long (The maximum number that can be displayed in a single byte) but what if the value is longer? VARINT to the rescue!

VARINT (aka Variable Integer) is a way to record a number within a variable number of bytes. As a great and relevant example, think of the message body of an iMessage. It may be as short as a single character such as "k" or as long as... well, as long as you want basically.

So from a file structure point of view, using a single byte to define the length won't work... You could allocate 2 bytes for every length definition (int16 has a max value of 32,767). But what about when the required value is 1 or 0? Or anything less that 254 for that matter. You are consistantly wasting a byte.

The not so obvious answer that Varint provides is to allow you to use 1 OR 2 bytes. This can be seen highlighted in green starting with the value 0A.

You may be thinking that this is a lot of work to get a value of 130 (which after all, is less than 255 and could fit in a single byte) but because we use the first bit to determine if we need another bit or not, we only have 7 bits available to store the number in a single byte, giving us a maximum of 128. Any value higher will require a second byte to be used. Another example would be:

'

This number notation is very similar to SQLite, but how it differs is that in SQLite, the length specified is inclusive of itself as well as the value. So the message "Hey" would be preceeded by a length definition of [4]; one byte for itself and 3 for the message. In this BLOB however, the message "Hey" would be preceeded by only the length of the message [3].

It seems that there are also times where the data is nested 2 or even 3 levels into a value. What I mean can be seen at the very start of the BLOB but can also be observed elsewhere.

The BLOB starts 2A 07 which we believe would mean header [2A] and the next [7] bytes are the value. However the next 7 bytes are 0A 05 12 03 XX XX XX (Where X is taken to be the content of the message). This doesn't make sense as none of those characters can be part of the string as they are too low to be printable in ascii.

However, if you take 0A as another header, then 05 is the length of the value. So we now have 2A as the header and another header with the value 12 03 XX XX XX as its value. I will display this as [2A][0A[12 03 XX XX XX]].

Finally, I did the same again with the header 12 to give me [2A[0A[12[XX XX XX]]].

Working from byte 0 to the end of the BLOB like this parses without errors on every file I've tested it with, but that's not to say it's perfect.

As for what all the data means? It includes the body of a message, the recipient(s) information, the senders information and the type of message (SMS, iMessage, Group Message). The senders information includes Suffix, First Name, Last Name, Display Name, Identifier. There are some fields that I simply don't recognize. Numbers that could be anything. For now, I'm happy to ignore them. We know the most important information about these messages with what we have.

So to reiterate, knowledgeC.db contains a PList which contains a PList which contains SQL data.

I find the inclusion of this information interesting because there is a chance that this still contains communications even when the record has been deleted from the sms.db file. I've yet to find definitive proof that this is the case however and it's possible that it is purged at the same time as sms.db is cleared.

Note that there is more 'intent' data in knowledgeC that just SMS. Call History is there as well for example and is organised in a similar way.

Additional to this, there may also be deleted data with knowledgeC.db that is not parsed by any SQLite viewer that I've tried. The data is there when viewed in a Hex viewer but nothing parses it. This appears to be similar in nature to file slack and I decided to treat it the same.

When the knowledgeC.db file is loaded, I performed a search for a common set of bytes that I had personally observed in every SendMessageIntent record I had seen. The bytes amounted to the string "XXXXXX". Using that offset as a start position, I then seached for a set of bytes I knew existed near the end of the plist : "troot$". I used that as a jumping off point to find a set of 8 nulls which I knew made up part of the plist footer.

At this point, I had a partial plist; with bytes missing at the top.

But the plists in use here are embedded, and so I performed the same byte searches again so that I had:

XXXX
troot
Footer
troot
Footer

I already knew that the header for the parent and child plists were the same on every file I'd seen, with the exception of the child plist length. Luckily, I had already worked this out as part of the process of getting to where I was now. I updated the length of the child plist within the pre-formed header I had and stuck it all together.

<Existing header with updated child length><Carved Child Plist><Carved Parent Plist>

Success! The files could be read just as if they were complete files within the database blobs.

*Check out the Software section for a simple tool for extracting the Messages or the raw Plists, which can then be parsed in Mushy.
*Currently, only messages are parsed using this tool, but others may be added soon.
*Also note that ArtEx can graph out these Intent Messages for easy comparison to existing/deleted messages and notifications*.

Onto the “Friends” part of the blog name

As explained elsewhere on my site, I aim to release a blog article along with every software tool I make available to explain the how's and why's. This article is related to my software tool "ArtEx", available in the software section for authorised users.

ArtEx started life to just display content from KnowledgeC but as time went on, it became apparent how much more useful it would be to include information from other sources, namely lockdownd.log, callhistory.dbstore, AddressBook.sqlitedb, sms.db, history.db, data_ark.plist and SystemVersion.plist.

What follows is a brief explanation of each of the sources. Bear in mind that these things may differ by iOS Version which I have not taken into account in this article.

data_ark.plist

This file is located at /private/var/root/Library/Lockdown/data_ark.plist
It is a Binary PList file which contains the name of the device amongst other things. Currently, the device name is the only piece of data ArtEx extracts from this file.

SystemVersion.plist

This file is located at /System/Library/CoreServices/SystemVersion.plist
It is an XML type Plist file which contains the iOS Version. Currently, the iOS version is the only piece of data ArtEx extracts from this file.

Note that PLIST files are basically a list of settings. In order to be able to read both XML and Binary PList files, I opted to write a parser which will be made available in both DLL and a standalone application in the coming weeks along with a better explanation of bplist files.

lockdownd.log

lockdown.log is located at /private/var/logs/lockdownd.log and is a simple text based log file which records lots of debug style information in a console format.
Importantly, you can find the term "Starting Up" within the file along with the time that the device booted.
However, to complicate matters, the term "Starting up" may also refer to other items too, such as other apps or services. So you need to distinguish the device start up verses any other item start up.
We can do this using the PID value (Process ID) number and by realising that the phone must be booted before anything else can startup.
Therefore, the "Starting Up" item with the lowest PID is assumed to be the device boot up.

The lockdownd.log file also stores other information such as:

callhistory.dbstore

CallHistory.dbstore is a standard SQLite database despite the unique file extension.
It stores all calls made/received indefinitely (until the user deletes it).

There are 5 tables in total but the ZCALLHISTORY table is really the only table to contain useful data.

ZCALLHISTORY is a large flat table without any relationships.

It is notable that the columns ZLOCATION and ZNAME contain data that results from the 'Caller ID' feature available on many cell networks and is not related to any information the user has stored on their device.

The actual phone number used for the call (if available) is in the ZADDRESS field. This may not always be obvious depending on the SQL software you are using.

In order to relate the phone number to a stored contact, you must use the value from the ZADDRESS field and search AddressBook.sqlitedb as described later.

Other useful information in this table includes:

The time stamp is in Apple Mac Time (Seconds since Jan 1 2001).

Keep reading for some useful information regarding deleted records.

sms.db

The relationships in sms.db are diagrammed below. As many of the tables contain a high number of columns, I have only shown the columns which have a relationship for easier viewing.

Keep reading for some useful information regarding deleted records.

AddressBook.sqlitedb

AddressBook.sqlitedb contains a total of 29 tables. The most important relationship is diagrammed below.
Many of the tables aren't covered here as although related, aren't important from a forensics standpoint.

AddressBook.sqlitedb is used by ArtEx in order to pull out contact names when showing Call or SMS data.

History.db

This database contains the Internet History on the device as accessed via Safari Mobile when not using Private Browsing.
These records are included by ArtEx if selected but ArtEx can also include Internet records found in the KnowledgeC database which appears to contain any access to the internet even by apps such as Maps. This also appears to include some records which were accessed in Private Browsing mode in Safari Mobile.

Photos.sqlite

This database contains the all data about the images/videos in the Photo Gallery. I'm not going to go into detail here as I get the feeling that this will be a whole other blog post pretty soon. I wanted to mention it though as it is a truly important database and is utilized by ArtEx.

interactionC.db

This database records almost all interactions between the device and others.
This includes Calls and Messages sent via SMS or iMessage. It doesn't record much actual content such as message body etc but can be useful when dealing with deleted data.

A bit about Deleted Records

Deleted records from these databases have a very short window for recovery before it's lost forever. A vacuum runs once the wal file hits a certain size or seemingly when the device is rebooted. The vacuum deletes the messages completely (I'm not going to go into the intricacies of SQL and WAL files in this post). Deletion of an entire conversation thread may be enough to trigger a vacuum on it's own.

Once a vacuum has occurred, there isn't a lot that's recoverable, but what I have found useful in these cases is to look for the gaps left from deleted messages as it may provide enough information to be helpful.

Firstly, most SQLite databases contain a table which keeps track of the number of records there should be in each table.
For example, the sms.db database has the table 'sqlite_sequence' which lists the running totals:

When we check the appropriate tables we can see that the number shown in the sqlite_sequence matches the last ID number (*Unless it is the last record(s) that has been deleted).

chat	message	attachment

The important thing to note is how all of the records are issued consecutive ID numbers, starting at 1. It will never skip or reuse a number, which means that if a number is missing from the table, it was deleted.

If the number shown in the sqlite_sequence is higher than the ID of the last record in the table, then records have been deleted from the end of the table whereas If number are missing from anywhere else in the table, you will see that the numbers are not consecutive.

You can see in the image above that records 4491 and 4492 are missing.

Because it is a completely missing row, we know absolutely nothing about it at this time. Was it an iMessage or SMS?, was it Sent, Received or Draft?? What time was it??? Who was it with????

Some of these questions can be answered by looking elsewhere in the table/extraction. Let's start with the easy one. Time stamp.

Because the records are created sequentially, we know that the 2 missing records were created AFTER 4490 and that there were created BEFORE 4492 (Or in the case of records being deleted from the end of the table, before the device was extracted). So we can take the timestamps from messages 4490 and 4492 and work out a time period during which the missing records were created.

So we now know that the missing messages were created between 09:30:02 and 09:31:40 on 15th May 2018.

We can then look in the KnowledgeC.db for ZSTREAMNAME '/notification/usage' which records whenever a notification is displayed on screen, including notifications of received messages.

This table doesn't store data very long, so your window to be able to do this is short at around 2 months, but assuming you have the correct time frame, you can search for any notifications during the time the missing records were created. A notification will not only indicate that a message was received as oppose to sent/ draft, but it will also give you a more exact time.
You an also check KnowledgeC for /App/Focus at the time of missing messages and confirm if Messages was being used.

*Note that if there are more missing records than there are notifications you will not be able to work out which messages were sent/received, only that X many were sent and received.

Digging even further, you can explore the InteractionC.db file. Specifically the ZINTERACTIONS table to see if the records you are interested in are present there.
InteractionC.db also contains a ZCONTACTS table which records a count of all interactions with a specific contact and just as importantly, a first and last contact date.

iOS Versions and all of these columns may not be present in all ZCONTACTS tables, but for each contact (referred to as X) there are <some variation of> these columns:

Column Name	Description
IncomingSenderCount	Number of messages received by this device where X was the sender
Recipient	Number of messages received where this device and X were both recipient's (in Group messages situations)
OutgoingRecipientCount	Number of messages where this device was the sender and X was the recipient.
FirstIncomingSender	The date of the first message received by this device where X was the sender.
FirstIncomingRecipient	The date of the first message received by this device where X was also a recipient. (Group message)
FirstOutgoingRecipient	The date of the first message sent from this device where X was a recipient.
LastIncomingSender	The date of the first message received by this device where X was the sender.
LastIncomingRecipient	The date of the last message received by this device where X was also a recipient. (Group message)
LastOutgoingRecipient	The date of the last message sent from this device where X was a recipient.

Recovering information about deleted data probably isn't going to make your case, but it could be an important tool to prove if records were deleted or if they never existed at all. It could be useful to compare against records provided by a cell phone carrier or a secondary device. And it's important to remember that this is not limited to Messages. Almost all SQLite databases function the same way and I have successfully used this method several times for different purposes, including to prove the last photograph taken by a device was deleted.

I have a couple of self-written tools that include a check for deleted records that I will post soon as none of the forensic tools I've seen take advantage of this information.

Wrapping Up

Thank you for reading! Hopefully you have found some of this information useful. I'm still awaiting the opportunity to work on an iOS13 phone to see what the differences are. But rest assured that I shall make a new post once I do and will make updates to ArtEx as required.

Remember, you can download my iOS Usage Visualization tool "ArtEx" and my PList viewer 'Mushy' for FREE from the 'Software' section of my site.