knowledgeC.db and Biome: Reconstructing Mac User Activity

Apple's pattern-of-life databases record what a Mac was doing over time: which application was in the foreground and for how long, when the display was on, whether the device was on power, what was browsed in Safari and more. For an investigator they answer questions that file system timestamps cannot: was this user actively using the application at 14:00, how long was a document editor in focus, was the laptop in use while the suspicious process ran. On macOS these data historically lived in knowledgeC.db, maintained by CoreDuet. On recent releases Apple has been moving the same kinds of streams into Biome, a newer storage framework with a different file format. This article covers both, with queries you can run and the caveats that come with them.

knowledgeC.db locations

There are typically two databases on a Mac:

Both are SQLite databases in WAL mode. Always collect the -wal and -shm files with the main database, or you may miss the most recent (and often most relevant) records:

sudo ls -l /private/var/db/CoreDuet/Knowledge/
# knowledgeC.db  knowledgeC.db-shm  knowledgeC.db-wal

On a live system with SIP enabled, the system copy may not be readable even as root (UAC only collects it with SIP disabled, and only the main knowledgeC.db file), so extract it from a full disk or Data volume image rather than disabling SIP. Copy all three together, hash them, and open the copy. Opening the original with sqlite3 can checkpoint the WAL into the main file and change the evidence. See the knowledgeC glossary entry for a short definition, and macOS forensic acquisition for Full Disk Access and collection tooling.

Database structure

knowledgeC.db is a Core Data store, which is why tables and columns carry the Z prefix. The key tables are:

• ZOBJECT: one row per event or interval. The central table.
• ZSOURCE: describes the producer of an event, including a bundle identifier column (ZBUNDLEID) on the versions most commonly examined.
• ZSTRUCTUREDMETADATA: extra key/value data for some streams, linked from ZOBJECT.ZSTRUCTUREDMETADATA. Its columns vary by stream and version.

The ZOBJECT columns you will use most often:

Schemas drift between releases. Before running a prepared query on a new case, check the actual schema:

sqlite3 knowledgeC.db '.schema ZOBJECT'

Streams

The set of streams depends on the macOS version, the hardware and which features are enabled. Streams that are commonly reported on macOS include /app/usage, /app/inFocus, /display/isBacklit, /device/isPluggedIn and /safari/history, but not every system has all of them, and some streams seen on iOS never appear on a Mac. Enumerate what exists instead of assuming:

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

The first and last dates per stream also tell you the retention window on this particular system, which you should state in your report.

Time conversion

ZSTARTDATE, ZENDDATE and ZCREATIONDATE are Mac Absolute Time: seconds, sometimes fractional, since 2001-01-01 00:00:00 UTC. Add 978307200 to get Unix seconds. Forgetting the offset shifts every event about 31 years too early (current data lands in the mid-1990s), which is an easy error to spot; applying it twice is less obvious, so sanity check against a known event.

Useful queries

Application usage intervals

SELECT
  datetime(o.ZSTARTDATE + 978307200, 'unixepoch') AS start_utc,
  datetime(o.ZENDDATE   + 978307200, 'unixepoch') AS end_utc,
  (o.ZENDDATE - o.ZSTARTDATE)                    AS seconds,
  o.ZVALUESTRING                                 AS bundle_id,
  o.ZSECONDSFROMGMT / 3600.0                     AS utc_offset_hours,
  datetime(o.ZCREATIONDATE + 978307200, 'unixepoch') AS written_utc
FROM ZOBJECT o
WHERE o.ZSTREAMNAME = '/app/usage'
ORDER BY o.ZSTARTDATE;

Replace the stream name with /app/inFocus if that is what the enumeration shows. Filter by bundle_id to answer "when was Terminal in use", for example AND o.ZVALUESTRING = 'com.apple.Terminal'.

Display and power state

SELECT
  o.ZSTREAMNAME,
  datetime(o.ZSTARTDATE + 978307200, 'unixepoch') AS start_utc,
  datetime(o.ZENDDATE   + 978307200, 'unixepoch') AS end_utc,
  o.ZVALUEINTEGER AS state
FROM ZOBJECT o
WHERE o.ZSTREAMNAME IN ('/display/isBacklit', '/device/isPluggedIn')
ORDER BY o.ZSTARTDATE;

A value of 1 generally means "on" or "plugged in" for the interval. Screen-on periods are a good baseline for whether anyone could have been looking at the Mac.

