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.
ORDER BY ZSTREAMNAME
The output of this on the system context knowledgeC.db database on macOS shows the following “types”:
An iOS example shows the following “types”:
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.
datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "ENTRY CREATION",
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",
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:
- ZCREATIONDATE - When the entry was written to the database.
- ZSTARTDAYOFWEEK – What day did this entry occur. I created a CASE statement here to make an analyst friendly output of the days of the week instead of numbers 1-7.
- ZSECONDSFROMGMT – The GMT offset in seconds. I converted this to an GMT offset in hours by dividing by 3600. This is useful if you have a device that has travelled time zones in the last 4 weeks.
- ZSTARTDATE and ZENDDATE – A start and end timestamp for the entry. The following “USAGE IN SECONDS” “column” is not a true database column. I’ve done the math between the START and END dates to quickly see usage times in seconds.
- ZSTREAMNAME – The “type” of entry.
- ZVALUESTRING – The bundle ID for the application.