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.