SQLite query & Powershell scripts to parse the Windows 10 (v1803) ActivitiesCache.db
» Revised query « for Windows Timeline - works with all versions (1803,1809,1903+) and is based on the smartlookup view #dfir. (Tested on Win10 pro 1903 OS Build 18895.1000)
SQLite queries to parse Windows 10 (1803+) Timeline’s ActivitiesCache.db Database
Either import the queries (.sql file) to your SQLite program, or Copy/Paste the code to a query tab. Your software needs to support the SQLIte JSON1 extension.
Updated to work with Win10 1809 (Build 17754.1
17744.1003 RC5), 1903
Screenshots of WindowsTimeline.sql
Screenshots of WindowsTimeline2.sql
SQLite Tables processed:
Other queries (Win10 - 1803): (Build 17754.1
Other queries (Win10 - 1809/1903):
WindowsTimeline (1903) - Full SQLite query that works with Win10 v1903 ActivitiesCache.db. Will not work with earlier Windows versions (1803/1809) as the latest Windows version 1903 (19H1) has more dB fields. Now copy/paste operations can be seen as well as clipboard text (Base64 encoded):
–> Clipboard copy/paste operations (1903) - SQLite query to get just clipboard related data. ______________________________
and Microsoft Windows 10 version 1803, 1903 (OS builds from 17134.48 to 17134.254) and version 1809 (Insider’s Build 17754.1
17744.rc5_release.180818-1845) and 1903 (18353.1)
Note: The output of the queries can be exported as a TX or CSV so that it can be used with log2timeline, TimelineExplorer or MS Excel. For example, in DB Browser for SQLite at the bottom right corner, click on
and select CSV. This will open this delimiter options window. After you make any needed changes (e.g. select comma as the delimiter), click ok,
and you will be presented with another window to select Folder and Filename to save the CSV file. ______________________________
:shipit: Require SQLite3.exe
Note: The PowerShell scripts are not the fastest way to parse Windows Timeline (~16min for a 10500 entry db)
Note1 - Add C:\sqlite to the system PATH
Note2 - After you install the latest SQLite3.exe, check the version from inside powershell by running
SQLite3.exe -version (you may already have an older version in your Path - you can check that by running FindSQLite3.ps1)
Powershell script to check the Platform DeviceID values in the database against the HKCU DeviceCache entries in the registry.
It appears that Type 8 entries are Smartphones, type 9 Full Sized PCs and type 15 Laptops).
Note that Platform Device IDs representing a specific device change over time.
Note: According to the Connected Devices Platform specification these are the device types. Curiously, type 15 is not in that list:
Powershell script that runs a simple SQLite query against one of the local ActivitiesCache.db’s available to the user, and adds info for the PlatformID from the registry. Json fields are parsed with Powershell’s convertfrom-json.
Powershell script that runs a simple SQLite query against any user selected ActivitiesCache.db, and adds info for the PlatformID from a related, user selected NTUser.dat file. Json fields are parsed with Powershell’s convertfrom-json. ______________________________
Related Windows Apps