Having access to precise and granular user and application usage can be extremely useful in a forensic investigation, some of which are listed here. I find that pattern-of-life data is some of the most useful information on a device - it really does tell the story about a user and their devices. I've done fairly extensive research on this previously on iOS (much of which can be used for macOS as well) but have yet to really dive into this database.

Application Usage

Application Activities

Safari Browser History

Device Power Status

Lock Status (iOS Only)

Battery Usage (iOS Only)

App Installations (iOS Only)

Audio Status (iOS Only)

We can use some of these records to help answer a myriad of investigative questions or just about any type of investigation.

What applications did a particular user use? How often do they use this application? How long are they using this application for?

What websites did they visit? Are they doing research and putting this information into another application?

Where did they ask for directions? Are they driving distracted?

How often do they check their email? What led them to click on a specific email and infect themselves?

How often are they chatting? Who are they chatting with?

The knowledgeC.db database can be found on macOS and iOS devices. On Mac systems there will be a system context database located in the /private/var/db/CoreDuet/Knowledge directory, while a user context database is located in the user’s ~/Library/Application Support/Knowledge/ directory. *Update 08/7/18: The data in this article is specific to macOS 10.13 and iOS 11. Other versions may contain the same data but the schemas/contents may be slightly different. A note about iOS 10 database is at the end of this article.

On iOS there is only one main knowledgeC.db database located in /private/var/mobile/Library/CoreDuet/Knowledge/ that appears to merge the contents of the user and system context databases found on macOS. (Note: Others may exist for other applications, they are not covered here but follow a similar database schema.) It is worth noting that this database only appears to be available on a physical acquisitions and/or jailbroken iOS devices. I have not seen it in iTunes-style backups.

The database has many tables which have many columns. This article will only go over three of these that I have found to be particularly interesting. I encourage you to look at your own data to discover other items of investigative value. Timestamps in this database use the Mac Epoch time (01/01/2001 00:00:00 UTC).

ZOBJECT – Contains potentially thousands of usage entries for approximately 4 weeks. I will use this table as my primary table of analysis and add on other tables as needed throughout this article. Other tables that ZOBJECT entries may reference are located in these tables:

ZSOURCE – Source of the ZOBJECT entries

ZSTRUCTUREDMETADATA – Additional metadata associated with ZOBJECT entries

Taking a look at ZOBJECT, you’ll see that entries appear to have a “type” associated in the ZSTREAMNAME column. Using the following tiny SQLite query, I can see what “types” of entries I’m dealing with.

SELECT DISTINCT ZOBJECT.ZSTREAMNAME FROM ZOBJECT ORDER BY ZSTREAMNAME

The output of this on the system context knowledgeC.db database on macOS shows the following “types”:

"/activity/level"

"/app/activity"

"/app/inFocus"

"/app/intents"

"/device/isPluggedIn"

"/display/isBacklit"

"/safari/history"

An iOS example shows the following “types”:

"/app/activity"

"/app/inFocus"

"/app/install"

"/app/intents"

"/audio/outputRoute"

"/device/batteryPercentage"

"/device/isLocked"

"/device/isPluggedIn"

"/display/isBacklit"

"/display/orientation"

"/inferred/motion"

"/media/nowPlaying"

"/portrait/entity"

"/safari/history"

"/search/feedback"

"/user/isFirstBacklightOnAfterWakeup"

"/widgets/viewed"

Application Usage

Let’s start with just the “/app/inFocus”. This “type” is available for macOS and iOS and will show us what application is being used at a given time. I will use the following SQL query for this.

SELECT datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "ENTRY CREATION", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "START", datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "END", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) as "USAGE IN SECONDS", ZOBJECT.ZSTREAMNAME, ZOBJECT.ZVALUESTRING FROM ZOBJECT WHERE ZSTREAMNAME IS "/app/inFocus" ORDER BY "START"

This query is only using data from the ZOBJECT table (no JOINS needed yet!) I’ve filtered out only entries that contain the “/app/inFocus” in the ZSTREAMNAME column using a WHERE statement. I sorted the output by the “START” timestamp using the ORDER BY statement.

For all timestamps in these queries, I have to add 978307200 to convert a Mac Epoch to a Unix Epoch value. SQLite has no idea what a Mac Epoch is – this is a handy conversion to use if you are doing any Mac/iOS forensic database analysis, you will see it very often. I’ve converted all timestamps to my local system time for sanity reasons (you can also choose to use UTC here if you are of the ‘UTC or GTFO’ persuasion). In my testing I have found the timestamps to be accurate.

I’ve extracted the following columns: