This artifact provides details about notes taken using the default Notes application on macOS. These notes can be useful during an investigation, especially if tied to interesting files.
Deleted notes and attachments can also be recovered in some instances.
The SQL query within this artifact was primarily derived from Yogesh Khatri’s referenced blog post.
NOTE: This artifact may not cover all attachments at this time, and there are many more great pieces of data to discover! More information can be found in the ZICCLOUDSYNCINGOBJECT table.
name: MacOS.Applications.Notes
description: |
This artifact provides details about notes taken using the default Notes application on macOS. These notes can be useful during an investigation, especially if tied to interesting files.
Deleted notes and attachments can also be recovered in some instances.
The SQL query within this artifact was primarily derived from Yogesh Khatri's referenced blog post.
NOTE: This artifact may not cover all attachments at this time, and there are many more great pieces of data to discover! More information can be found in the `ZICCLOUDSYNCINGOBJECT` table.
reference:
- http://www.swiftforensics.com/2018/02/reading-notes-database-on-macos.html
type: CLIENT
author: Wes Lambert - @therealwlambert|@weslambert@infosec.exchange
parameters:
- name: NotesGlob
default: /Users/*/Library/Containers/com.apple.Notes/Data/Library/Notes/NotesV*.storedata,/Users/*/Library/Group Containers/group.com.apple.notes/NoteStore.sqlite
- name: UploadFiles
default: False
description: "Upload attachments in scope"
type: bool
precondition:
SELECT OS From info() where OS = 'darwin'
sources:
- query: |
LET NotesList = SELECT OSPath
FROM glob(globs=split(string=NotesGlob, sep=","))
LET NotesDetails = SELECT Key AS _Key,
split(sep='\/', string=OSPath)[2] AS User,
Note,
Title,
Snippet,
NoteID AS _NoteID,
timestamp(cocoatime=CreatedTS) AS CreatedTime,
timestamp(cocoatime=LastOpenedDate) AS LastOpenedTime,
timestamp(cocoatime=DirModificationDate) AS LastDirModifcation,
Account AS _Account,
Directory,
DirectoryID,
AttachmentName,
AttachmentSize,
AttachmentUUID,
if(condition=AttachmentUUID,then='Users/' + split(sep='\/', string=OSPath)[2] + '/Library/Group Containers/group.com.apple.notes/Accounts/LocalAccount/Media/' + AttachmentUUID + '/' + AttachmentName) AS AttachmentLocation,
AccountName AS _AccountName,
AccountID AS _AccountID,
AccountType AS _AccountType,
gunzip(string=Data) AS Data,
OSPath
FROM sqlite(file=OSPath,
query=if(condition=OSPath =~ ".sqlite",
then='''SELECT n.Z_PK AS Key,
n.ZNOTE as Note,
c1.ZTITLE1 as Title,
c1.ZSNIPPET as Snippet,
c1.ZIDENTIFIER as NoteID,
c1.ZCREATIONDATE3 as CreatedTS,
c1.ZFOLDERMODIFICATIONDATE AS DirModificationDate,
c1.ZLASTOPENEDDATE AS LastOpenedDate,
c2.ZACCOUNT3 as Account,
c2.ZTITLE2 as Directory,
c2.ZIDENTIFIER as DirectoryID,
c4.ZFILENAME as AttachmentName,
c3.ZFILESIZE as AttachmentSize,
c4.ZIDENTIFIER as AttachmentUUID,
c5.ZNAME as AccountName,
c5.ZIDENTIFIER as AccountID,
c5.ZACCOUNTTYPE as AccountType,
n.ZDATA as Data
FROM ZICNOTEDATA as n
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.ZNOTEDATA = n.Z_PK
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = c1.ZFOLDER
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE= n.ZNOTE
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c4.ZATTACHMENT1= c3.Z_PK
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2
ORDER BY Key''',
else='''SELECT n.Z_PK as Key,
datetime(n.ZDATECREATED + 978307200, 'unixepoch') as CreatedTS,
datetime(n.ZDATEEDITED + 978307200, 'unixepoch') as Modtime,
n.ZTITLE AS Title,
(SELECT ZNAME from ZFOLDER where n.ZFOLDER=ZFOLDER.Z_PK) as Directory,
(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 DirectoryParent,
ac.ZEMAILADDRESS as Email,
ac.ZACCOUNTDESCRIPTION as AccountDescription,
b.ZHTMLSTRING as HTMLString,
att.ZCONTENTID as ContentID,
att.ZFILEURL as FileURL
FROM ZNOTE as n
LEFT JOIN ZNOTEBODY as b ON b.ZNOTE = n.Z_PK
LEFT JOIN ZATTACHMENT as att ON att.ZNOTE = n.Z_PK
LEFT JOIN ZACCOUNT as ac ON ac.Z_PK = DirectoryParent'''))
SELECT *,
if(condition=UploadFiles,then=if(condition=AttachmentLocation, then=upload(file=AttachmentLocation))) AS Upload
FROM foreach(row=NotesList, query=NotesDetails)