sqlite

Just Call Me Buffy the Proto Slayer – An Initial Look into Protobuf Data in Mac and iOS Forensics

I was first introduced to the protobuf data format years ago accidentally when I was doing some MITM network analysis from an Android device. The data I was looking at was being transferred in this odd format, I could tell there were some known strings and some patterns to it - but I did not recognize the format. It had no magic number or file header. I started looking at it more in depth, it did have a structure. I spent an embarrassingly amount of time trying to reverse engineer this structure before I realized it was in fact a protobuf data blob. I think it was a moment of frustration in reversing this structure that lead me to searching something like “weird data format Android network traffic” that finally lead me to protobufs. 

 Ok, so what the heck is a protobuf? It actually stands for Protocol Buffer, but everyone calls them protobufs. It is a “language-neutral, platform-neutral extensible mechanism for serializing structured data” created by Google. It is a super-efficient way of storing and transferring data.

Since I was looking at an Android device, a protobuf made perfect sense. This was a Google thing afterall. I started noticing them more and more on Android devices, not just in the network traffic but also storing data on disk as well. It took me a long time to also notice that they were being stored on Apple devices! Native applications, 3rdparty applications, they are used EVERYWHERE! A great example was found by my friend Phill Moore in the iOS Spotify application to keep track of items listened to

In this article I’ll introduce you to some of the Apple-specific protobufs that I’ve come across. Some are fairly straight forward, others are less so. The kicker with protobufs is that there is an accompanying *.proto file that contains the definition to what is contained in these buffers. Unfortunately, we likely do not have this file as it is most likely server-side or inaccessible therefore we need to reverse engineer the contents and meaning of the items stored in this blob.

To parse these protobufs, I use protoc from Google to get a raw output. If you have the .proto file you can use this as well, but I have yet to give that a go. On a Mac, I would do a ‘brew install protobuf’ to get protoc installed. To parse a given buffer I will use the following command:

protoc --decode_raw < [protobuf_blob_file]

I will parse out some protobufs from different applications to give you an idea of what is stored in them - Maps, Locations, Health, and Notes.

Maps Data

The Maps application on both macOS and iOS use many protobufs to store location data. These can be found in quite a few different Maps related plist files. I will focus on GeoHistory.mapsdata plist file from iOS which stores historical locations that were mapped. This plist has GUID keys that contain a “contents” subkey. This “contents” blob contains the protobuf of the mapped location. I chose a small example to begin with as some of these can be very large

I’ve extracted this blob and put into a hex editor. You can see some visible strings in this blob but really no context. This is where parsing with protoc can be helpful.

I saved this protobuf to a file I named geohistory.pb. Using protoc I decoded it to the output below. I can see the same GUID and location name but now I get some other hex based values. This is where you will have to determine what these values mean and what they are used for, it may not be obvious initially.

At this time, I believe the highlighted pairs of hex in the screenshot above are coordinates. To read these, I copy them into a hex editor to read their values highlighted below. On the left is latitude, on the right is longitude. Plotting these two pairs above I get one location in Manassas, VA and another in Bethesda, MD. Clearly, neither of these are in Fairfax, VA. This is the tricky part, what do these values mean? More testing needs to be done here.

Location Data

The next example shows a protobuf blob being stored inside of a SQLite database. The example below is from the Local.sqlite routine locations database from iOS 11. (In iOS 12, the same data exists but has been placed into separate columns – which IMHO, is far easier to interpret.) The ZRTLEARNEDLOCATIONOFINTERESTMO table contains “learned” locations of interest or significant locations. This example is Heathrow Airport. Two columns contain protobuf data -  ZPLACEMAPITEMGEOMAPITEMHANDLE (highlighted), and ZPLACEMAPITEMGEOMAPITEM. To decode these with protoc, I will need to extract them to separate files. In DB Browser for SQLite, I can use the export feature.

The ZPLACEMAPITEMGEOMAPITEMHANDLE protobuf blob parsed with protoc contains much of the same location data as seen before. You will find that most of the location blobs will look similar. 

Taking the highlighted hex coordinates and plotting them using Google Maps, they make more sense than the ones highlighted above. These coordinates are right at Heathrow Airport in London.

Health Data 

Protobufs are not just used for location data, but also for other data storage such as split times when I have a running or walking workout. The healthdb_secure.sqlite database on iOS contains this information. Shown below is the “workout_events” table. I have selected the BLOB data for one “event” or one split time (a workout may consist of multiple split times for multiple associated “events”).

I exported and decoded with protoc using the same methods described above. The example below is from a walk I took in Monaco. Presumably the labels “s” is for seconds and “m” is for meters, however I’m still verifying this assumption.

Notes Data

One last example brings us to the Notes application. Anyone who has looked into this database likely knows that it is a complicated one. I created a sample note to show how it may look in the protobuf produced. This note has some text, a link, and a cat meme that I’ve been enjoying recently.

This example comes from MacOS in the NotesStore.sqlite database (iOS is similar). The “ZICNOTEDATA” table contains the contents of each note. Some eagle-eyed readers may notice the highlighted bytes in the binary output. It stores note contents in gzip archives embedded in the SQLite database.

I exported this gzip archive and used gzcat and xxd to preview the contents of it. I can see the text and link in the note along with some media information. What we have here is another protobuf!

The protoc output is below, many of the same strings are visible but there are some odd ones in there.

One in strange one in particular is the line "\033\'w\213\[email protected]\251IHEn\221\242\260". This is escaped octal. You will find this being used in a variety of different protobuf data. Some are smaller, some are much larger. I recall the one that Heather Mahalik and I looked at for Android Auto. That one was just full of octal nastiness, it was awful.

This one is small and converts well using echo. What does it represent? I have no idea…yet.

The media itself is stored in the file system (you can use information in the database to find the proper path). We can use the GUID in the protoc output to find the preview thumbnail.

Summing Up 

I will be very honest, I have been looking at these weird data blobs for years without knowing what they were. I am at the point now where I’m a little obsessed with protobufs. I know…its weird – but now every time I see rando-blob-data with a bunch of 0x0A hex bytes – I think protobuf, and nine out of ten times, I am correct! 

If you happen to know where I can find more information on the .proto files, please let me know!

Do it Live! Dynamic iOS Forensic Testing

fuck-it-bill-fubar-well-do-it-live.jpg

Testing and forensics go hand in hand. You cannot be sure about a certain artifact on what it contains or what certain pieces mean without testing (and not just once, but over and over and on multiple devices and operating systems!) I probably do this more than most forensic investigators but it is something I obsess about. If I’m not absolutely sure, I test – always. Even then I will caveat it to very specific instances of my testing platform. I hope this article will help most investigators and researches up and running with dynamic iOS testing.

Requirements & Prerequisites:

  • Jailbroken iOS Test Device (the newer iOS the better, generally!)

    • iPad, iPhone, iPod Touch – doesn’t matter.

  • Familiarity with your jailbreak, every jailbreak is different and may provide different sets of utilities.

  • Patience – Testing is neither quick nor easy and can be downright infuriating sometimes.

Connecting to Your iOS Device

