How to handle storedata files?

[iwan-uschka]

I just lost all my notes accidentally and i tried to recover them using this tool. Unfortunately i wasn't able to make it work. I tried to run

python ~/Desktop/NOTES/apps/mac_apt-master/mac_apt_artifact_only.py NOTES -o ~/output -i ~/Desktop/NOTES/data/NotesV7.storedata

without any luck:

2021-01-24 17:39:26|MAIN|INFO|Started macOS Artifact Parsing Tool - Artifact Only mode, version 0.9.dev
2021-01-24 17:39:26|MAIN|INFO|Dates and times are in UTC unless the specific artifact being parsed saves it as local time!
2021-01-24 17:39:26|MAIN|INFO|--------------------------------------------------
2021-01-24 17:39:26|MAIN|INFO|Running plugin NOTES
2021-01-24 17:39:26|MAIN|INFO|--------------------------------------------------
2021-01-24 17:39:26|MAIN.NOTES|INFO|Module Started as standalone
2021-01-24 17:39:26|MAIN.NOTES|INFO|Processing file /Users/iwanuschka/Desktop/NOTES/daten/NotesV7.storedata
2021-01-24 17:39:26|MAIN.NOTES|ERROR|Failed to open database, is it a valid Notes DB?
Traceback (most recent call last):
  File "/Users/iwanuschka/Downloads/mac_apt-master/plugins/notes.py", line 376, in OpenDb
    conn = CommonFunctions.open_sqlite_db_readonly(inputPath)
  File "/Users/iwanuschka/Downloads/mac_apt-master/plugins/helpers/common.py", line 179, in open_sqlite_db_readonly
    return sqlite3.connect (f"file:{path}?mode=ro", uri=True)
sqlite3.OperationalError: unable to open database file
2021-01-24 17:39:26|MAIN.NOTES|INFO|No notes found in /Users/iwanuschka/Desktop/NOTES/daten/NotesV7.storedata
2021-01-24 17:39:26|MAIN|INFO|--------------------------------------------------
2021-01-24 17:39:26|MAIN|INFO|Finished in time = 00:00:00
2021-01-24 17:39:26|MAIN|INFO|Review the Log file and report any ERRORs or EXCEPTIONS to the developers

I went through the installation process described in https://drive.google.com/uc?export=download&id=1JEYlq_3WX6DgaLgm_LZ6jzPRT4iEg5Ge. The only exception: i am using pyenv (=> pyenv shell 3.7.9) instead of python3.

NotesV7.storedata has been taken from a system running Mac OS Catalina.

Do you have any idea how to solve this problem?

[ydkhatri]

The error seems to indicate something wrong with the database file itself, it cannot be opened. If possible, can you share the file and I can try to take a look. Email if you don't want to share publicly.
Also remember that you should get all files that begin with NotesV7.storedata, ie, NotesV7.storedata-wal and/or NotesV7.storedata-journal

[ydkhatri]

Also, I would check if permissions/ownership is an issue to accessing your file! Try running as root if so.

[iwan-uschka]

Hi @ydkhatri , thx for taking the time!!

Unfortunately sudo didn't do the trick.

What do you mean by saying

Also remember that you should get all files that begin with NotesV7.storedata, ie, NotesV7.storedata-wal and/or NotesV7.storedata-journal

I have NotesV7.storedata, NotesV7.storedata-shm and NotesV7.storedata-wal located in the same directory.

There is a lot of private stuff going on in these files so i can't upload them unfortunately.

I also tested a backup file (some months old) without any success too.

[iwan-uschka]

I just created some Notes in the Mac Notes app and tried to run

sudo python ~/Desktop/NOTES/apps/mac_apt-master/mac_apt_artifact_only.py NOTES -o ~/output -i ~/Library/Group\ Containers/group.com.apple.notes/NoteStore.sqlite

which returned the same error.

I also tried to use the binaries like described in https://apple.stackexchange.com/questions/355932/mac-os-x-notes-moved-to-exchange-disappear?newreg=3cc929e5bedf4641a2ad5ac7fd89f350#comment575718_355998 but without any luck.

I lost my notes and was quite shocked how many people tried to solve the same problem as i do now. I really appreciate all the effort. But i am wondering why none of the solutions works for me 🤪

[iwan-uschka]

I opened the storedata file in a SQLite browser. The table structure looks like this:

[ydkhatri]

OK, it seems there was a bug in the code, which affected macos/linux. I just fixed it, now the db should open correctly . Just download and replace the file common.py (mac_apt/plugins/helpers/common.py) with the new one from the repo.

[iwan-uschka]

A little speechless, thanks!! Now it's working (using sudo) and i can create a CSV using -c parameter. Thx!

But there is a little but: i was searching kinda blindly for a way to recover my notes and i considered every possible option out there while trying to figure out my own custom solution in parallel. The CSV file which has just been created contains all the notes from the database (which i assume is correct ) but i hoped to just get the notes which have been shown the last time i opened my Notes app. I already saw some notes duplicated 2000 times and more in the database. It seems like a new version of a note has been saved for every little change (key press) i made 😱

So i finally successfully created an SQL query to fetch the latest version of each note. So if anyone should stumble in here, please try out this query to receive the latest version of all notes ever stored in the corresponding database:

SELECT ZTITLE, ZHTMLSTRING, ZNAME FROM (
	SELECT * from ZNOTE p1
	INNER JOIN
	(
		SELECT * FROM ZNOTEBODY
	) p2
	ON p2.Z_PK = p1.ZBODY
	INNER JOIN
	(
		SELECT * FROM ZFOLDER
	) p3
	ON p3.Z_PK = p1.ZFOLDER
	ORDER BY p1.ZDATEEDITED DESC
) GROUP BY ZDATECREATED

Please be aware that this query should work with a V7 version of the notes database (storedata). The result contains the title, body and folder name of a note. And the query does not pretend to be beautifully written but it works ;)

I used SQLPro for SQLite which provides the option to export the query result in multiple formats.

Now it's up to me to restore the notes.

Again, thx @ydkhatri !!

[iwan-uschka]

case closed

[ydkhatri]

The query you used is very similar to what the script uses, except there is no grouping, as for forensics we want to see all the data (edits). In addition, the data in the notes is not always a flat list or spreadsheet, its more like a tree, when things like attachments are also included. So, I flatten the tree by repeating those columns , just like an sql join query will match one item on left with many on right, so you end up with a lot of repeated data.