Joining source metadata

SELECT
  datetime(o.ZSTARTDATE + 978307200, 'unixepoch') AS start_utc,
  o.ZSTREAMNAME,
  o.ZVALUESTRING,
  s.ZBUNDLEID
FROM ZOBJECT o
LEFT JOIN ZSOURCE s ON o.ZSOURCE = s.Z_PK
ORDER BY o.ZSTARTDATE DESC
LIMIT 100;

If a column does not exist in your version, sqlite3 will say so; check .schema ZSOURCE and adapt.

The move to Biome

Over roughly the macOS 12 Monterey to macOS 13 Ventura releases and later (and the corresponding iOS releases), Apple began moving many streams out of knowledgeC.db into Biome. On a recent Mac you may find knowledgeC.db present but sparse, with application focus data now in Biome. Treat both as parts of one dataset and check both on every case.

Locations and layout

Inside, streams are organised in directories named after the stream, for example under streams/restricted/<StreamName>/local/ on the systems commonly documented. Stream names use a dotted style, such as App.InFocus; other names vary by release, so list the directories rather than relying on a fixed list:

ls ~/Library/Biome/streams/restricted/ 2>/dev/null
sudo ls /private/var/db/biome/streams/restricted/ 2>/dev/null

Access to these directories on a live system requires Full Disk Access for the terminal or collection tool.

SEGB files

Biome stream data is stored in SEGB files, a segmented binary format named after its magic value. Each file holds a header and a series of records, each with its own timestamps and a payload that is usually a protocol buffer message specific to the stream. At least two SEGB layouts have been documented, and the protobuf schemas are not published by Apple, so field meanings come from community research and should be validated against test data from the same macOS version. Timestamps inside SEGB records are generally Mac Absolute Time as well, but confirm per stream.

Tools and libraries that parse SEGB include ccl_segb from CCL Solutions and the Biome parsers in the iLEAPP project (built for iOS, but the format is shared). Expect to do some manual validation for macOS-specific streams.

APOLLO

APOLLO (Apple Pattern of Life Lazy Output'er) from mac4n6 by Sarah Edwards runs a library of SQL modules against pattern-of-life databases, including knowledgeC.db, and merges the results into a single timeline. Its modules are plain SQL with metadata, which makes them a good reference for column names and stream semantics on specific versions. Coverage of knowledgeC is strong; for Biome you will usually need an additional parser. Review which modules match your macOS version before relying on the output.

KnowledgeC Parser covers both sources in the browser: it reads knowledgeC.db with its -wal applied and decodes Biome SEGB v1 and v2 streams, without uploading them.

Caveats

• Retention is short. Activity streams are pruned; expect weeks rather than months. Collect early and record the first event per stream.
• WAL handling. Missing -wal files is the most common reason for "no recent data". Never open the original in place.
• Foreground is not presence. An app in focus while the screen was on does not prove a specific person was using it. Corroborate with login data, Unified Logs and other user activity sources such as Spotlight and Safari history.
• Time zone. Stored times are UTC. ZSECONDSFROMGMT gives the offset the device was using, which helps when a laptop travelled.
• Version drift. Stream names, columns and the split between knowledgeC and Biome change between releases. Enumerate, then query.
• Synced data. Some streams can include events from other devices signed into the same Apple Account. Where a device identifier is available in the source or metadata, check it before attributing an event to the examined Mac.
• Privacy protection. Both stores sit behind TCC protection on a live system. A collection without Full Disk Access will silently miss them.

Frequently asked questions

Why is knowledgeC.db almost empty on a recent Mac?

On recent macOS releases Apple has moved many activity streams, including app focus data, from knowledgeC.db to Biome SEGB files. Check ~/Library/Biome and /private/var/db/biome before concluding that no activity data exists.

How do I convert ZSTARTDATE to a readable date?

ZSTARTDATE, ZENDDATE and ZCREATIONDATE are Mac Absolute Time, seconds since 2001-01-01 UTC. Add 978307200 to get Unix time, for example datetime(ZSTARTDATE + 978307200, 'unixepoch') in SQLite.

Does knowledgeC or Biome prove the user was at the keyboard?

Not on its own. It shows that an application was in the foreground or that the display was on for a period. Combine it with login records, unified logs, input-related streams and physical context before attributing activity to a person.