@night 1803 access accessdata active directory admissibility ads aduc aim aix ajax alissa torres amcache analysis anjp anssi answer key antiforensics apfs appcompat appcompatflags applocker april fools argparse arman gungor arsenal artifact extractor attachments attacker tools austin automating automation awards aws azure azuread back to basics backstage base16 best finds beta bias bitcoin bitlocker blackbag blackberry enterprise server blackhat blacklight blade blanche lagny book book review brute force bsides bulk extractor c2 carved carving case ccdc cd burning ceic cfp challenge champlain chat logs Christmas Christmas eve chrome cit client info cloud forensics command line computer forensics computername conference schedule consulting contest cool tools. tips copy and paste coreanalytics cortana court approved credentials cryptocurrency ctf cti summit cut and paste cyberbox Daily Blog dbir deep freeze defcon defender ata deviceclasses dfa dfir dfir automation dfir exposed dfir in 120 seconds dfir indepth dfir review dfir summit dfir wizard dfrws dfvfs dingo stole my baby directories directory dirty file system disablelastaccess discount download dropbox dvd burning e01 elastic search elcomsoft elevated email recovery email searching emdmgmt Encyclopedia Forensica enfuse eric huber es eshandler esxi evalexperience event log event logs evidence execution exfat ext3 ext4 extended mapi external drives f-response factory access mode false positive fat fde firefox for408 for498 for500 for526 for668 forenisc toolkit forensic 4cast forensic lunch forensic soundness forensic tips fraud free fsutil ftk ftk 2 full disk encryption future gcfe gcp github go bag golden ticket google gsuite guardduty gui hackthebox hal pomeranz hashlib hfs honeypot honeypots how does it work how i use it how to howto IE10 imaging incident response indepth information theft infosec pro guide intern internetusername Interview ios ip theft iphone ir itunes encrypted backups jailbreak jeddah jessica hyde joe sylve journals json jump lists kali kape kevin stokes kibana knowledgec korman labs lance mueller last access last logon leanpub libtsk libvshadow linux linux-3g live systems lnk files log analysis log2timeline login logs london love notes lznt1 mac mac_apt macmini magnet magnet user summit mathias fuchs md viewer memorial day memory forensics metaspike mft mftecmd mhn microsoft milestones mimikatz missing features mlocate mobile devices mojave mount mtp multiboot usb mus mus 2019 mus2019 nccdc netanalysis netbios netflow new book new years eve new years resolutions nominations nosql notifications ntfs ntfsdisablelastaccessupdate nuc nw3c objectid offensive forensics office office 2016 office 365 oleg skilkin osx outlook outlook web access owa packetsled paladin path specification pdf perl persistence pfic plists posix powerforensics powerpoint powershell prefetch psexec py2exe pyewf pyinstaller python pytsk rallysecurity raw images rdp re-c re-creation testing reader project recipes recon recursive hashing recycle bin redteam regipy registry registry explorer registry recon regripper remote research reverse engineering rhel rootless runas sample images san diego SANS sans dfir summit saturday Saturday reading sbe sccm scrap files search server 2008 server 2008 r2 server 2012 server 2019 setmace setupapi sha1 shadowkit shadows shell items shellbags shimcache silv3rhorn skull canyon skype slow down smb solution solution saturday sop speed sponsors sqlite srum ssd stage 1 stories storport sunday funday swgde syscache system t2 takeout telemetry temporary files test kitchen thanksgiving threat intel timeline times timestamps timestomp timezone tool tool testing training transaction logs triage triforce truecrypt tsk tun naung tutorial typed paths typedpaths uac unc understanding unicorn unified logs unread usb usb detective usbstor user assist userassist usnjrnl validation vhd video video blog videopost vlive vmug vmware volatility vote vss web2.0 webcast webinar webmail weekend reading what are you missing what did they take what don't we know What I wish I knew whitfield windows windows 10 windows 2008 windows 7 windows forensics windows server winfe winfe lite wmi write head xboot xfs xways yarp yogesh zimmerman zone.identifier

Daily Blog #642: Solution Saturday 3/9/19

Hello Reader,
         I love weeks when we get to crown new winners. Tun is not new DFIR, you may have seen his tweets before, but he is new to the Sunday Funday winners circle. Tun did some great testing which he documented below specifically for OSX. Give his work a look and join me on congratulating Tun on his win!


The Challenge:
On OSX Mojave, what information can you determine from the KnowledgeC database?

The Winning Answer:

Sarah Edwards has done quite an extensive research on the knowledgeC database and wrote a few posts on her blog mac4n6.com.  I have never used a Mac before and this is a good opportunity to learn Mac forensics. So, I decided to take the challenge and installed a macOS Mojave in Virtual Box. As a beginner I simply follow the same testing that Sarah did on macOS 10.13 to see the output of macOS 10.14 Mojave.


On macOS 10.14 Mojave, the knowledgeC.db database is found in the same locations as the previous version, macOS 10.13.
/private/var/db/CoreDuet/Knowledge system context database
~/Library/Application Support/Knowledge user context database
adams-iMac:~ adam$ sw_vers ProductName: Mac OS X ProductVersion: 10.14
BuildVersion: 18A391


adams-iMac:Knowledge adam$ pwd

/private/var/db/CoreDuet/Knowledge adams-iMac:Knowledge adam$ ls -l total 6120
-rw------- 1 root wheel 774144 Mar 8 04:25 knowledgeC.db

-rw------- 1 root wheel 32768 Mar 8 04:25 knowledgeC.db-shm

-rw------- 1 root wheel 2323712 Mar 8 05:23 knowledgeC.db-wal


adams-iMac:knowledge adam$ pwd