Most newer jailbreaks will come with some sort of SSH server on them, determine how to connect to it via SSH. All examples in this article will be performed on MacOS (big surprise) however it shouldn’t be impossible to do this on other platforms. I’ve written a couple of articles on accessing jailbroken devices:

To connect to your iOS device, you can use normal access via Wi-Fi, but this may lead to stability issues when copying off large amounts of data or general connection issues. You may want to access the device off the network instead. I like to use a utility from the libimobiledevice suite of tools called iproxy. This allows me to access it via a USB lightning cable and no network. This uses usbmuxd to create a TCP tunnel over USB. More info/similar tools here if you need it.

Run iproxy in a separate terminal window or set it up as a Launch Agent.

Usage: iproxy 4242 22 [UDID]

  • Local Port 4242 – You can use whatever port you like, I like 4242 (It’s the answer to everything.)

  • Device Port 22 – Default SSH port for most jailbreaks, except for Meridian that likes to be fancy and run on 2222 (customize as required).

  • Device ID (UDID) is optional but useful if you are connected to multiple devices at a time.

Nothing will be displayed apart from “waiting for connection” until you connect a device. Once you do, you’ll see the devices UDID and port number in the iproxy output.

In another terminal window, SSH into it using the local port you setup with iproxy. You can use localhost or 127.0.0.1, whatever your personal preference is. As for the username you have two choices – root or mobile, root is obviously root and mobile is a limited user account. I always choose root but worth knowing about both. Also FWIW, make sure you change the default iOS password of these accounts from ‘alpine’ with passwd if it’s connecting to any network at all.

Executing Non-Native Binaries

Depending on which jailbreak you are using, certain utilities may not be made available to you. With any luck the basics will be provided by the jailbreak using Jonathan Levin’s binpack. If you want to upload your own, you’ll have to sign them and provide entitlements to run them on the device.

One tool that I really like for file monitoring (discussed later) is fsmon by NowSecure. I’ve attempted to build this from source for iOS but once uploaded I get an error that I don’t know how to fix (granted I didn’t research it much). Instead I pulled the binary from the DEB package provided here https://github.com/nowsecure/fsmon/releases.

I can unarchive the DEB archive using The Unarchiver (My favorite unarchive on macOS!). I then unarchive the data.tar.gz using The Unarchiver or native utilities to get the ‘/usr/bin/fsmon’ binary.

The fsmon binary is Fat or Universal binary meaning it can have multiple architectures stuffed into a single file. This particular binary has arm_v7 (32-bit) and arm64 (64-bit) Mach-O binaries.

We will need to thin this binary to a single architecture to get to run on our device. I chose arm64 for this since I have a 64-bit device (iPhone 7). You can thin/sign/entitle on the Mac too using jtool but just in the event you are not working from one (and why not!? I’m sitting here judging you right now.) I’ll upload the binary to the device and run the same commands.

Using scp I can upload this binary. For my particular jailbreak I needed to create a symlink (ln -s) to the scp binary provided by Jonathan’s binpack before I could use it.

Using scp with the CAPITAL P argument (why the port flags are not consistent between SCP and SSH is beyond me) and our iproxy port of 4242 to copy fsmon to root’s home directory, /var/root.

Now if we try to run it from root’s home directory, it will fail (“Operation no permitted”) since binaries on newer iOS’s can only run from certain directories. That’s not the only problem, it also needs to be signed and entitled.

First let’s deal with only one binary, in order to sign and entitle we need to extract the 64-bit binary from Fat binary. We can do with a couple tools. If you are on macOS and have lipo (get it? fat binary…lipo…thin…sorry, this makes me giggle every time.) I will output to a file named fsmon64 so I know it is the 64-bit binary and upload it to the device.

lipo fsmon -thin arm64 -output fsmon64

Since I’ve uploaded mine to the device already, I don’t have lipo on the device I will use Jonathan’s jtool instead.

jtool -arch arm64 -e arch fsmon

This will extract the 64-bit binary to a file named fsmon.arch_arm64 into the current directory. You will have to change the permissions to execute it (chmod 700), but we still need to deal with signature and entitlements. As shown below it has an adhoc signature and no entitlements.

To get the signature and entitlements from any binary you can run the following jtool command, example below is from the dd binary. Notice it has a Platform Binary signature and the “platform-applications” entitlement.

jtool --sig --ent <binary>

If I tried to execute the fsmon binary in /var/root, I’ll still get the “Operation not permitted” error. If I move it to a directory, I should be able to execute from I’ll get a different error, “Killed: 9”, next step is fixing the signature and entitlements.

Extract the entitlements from another (working) binary on the device using jtool and save this to a file named ent.xml for use later.

jtool --ent /jb/bin/dd > ~/ent.xml

Using jtool again, lets sign fsmon.arch_arm64 as a platform application and provide the binary the entitlements we just extracted. Verify it worked with (--sig/--ent) and execute it. Yay, working binary! (Feel free to rename as necessary, again I use fsmon64 or just fsmon.) On newer versions of jtool, ‘platform’ is no longer required.

jtool --sign platform --inplace --ent ~/ent.xml /jb/bin/fsmon.arch_arm64

Reviewing Directories and Files

Quick iOS partition review - take a look at the /etc/fstab file. There are two primary partitions (and a baseband one if you’re into that kinda thing.) The first mounted on / is the system partition where the operating system files are contained. It is theoretically read only as noted by the ‘ro’, however recall that we just put the fsmon binary in /jb/bin. When it comes to jailbreaks that ‘ro’ is more of a reminder of what it is on stock devices. The data partition mounted on /private/var is where all the user data is, this is the primary partition that you’ll be using for your forensic analysis. All native and 3rd party application plists, databases, and other files are located there.

In order to find data associated with a particular application I can use the find command and the bundle ID associated with an app.

find /private/var -ipath *net.whatsapp.Whatsapp*

In the example below, I started looking for WhatsApp data. This is a good initial triage of the applications data, this doesn’t get me all the files but it helps direct me to the related directories. You’ll notice that GUID in the file path, this will be different for all applications across all devices and will change.

Going to the following directories and perusing the data will help me determine what type of data a certain application stores and how it stores it.

  • iCloud Artifacts:

    • /private/var/mobile/Library/Caches/CloudKit/com.apple.bird/57T9237FN3.net.whatsapp.WhatsApp

  • /private/var/mobile/Library/Application Support/CloudDocs/session/containers/57T9237FN3.net.whatsapp.WhatsApp

  • The combination of the ‘Shared App Group’ and ‘Data Application’ directories will generally hold most of the user data associated with an application.

    • /private/var/mobile/Containers/Shared/AppGroup/133904F3-EAA0-48E9-905C-90BB93A7DDA2/

    • /private/var/mobile/Containers/Data/Application/97AD4FDE-2089-455A-8B21-06E4E2225626/

  • It is worth mentioning that if you are looking for the Applications bundle the Bundle ID will not get you there, instead look for the name of the App.

    • /private/var/containers/Bundle/Application/6B6D4621-845A-4EE7-AECF-D68CC00E5C4E/WhatsApp.app/

