script

On the First Day of APOLLO, My True Love Gave to Me - A Python Script – An Introduction to the Apple Pattern of Life Lazy Output’er (APOLLO) Blog Series

I originally released APOLLO at the Objective by the Sea conference in early November. Since then I’ve received a surprising amount of positive feedback about various analysts using this tool or the accompanying SQL queries on their file system dumps to help a variety of investigations.  

It now time for a proper introduction. I will present this to you in what I’d like to call “The Twelve Days of APOLLO” a holiday themed (very, very loosely) blog series starting today! 

Why

APOLLO stands for Apple Pattern of Life Lazy Output’er. I wanted to create this tool to be able to easily correlate multiple databases with hundreds of thousands of records into a timeline that would make the analyst (me, mostly) be able to tell what has happened on the device.

iOS (and MacOS) have these absolutely fantastic databases that I’ve been using for years with my own personal collection of SQL queries to do what I need to get done. This is also a way for me to share my own research and queries with the community. Many of these queries have taken hours, even days to research and compile into something useful. 

My goal with this script is to put the analysis function the SQL query itself. Each query will output a different part of the puzzle. The script itself just compiles the data into a CSV or SQLite database for viewing and filtering. While this database/spreadsheet can get very large, it is still more efficient that running queries on multiple databases and compiling the data into a timeline manually.

Because this script is all based on investigator provided queries it is highly customizable. If an investigator only wants health data, they can elect to run only those query modules. Each query can be customized by the investigator relatively easy, if you don’t need that column – remove or comment it out! I’ve uploaded many queries that I think most investigators would find useful. It is my hope that other investigators create their own and share them with the community.

How

The script is a simple Python script that intakes what I’m calling modules. Each module is a single SQL query that pulls out specific data from a database. The module will also have some metadata about what the SQL query

The module is a text file that contains a few required items:

  • DATABASE – The exact name of the database to perform the SQL query on.

  • ACTIVITY – What this particular record is categorized as.

  • KEY_TIMESTAMP – The timestamp to be used as the key for timelining.

  • SQL Query – The query that is performed on the database. They query should extract one specific type of record from the database, thus a database may have many modules for very specific outputs.

Depending on what the user is looking for they can run one, some, or all the modules and it will output to either a CSV or SQLite database file.

Example Usage

The script is a simple python script that only takes only a few arguments. 

python apollo.py -output {csv, sql} <modules directory> <data directory>

There are two output options, a CSV file or a SQLite database. Please let me know if other outputs are required. Following that, the path to the module’s directory and the path to the data directory. The data directory can be a single directory full of the databases needed to parse or a full file system dump – the script will find the databases by name in the module.

An example of the output is seen below. Each SQL query run will have different output for that particular query and database in the output column. In the example we see everything from health steps/distance, to location, to application usage. The Key column contains the timestamp that each record is organized on, the Activity contains the type of record. The database and module columns contain which database and module parsed that information.

Challenges 

One of the main challenges with these Pattern of Life databases is access. Most of the really good forensically useful POL data is not easily accessible, particularly with iOS devices. With macOS devices we may need to deal with FileVault encryption or database level encryption. This script assumes you have good data to work with. Some databases may be available some may not be.

Feedback

While I primarily created this script for my use (don’t we all), I am open to feedback. If instead of using ConfigParser module files you want something else, let me know. If you need different output formats, let me know. I will consider all feedback.

I would appreciate all the help I can get. I’ve primarily tested these queries with a focus on iOS 11, however I know many will work on older versions and some have been tested with available data from iOS 12 (iOS Health). I’ve also tested running the script on macOS with Python 2.7 installed, it may not run as expected on other platforms. (FWIW: I will upgrade to python3 when macOS does.)

The script was just updated to be more efficient by Sam Alptekin of @sjc_CyberCrimes. Sam made the script much faster when running against full file system dumps. He was able to take it down from hours to run to mere minutes. Thanks Sam!

The Next Eleven Days

Each day will have a different topic that will guide you through the usefulness of the APOLLO framework. I focus primarily on iOS in these articles, but many of the queries can be ported over to macOS as well. I do intend to work on this in the future however I will discuss more on improvements later.

I will cover all sorts of topics in the next couple of weeks.

  • Device State

  • Media

  • Health

  • GUI Artifacts

  • Network and Communications

  • Connections

  • Application Usage

  • So much more!

Get APOLLO here and take it for a spin!

Day 2: On the Second Day of APOLLO, My True Love Gave to Me - Holiday Treats and a Trip to the Gym - A Look at iOS Health Data

Day 3: On the Third Day of APOLLO, My True Love Gave to Me – Application Usage to Determine Who Has Been Naughty or Nice

Day 4: On the Fourth Day of APOLLO, My True Love Gave to Me – Media Analysis to Prove You Listened to “All I Want for Christmas is You” Over and Over Since Before Thanksgiving

Day 5: On the Fifth Day of APOLLO, My True Love Gave to Me – A Stocking Full of Random Junk, Some of Which Might be Useful!