/Users/adam/Library/Application Support/knowledge

adams-iMac:knowledge adam$ ls -l total 1008
-rw------- 1 adam staff 425984 Mar 8 04:28 knowledgeC.db


-rw------- 1 adam staff 32768 Mar 8 04:28 knowledgeC.db-shm

-rw------- 1 adam staff 53592 Mar 8 04:38 knowledgeC.db-wal


From this database, we can get the information about

  • Application Usage
  • Application Activities
  • Safari Browser History

Using the DB Browser for SQLite, we can open the knowledgeC.db database and look at the structure. The database has many tables with many columns.
Note: timestamps in this database use the Mac Epoch time (01/01/2001 00:00:00 UTC).


Figure 1: Database structure of the knowledgeC.db, both system and user context database have 16 tables

Three tables are of particular interest and they store the information which is useful and valuable for an investigator.
  • ZOBJECT – contains usage entries (Sarah mentioned four weeks, but I could not test as my mac has been running for a week or two only)
  • ZSOURCE – source of ZOBJECT entries
  • ZSTRUCTUREDMETADATA – additional metadata associated with ZOBJECT entries

By running the SQLite query on the system context knowledgeC.db database, we get the following

“types”:
SELECT
DISTINCT ZOBJECT.ZSTREAMNAME


FROM ZOBJECT
ORDER BY ZSTREAMNAME

    • /activity/level

    • /app/activity
    • /app/inFocus
    • /app/usage
    • /display/isBacklit
    • /safari/history

For application usage information, we can run the SQLite query below where “/app/inFocus” type will provide what application is being used at a given time.
SELECT

datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH','LOCALTIME') as "ENTRY CREATION", CASE ZOBJECT.ZSTARTDAYOFWEEK
WHEN "1" THEN "SUNDAY" WHEN "2" THEN "MONDAY" WHEN "3" THEN "TUESDAY" WHEN "4" THEN "WEDNESDAY" WHEN "5" THEN "THURSDAY" WHEN "6" THEN "FRIDAY" WHEN "7" THEN "SATURDAY"
END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET",
datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH','LOCALTIME')    AS "START", datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH','LOCALTIME')   AS "END", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", ZOBJECT.ZSTREAMNAME,
ZOBJECT.ZVALUESTRING FROM ZOBJECT
WHERE ZSTREAMNAME IS "/app/inFocus" ORDER BY "START"
-- Result: 697 rows returned in 145ms




Figure 2: Snapshot of the result of SQLite query which show the application usage


Note: the application usage is only for GUI-based applications. User attribution is an issue as this data is from system context database on macOS.

For application activities, we can use “/app/activity” stream type to add more context.
SELECT
datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH','LOCALTIME') as "ENTRY CREATION", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET",
CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "SUNDAY" WHEN "2" THEN "MONDAY" WHEN "3" THEN "TUESDAY" WHEN "4" THEN "WEDNESDAY" WHEN "5" THEN "THURSDAY" WHEN "6" THEN "FRIDAY" WHEN "7" THEN "SATURDAY"
END "DAY OF WEEK", datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH','LOCALTIME')    AS "START", datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH','LOCALTIME') AS "END", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS",


ZOBJECT.ZSTREAMNAME,
ZOBJECT.ZVALUESTRING,

ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY   ACTIVITYTYPE AS "ACTIVITY TYPE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY   TITLE AS "TITLE",

ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY  USERACTIVITYREQUIREDSTRI NG AS "ACTIVITY STRING",
datetime(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY  EXPIRATIONDATE+9 78307200,'UNIXEPOCH','LOCALTIME') AS "EXPIRATION DATE"
FROM ZOBJECT

LEFT JOIN ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA=ZSTRUCTUREDMETADATA.Z_PK
WHERE ZSTREAMNAME IS "/app/activity" or ZSTREAMNAME is "/app/inFocus" ORDER BY "START"
-- Result: 754 rows returned in 207ms



Figure 3: Snapshot of the result of the SQLite query which show more context to the application activity


Even though I did edited the note, activity type data is not populated. Further testing is required for this.
Note: ZSTRUCTUREDMETADATA table has more than 100 columns which is worth looking into to see what data the apps are populating in this table.

For Safari history information, we can run the same query as the previous queries.
SELECT

datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH','LOCALTIME') as "ENTRY CREATION", CASE ZOBJECT.ZSTARTDAYOFWEEK
WHEN "1" THEN "SUNDAY"


WHEN "2" THEN "MONDAY" WHEN "3" THEN "TUESDAY" WHEN "4" THEN "WEDNESDAY" WHEN "5" THEN "THURSDAY" WHEN "6" THEN "FRIDAY" WHEN "7" THEN "SATURDAY"
END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET",
datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH','LOCALTIME')    AS "START", datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH','LOCALTIME')   AS "END", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", ZOBJECT.ZSTREAMNAME,
ZOBJECT.ZVALUESTRING FROM ZOBJECT
WHERE ZSTREAMNAME IS "/safari/history" ORDER BY "START"
-- Result: 57 rows returned in 28ms


Figure 4: Snapshot of the results of the SQLite query which shows the Safari history

Note: if the user clears the browsing history, these entries get removed too. Private Mode browsing does not show up either.


For user correlation, we can run the SQLite query on the user context knowledgeC.db database.
SELECT

DISTINCT ZOBJECT.ZSTREAMNAME FROM ZOBJECT
ORDER BY ZSTREAMNAME

  • /event/tombstone

  • /portrait/entity
  • /portrait/topic

There are a lot more testing to be done as this database contains a lot of information.

- Knowledge is power -

Credits:



Post a Comment

[blogger][disqus][facebook][spotim]

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.