Finding the right directory/directories for the app in question can be time consuming. One of my favorite tools that solves this issue is cda. Using the same method above, I’ll upload this binary to my device.

As shown below, all you need to provide cda is a search term. I provided it the term ‘whatsapp’ and it provided me the same directories (more even) as above. If you’re not quite sure what you are looking for yet, provide it a single period ‘.’ and get a listing by Bundle ID. It is worth noting that this will only provide app data, if you are doing research on native iOS data you won’t get it using this – time to dig in and find it the hard way.

Looking into one of the WhatsApp directories, I start to get a feeling for what data an application is storing. You will normally find SQLite databases, plist files, media, log files and other files related to an application. ~90% of what you’ll be looking at are SQLite databases and plist files, so I’ll focus on those for this part of the article.

Starting with SQLite databases, we can use sqlite3 (provided by the binpack) on the device to triage the database. My process is to look at the database names first and see if anything is obvious. If I’m looking at a chat app, I’ll look for keywords like chat, message, etc. I’ll focus on WhatsApp’s ChatStorage.sqlite database for this example – seems like a reasonable choice for chat messages. Using sqlite3, I’ll dump the table listing and peruse it for anything interesting. Seems to me that ZWAMESSAGE would contain the chat messages!

Using a SQL SELECT statement I can dump the contents of this table; however this is where sqlite3 may not provide the best analytical view. In this case I might use scp to copy it off the device and use a GUI based SQL viewer to create queries and join multiple tables. (FWIW, I like DB Browser for SQLite). Need a SQLite reference guide, check out the one Heather Mahalik and I created for our classes (FOR585 – Advanced Smartphone Forensics, FOR518 – Mac Forensic Analysis and Incident Response).

I will still use sqlite3 for per-message testing to answer different questions.

  • What does xyz flag in this column mean? Are 0 and 1 the only values?

  • If I send a piece of media, what does it look like in the database? Is it stored in a different table?

  • Message direction or contact information which column stores that?

I will run the same query over and over testing different flags in different columns as I populate data manually on the device. Does this take time? Sure does! But it’s the only way to be sure. Applications are known to change database schemas and values as their application updates.

sqlite3 ProTip: Ctrl+D to quit out of the sqlite3 shell

Next let’s dive into those pesky plist files. For third party applications I will usually start in the applications Preferences directory. The Preferences directory will usually contain a configuration plist file containing some useful bits like usernames, servers, usage timestamps, keys, and passwords. Yes, plaintext, non-obfuscated passwords! 🤷🏻‍♀️

The example below is one for Whatsapp (specifically the one located in the Shared App Group directory. There may be multiple for each application.) I’m using jlutil (Jonathan’s interpretation of plutil, the native macOS utility, to view this plist.)

Another option is plconvert which will convert it to a text file representation and show a less-than-helpful representation on standard out. It will also output to an XML file, here named tmpfile by myself. I consider this slightly less “clean” as it will leave these converted files everywhere on the system, but it’s all personal preference. I will say the plconvert is more useful from a timestamp interpretation and data BLOB stance.

File Monitoring

Say you’re testing a application that has the ability to take photos using the iPhone camera. Where are those photos stored? Or how about if you toggle one of the switches in the Settings application, where is that stored? In a database or a plist file?

An easy way to figure this out is to use a file system monitor, it should at least point you in the right direction. As shown above I prefer to use the fsmon utility from NowSecure, however Jonathan’s binpack includes fs_usage which provides the same data. I prefer the layout of fsmon’s output.

In the example below, I took a picture of my sidekick Elwood with the Camera app on the iPhone while running fsmon. (I’d like to say he helped with this article, but he just slept all day next to me. Cats don’t make the best research assistants.) In the fsmon output you can see that when I took a picture, the “Camera” process saves the picture in a few temp files before it finally saves it in the DCIM directory as an HEIC file (the newer iOS image format) and creates a thumbnail image for the Camera app, PreviewWellImage.tiff.

I hope this helps everyone getting started doing their own iOS forensic research!

(In the rare event anyone wants to see my sidekick Elwood…here is the pic I took. Super Lazy.)

Knowledge is Power II – A Day in the Life of My iPhone using knowledgeC.db

iOS devices may potentially have more personal information and user patterns than their macOS counterparts. People tend to go about their daily lives with their mobile devices rarely being separated from them. In this post I will present to you a day in the life of my iPhoneX – Monday September 10, 2018.

My previous post on the knowledgeC.db database focused more on macOS devices versus iOS, with a few scattered iOS examples. This post will focus entirely on iOS analysis of this database. This database is located on physical dumps of devices in /private/var/mobile/Library/CoreDuet/Knowledge/knowledgeC.db. It is not captured by an iTunes backup.

The first query I would like to do execute for iOS analysis is a simple SQL query to show which “Stream Names” I have. These can provide an idea of what kind of data is potentially available.

SELECT
DISTINCT ZOBJECT.ZSTREAMNAME
FROM ZOBJECT
ORDER BY ZSTREAMNAME

This query outputted the following streams. Some of these I’ve already covered in my previous post. Reading through some of these it is likely you can guess what some of them may contain.

  • /app/activity

  • /app/inFocus

  • /app/install

  • /app/intents

  • /app/locationActivity

  • /audio/inputRoute

  • /audio/outputRoute

  • /bluetooth/isConnected

  • /carplay/isConnected

  • /device/batteryPercentage

  • /device/isLocked

  • /device/isPluggedIn

  • /display/isBacklit

  • /display/orientation

  • /inferred/motion

  • /media/nowPlaying

  • /portrait/entity

  • /portrait/topic

  • /safari/history

  • /search/feedback

  • /siri/ui

  • /user/isFirstBacklightOnAfterWakeup

  • /watch/nearby

  • /widgets/viewed

Application Usage

Let’s start with what apps did I use on that day. I may have hundreds of apps on my phone but in reality, I use only a fraction. I used the following query from my previous post to capture all the “/app/inFocus” entries. I’ve screenshotted the majority of my day to give you a good idea of what this data looks like. My apologies in advance for the lengthiness of this post – but hey, everyone loves pictures! This is just one day’s worth of data, imagine having the same data for up to four weeks! I will never complain about too much data, queries and analysis can help you digest this information.

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"

I started my “day” for this post on 09/09/2018 at 22:04:58, you will notice that all timestamps are recorded in local time (Eastern) as per my SQL query. Note the column ‘GMT OFFSET’ where it shows what time zone this data was recorded in. The following queries may not show some of these columns due to screenshot readability.

Around 22:04 on 09/09/2018 I checked my email (com.apple.mobilemail), Messages (com.apple.MobileSMS), and Twitter (com.atebits.Tweetie2). In fact, I spend a TON of time checking Twitter, I’m always on Twitter.

Next at 22:38:34 I check my Orangetheory app (com.shufflecloud.orangetheoryfitness) to see what time I need to head to the gym in the morning. I opened the app a couple of times trying to convince myself to keep my reservation. I actually cancel my class, I’m too sore from the previous day to go - don’t you judge me! If I’m not going to the gym in the morning – I can sleep in! I access the Clock app (com.apple.mobiletimer) to change my morning alarm.

