Pages

Friday, February 2, 2018

Reading Notes database on macOS

The notes app comes built-in with every OSX/macOS release since OSX 10.8 (Mountain Lion). It is great for quick notes and keeps notes synced in the cloud. It can have potentially useful tidbits of user information in an investigation.

Artifact breakdown  & Forensics

Locations 

Depending on version of macOS, this can vary. Sometimes there is more than one database, probably as a result of an upgrade! But only one is actively used at any given time (not both). So far, we haven't seen any duplicate data when two are present.

Location 1

/Users/<USER>/Library/Containers/com.apple.Notes/Data/Library/Notes/
In here, databases will be named as one of the following:
NotesV1.storedata ← Mountain Lion  (Thanks Geoff Black)
NotesV2.storedata ← Mavericks
NotesV4.storedata ← Yosemite
NotesV6.storedata ← Elcapitan & Sierra
NotesV7.storedata ← HighSierra
If a note has an attachment, then the attachment is usually stored at the following location:
/Users/<USER>/Library/Containers/com.apple.Notes/Data/Library/CoreData/Attachments/UUID/

There does not appear to be much difference between the database types. The following tables have been seen.
     
Tables for NotesV2
Tables for NotesV6

Each note can be associated with either a local account or an online one. The following account information can be obtained.  

Account email address, id and username from ZACCOUNT table

Individual note data is stored in ZNOTEBODY and the rest of the tables provide information about note parent folder, sync information, and attachment locations. 

Notes converts all data to HTML as seen below.

ZNOTES Table with note Html content
The graphic below shows how you can find and resolve note attachments to their locations on disk. If an attachment is present, ZNOTEBODY.ZHTMLSTRING will contain the UUID of that which can be matched up to the ZATTACHMENT.ZCONTENTID to get a binary plist blob. When parsed, you can find the full path to the attachment in the plist.

Resolving attachment location

The dates and times fetched are Mac Absolute time, which is the number of seconds since 1/1/2001.

Reading the data

The following SQL query will pull out most pertinent information from this database:

SELECT n.Z_PK as note_id, datetime(n.ZDATECREATED + 978307200, 'unixepoch') as created, datetime(n.ZDATEEDITED + 978307200, 'unixepoch') as edited, n.ZTITLE, (SELECT ZNAME from ZFOLDER where n.ZFOLDER=ZFOLDER.Z_PK) as Folder,(SELECT zf2.ZACCOUNT from ZFOLDER as zf1  LEFT JOIN ZFOLDER as zf2 on (zf1.ZPARENT=zf2.Z_PK) where n.ZFOLDER=zf1.Z_PK) as folder_parent,ac.ZEMAILADDRESS as email, ac.ZACCOUNTDESCRIPTION, b.ZHTMLSTRING, att.ZCONTENTID, att.ZFILEURLFROM ZNOTE as nLEFT JOIN ZNOTEBODY as b ON b.ZNOTE = n.Z_PKLEFT JOIN ZATTACHMENT as att ON att.ZNOTE = n.Z_PKLEFT JOIN ZACCOUNT as ac ON ac.Z_PK = folder_parent

Location 2

/Users/<USER>/Library/Group Containers/group.com.apple.notes/NoteStore.sqlite
This one has been seen on El Capitan, Sierra and HighSierra. Attachments are stored in the Media folder located here:
/Users/<USER>/Library/Group Containers/group.com.apple.notes/Media/<UUID>/
Here UUID is the unique identifier for each attachment. The database scheme is different here.

NoteStore.sqlite in HighSierra

NotesStore.sqlite in ElCapitan





Only account name and identifier (UUID) is available. To get full account information, this will need to be correlated with the account info database stored elsewhere.
ZICLOUDSYNCINGOBJECT account info
Note data is available in the ZICNOTEDATA table. 

ZICNOTEDATA table
That ZICNOTEDATA.ZDATA blob is gzip compressed. Upon decompression, it reveals the note data stored in a proprietary unknown binary format. As seen below, you can spot the text, its formatting information and attachment info.

ZDATA gzipped blob showing gzip signature (1F8B08)
Uncompressed ZDATA blob showing text, formatting and attachment info

Most notable in this database is the presence of several timestamps-
Note Title Modified → ZDATEFORLASTTITLEMODIFICATION
Note Created → ZCREATIONDATE
Note Modified → ZMODIFICATIONDATE1
Attachment Modified → ZMODIFICATIONDATE
Attachment Preview Updated → ZPREVIEWUPDATEDATE

Reading NoteStore.sqlite