Day 6: On the Sixth Day of APOLLO, My True Love Gave to Me – Blinky Things with Buttons – Device Status Analysis

Day 7: On the Seventh Day of APOLLO, My True Love Gave to Me – A Good Conversation – Analysis of Communications and Data Usage

Day 8: On the Eighth Day of APOLLO, My True Love Gave to Me – A Glorious Lightshow – Analysis of Device Connections

Day 9: On the Ninth Day of APOLLO, My True Love Gave to Me – A Beautiful Portrait – Analysis of the iOS Interface

Day 10: On the Tenth Day of APOLLO, My True Love Gave to Me – An Oddly Detailed Map of My Recent Travels – iOS Location Analysis

Day 11: On the Eleventh Day of APOLLO, My True Love Gave to Me – An Intriguing Story – Putting it All Together: A Day in the Life of My iPhone using APOLLO

Day 12: On the Twelfth Day of APOLLO, My True Love Gave to Me – A To Do List – Twelve Planned Improvements to APOLLO

Slides and Script! From Apple Seeds to Apple Pie & Introducing APOLLO: The Apple Pattern of Life Lazy Output'er

I had the privilege and honor to present at the first ever Objective by the Sea Mac Security Conference yesterday in Maui (hardship, right?). It was only the first day and it was absolutely spectacular, I may have to make this one a regular! I can easily recommend attending this conference.

I presented From Apple Seeds to Apple Pie - an Apple Pattern of Life talk (mostly focused on iOS devices). You can find the slides in my Resources section.

I also just released a (very) beta version of APOLLO (Apple Pattern of Life Lazy Output’er) on my GitHub page. The TL;DR of the script: Take all the creepy databases that Apple writes events to, perform individual SQL queries on them to pull out investigative useful data, and combine them into another SQLite database for easier/quicker analysis and correlation.

This script and its modules are still in the testing phases so please be careful when using this on real cases. Expect more modules and testing to be released, I’m holding some back due to some timestamp issues and other are partially written up.

Script Update - Mac MRU Parser v1.5 - Added Volume Analysis Support and Other Stuff!

Get the script here!

Added volume analysis support for the following plists. These are not really MRUs but it could be damn useful to gather this info.

  • Sidebar List plist [10.12-] - /Users/<username>/Library/Preferences/com.apple.sidebarlists.plist
  • Favorite Volumes SFL2 - [10.13+] /Users/<username>/Library/Application Support/com.apple.sharedfilelist/com.apple.LSSharedFileList.FavoriteVolumes.sfl2
  • Finder Plist - /Users/<username>/Library/Preferences/com.apple.finder.plist
    • I was parsing FXRecentFolders Key, I've added FXDesktopVolumePositions Key
    • FXDesktopVolumePositions has a volume creation timestamp embedded into the "file" name for the volume (highlight in yellow below). This has been extracted and converted to human readable time (highlight in orange below).

Thanks to @4n68r for pointing out broke stuff (and for using the script!)

  • The ICNS Icon export function was also fixed.
  • Fixed support for some *.sfl2 files that were cranky and had no names.
  • Documentation/Spelling

Script Update - Mac MRU Parser - Spotlight Shortcuts & BLOB Parsing!

Get the script here!

Added in Spotlight Shortcuts

I've updated my macMRU.py script to parse the Spotlight Shortcuts plist file that I consider to be very MRU-like. This plist file contains what the user typed into the Spotlight search window, what they clicked on, and when this all occurred. You can find artifacts of application usage, documents, emails, photos, etc.

Bookmark and Alias BLOB Parsing

The second part of the update (and probably the most requested!) was to add in arguments to allow the analyst to see the parse out the Bookmark and Alias BLOB data. Previously the script only had an option to show the BLOB hexdump. Using mac_alias [download here], the script can now parse these BLOBs. This comes with a caveat, this script relies on the documented structures by others (with a few minor changes by my own research). These have not been officially documented by Apple therefore YMMV. Many of us are still researching these, hopefully this script will help the process! This update provides three new arguments:

  • --blob_hex = This is the same as what --blob was before. Just a hexdump of the BLOB data.
  • --blob_parse_raw = This shows the raw output from mac_alias, not exactly the easiest to read but good for debugging.
  • --blob_parse_human = This shows the pieces of the BLOBs in a (mostly) human-friendly (example below).

I want to say thank you to a few folks that allowed these additions to be made:

  • Alastair Houghton & Wim Lewis - For mac_alias and the reversing the Alias/Bookmark structures. I took a shot at it years ago and gave up, really appreciate someone else's effort!
  • Phill Moore of thisweekin4n6.com - You'll notice some support for legacy keys in the com.apple.recentitems.plist have also been added in this update thanks to Phil and his oddly old MRUs files! :) I also appreciate his testing of this tool on Windows also. (Sorry Phil, didn't get a chance to test it on Windows yet!) 
  • Shout out to Patrick Olsen for his work on Alias V3 structures. Unfortunately I did not get to use his research - turns out V3 Alias BLOBs do not get used in any of the MRUs that I'm parsing in this script (they are all V2)! Maybe in the future!