Around 04:25 on Monday I wake up and can’t sleep fall back asleep so I put in my fancy shmancy Bose Sleepbuds and use the app (com.bose.corporation.bosesleep) to provide me some dreamy white noise. I proceed to wake up a couple times to adjust the white noise type and to change my alarm again (definitely sleeping in, I require lots of sleep to function as a human.) I finally wake at 7:55am to turn off the Sleepbuds and to check Messages and Slack (com.tinyspeck.chatlyio).

At 07:58 in the morning, I turn my alarm off (was set to go off at 08:00) and proceed to check email, Twitter, Weather, and Messages.

While getting ready for work I check Messages, send a Bitmoji using the Bitmoji keyboard (com.bitstrips.imoji.BitmojiKeyboard) in Messages and again check weather (Hurricane Florence is making her way in!)

At 09:49 at work, my coworker asks how my SANS Fantasy Football (com.espn.fantasyFootball) team did. I check my scores - I got destroyed by Alissa’s team. This is going to be a rough season.

Check email again and my calendar.

At 10:42, a quick check of Facebook (com.facebook.Facebook) and check a setting in the iOS Settings (com.apple.Preferences) application.

I got a phone call (com.apple.InCallService) at 11:25 but was unable to pick up as I was eating lunch. I called them back at 11:56 using the Phone app (com.apple.mobilephone), it was a 10 minute call that ended at 12:06. Notice the artifacts of app usage when you leave an app and come back to it 40 minutes later. I was using the Phone app for all of 2 seconds before switching to Facebook to gaze at photos of Stacy’s super cute puppy, Piper.

In the afternoon, I check some Slack, screw around with Settings, email, and of course check Twitter.

Finally commuting home, I need some Apple Music (com.apple.Music) to listen too! I have my phone hooked up to my car with CarPlay and I’m getting directions using Apple Maps (something about that makes the com.ubercab.UberClient.intentsextension go nuts!). I also use Siri in my car to create a Note (com.apple.mobilenotes.IntentsExtension) just after 18:00. (Hands free of course!)

I get home and proceed to listen to Apple Music, check Slack, email, Twitter, etc. I also check to see if I have an Orangetheory class scheduled – sure do! I set my alarm for zero dark thirty, I’m not canceling this one. I also edit a Note and check my calendar before I settle in to write this post.

The ‘app/inFocus’ gives a good rundown of what apps the user uses and when, however it is missing quite a bit of detail that can provide an investigator more context to what exactly the user was doing during these events.

Application Activity

