knowledgeC.db: macOS App Usage and Device Activity

What it is

knowledgeC.db is a SQLite database written by Apple's CoreDuet framework. It records "pattern of life" events as intervals: an application in use, the display lit, the Mac on power, a web domain visited, and more. Each event belongs to a named stream such as /app/usage and carries a start and end time, which is why it answers duration questions that file system timestamps cannot.

The database is a Core Data store, so tables and columns carry the Z prefix. On recent macOS releases Apple has moved many streams to Biome, so a modern knowledgeC.db can be sparse. Always examine both.

Where it lives

Both databases run in WAL mode. Collect knowledgeC.db-wal and knowledgeC.db-shm with the main file: the most recent events often sit only in the WAL.

What it proves

• Which bundle IDs were in use (/app/usage) or in focus (/app/inFocus) and for how many seconds.
• When the screen was lit (/display/isBacklit) and when the Mac was on AC power (/device/isPluggedIn).
• Web and media usage per app (/app/webUsage, /app/mediaUsage) and Safari history entries (/safari/history) where those streams exist.
• Activity from other devices on the same Apple Account, where synced rows are present (see tips).

It does not prove that a specific person was at the keyboard, and it does not record file names or command lines. An app "in use" can be a window left open in front of an idle screen.

Key fields

Main table ZOBJECT (one row per event):

ZSOURCE describes the producer; APOLLO reads ZSOURCE.ZDEVICEID as the hardware UUID of the originating device. ZSTRUCTUREDMETADATA holds stream-specific extra columns that vary by release.

Streams covered by APOLLO macOS modules include /app/usage, /app/inFocus, /app/activity, /app/intents, /app/webUsage, /app/mediaUsage, /safari/history, /display/isBacklit, /device/isPluggedIn, /device/isLocked and /bluetooth/isConnected. Not every Mac has every stream.

Timestamps

ZSTARTDATE, ZENDDATE and ZCREATIONDATE are Mac absolute time: seconds (often fractional) since 2001-01-01 00:00:00 UTC. Add 978307200 to get Unix seconds. Stored values are UTC; ZSECONDSFROMGMT gives the local offset the device used.

SELECT datetime(ZSTARTDATE + 978307200, 'unixepoch') AS start_utc,
       datetime(ZSTARTDATE + ZSECONDSFROMGMT + 978307200, 'unixepoch') AS start_local
FROM ZOBJECT LIMIT 5;

date -u -r $((694224000 + 978307200))   # macOS date: Mac absolute 694224000 -> UTC

Retention

Sarah Edwards (mac4n6) documented roughly four weeks of events in ZOBJECT. Pruning is per stream and version dependent, so measure it on the evidence: the oldest ZSTARTDATE per stream is the effective retention window and belongs in your report.

Collection

Live, with Full Disk Access granted to Terminal or the collector:

mkdir -p case/knowledgec_user
cp -p ~/Library/Application\ Support/Knowledge/knowledgeC.db* case/knowledgec_user/
shasum -a 256 case/knowledgec_user/*

• UAC has a knowledgec artifact for both paths, but it collects only knowledgeC.db, not the -wal and -shm files; its own notes say the system copy is only collected when SIP is disabled. Do not disable SIP to collect it: prefer a full disk or Data volume image and extract /private/var/db/CoreDuet/Knowledge/ from it.
• mac_apt can export files from an image; open the copy, never the original, because opening a WAL database in place can checkpoint and change it.

Parsing

• APOLLO runs SQL modules (knowledge_app_usage, knowledge_app_inFocus, knowledge_device_is_backlit, etc.) and merges them into a timeline:

python3 apollo.py extract -o csv -p apple -v yolo -k modules/ case/knowledgec_user/

• Plaso includes the mac_knowledgec SQLite plugin (--parsers sqlite/mac_knowledgec).
• Plain SQL, first enumerate streams, then query:

SELECT ZSTREAMNAME, COUNT(*),
       datetime(MIN(ZSTARTDATE)+978307200,'unixepoch') AS first_utc,
       datetime(MAX(ZSTARTDATE)+978307200,'unixepoch') AS last_utc
FROM ZOBJECT GROUP BY ZSTREAMNAME ORDER BY 2 DESC;

SELECT datetime(o.ZSTARTDATE+978307200,'unixepoch') AS start_utc,
       o.ZENDDATE - o.ZSTARTDATE AS seconds,
       o.ZVALUESTRING AS bundle_id,
       s.ZDEVICEID AS device_id
FROM ZOBJECT o LEFT JOIN ZSOURCE s ON o.ZSOURCE = s.Z_PK
WHERE o.ZSTREAMNAME = '/app/usage'
ORDER BY o.ZSTARTDATE;

KnowledgeC Parser reads knowledgeC.db with its -wal applied (flagging rows that exist only in the WAL) and Biome SEGB streams in the browser, with sessions, per-app totals and CSV or Timesketch export; nothing is uploaded.

Investigator tips

• Check .schema ZOBJECT and .schema ZSOURCE before running canned queries; columns drift between releases.
• A sparse or empty /app/inFocus on a recent Mac is expected, not evidence of wiping. Look in Biome (App.InFocus) before drawing conclusions. See knowledgeC and Biome.
• Group events by ZSOURCE.ZDEVICEID: rows synced from an iPhone or another Mac on the same account must not be attributed to the examined machine.
• Pair app intervals with /display/isBacklit and login events in the Unified Logs: an app in focus while the display was off is weak evidence of use.
• Missing -wal files are the most common reason for "no recent activity". Note in your report whether they were collected.
• ZSECONDSFROMGMT changing between events is a useful travel or time zone change indicator.
• Correlate with Screen Time usage totals and with notification history.