Some notes on accessing / exporting Apple's Screen Time data

accessing-apple-screen-time-data.md

Accessing / Exporting Apple's Screen Time Data

Some notes on accessing / exporting Apple's Screen Time data

Table of Contents

• knowledgeC.db SQLite CoreData Models / Tables
• Unsorted
• See Also

knowledgeC.db SQLite CoreData Models / Tables

See also:

• extract-coredata-model-hierarchy.py

⇒ ./extract-coredata-model-hierarchy.py ~/Library/Application\ Support/Knowledge/knowledgeC.db
- 1: AdditionChangeSet (Table: ZADDITIONCHANGESET)
- 2: ContextualChangeRegistration (Table: ZCONTEXTUALCHANGEREGISTRATION)
- 3: ContextualKeyPath (Table: ZCONTEXTUALKEYPATH)
- 4: CustomMetadata (Table: ZCUSTOMMETADATA)
- 5: DeletionChangeSet (Table: ZDELETIONCHANGESET)
- 6: Histogram (Table: ZHISTOGRAM)
- 7: HistogramValue (Table: ZHISTOGRAMVALUE)
- 8: KeyValue (Table: ZKEYVALUE)
- 9: Object (Table: ZOBJECT)
  - 10: Category
  - 11: Event
  - 12: Identifier
  - 13: Quantity
- 14: Source (Table: ZSOURCE)
- 15: StructuredMetadata (Table: ZSTRUCTUREDMETADATA)
- 16: SyncPeer (Table: ZSYNCPEER)

Unsorted

• From ChatGPT (Ref):
  • Note: This seems to be wrong, at least on macOS Ventura.. not sure if it was ever correct.

  • On macOS, the Screen Time data is stored in a SQLite database located at:

  • ~/Library/Application Support/ScreenTimeAgent/Database/CoreDuetData.db

  • To access this database, you can use a SQLite browser tool, such as DB Browser for SQLite, which is a free, open-source tool that can be downloaded from their website at https://sqlitebrowser.org/.

  • Once you have installed DB Browser for SQLite, you can open the CoreDuetData.db file and browse the tables to view the Screen Time data. Note that the data is stored in a compressed format, so you may need to use a SQLite extension or command-line tool to decompress the data for viewing.

• https://stackoverflow.com/questions/66935741/how-to-get-screen-time-data-on-macos/70627001#70627001

  • The data screenTime is stored in /private/var/db/CoreDuet/Knowledge /Users/wgnb/Library/Application Support/Knowledge

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

  • read Sarah blog for details

    • https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage

      • 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.

      • 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.

      • ZOBJECT – Contains potentially thousands of usage entries for approximately 4 weeks.

      • ZSOURCE – Source of the ZOBJECT entries

      • ZSTRUCTUREDMETADATA – Additional metadata associated with ZOBJECT entries

• https://stackoverflow.com/questions/66935741/how-to-get-screen-time-data-on-macos/66946521#66946521

  • The closest I've come is this Github Repo: https://github.com/Evian-Zhang/ScreenTimeController

    • DB Location: https://github.com/Evian-Zhang/ScreenTimeController#%E8%8E%B7%E5%BE%97%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BD%8D%E7%BD%AE
    • DB Tables: https://github.com/Evian-Zhang/ScreenTimeController#%E5%88%86%E6%9E%90%E6%95%B0%E6%8D%AE%E5%BA%93

  • He confirmed the research I had already done by looking at the open file descriptors for the Screen Time agent. The ScreenTime database is stored here:

    • /var/folders/XYZ/xxxxxxxxxx/0/com.apple.ScreenTimeAgent/Store

  • You can read more about this part of the filesystem here

  • In this folder, there are several SQLite files which is what the agent writes to. Unfortunately, this folder is really locked down by MacOS.

  • You can read about how its locked down here and here.

• https://www.r-bloggers.com/2019/10/spelunking-macos-screentime-app-usage-with-r/

  • Thankfully, Sarah Edwards has foraged through the macOS filesystem for us and explained where these bits of knowledge are in her post, Knowledge is Power! Using the macOS/iOS knowledgeC.db Database to Determine Precise User and Application Usage, which ultimately reveals the data lurks in ~/Library/Application Support/Knowledge/knowledgeC.db. Sarah also has a neat little Python utility dubbed APOLLO (Apple Pattern of Life Lazy Output’er) which has a smattering of knowledgeC.db canned SQL queries that cover a myriad of tracked items.

• https://apple.stackexchange.com/questions/424955/is-there-a-way-to-save-or-export-screentime-data

  • Is there a way to save or export Screentime data?

  • My Answer
• https://apple.stackexchange.com/questions/346091/is-there-a-way-to-download-screen-time-data-from-iphone-or-other-apple-ios-devic

  • Is there a way to download Screen Time data from iPhone or other Apple iOS devices?

  • My Answer