The following SQL query will pull out most pertinent information from this database:
SELECT n.Z_12FOLDERS as folder_id , n.Z_9NOTES as note_id, d.ZDATA as data,
c2.ZTITLE2 as folder,
datetime(c2.ZDATEFORLASTTITLEMODIFICATION + 978307200, 'unixepoch') as folder_title_modified,
datetime(c1.ZCREATIONDATE + 978307200, 'unixepoch') as created,
datetime(c1.ZMODIFICATIONDATE1 + 978307200, 'unixepoch')  as modified,
c1.ZSNIPPET as snippet, c1.ZTITLE1 as title, c1.ZACCOUNT2 as acc_id,
c5.ZACCOUNTTYPE as acc_type, c5.ZIDENTIFIER as acc_identifier, c5.ZNAME as acc_name,
c3.ZMEDIA as media_id, c3.ZFILESIZE as att_filesize,
datetime(c3.ZMODIFICATIONDATE + 978307200, 'unixepoch') as att_modified,
datetime(c3.ZPREVIEWUPDATEDATE + 978307200, 'unixepoch') as att_previewed,
c3.ZTITLE as att_title, c3.ZTYPEUTI, c3.ZIDENTIFIER as att_uuid,
c4.ZFILENAME, c4.ZIDENTIFIER as media_uuid
FROM Z_12NOTES as n
LEFT JOIN ZICNOTEDATA as d ON d.ZNOTE = n.Z_9NOTES
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.Z_PK = n.Z_9NOTES
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = n.Z_12FOLDERS
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE = n.Z_9NOTES
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c3.ZMEDIA = c4.Z_PK
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2
ORDER BY note_id
 
On HighSierra, use this query:
SELECT n.Z_PK, n.ZNOTE as note_id, n.ZDATA as data,c3.ZFILESIZE,c4.ZFILENAME, c4.ZIDENTIFIER as att_uuid,c1.ZTITLE1 as title, c1.ZSNIPPET as snippet, c1.ZIDENTIFIER as noteID,datetime(c1.ZCREATIONDATE1, 'unixepoch') as created, datetime(c1.ZLASTVIEWEDMODIFICATIONDATE, 'unixepoch'), datetime(c1.ZMODIFICATIONDATE1, 'unixepoch') as modified,c2.ZACCOUNT3, c2.ZTITLE2 as folderName, c2.ZIDENTIFIER as folderID,c5.ZNAME as acc_name, c5.ZIDENTIFIER as acc_identifier, c5.ZACCOUNTTYPEFROM ZICNOTEDATA as nLEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.ZNOTEDATA = n.Z_PKLEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = c1.ZFOLDERLEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE= n.ZNOTELEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c4.ZATTACHMENT1= c3.Z_PKLEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2ORDER BY note_id
If you are looking for an automated way to read this, use mac_apt, the NOTES plugin will parse it.

18 comments:

  1. Nice work! FYI Mountain Lion has NotesV1.storedata, and the table list is the same as NotesV2.storedata.

    ReplyDelete
  2. Great, I don't have a Mountain Lion image to test it, but I will put the change in the code to parse that too. Then there may be a v3 and v5 too somewhere..

    ReplyDelete
  3. The NoteStore.sqlite file seems to contain the notes stored in iCloud. When extracting the note data, mac_apt gives a plain text version of the note with newlines replaced by commas. Have you figured out how to get the html version of the note in this case?

    ReplyDelete
  4. this post help me to solve problems that is very confusing if you look for help in apple forums, so what I did is simple replacement of files in method 2 from backup (disk image) to my running OS everything including links to images where imported, in short replace storedata and sqlite files and that it.

    thanks you for this post and .i. apple for the support

    ReplyDelete
  5. Hello! Any ideas on how formatting (lists, checkboxes, etc) are stored in the ZDATA blob? Thank you!

    ReplyDelete
  6. I believe they're stored as "archived" Objective-C objects.

    It's probably better to continue this discussion in the issue I created:
    https://github.com/ydkhatri/mac_apt/issues/13

    ReplyDelete
  7. how can i restore or view note from archive?

    ReplyDelete
  8. See my savenotes program: https://github.com/bshannon/savenotes

    ReplyDelete
  9. Thank you soo much I thought my notes were gone for ever! I was able to restore them using mac_apt

    ReplyDelete
  10. Hi !this is the only page that has the information ive been looking for hopefully you can help !! I managed to delete all my notes off icloud yesterday accidentally. I managed to copy those files over before they were overwritten - NotesV7.storedata-wal seems to have all the info as it is 4.1 MB, however when i put the three files back into the library path with the wifi off- they still dont come back - do you have any advice ? my email is [email protected]

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. I am new to trying to learn how to navigate the metadata of macOS files. Your blog entry has been very helpful! Thank you for that!

    I am still a newbie at this, so please bear with me with regards to this question: how exactly can I access the ZDATEFORLASTTITLEMODIFICATION data in NoteStore.sqlite? I have been trying to use the SQLite Viewer in https://inloop.github.io/sqlite-viewer/ for that, but not been too successful at it. The SQLite Viewer I am using does not show a ZDATEFORLASTTITLEMODIFICATION column in the ZICNOTEDATA table. I thought that could be because ZDATEFORLASTTITLEMODIFICATION could be within the compressed ZDATA column in ZICNOTEDATA. My ZDATA column seems not to be compressed, though, as it shows entries that are either null or comma-separated lists of integers. What do I need to do to visualize the ZDATEFORLASTTITLEMODIFICATION data if it is not part of ZDATA? In case it is part of ZDATA, how exactly do I extract it from it?

    Thanks beforehand for your patience! 🙂

    ReplyDelete
    Replies
    1. You may just be having a version of the Notes app that does not have that field. The Notes app database structure has changed between versions of macOS

      Delete
  13. Nice topic, but I got my note data when I ran your query, but it was unreadable.
    Such, how can I make it so that it's easy to read?

    ReplyDelete