mindspace

Appearance

Analiza knowledgeC.db w macOS ​

Narzędzia ​

  • mac-apt (więcej plików niz tylko knowledgeC.db)
  • timeliner (https://github.com/mholt/timeliner)
    • calendars (priority: 5)
    • icloud - co konkretnie?
    • iPhone (priority: 5)
      • messages (priority: 0)

Userful resources ​

Wprowadzenie ​

KnowledgeC.db to baza danych używana przez system macOS do przechowywania informacji o aktywności użytkownika, użyciu aplikacji, oraz innych danych związanych z zachowaniem systemu. Ta baza danych jest częścią mechanizmu, który wspiera funkcje takie jak Siri Suggestions, Screen Time i inne elementy inteligentnego zachowania systemu.

Lokalizacja bazy danych ​

KnowledgeC.db znajduje się w:

~/Library/Application Support/Knowledge/knowledgeC.db

Dostęp do tej bazy danych wymaga uprawnień administratora.

Schemat bazy danych ​

Główne tabele ​

  • Z_4EVENT, columns:

    • Z_4CUSTOMMETADATA
    • Z_11EVENT

    puste w knowledgeC.db firmowy macbook

  • Z_METADATA, columns:

    • Z_VERSION = 1,
    • Z_UUID,
    • Z_PLIST (jakiś hash)

    jeden wiersz

  • Z_MODELCACHE

    • Z_CONTENT (zahashowane tak jak Z_PLIST w Z_METADATA)

    jeden wiersz

  • Z_PRIMARYKEY

    • Z_ENT - prawdopodobnie numer encji, od 1 do 16
    • Z_NAME - nazwa encji / tabeli(?) - nazwy pokrywają się z nazwami tabel w knowledgeC.db
    • Z_SUPER - wartości 0 albo 9
    • Z_MAX - rózne wartości od 0 do 18k, count wierszy?

    16 wierszy, każdy dla innej encji

  • ZADDITIONCHANGESET

    • Z_PK
    • Z_ENT
    • Z_OPT
    • ZSEQUENCENUMBER
    • ZVERSION
    • ZENDDATE
    • ZSTARTDATE
    • ZCKFOREIGNKEY
    • ZCKRECORDID
    • ZDEVICEIDENTIFIER
    • ZCHANGESET (UUID)
    • ZCKRECORDSYSTEMFIELDS

    pusta tabela w knowledgeC.db firmowy macbook

  • ZCONTEXTUALCHANGEREGISTRATION

    pusta tabela w knowledgeC.db firmowy macbook

  • ZCONTEXTUALKEYPATH

    pusta tabela w knowledgeC.db firmowy macbook

  • ZCUSTOMMETADATA

    pusta tabela w knowledgeC.db firmowy macbook

  • ZDELETIONCHANGESET

    pusta tabela w knowledgeC.db firmowy macbook

  • ZHISTOGRAM

    • Z_PK (1, 2, 3)
    • Z_ENT = 6
    • Z_OPT = 1
    • ZSTREAMTYPECODE = 5907650307545087287
    • ZENDDATE
    • ZSTARTDATE
    • ZCUSTOMIDENTIFIER = _DKDeviceActivityStandingQuery-7-15
    • ZDEVICEIDENTIFIER
    • ZIDENTIFIER
    • ZSTREAMNAME

    3 wiersze, kazdy inny ZIDENTIFIER, ten sam ZSTREAMNAME "/activity/level"

  • ZHISTOGRAMVALUE

    • Z_PK (1 - 50)
    • Z_ENT = 7
    • Z_OPT = 1
    • ZINTEGERVALUE
    • ZHISTOGRAM (1, 2, 3)
    • ZCOUNT (floating number)
    • ZSTRINGVALUE (null)

    50 wierszy, każdy inny Z_PK, ZHISTOGRAM to 1, 2 lub 3

  • ZKEYVALUE

    • Z_PK (1 - 4)
    • Z_ENT = 8
    • Z_OPT = (1, 22, 70)
    • ZDOMAIN
    • ZKEY
    • ZVALUE (zahashowane dane binarne)

  • ZSYNCPEER

    pusta tabela w knowledgeC.db firmowy macbook

  • ZOBJECT - Główna tabela zawierająca informacje o aktywności użytkownika

    • Z_PK
    • Z_ENT = 11
    • Z_OPT = (1, 2)
    • ZUUIDHASH
    • ZEVENT = null
    • ZSOURCE (null, 1, 2, 3)
    • ZCATEGORYTYPE = null
    • ZINTEGERVALUE = null
    • ZCOMPATIBILITYVERSION = 0
    • ZENDDAYOFWEEK = 1-7
    • ZENDSECONDOFDAY
    • ZHASCUSTOMMETADATA = 0
    • ZHASSTRUCTUREDMETADATA = (0, 1)
    • ZSECONDSFROMGMT
    • ZSHOULDSYNC = 0
    • ZSTARTDAYOFWEEK = 1-7
    • ZSTARTSECONDOFDAY
    • ZVALUECLASS = (1, 2)
    • ZVALUEINTEGER
    • ZVALUETYPECODE
    • ZSTRUCTUREDMETADATA
    • ZVALUE = null
    • Z9_VALUE = null
    • ZIDENTIFIERTYPE = null
    • ZQUANTITYTYPE = null
    • ZCREATIONDATE (np. 769108440.012471)
    • ZLOCALCREATIONDATE (np. 769108440.012471)
    • ZCONFIDENCE = 1
    • ZSTARTDATE (np. 769108413)
    • ZENDDATE (np. 769108413)
    • ZVALUEDOUBLE
    • ZUUID - jakieś UUID, np. 1B2D3E4F-5A6B-7C8D-9E0F-1A2B3C4D5E6F
    • ZSTREAMNAME = "app_usage" (lub inne, np. "/activity/level")
    • ZVALUESTRING - jakieś UUID, np. 1B2D3E4F-5A6B-7C8D-9E0F-1A2B3C4D5E6F
    • ZSTRING = null
    • ZMETADATA (UUID) = null

  • ZSTRUCTUREDMETADATA - Metadane związane z aktywnościami

    • Z_PK
    • Z_ENT
    • Z_OPT
    • Z_DKBLUETOOTHMETADATAKEY_BATTERYLEVELHEADPHONELEFT
    • Z_DKBLUETOOTHMETADATAKEY_BATTERYLEVELHEADPHONERIGHT
    • Z_DKBLUETOOTHMETADATAKEY__DEVICETYPE
    • Z_DKBLUETOOTHMETADATAKEY__ISAPPLEAUDIODEVICE
    • Z_DKBLUETOOTHMETADATAKEY__ISUSERWEARING
    • Z_DKBLUETOOTHMETADATAKEY__PRODUCTID
    • Z_DKDIGITALHEALTHMETADATAKEY__ISUSAGETRUSTED
    • Z_DKDIGITALHEALTHMETADATAKEY__USAGETYPE
    • Z_DKBLUETOOTHMETADATAKEY__ADDRESS
    • Z_DKBLUETOOTHMETADATAKEY__NAME
    • Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN
    • Z_DKNOTIFICATIONUSAGEMETADATAKEY__BUNDLEID
    • Z_DKNOTIFICATIONUSAGEMETADATAKEY__IDENTIFIER
    • Z_DKTOMBSTONEMETADATAKEY__EVENTSTREAMNAME
    • ZMETADATAHASH
    • Z_DKAPPMEDIAUSAGEMETADATAKEY__URL
    • Z_DKAPPMEDIAUSAGEMETADATAKEY__MEDIAURL
    • Z_DKDIGITALHEALTHMETADATAKEY__WEBPAGEURL

  • ZSOURCE - Źródła danych

    • Z_PK
    • Z_ENT = 14
    • Z_OPT = 1
    • ZUSERID = null
    • ZBUNDLEID = com.apple.assistantd
    • ZDEVICEID = null
    • ZGROUPID = null
    • ZINTENTID = null
    • ZITEMID = null
    • ZSOURCEID = null

Entities ​

IDEntity Name
1AdditionChangeSet
2ContextualChangeRegistration
3ContextualKeyPath
4CustomMetadata
5DeletionChangeSet
6Histogram
7HistogramValue
8KeyValue
9Object
10Category
11Event
12Identifier
13Quantity
14Source
15StructuredMetadata
16SyncPeer

Relacje między obiektami ​

Główne relacje między tabelami:

  • ZOBJECTS zawiera klucze obce do innych tabel, takie jak:
    • ZSOURCE (źródło danych)
    • ZSTRUCTUREDMETADATA (powiązane metadane)
    • ZATTRIBUTION (przypisanie do aplikacji)

Kwerendy SQL do analizy ​

Przykładowe kwerendy SQL do eksploracji bazy danych knowledgeC.db:

sql
-- Wyświetlenie aktywności aplikacji
SELECT
  datetime(ZOBJECT.ZSTARTDATE + 978307200, 'UNIXEPOCH', 'LOCALTIME') as "Start",
  datetime(ZOBJECT.ZENDDATE + 978307200, 'UNIXEPOCH', 'LOCALTIME') as "End",
  ZOBJECT.ZVALUESTRING as "App Name",
  (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) as "Usage in Seconds"
FROM ZOBJECT
ORDER BY "Start" DESC;

-- 1. PODSTAWOWA ANALIZA AKTYWNOŚCI APLIKACJI
-- Wyświetla aktywność aplikacji posortowaną czasowo
SELECT
    datetime(ZOBJECT.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as start_time,
    datetime(ZOBJECT.ZENDDATE + 978307200, 'unixepoch', 'localtime') as end_time,
    date(ZOBJECT.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as activity_date,
    strftime('%H:%M:%S', datetime(ZOBJECT.ZSTARTDATE + 978307200, 'unixepoch', 'localtime')) as start_hour,
    ZOBJECT.ZVALUESTRING as app_identifier,
    ROUND((ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE), 2) as duration_seconds,
    ROUND((ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0, 2) as duration_minutes,
    ZOBJECT.ZSTREAMNAME as stream_type,
    CASE 
        WHEN ZOBJECT.ZSTREAMNAME = '/app/usage' THEN 'Aplikacja'
        WHEN ZOBJECT.ZSTREAMNAME = '/safari/history' THEN 'Strona web'
        WHEN ZOBJECT.ZSTREAMNAME = '/activity/level' THEN 'Aktywność fizyczna'
        ELSE ZOBJECT.ZSTREAMNAME
    END as activity_type
FROM ZOBJECT
WHERE ZOBJECT.ZSTREAMNAME IN ('/app/usage', '/safari/history', '/app/webUsage')
    AND ZOBJECT.ZSTARTDATE IS NOT NULL
    AND ZOBJECT.ZENDDATE IS NOT NULL
    AND date(ZOBJECT.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime') -- dzisiaj
ORDER BY ZOBJECT.ZSTARTDATE DESC;


-- 2. TIMELINE Z METADANYMI (STRONY INTERNETOWE)
-- Łączy z tabelą metadanych aby uzyskać URL-e stron
SELECT
    datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as start_time,
    datetime(obj.ZENDDATE + 978307200, 'unixepoch', 'localtime') as end_time,
    date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as activity_date,
    obj.ZVALUESTRING as app_bundle,
    meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN as web_domain,
    meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBPAGEURL as web_url,
    ROUND((obj.ZENDDATE - obj.ZSTARTDATE) / 60.0, 2) as duration_minutes,
    'Strona internetowa' as activity_type
FROM ZOBJECT obj
LEFT JOIN ZSTRUCTUREDMETADATA meta ON obj.ZSTRUCTUREDMETADATA = meta.Z_PK
WHERE obj.ZSTREAMNAME LIKE '%web%'
    AND obj.ZSTARTDATE IS NOT NULL
    AND obj.ZENDDATE IS NOT NULL
    AND date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime')
ORDER BY obj.ZSTARTDATE DESC;

-- 3. KOMPLETNY DZIENNY TIMELINE (APLIKACJE + STRONY)
-- Łączy wszystkie typy aktywności w jeden timeline
WITH timeline_data AS (
    SELECT
        datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as start_time,
        datetime(obj.ZENDDATE + 978307200, 'unixepoch', 'localtime') as end_time,
        date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as activity_date,
        strftime('%H:%M', datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime')) as start_hour,
        obj.ZSTARTDATE + 978307200 as unix_start,
        obj.ZENDDATE + 978307200 as unix_end,
        CASE 
            WHEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN
            ELSE obj.ZVALUESTRING 
        END as activity_name,
        COALESCE(meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBPAGEURL, '') as web_url,
        ROUND((obj.ZENDDATE - obj.ZSTARTDATE) / 60.0, 2) as duration_minutes,
        CASE 
            WHEN obj.ZSTREAMNAME LIKE '%web%' OR meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN 'Strona internetowa'
            WHEN obj.ZSTREAMNAME = '/app/usage' THEN 'Aplikacja'
            ELSE 'Inne'
        END as activity_type,
        obj.ZSTREAMNAME as stream_name
    FROM ZOBJECT obj
    LEFT JOIN ZSTRUCTUREDMETADATA meta ON obj.ZSTRUCTUREDMETADATA = meta.Z_PK
    WHERE obj.ZSTREAMNAME IN ('/app/usage', '/safari/history', '/app/webUsage', '/app/inFocus')
        AND obj.ZSTARTDATE IS NOT NULL
        AND obj.ZENDDATE IS NOT NULL
        AND (obj.ZENDDATE - obj.ZSTARTDATE) > 5 -- tylko sesje dłuższe niż 5 sekund
        AND date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime')
)
SELECT 
    start_time,
    end_time,
    activity_date,
    start_hour,
    activity_name,
    web_url,
    duration_minutes,
    activity_type,
    stream_name,
    -- Obliczenie przerw między aktywnościami
    LAG(end_time) OVER (ORDER BY unix_start) as previous_end,
    CASE 
        WHEN LAG(unix_end) OVER (ORDER BY unix_start) IS NOT NULL 
        THEN ROUND((unix_start - LAG(unix_end) OVER (ORDER BY unix_start)) / 60.0, 1)
        ELSE 0 
    END as gap_minutes
FROM timeline_data
ORDER BY unix_start;

-- 4. STATYSTYKI DZIENNE - PODSUMOWANIE AKTYWNOŚCI
-- Agreguje czas spędzony w aplikacjach i na stronach
SELECT 
    activity_type,
    COUNT(*) as sessions_count,
    ROUND(SUM(duration_minutes), 2) as total_minutes,
    ROUND(SUM(duration_minutes) / 60.0, 2) as total_hours,
    ROUND(AVG(duration_minutes), 2) as avg_session_minutes,
    MIN(start_time) as first_activity,
    MAX(end_time) as last_activity
FROM (
    SELECT
        datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as start_time,
        datetime(obj.ZENDDATE + 978307200, 'unixepoch', 'localtime') as end_time,
        ROUND((obj.ZENDDATE - obj.ZSTARTDATE) / 60.0, 2) as duration_minutes,
        CASE 
            WHEN obj.ZSTREAMNAME LIKE '%web%' OR meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN 'Strony internetowe'
            WHEN obj.ZSTREAMNAME = '/app/usage' THEN 'Aplikacje'
            ELSE 'Inne'
        END as activity_type
    FROM ZOBJECT obj
    LEFT JOIN ZSTRUCTUREDMETADATA meta ON obj.ZSTRUCTUREDMETADATA = meta.Z_PK
    WHERE obj.ZSTREAMNAME IN ('/app/usage', '/safari/history', '/app/webUsage', '/app/inFocus')
        AND obj.ZSTARTDATE IS NOT NULL
        AND obj.ZENDDATE IS NOT NULL
        AND (obj.ZENDDATE - obj.ZSTARTDATE) > 5
        AND date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime')
) as daily_stats
GROUP BY activity_type
ORDER BY total_minutes DESC;

-- 5. TOP APLIKACJE I STRONY DZISIAJ
-- Pokazuje najczęściej używane aplikacje i strony
SELECT 
    activity_name,
    activity_type,
    COUNT(*) as sessions,
    ROUND(SUM(duration_minutes), 2) as total_minutes,
    ROUND(AVG(duration_minutes), 2) as avg_session_minutes,
    MIN(start_time) as first_use,
    MAX(end_time) as last_use
FROM (
    SELECT
        datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') as start_time,
        datetime(obj.ZENDDATE + 978307200, 'unixepoch', 'localtime') as end_time,
        CASE 
            WHEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN
            ELSE obj.ZVALUESTRING 
        END as activity_name,
        ROUND((obj.ZENDDATE - obj.ZSTARTDATE) / 60.0, 2) as duration_minutes,
        CASE 
            WHEN obj.ZSTREAMNAME LIKE '%web%' OR meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN 'Strona'
            WHEN obj.ZSTREAMNAME = '/app/usage' THEN 'Aplikacja'
            ELSE 'Inne'
        END as activity_type
    FROM ZOBJECT obj
    LEFT JOIN ZSTRUCTUREDMETADATA meta ON obj.ZSTRUCTUREDMETADATA = meta.Z_PK
    WHERE obj.ZSTREAMNAME IN ('/app/usage', '/safari/history', '/app/webUsage', '/app/inFocus')
        AND obj.ZSTARTDATE IS NOT NULL
        AND obj.ZENDDATE IS NOT NULL
        AND (obj.ZENDDATE - obj.ZSTARTDATE) > 5
        AND date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime')
) as app_stats
WHERE activity_name IS NOT NULL
GROUP BY activity_name, activity_type
HAVING total_minutes > 1 -- tylko aktywności dłuższe niż 1 minuta
ORDER BY total_minutes DESC
LIMIT 20;


-- 6. TIMELINE GODZINOWY - AKTYWNOŚĆ W CIĄGU DNIA
-- Pokazuje rozkład aktywności w poszczególnych godzinach
SELECT 
    strftime('%H:00', datetime(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime')) as hour_slot,
    COUNT(*) as activities_count,
    ROUND(SUM((obj.ZENDDATE - obj.ZSTARTDATE) / 60.0), 2) as total_minutes,
    COUNT(DISTINCT 
        CASE 
            WHEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN IS NOT NULL 
            THEN meta.Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN
            ELSE obj.ZVALUESTRING 
        END
    ) as unique_apps_sites
FROM ZOBJECT obj
LEFT JOIN ZSTRUCTUREDMETADATA meta ON obj.ZSTRUCTUREDMETADATA = meta.Z_PK
WHERE obj.ZSTREAMNAME IN ('/app/usage', '/safari/history', '/app/webUsage', '/app/inFocus')
    AND obj.ZSTARTDATE IS NOT NULL
    AND obj.ZENDDATE IS NOT NULL
    AND (obj.ZENDDATE - obj.ZSTARTDATE) > 5
    AND date(obj.ZSTARTDATE + 978307200, 'unixepoch', 'localtime') = date('now', 'localtime')
GROUP BY hour_slot
ORDER BY hour_slot;

Dalsze kroki analizy ​

  1. Zbadanie pełnego schematu bazy danych (wszystkich tabel i ich pól)
  2. Identyfikacja typów danych przechowywanych w każdej tabeli
  3. Analiza relacji między tabelami przy użyciu kluczy obcych
  4. Rozpoznanie formatów danych używanych do przechowywania różnych typów informacji
  5. Eksperymentowanie z kwerendami SQL dla wydobycia konkretnych informacji

Narzędzia do analizy ​

  • SQLite Browser - graficzny interfejs do przeglądania baz danych SQLite
  • Terminal z poleceniem sqlite3 - bezpośredni dostęp przez linię komend
  • Python z biblioteką sqlite3 - programistyczna analiza i wydobycie danych

Uwagi ​

  • Baza knowledgeC.db używa epoki czasu macOS (różnica 978307200 sekund od epoki Unixa)
  • Niektóre pola zawierają dane binarne, które mogą wymagać specjalnego dekodowania
  • Pełna analiza może wymagać odniesienia do dokumentacji Apple Developer lub inżynierii wstecznej

Last updated:

Released under the MIT License.

Copyright © 2025-present Wojciech Tyziniec