• https://apple.stackexchange.com/questions/354668/screen-time-statistic-for-longer-periods

  • Screen time statistic for longer periods

  • My Answer
• https://apple.stackexchange.com/questions/444417/copy-the-screen-time-data/459161#459161

  • Copy the screen time data

  • My Answer
• How can I list the open file descriptors for a process running on macOS? (ChatGPT Ref)
  • Get the PID: pgrep ScreenTimeAgent
  • List open file descriptors: lsof -p <pid>
  • One liner: ST_PID=$(pgrep ScreenTimeAgent); lsof -p $ST_PID

⇒ ST_PID=$(pgrep ScreenTimeAgent); lsof -p $ST_PID
COMMAND   PID     USER   FD      TYPE DEVICE  SIZE/OFF                NODE NAME
ScreenTim 826 devalias  cwd       DIR    1,4       384               40725 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data
ScreenTim 826 devalias  txt       REG    1,4   2858656 1152921500312301519 /System/Library/PrivateFrameworks/ScreenTimeCore.framework/Versions/A/ScreenTimeAgent
ScreenTim 826 devalias  txt       REG    1,4     52300           119749843 /Library/Preferences/Logging/.plist-cache.SaRrvI6F
ScreenTim 826 devalias  txt       REG    1,4    281152 1152921500312437674 /usr/lib/libobjc-trampolines.dylib
ScreenTim 826 devalias  txt       REG    1,4     32768               41700 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-shm
ScreenTim 826 devalias  txt       REG    1,4       110 1152921500312100418 /System/Library/CoreServices/SystemVersion.bundle/English.lproj/SystemVersion.strings
ScreenTim 826 devalias  txt       REG    1,4    237664           107197209 /private/var/db/timezone/tz/2023c.1.0/icutz/icutz44l.dat
ScreenTim 826 devalias  txt       REG    1,4     32768               41754 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-shm
ScreenTim 826 devalias  txt       REG    1,4  30447056 1152921500312449382 /usr/share/icu/icudt70l.dat
ScreenTim 826 devalias  txt       REG    1,4    134457 1152921500312134360 /System/Library/Frameworks/CoreData.framework/Versions/A/Resources/CoreDataErrors.loctable
ScreenTim 826 devalias  txt       REG    1,4     32768               82945 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-shm
ScreenTim 826 devalias  txt       REG    1,4    626688           104208182 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/C/com.apple.iconservices/store.index
ScreenTim 826 devalias  txt       REG    1,4    825440 1152921500312138426 /System/Library/Frameworks/Foundation.framework/Versions/C/Resources/FoundationErrors.loctable
ScreenTim 826 devalias  txt       REG    1,4    132070           121959101 /private/var/db/analyticsd/events.allowlist
ScreenTim 826 devalias    0r      CHR    3,2       0t0                 314 /dev/null
ScreenTim 826 devalias    1u      CHR    3,2       0t0                 314 /dev/null
ScreenTim 826 devalias    2u      CHR    3,2 0t7142668                 314 /dev/null
ScreenTim 826 devalias    3u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias    4u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias    5u      REG    1,4     32768               41700 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-shm
ScreenTim 826 devalias    6u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias    7u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias    8u      REG    1,4  13381632               41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias    9u      REG    1,4    218392               41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias   10u      REG    1,4     32768               41754 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-shm
ScreenTim 826 devalias   11u      REG    1,4  13381632               41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias   12u      REG    1,4  13381632               41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias   13u      REG    1,4    218392               41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias   14u      REG    1,4    218392               41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias   15u      REG    1,4  13381632               41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias   16u      REG    1,4    218392               41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias   17u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias   18u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias   19u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias   20u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias   21   NPOLICY
ScreenTim 826 devalias   22u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias   23u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias   24u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias   25u      REG    1,4     49152               82941 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db
ScreenTim 826 devalias   26u      REG    1,4  14741504               41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias   27u      REG    1,4    778712               82944 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-wal
ScreenTim 826 devalias   28u      REG    1,4     32768               82945 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-shm
ScreenTim 826 devalias   29u      REG    1,4     49152               82941 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db
ScreenTim 826 devalias   30u      REG    1,4    778712               82944 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-wal
ScreenTim 826 devalias   31u      REG    1,4   2550312               41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal

See Also

• Accessing / Exporting Apple's Reminders Data on macOS (0xdevalias' gist)
• Accessing Apple's iCloud Synced Passwords Keychain (0xdevalias' gist)
• Decompile Apple Shortcuts into raw XML 'source code' (0xdevalias' gist)
• AppleScript Automation Snippets (0xdevalias' gist)
• macOS Launchpad DB Tricks
• Reverse Engineering on macOS (0xdevalias' gist)