APOLLO

Socially Distant but Still Interacting! New and Improved Updates to macOS/iOS CoreDuet interactionC.db APOLLO Modules

in , , ,

The interactionC.db database certainly does not get as much as attention as its CoreDuet partner in crime, knowledgeC.db. However, I think it has quite a bit of investigative potential. I’ve written about it before in a prior blog, however I’d like to give it more attention here.

I spent this weekend updating the APOLLO modules to have more contextual support and better backwards compatibility with older iOS versions. This database was also introduced to the macOS side with 10.15. 

I’ve added a new query for this database for the ZKEYWORDS table. This tables appears to capture keywords that are contained in various Calendar (com.apple.mobilecal) events. It seems only select certain events as not all my calendar event has keywords in this table.

The main interactionsC.db query has many new updates including attachments and sender/recipient correlation. In general, this database is keeps track of “recent” contact interactions. As an example, I used this query on my iOS database that I copied off of my iPhone a few days ago and it shows ~28,000 entries going all the way back to January of this year, 6+ months! 

In the screenshot below is a Messages (com.apple.MobileSMS) conversation between Heather Mahalik and I. Items that are blurred are our phone numbers. The GUIDs are our Contact Person IDs, these can be correlated with information in the iOS Address Book database. Why some recipient information is blank, I’m not sure. I scrolled way back in our conversation history and the timestamps are spot on. The content of this conversation could (and should) be correlated with the Messages database (sms.db).

This data is not just for Messages and may include other application bundle IDs. Some that I’ve seen in my data include:

  • com.apple.InCallService – Phone Calls

  • com.apple.MobileSMS - Messages

  • com.apple.Preferences - Settings

  • com.apple.ScreenshotServicesService - Screenshots

  • com.apple.mobilecal - Calendar

  • com.apple.mobilemail - Mail

  • com.apple.mobilesafari - Safari

  • com.apple.mobileslideshow - Photos

Contact Interactions & Attachments

For another example, let’s take a look at some interactions for the Photos app (com.apple.mobileslideshow). Not every interaction will have attachments associated with them. The screenshot below contains some AirDrop activity from this device. Some images were AirDropped from the Photos app itself, while one image was AirDropped from within the Messages application as shown in the Target Bundle ID column. It also shows the contact information to whom it was sent - helpful! 

Some of these attachments have a type associates with them in the UTI column (HEIC/PNG), the other has an associated attachment ID, show in hex (0x DCCB49C2FC74461EAD90DAB0C537DBF7). This is a hex representation of the UUID for the image. It seems not every image will have this attachment ID.

This image UUID can be searched for in the Photos database, as shown below to determine which image exactly was AirDropped. 

This might be another good artifact to look for in addition to some of the unified logs I’ve highlighted previously in AirDropping some Knowledge. This is what Elwood’s Phone looks like after lots of AirDropping of various items for that blog. 

Disassociated but not Forgotten Attachments

It seems some attachments are no longer associated with those in the ZINTERACTIONS Table but are not necessarily removed from the ZATTACHMENTS Table. This has potential forensic use as well. APOLLO will not extract these as there doesn’t appear to be timestamps associated with it however, I HIGHLY recommend at least taking a brief look at this table when doing an investigation.

The example below shows may attachment files of different types were associated with a contact interaction.  

  • PDF

  • Images (PNG, HEIC, JPG)

  • Contact VCard

  • Text

  • Archives

  • Movies

  • Documents

Unfortunately, we lose the contact context here. However, we can still find useful tidbits of information like text, file types, URLs, image UUIDs, etc.

Other examples include text that was copy/pasted, or URLs like a Google search link or a YouTube video that was sent.

A Note about Accounts & Recipient Counts

One more example of Mail (com.apple.mobilemail) shows the ACCOUNT column. This GUID can be used to tie interactions to a specific email account in the Accounts databases (Accounts3.sqlite/Accounts4.sqlite).

The Recipient Count columns can be a bit misleading. The column I’ve labeled ‘Recipient Count’ is the amount of recipients on an interaction. This examples shows 2, however that does not include myself. This is an example of an email thread between Heather, Lee, Phil, and myself. I would have thought there would be at least a 3 in this column however that doesn’t appear to be the case. A good example to not make assumptions!

The incoming/outgoing sender/recipient count columns are even more misleading – I haven’t quite figured those out but I believe this might be the total amount of interactions versus the amount of “recipients” on those interactions.

InteractionsC.db can really be a useful database, especially when it is used along with all the other data that APOLLO extracts – context can be everything!

iOS Location Mapping with APOLLO – Part 2: Cellular and Wi-Fi Data (locationd)

in , ,