The streams for ‘app/activity’ provide more details on what exactly the app is doing. I used the following query for this data. NOTE: For the sake of getting a readable screenshot, I’ve commented out a few columns (--) in my query below. When running you will just want to uncomment those lines or customize as necessary.

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",
--datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "START", 
--datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "END", 
--ZOBJECT.ZSTREAMNAME, 
ZOBJECT.ZVALUESTRING,
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE",  
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__TITLE as "TITLE", 
datetime(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "EXPIRATION DATE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDCONTENTURL as "CONTENT URL",
datetime(ZSTRUCTUREDMETADATA.ZCOM_APPLE_CALENDARUIKIT_USERACTIVITY_DATE+978307200,'UNIXEPOCH', 'LOCALTIME')  as "CALENDAR DATE",
datetime(ZSTRUCTUREDMETADATA.ZCOM_APPLE_CALENDARUIKIT_USERACTIVITY_ENDDATE+978307200,'UNIXEPOCH', 'LOCALTIME')  as "CALENDAR END DATE"
FROM ZOBJECT
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME is "/app/activity" 
ORDER BY "ENTRY CREATION"

Sunday evening, I checked my email around 22:04:58. What emails was I actually reading? My Inbox - reading a message that I had just been added from the waitlist (hence the canceling of it later! (#DFIRFIT? More like #DFIRSore!)

Next, I changed my alarm to sleep in at 05:52 and to turn it off at 07:58. The activity type of com.apple.clock.alarm is a good way to tell what “screen” a certain app is using. For instance, I was not viewing the “World Clock”, “Stopwatch”, or “Timer” screens.

Around 17:54pm, I start to drive home. I used Maps (com.apple.Maps) through CarPlay to get directions. This is something that is nearly missed if you only look at /app/inFocus entries. The blurred section is my home address which is assigned to “Home” when I ask Siri to take me there.

When I got home at 18:42, I started browsing Twitter. Some apps yield more detailed information than others (other examples in my data include RedFin, Zappos, and Yelp). Each time I clicked though to a particular tweet (versus just aimlessly scrolling), it would record it as an activity type of “com.atebits.Tweetie2.spotlight”, complete with full URL to the tweet.

Piper is a good #DFIRpup.

Finally, I do a few more tasks before sitting down to write this. Set an alarm to get up, check a few days in my calendar and make sure I have the Apple Event in there – I need a new Apple Watch!

Application Intents

You can never have enough context when doing data analysis. We can use the “app/intents” entries for even more detail. I provided the query I used below with some items commented out for screenshot purposes.

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",
datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "START", 
datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "END", 
--ZOBJECT.ZSTREAMNAME, 
ZOBJECT.ZVALUESTRING,
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__INTENTCLASS as "INTENT CLASS", 
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__INTENTVERB as "INTENT VERB", 
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION as "SERIALIZED INTERACTION",
ZSOURCE.ZBUNDLEID,
ZSOURCE.ZGROUPID,
ZSOURCE.ZITEMID,
ZSOURCE.ZSOURCEID
FROM ZOBJECT
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME is "/app/intents" 
ORDER BY "START"

Far more detail is provided in the “app/intents” entries. Messages sent, the “Serialized Interaction” provides message details (contact/message info) that can be correlated with the sms.db. If the Alarms were enabled or disabled, the “Serialized Interaction” BLOB provides the specific alarm GUID info if that is necessary to your investigation. Always look at BLOB data – you never know what you can find. This particular BLOB is an NSKeyedArchiver plist embedded into another NSKeyedArchiver plist – plist inception!

Note the two different Bundle IDs for the Sent Messages:

  • com.apple.MobileSMS – This one is used when I was actually interacting with the Messages application and typing my response.

  • com.apple.assistant_service – This one is used when Siri is “helping”. In my case I was using CarPlay to dictate my messages while on my commute. Siri (w/o CarPlay) looks similar.

Recall that I received a call (but was eating lunch) at 11:25. I called them back at 11:56. The intents are showing a “StartAudioCall” intent when I received the call (but didn’t take it) and when I ended the call at 12:06pm. Without testing, this can be misleading. Always test before making assumptions.

When driving home at 17:59, I created a Note and attempted to append text to that note. I can see the original note creation as well as attempted “Appends”. Sadly, Siri couldn’t understand what I wanted to do when I wanted to append the note, so she just read the title of all bajillion notes that I have (not super helpful Siri!). Some of the text in the “Serialized Interaction” show what Siri interpreted me saying.

When driving home, I asked Siri to take me “Home” and she populated the directions to my place in the Maps application. While you see an “EndNavigation” intent you may not necessarily see a “StartNavigation” intent. It depends on how you initiate the driving directions.

When I got home I also wanted listen to some NPR news through Apple Music app. This shows a Search and various “Selects”. Looking into these BLOBS you can see what I searched for and what I selected to listen to. (More on this in a bit.)

Finally, I append what I wanted to in the Note, manually this time (no thanks to Siri). I also update a calendar entry.

Device Status

The /device/* streams track the device’s status such as whether the device is plugged in, locked, and what the battery level is. This data is tracked in other databases as well (See my iOS of Sauron presentation), but I’ll never complain about data redundancy.

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",
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.ZVALUEDOUBLE
FROM ZOBJECT
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME like "/device/%"  
ORDER BY "START"

The example shows when the device is locked (isLocked=1), unlocked (isLocked=0), unplugged (isPluggedIn=0), and plugged in (isPluggedIn=1) in the ZVALUEDOUBLE column. You can also see the battery charging and discharging in line with the plugged-in status.

Extracting just the /device/isPluggedIn events I can distill when this device was plugged into something. Sunday evening, I plugged the device in before going to sleep and unplugged it at 07:55 in the morning. At 08:42 in the morning, I plugged it into my car for my morning commute and unplugged it at 9:15 when I got to work.

In the afternoon I plugged it into my car again at 15:34-16:06 to drive to a different site and then finally plugged it in from 17:47-18:15 for my commute home.

This output doesn’t provide any detail into what the device was plugged into, but other streams can provide some hints.

Audio & Media

This database keeps track of what is playing (depending on the app) and how. The SQL query below is a good “all in one” query to use for all the audio and media events. The query examples I provide in screenshots have been edited down to be viewable.

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",
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_DKAUDIOMETADATAKEY__IDENTIFIER as "AUDIO IDENTIFIER",
ZSTRUCTUREDMETADATA.Z_DKAUDIOMETADATAKEY__PORTNAME as "AUDIO PORT NAME",
ZSTRUCTUREDMETADATA.Z_DKAUDIOMETADATAKEY__PORTTYPE as "AUDIO PORT TYPE",
ZSTRUCTUREDMETADATA.Z_DKBLUETOOTHMETADATAKEY__ADDRESS as "BLUETOOTH ADDRESS",
ZSTRUCTUREDMETADATA.Z_DKBLUETOOTHMETADATAKEY__NAME as "BLUETOOTH NAME",
ZSTRUCTUREDMETADATA.Z_DKNOWPLAYINGMETADATAKEY__ALBUM as "NOW PLAYING ALBUM",
ZSTRUCTUREDMETADATA.Z_DKNOWPLAYINGMETADATAKEY__ARTIST as "NOW PLAYING ARTIST",
ZSTRUCTUREDMETADATA.Z_DKNOWPLAYINGMETADATAKEY__GENRE as "NOW PLAYING GENRE",
ZSTRUCTUREDMETADATA.Z_DKNOWPLAYINGMETADATAKEY__TITLE as "NOW PLAYING TITLE",
ZSTRUCTUREDMETADATA.Z_DKNOWPLAYINGMETADATAKEY__DURATION as "NOW PLAYING DURATION"
FROM ZOBJECT
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME like "/audio%" or ZSTREAMNAME like "/bluetooth%" or ZSTREAMNAME like "/media%" 
ORDER BY "START"

Starting with audio output and input, where we can get an idea of what type of device this phone was plugged into and how the audio directed.

This is an example of my afternoon commute. I plugged my phone into my car and used CarPlay. You can see indications of CarPlay and the iPhone Microphone. You may see this audio input go back and forth between CarAudio and Microphone in instances where the user is using Siri to do dictation for SMS messages, Maps, Music, Notes, etc. Just after I got home I listed to audio on my iPhone using my AirPods, note the “/Bluetooth/isConnect” connection switch.

It is worth noting here that my Bluetooth Bose QC35s show up in the Bluetooth/Audio events, but my Bose Sleepbuds did not.

Now that we know how the audio is routed, what was I listening to at the time? Everything I listen to gets recorded in (embarrassing) detail. On my commute to work I decided to get my day started with the 90s Radio Station in Apple Music. You can determine if I listened to a song or skipped it by looking at the ‘Usage is Seconds’ column (Sorry Spice Girls, you’re just not my jam). Looking at the data you may think I’m a huge Cher fan for listening to Believe for 22758+ seconds but remember it will record time from last “usage”. When I plugged my phone back into my car and it started playing where it left off.

On my jaunt between work sites, I put a podcast on and listened to the whole thing (~29 minutes). The “Usage in Seconds” column makes it look like I skipped it if you go with the logic that I presented with the Apple Music songs. It appears not all audio apps will store the data the same. This is an important caveat and another reason to test!

After I was done with my podcast, I switched back to music – the 80’s Radio Station this time. After work, I felt like I needed to rock out, so I asked Siri to “Play Muse”. The “Loading…” title gets recorded and a custom Muse playlist started playing.

Installed Apps

As expected this one keeps track of installed apps! Nothing more, nothing less. It does not appear to keep track of app updates.

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",
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
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME is "/app/install" 
ORDER BY "START"

Other Streams

A few other streams of interest:

  • /app/locationActivity – Contains location data, but not exactly what you think. In the examples I’ve seen on my own phone it was Redfin MLS locations – not where I was at a given moment.

  • /display/isBacklit – Was the display on or off?

    • 0 = no

    • 1 = yes

  • /display/orientation – How was the devices being viewed?

    • 0 = landscape

    • 1 = portrait

  • /safari/history – Safari history, same as macOS – see my previous article.

  • /watch/nearby – Determines if watch is within communication distance or not.

    • 0 = no

    • 1 = yes

  • /widgets/viewed – Swipe right to view widgets, it will show how many were “viewed” but not exactly which ones.

Now what?

Correlation! This database holds a serious amount of data and it can be easy to get tunnel vision. Think about correlating this data with the location data I’ve presented in other presentations and blog articles. Where was the user when they were looking at a specific app or browsing to a specific website? Were they driving distracted and watching YouTube when they shouldn’t have? If the user was using a specific app during a time of interest, go to that app’s data and look to see if it may contain data relevant to your investigation.

Knowledge is Power! Using the macOS/iOS knowledgeC.db Database to Determine Precise User and Application Usage

Having access to precise and granular user and application usage can be extremely useful in a forensic investigation, some of which are listed here. I find that pattern-of-life data is some of the most useful information on a device - it really does tell the story about a user and their devices. I've done fairly extensive research on this previously on iOS (much of which can be used for macOS as well) but have yet to really dive into this database.

  • Application Usage
  • Application Activities
  • Safari Browser History
  • Device Power Status
  • Lock Status (iOS Only)
  • Battery Usage (iOS Only)
  • App Installations (iOS Only)
  • Audio Status (iOS Only)

We can use some of these records to help answer a myriad of investigative questions or just about any type of investigation.

  • What applications did a particular user use? How often do they use this application? How long are they using this application for?
  • What websites did they visit? Are they doing research and putting this information into another application?
  • Where did they ask for directions? Are they driving distracted?
  • How often do they check their email? What led them to click on a specific email and infect themselves?
  • How often are they chatting? Who are they chatting with?

The knowledgeC.db database can be found on macOS and iOS devices. On Mac systems there will be a system context database located in the /private/var/db/CoreDuet/Knowledge directory, while a user context database is located in the user’s ~/Library/Application Support/Knowledge/ directory. *Update 08/7/18: The data in this article is specific to macOS 10.13 and iOS 11. Other versions may contain the same data but the schemas/contents may be slightly different. A note about iOS 10 database is at the end of this article.

On iOS there is only one main knowledgeC.db database located in /private/var/mobile/Library/CoreDuet/Knowledge/ that appears to merge the contents of the user and system context databases found on macOS. (Note: Others may exist for other applications, they are not covered here but follow a similar database schema.) It is worth noting that this database only appears to be available on a physical acquisitions and/or jailbroken iOS devices. I have not seen it in iTunes-style backups. 

The database has many tables which have many columns. This article will only go over three of these that I have found to be particularly interesting. I encourage you to look at your own data to discover other items of investigative value. Timestamps in this database use the Mac Epoch time (01/01/2001 00:00:00 UTC).

ZOBJECT – Contains potentially thousands of usage entries for approximately 4 weeks. I will use this table as my primary table of analysis and add on other tables as needed throughout this article. Other tables that ZOBJECT entries may reference are located in these tables:

  • ZSOURCE – Source of the ZOBJECT entries
  • ZSTRUCTUREDMETADATA – Additional metadata associated with ZOBJECT entries

Taking a look at ZOBJECT, you’ll see that entries appear to have a “type” associated in the ZSTREAMNAME column. Using the following tiny SQLite query, I can see what “types” of entries I’m dealing with.

SELECT
DISTINCT ZOBJECT.ZSTREAMNAME
FROM ZOBJECT
ORDER BY ZSTREAMNAME

The output of this on the system context knowledgeC.db database on macOS shows the following “types”:

  • "/activity/level"
  • "/app/activity"
  •  "/app/inFocus"
  • "/app/intents"
  • "/device/isPluggedIn"
  • "/display/isBacklit"
  • "/safari/history"

An iOS example shows the following “types”:

  • "/app/activity"
  • "/app/inFocus"
  • "/app/install"
  •  "/app/intents"
  • "/audio/outputRoute"
  • "/device/batteryPercentage"
  • "/device/isLocked"
  • "/device/isPluggedIn"
  • "/display/isBacklit"
  • "/display/orientation"
  • "/inferred/motion"
  • "/media/nowPlaying"
  • "/portrait/entity"
  • "/safari/history"
  • "/search/feedback"
  • "/user/isFirstBacklightOnAfterWakeup"
  • "/widgets/viewed"

 Application Usage

Let’s start with just the “/app/inFocus”. This “type” is available for macOS and iOS and will show us what application is being used at a given time. I will use the following SQL query for this.

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"

This query is only using data from the ZOBJECT table (no JOINS needed yet!) I’ve filtered out only entries that contain the “/app/inFocus” in the ZSTREAMNAME column using a WHERE statement. I sorted the output by the “START” timestamp using the ORDER BY statement.

For all timestamps in these queries, I have to add 978307200 to convert a Mac Epoch to a Unix Epoch value. SQLite has no idea what a Mac Epoch is – this is a handy conversion to use if you are doing any Mac/iOS forensic database analysis, you will see it very often. I’ve converted all timestamps to my local system time for sanity reasons (you can also choose to use UTC here if you are of the ‘UTC or GTFO’ persuasion). In my testing I have found the timestamps to be accurate.

I’ve extracted the following columns:

  • ZCREATIONDATE - When the entry was written to the database.
  • ZSTARTDAYOFWEEK – What day did this entry occur. I created a CASE statement here to make an analyst friendly output of the days of the week instead of numbers 1-7.
  • ZSECONDSFROMGMT – The GMT offset in seconds. I converted this to an GMT offset in hours by dividing by 3600. This is useful if you have a device that has travelled time zones in the last 4 weeks.
  • ZSTARTDATE and ZENDDATE – A start and end timestamp for the entry. The following “USAGE IN SECONDS” “column” is not a true database column. I’ve done the math between the START and END dates to quickly see usage times in seconds.
  • ZSTREAMNAME – The “type” of entry.
  • ZVALUESTRING – The bundle ID for the application.

The output example above shows a few items of interest.

  • No entries were recorded on Friday 08/03/2018. (I can in fact take a whole day off and not use my laptop – crazy right?!)
  • In these (very) few entries shown, I spend a decent amount of time using Google Chrome (com.google.Chrome).
  • The com.apple.loginwindow entry is tracking time between device logons. Since I didn’t log in on Friday, this “app usage” time is a quite long (~35 hours, divide by 3600 to get number of hours). During this time the laptop lid was closed and just sitting idle.
  • Other applications that I used during this period include:
    • Calendar (com.apple.iCal) – Very briefly, it was only a few seconds – perhaps I just switched to a desktop with the application open. I tend to flip between many desktops – each with a different app open on it.
    • Apple Mail (com.apple.mail)
    • Messages (com.apple.iChat)

Since some data can appear slightly different I will bounce between macOS and iOS examples. This example is from an iOS device.

The examples above is a tiny fraction of events that took place on my system. Once you take the entirety of the data available (4 weeks!) you can start to see patterns. 

  • When is the device in use?
  • What applications are used?
  • How applications used? How often, how long?

This data is great but there are of course caveats associated! This application usage is really only for GUI-based applications. If your users are always in the Terminal, you’ll see plenty of time in com.apple.Terminal. 

Another issue is user attribution. The first example came from the system level context database on macOS how can I associate this usage to a particular user? (More on this in a bit. The second example came from an iOS device. There is only one “user” for iOS devices.)

Application Activities

Using another stream “type” - “/app/activity”, I can add more context to what is happening on the device. Using the same query above, I’ll add a few more items. 

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__USERACTIVITYREQUIREDSTRING as "ACTIVITY STRING",
datetime(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE+978307200,'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"

I performed a LEFT JOIN on ZSTRUCTUREDMETADATA as the activity entries contain some extra metadata info (this is where I can get more context.) From this new table I’m extracting the following:

  • Z_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE – What is the user viewing or doing with the application.
  • Z_DKAPPLICATIONACTIVITYMETADATAKEY__TITLE – Name of the item being viewed or edited.
  • Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYREQUIREDSTRING – A combination of the previous two columns above. You may choose to use this one, the previous two, or all three!
  • Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE – An “expiration” timestamp. I’m still researching this one – it always seems to be a month into the future.

It is worth noting the ZSTRUCTUREDMETADATA table has over 100 columns – it is worth it to peruse what data your apps are populating in this table. I’m only extracting a fraction of the column data. Customize these SQL queries as needed for your data, each device, each user will be different.

On the macOS system context example I can now see application activities. On my macOS example I’ve only seen the Apple Mail and Notes applications populate this data. (Note: I’ve cut off the first few columns to make it easier to view for this article.)

  • What email mailbox or message am I viewing?
  • What Note am I editing?

The same query can be used on iOS. However, one line needs to be removed as that column does not exist.

ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYREQUIREDSTRING as "ACTIVITY STRING",

In my iOS example I found you can get more context to Apple Maps data as shown below. (I also saw Apple News populated.) Now you can see what directions I was searching!

Another stream “type” - /app/intents can provide more context to application activity.

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",
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__USERACTIVITYREQUIREDSTRING as "ACTIVITY STRING", 
datetime(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "EXPIRATION DATE",
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__INTENTCLASS as "INTENT CLASS", 
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__INTENTVERB as "INTENT VERB", 
ZSTRUCTUREDMETADATA.Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION as "SERIALIZED INTERACTION",
ZSOURCE.ZBUNDLEID
FROM ZOBJECT
left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
left join ZSOURCE on ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
WHERE ZSTREAMNAME is "/app/activity" or ZSTREAMNAME is "/app/inFocus" or ZSTREAMNAME is "/app/intents" 
ORDER BY "START"

Building on the same query as before, I’m now LEFT JOIN’ing another table ZSOURCE for the ZBUNDLEID column. I also added three more columns from the ZSTRUCTUREDMETADATA table.

  • Z_DKINTENTMETADATAKEY__INTENTCLASS – An activity type
  • Z_DKINTENTMETADATAKEY__INTENTVERB – Activity verb
  • Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION – Binary plist with extra metadata information.

On my macOS system context example I have more context on when Messages are sent, or calls are synced. In my research, I have found that the messages sent timestamps are accurate however the call syncing is misleading. This isn’t likely when the calls are being made but when they are synced to the macOS system. A further look at the BLOB data can provide the appropriate timestamps. (Note: I’ve removed the /app/activity columns to make this data easier to view.)

The BLOB data can be very important but very easy to miss – it depends on how your SQLite viewer of choice shows it to you. I usually use SQLite Database Browser which will show the word “BLOB” for this type of data. I can double-click on this cell and review the data in various formats (Text, Binary, and Image). I use the Binary option for a quick review of embedded binary plists. I can extract this plist and view it in something a tad more analyst friendly like XCode or plutil.

Once extracted I can review the data. I used plutil in this example. It is a serialized NSKeyArchiver plist, with an embedded binary plist (see the hex at beginning 0x62706c69 73743030 = “bplist00”) because why the heck not, yay for binary plist inception! After extracting the hex (again) and saving it as another binary plist I can open it and view its metadata. 

It’s another NSKeyedArchiver plist! For more information on how to parse this type of plist see my article here. It will be worth it, this will provide user context (ie: who is chatting with whom, when, and using which accounts!).

What else do we get on iOS? How about Maps specific data! Was someone driving distracted using many apps? (FWIW I wasn’t, I was a passenger in a car.) The BLOB data for these entries contains geo location based metadata details.

…or some Spotlight interactions?

Safari History

For some reason Safari browsing activity is specifically tracked under its own “type” – /safari/history. Add ‘or ZSTREAMNAME is "/safari/history"’ to the end of the WHERE clause in the previous query to get this information. In this case, the ZVALUESTRING is not a bundle ID but a URL that was visited using Safari. This is another method you can use to attempt to tie a specific user account to application usage. Correlate this data to user Safari history and caches. Similar data will be shown on iOS however the bundle ID will be com.apple.mobilesafari.

Fantastic you might say! Now I have multiple places to look for Safari history entries. Oh, but wait – if the user clears their browsing history, these entries get removed too. Damn….and before you ask – Private Mode browsing doesn’t show up either.

User Correlation

If you’ve read this far, I’ve been focused on the knowledgeC.db database for iOS and the system level context database. Attributing this to specific users may be potentially difficult. On iOS there is really only one “user” but on macOS there could be many. You should also take a look at the user’s knowledgeC.db database to provide context.

It may be possible to correlate entries in each database to determine which user is doing what. Some of the data contains more specific information such as the email or phone number whom they are chatting with or the address searched for in the Maps application. 

This database may also have synced data from associated devices. The items that show a specific iOS version in the OSBUILD column and a GUID in the ZDEVICEID column came from my iPhone X. This includes third-party applications (not shown).

The stream “types” for the ZOBJECT table are /portrait/entity and
/portrait/topic. Items of type “portrait/entity” are fairly self-explanatory, however the “/portrait/topic” are unknown at this time. Each of these start with “Q” in the ZVALUESTRING column. 

The strings in the NAME column are extracted from the email, chat, webpage, app that is being used. Looking at my own data I see everything from names, addresses, contact information, to terms on webpages I’ve viewed, messages I’ve sent, or emails I’ve written.

Still Reading?

I’m impressed! There are many stream “types” that I didn’t get a chance to cover such as the device status and iOS specific entries. I hope to have a Part II out sometime soon!

*Update 08/7/18: The databases presented in this article are for iOS 11 and macOS 10.13. Thanks to a reader, I was informed that the iOS 10 database schema is a bit different but most of the data appears to be the same. the ZEVENT number needs to be paired with the primary key in the ZOBJECT table and the BLOB data in ZSTRUCTUREDMETADATA table is stored in the ZMETADATA column. Thanks to "PN" for the info!

Pincodes, Passcodes, & TouchID on iOS - An Introduction to the Aggregate Dictionary Database (ADDataStore.sqlite)

Have you ever wondered how Apple can put out statistics such as “The average iPhone is unlocked 80 times a day”? How the heck do they know?

Now I do not know for sure that they use this database, however I'd consider putting some cash down on this bet. The Aggregate Dictionary database aggregates (hence the name) certain features of the iOS operation system.

This database is located on the physical device in the path below, and it is not backed up in iTunes/iCloud backups.

  • /private/var/mobile/Library/AggregateDictionary/ADDataStore.sqlitedb

It is worth mentioning here, the ‘dbbuffer’ plaintext file) in this same directory – is exactly what the filename suggests, a buffer for the database. Worth looking at for entries not yet written into the main database.

The ADDataStore.sqlitedb aggregates data on a 7-day per-day basis (first day in, first day out). You will generally see the last weeks worth of data. The day is stored in the column ‘daysSince1970’ which can be converted to human-readable time by using the SQL date function as shown below. It is worth noting this data is being stored in UTC.

I’ve known about this database for a while but have not had the chance to really dig into it. Recently, a reader emailed me about a case they were working on and wanted to know if the passcode was turned on or off on a specific day. Luckily this investigator had a physical acquisition to analyze and the specific day was in the last week of when the acquisition was created. The physical acquisition has many more of these logging databases are available for analysis too! - Want to see more about these awesome databases, check out my iOS of Sauron presentation.

The ADDataStore.sqlite database stores all sorts of seemingly odd settings in the ‘key’ column of the ‘Scalers’ table. The key that I was interested for this readers inquiry was the ‘com.apple.passcode.PasscodeType’ key which stores the passcode type. The ‘value’ column stores a numerical representation of the passcode type last used during each day (UTC time). If the user changes their passcode type throughout the day, it will store the last one configured.

I have enumerated the following passcode types. The example device show above used the 6-Digit passcode, then recently switched to a Custom Numeric passcode.

  • -1 = 6-Digit
  • 0 = No Passcode
  • 1 = 4-Digit
  • 2 = Custom Alphanumeric
  • 3 = Custom Numeric

We can take a look at how many times the device was unlocked successfully or unsuccessfully by using the keys ‘com.apple.passcode.NumPasscodeEntered’ and ‘com.apple.passcode.NumPasscodeFailed’, respectively. The user was mostly successful inputting their passcode, but put the wrong passcode in once (highlighted in pink).

Hrm. Those values look small – the user is probably using TouchID instead of manually entering their passcode. TouchID allows users to enroll up to five fingers to unlock their devices. Note: They still need a passcode to do this, but it allows the user to have a more complex passcode because they can use their enrolled fingerprints to unlock the device for convenience and general usability

Looking at the ‘com.apple.fingerprintMain.templateCount’ key we can determine how many finger “templates” have been enrolled. The example tells us that four fingerprints were enrolled.

By default the enrolled fingers are named ‘Finger 1’, ‘Finger 2’, and so on as shown below. (Nerd Note: When you are in this screen as a user of the device you can check if a finger is enrolled because it will be highlighted in gray as shown - well, I thought it was neat.).

*** WARNING - RESEARCH BELOW IS ONGOING - READ AT YOUR OWN RISK ***

...just kidding...its actually kinda interesting.

I did want to show the complexity of this database therefore I decided to post this as some readers might find it interesting, however don't take my word on this - I can be wrong. In encourage everyone to do their own testing!

This is where it becomes less clear and more research will need to be done. In the Scaler table at any given moment there are many entries that contain the keyword “fingerprint” for all sorts data. I've listed some of the more interesting keys below.

  • com.apple.fingerprintMain.enabled – Binary value – 1 = Enabled, 0 = Not enabled
  • com.apple.fingerprint.countimagesForProcessing – This appears to be the number of times that TouchID was used (whether or not it was used to unlock the device).
  • com.apple.fingerprint.match.autonomousAttempts & com.apple.fingerprint.match.attempts - Match attempts (Not entirely sure the difference as of yet – I think one may be for unlocks (autonomous) and others for other TouchID functions.)
  • com.apple.fingerprint.unlock.touchesTouchIDNotAllowed – How many times a TouchID was attempted but not allowed for some reason, perhaps fingers were the wrong ones or greasy from a hamburger! (c’mon, we’ve all been there)
  • com.apple.fingerprint.unlock.bioLockouts – I found this key to be incremented when I attempted to unlock the device with the wrong finger too many times.
  • com.apple.fingerprint.unlock.passcodeUnlocksNonMandatory – The user put in the passcode, however they were not required to do so.

Enrolling Fingerprints

  • com.apple.fingerprint.enroll.attempts – Fingerprints “enrolled”
  • com.apple.fingerprint.enroll.popup.tooLittleMoves – Apparently I didn’t move my finger enough when enrolling. 

TouchID Passes – Various keys that I’ve seen that show the specifics of how a TouchID match was passed. Many of these are hard to test to explain.

  • com.apple.fingerprint.match.autonomousPassesAfterHomeButton – Incremented when a match was made after the home button was pressed.
  • com.apple.fingerprint.match.autonomousPassesAfterPowerButton – Incremented after boot when TouchID was used (after passcode of course).
  • com.apple.fingerprint.match.autonomousPassesButtonDown – Incremented after the home button was pressed or “pressed” if you have an solid state button.
  • com.apple.fingerprint.match.autonomousPassesButtonLifting
  • com.apple.fingerprint.match.autonomousPassesButtonUp
  • com.apple.fingerprint.match.autonomousPassesButtonUpWithPressureMitigation
  • com.apple.fingerprint.match.autonomousPassesHumid – I assume this one has to do if you are a live body or not.
  • com.apple.fingerprint.match.passesButtonDown
  • com.apple.fingerprint.match.passesButtonUp

TouchID Fails – On the flip side, if a fingerprint fails – we have many keys with many reasons, many with the same characteristics as above but failed.

  • com.apple.fingerprint.match.autonomousFailsBadImageBadBlocks
  • com.apple.fingerprint.match.autonomousFailsCancels
  • com.apple.fingerprint.match.autonomousFailsFingerOffAfterHomeButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchAfterHomeButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchAfterPowerButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonDown
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonLifting
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonUp
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonUpWithPressureMitigation
  • com.apple.fingerprint.match.autonomousFailsNoMatchHumid – I guess I might have been a bit under the weather? ;)
  • com.apple.fingerprint.match.failsNoMatchButtonUp
  • com.apple.fingerprint.match.failsNoMatchHumid

Getting specific, TouchID unlocks appear to be recorded in the ‘com.apple.fingerprintMain.unlock.unlocksByFinger*’ keys.

As far as as I can tell the keys containing “unlocksByFinger” (colored below in yellow) contain the actual number of unlocks, however in the example below the total unlocks were 11, not 22. I am not sure why there are two entries – I’m sure they record different items, however I cannot find documentation to sort out each one. I would rely on the ‘com.apple.fingerprintMain.unlock.unlocksByFinger#Fail’ entries. (As an aside, it’s worth noting that if you get ‘Finger2’ entries, they have an ‘s’ appended ie: com.apple.fingerprintMain.unlock.unlocksByFinger2Fails, maybe a strange type in the code?)

The ‘fail’ in the key would seem to suggest it records “failed” attempts however my testing shows these are in the green highlighted ‘unlocksCanceled’ entries instead.

What’s the ‘QT’ stand for? I have no idea. I tried looking through Apple documentation to find out, but I’ve got nothin’. Suggestions are welcome!

You might think, “Hey, I see Finger 0, Finger 1 in there – that must be the unlocks for each enrolled fingerprint right?”. That’s what I thought, however that was not the case in my testing. In my tests no matter which finger I unlocked the phone with it would be added to the ‘Finger0’ count.

Another key that looks interesting and related are the ‘com.apple.fingerprint.sortedTemplateMatchCount#’ keys. I would have thought these would be equal to the number of times the fingerprint template was used, however it appears that is not the case. I can try two different fingers (each enrolled) and sometimes they show up under the expected number (ie: finger 1 = com.apple.fingerprint.sortedTemplateMatchCount1) and sometimes it won’t – however one of them will increment. Not sure what is going on here.

In conclusion - I’ve only barely touched one of the tables in this database. There is so much data in this database! There is also the ‘DistributionKeys’ and ‘DistributionValues’ tables which store more numerical-based stats versus the incremental/binary stats of the 'Scaler' table. Try the following SQL query on your own and see what you find!

select 
DistributionKeys.key,
date(DistributionKeys.daysSince1970*86400,'unixepoch','localtime') as daysSince1970,
DistributionValues.secondsInDayOffset,
DistributionValues.value
from DistributionKeys
left join DistributionValues on DistributionKeys.rowid = DistributionValues.distributionID
whereDistributionKeys.key like '%unlock%'

I will hopefully be putting out a few more blog entries on other data found in this database (and other databases!). I will of course post updates to this research if and when I get the chance to do it! Stay tuned!