Parsing/Merge of SHM/WAL Transaction and Event Logs

[chrisnelmes]

Hi Velocidex,

I'm trying to use SQLiteHunter to parse Microsoft Sticky Notes (see YAML below).

Sticky Notes uses SQLite3, SHM/WAL Transaction and Event Logs. I've not been able to determine from the readme if parsing of these logs is supports but the following definition fails.

Can you clarify if sqlite-shm/sqlite-wal log parsing is supported?

Name: Microsoft Sticky Notes
Author: Christopher Nelmes
Email: Chris.Nelmes@harmonic.ky
Reference: N/A
Description: |
Note SQLiteHunter does not parse SHM and WAL files, therefore only notes that have been commited to the sqlite file will be accessible.
SQLiteIdentifyQuery: |
SELECT count(*) AS Check
FROM sqlite_master
WHERE type='table'
AND name='Note';
SQLiteIdentifyValue: 1
Categories:

• Test
• Windows
  FilenameRegex: "plum.sqlite*"
  Globs:
• "C:/Users//AppData/Local/Packages/Microsoft.MicrosoftStickyNotes_8wekyb3d8bbwe/LocalState/plum.sqlite"
  Sources:
• name: Notes
  VQL: |
  SELECT * FROM Rows
  SQL: |
  SELECT Text, CreatedAt, UpdatedAt from Note

Regards
Christopher Nelmes

[scudette]

It depends. Velociraptor uses the standard SQLite library to access the file. This means that if we do parse the same file then we will be able to see the transaction log because this is just a normal sqlite connection.

However, many sqlite files have locks on them which means that when Velociraptor opens the file, we may not have access to the file, or be very slow as sqlite manages locks with the program which is currently running.

So this is why we have the parameter SQLITE_ALWAYS_MAKE_TEMPFILE when it is set, we always just copy the file to a tempfile and open that. We do not currently support also copying the wal files so those will be lost when we open the file. The Sqlite library will recover to file to some extent and should work fine but at least we wont be competing with the currently running program with locks on the file.