My previous article showed a new capability of APOLLO with KMZ location file support. It worked great…for routined data, but there was something missing. What about the cellular and Wi-Fi locations that are stored in databases? Well, turns out I need to test better. I fixed the locationd modules to have the activity as “Location” versus “LOCATION”. Case sensitivity is apparently thing in Python…my bad. 🤷🏻‍♀️😉

I should also mention with the fixes, my total location data points for a iOS 12.1.1 device jumped to ~57,000! I should note this is not inclusive of workout locations. Those are a bit different as they are stored as separate records, one for latitude and one for longitude. In the future I might attempt to pair these up for KMZ support.

The previous article showed the routined (user/device patterns) data, I have found these locations to be quite accurate. To be fair, I have not looked at all (40k+) of them specifically, only spot checked. When I visualized the locationd data, I saw some interesting outliers.

The first module I found particularly interesting is the output from the locationd_cacheencryptedAB_celllocation module. Most of the locationd output is kept for about a week. This particular week I traveled from DC to Portland via Chicago for DFRWS and then back to DC (again via Chicago). You will notice that there are some data point clusters around DC, Chicago and Portland as expected – but there are a few scattered data points in the Midwest. I do not remember for sure if my device was in Airplane Mode, but this may be an artifact of potentially being able to access cell towers during my flight. (The locationd_cacheencryptedAB_ltecelllocation module output showed a similar pattern.)

The next module, locationd_cacheencryptedAB_ltecelllocationlocal, shows my path home from fairly accurately. These locations tend to be kept for less time. I was going north on 95, past the DC Beltway (near Springfield, VA) and onto Glebe Road towards Arlington, VA. It may be hard to see in the screenshot but the airport to the right near the Potomac is DCA. Just north of that is the Pentagon and Arlington Cemetery.

Finally, we have the output from the locationd_cacheencryptedAB_wifilocation module. This one has a few more outliers that I have a hard time explaining. I was DC and Chicago in this screenshot but not in Buffalo (NY), Pennsylvania, or NYC.

These are good examples of not completely believing and relying on what you see in the data. I can only imagine forensics cases relying heavily on this data. While I have found routined data points to be far more accurate, locationd on the other hand does have its oddities. Perhaps it is something with how the data is populated or the coordinates being reported by cell towers and/or access points. This requires additional investigation. TL;DR – Don’t assume what you are looking at is the end all be all of the data – always investigate further and correlate with other information.

In other news, if anyone knows more about how this data is populated – I’d love to know. Drop me a note!

iOS Location Mapping with APOLLO - I Know Where You Were Today, Yesterday, Last Month, and Years Ago!

in , ,

I added preliminary KMZ (zipped KML) support to APOLLO. If any APOLLO module’s SQL query has “Location” in its Activity field, it will extract the location coordinates in the column “Coordinates” as long as they are in Latitude, Longitude format (ie: 38, -77). These are more a less an upgrade/replacement from my previous iOS location scripts. (FYI: Those will not likely be updated further.)

You can find more details on the different modules and outputs here. The APOLLO output will also show counts of how many location data points were extracted from each module. An example from my own data contained 41,262 points! Due to the amount of data points and how applications like Google Earth might handle them I’ve decided to split them by module to be loaded separately. It’s not ideal, but my goal is not to crash Google Earth. (FWIW, I’m still working on other solutions, if you have experience in this area - drop me a line.) The most troublesome module (by a large magnitude) is routined_cache_zrtcllocationmo since it keeps track of extremely granular locations for about the last week. Mine had ~38,000 coordinates!

Let’s take a look at some examples! This data was collected on 07/18/2019 on iOS 12.1.1 to give you an idea on timeframe.

This one is from is from the routined_cloud_visit_inbound_start module. The earliest coordinate is from a few months prior from my trip to Amsterdam to teach FOR518 - Mac and iOS Forensic Analysis and Incident Response.

These are some of my “Significant Locations”, you can click on any coordinate and gather more detail. This is the same output that is captured in the APOLLO database or CSV file. 

The next example is from routined_local_learned_location_of_interest_entry. Here you can see some of my travels since 2017! These contain the “Learned Locations of Interest”. You will probably see a bit more historical location data. Looks like I need to visit the middle of the US more!

Last but not least is the massive amount of data points from the routined_cache_zrtcllocatiomo module. This will show nearly exact, granular location for about the last week before collection. Here is my trip to Portland, OR for DFRWS! Not many guesses as to how I got to downtown Portland from the airport is there! (This is the module that produces many, many datapoints. It took patience with Google Earth to even get this screenshot, this is your warning.)

I hope this adds a bit of visual context to some of the APOLLO output, let me know if you have different ideas on how to represent some of this data! Pictures can certainly tell a story where it might otherwise get lost in the noise.