Daily Blog #698: Solution Saturday 5/9/20 - Updating a Previous Challenge on KnowledgeC


Hello Reader,
         It was week of returning champs coming to see who could win and this week that was Oleg Skulkin who did some solid work on updating a previous challenge on KnowledgeC. So congrats Oleg another win for the board!

The Challenge:
KnowledgeC on iOS is a jam packed knowledge resource, but on OSX it seems to be less used. 
1. What does each table in the KnowledgeC database correspond to activity wise
2. What data is logged  in each table
3. What data is not logged
4. Is there a similar datasource that would fill in the gaps?

The Winning Answer:
Oleg Skulkin


Know Your KnowledgeC

I’m using macOS devices quite often, for example, to read blogs and general web-surfing, but don’t look at them from a forensic perspective quite often, so Sunday Funday gives me a good opportunity to do it.

KnowledgeC. This is quite known source of forensic artifacts, many forensic tools even extract relevant data from it automatically (e.g. Magnet AXIOM, Plaso also has a parser for it - mac_knowledgec).

Regarding research, Sarah Edwards proved that KnowledgeC is power (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), also we already had a Sunday Funday on this topic, but focusing on macOS Mojave, and Tun Naung (https://twitter.com/tunnaunglin) won it (https://www.hecfblog.com/2019/03/daily-blog-642-solution-saturday-3919.html).

But now we already have macOS Catalina (10.15), so it’s high time to look at the data source again.

In fact, there are two knowledge databases on macOS: system and user context. The first is located under /private/var/db/CoreDuet/Knowledge, the second – under /Users/username/Library/Application Support/Knowledge.

Let’s start from the first one, system context database. It was obtained from a macOS image presented at recent Champlain CTF.
There are 16 tables in the database:

updating a previous challenge on KnowledgeC


System context database tables
Most of the tables are empty. The most interesting things start from ZOBJECT table. ZSTREAMNAME column contains information about the data streams. In the database I’m looking at there are several streams:


  • com.apple.spotlightviewer.events
  • /safari/history
  • /media/nowPlaying
  • /display/isBacklit
  • /app/inFocus
  • /app/activity
  • /activity/level/feedback
  • /activity/level
ZVALUESTRING column contains additional information. For example, for /app/inFocus is shows the application used, for /safari/history – URL. That’s not all, for Safari related activity and /media/nowPlaying it contains additional metadata in ZSTRUCTUREDMETADATA table, corresponding ID can be found in the column with the same name. For example, for Safari history it will store webpage’s title in Z_DKSAFARIHISTORYMETADATAKEY__TITLE column:

updating a previous challenge on KnowledgeC


Of course, we shouldn’t forget about the timestamps: there are three columns in ZOBJECT table: ZSTARTDATE, ZENDDATE and ZCREATIONDATE, all contain timestamps in Mac Absolute Time format.

It’s time for an SQL query!

updating a previous challenge on KnowledgeC




Let’s move on to the user context database. I got this one from our iMac. It’s used very often, so there should be a lot of data in the database.

Tables are the same – we have 16 of them. Let’s look inside ZOBJECT table. Here are the streams available in ZSTREAMNAME:


  • /portrait/topic
  • /portrait/entity
  • /notification/usage
  • /knowledge-sync-deletion-bookmark/
  • /knowledge-sync-addition-window/
  • /display/isBacklit
  • /app/usage
  • /app/intents

First of all, we have some information about database synchronization. It means that it may contain not only information about this iMac, but also synced data, for example, from an iPhone. There’s a table called ZSYNCPEER that includes some information about these devices:

updating a previous challenge on KnowledgeC


There’s another useful table – ZSOURCE. Here we can find was it a WhatsApp message, a phone call or an SMS. Also it can help us to understand some not common data types. For example, we can see that /portrait/topic refers to Pinterest, /portrait/entity – to Safari.

Let’s look inside ZSTRUCTUREDMETADATA, especially at Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION column. Here we can see some BLOBs. Let’s export one of them, it can be done, for example, with DB Browser for SQLite. In fact, it’s a binary plist. But that’s not all, there is another plist inside! It’s inside NS.data. In my case it was a WhatsApp message, and I could get not only the phone number (it’s also available at in Z_DKINTENTMETADATAKEY__DERIVEDINTENTIDENTIFIER), but also contact’s name. The same can be done with phone calls – we can recover the phone number. Unfortunately, we can’t recover the message body.

Again, we can gather a lot of information about the usage of applications from /app/usage stream:
updating a previous challenge on KnowledgeC



Let’s write an SQL query to gather this information:

updating a previous challenge on KnowledgeC


As you can see, the first record is April 4, 2020, today is May 3, 2020, so the database stores data for only 30 days.

So, what other similar data sources are available? For example, another interesting database is located under /private/var/db/CoreDuet/People. It’s interaction.db. There are 11 tables inside:

updating a previous challenge on KnowledgeC


If we look inside ZINTERACTIONS and ZCONTACTS, we can gather some information about calls the user performed. Again, it seems the data is written to the database as part of synchronization process, and, of course, it’ll contain different datasets – it’ll depend on the device.

1 comment :

  1. Thank you! Let me ask you something, macOS Ventura does not allow querying the system context knowledgeC database. Is there another way?

    ReplyDelete