Friday, March 8, 2019

UsRT - Graphical interface for Android Usagestats and Recent Tasks XML parsers.

Introducing UsRT

Thanks to the hard work of Chris Weber (@RD4N6) we now have a way to parse the essential data contained in the Android Usage Stats and Recent Tasks XML files through a graphical interface. Like Eric Zimmerman says it is agent proof. Chris took my scripts, based on the research done by Jessica Hyde (@B1N2H3X), and made them accessible to all. Point and click goodness.

The application can be run as an executable (UsRT.exe) via the provided installer or through the python scripts directly. The installer has all dependencies included and is the easiest and fastest way to use the parser.

For details on the original research that motivated these scripts and the interface see Jessica Hyde's research at the SANS DFIR Summit 2018. For details on the parsing scripts see my previous blog posts for Usage Stats and Recent Tasks.

Script and installer links at the end of the blog post.

Features for Usage Stats:
  • Case information fields

  • Visual listing of files as they are processed in the left bottom corner of the interface

  • Rows and columns format with the ability to hide columns and select all rows, check rows or unchecked rows.

  • HTML reporting

  • Ability to open already processed cases through the application generated case json file.
  • Included Read Me file that has a quick overview on usage with related screenshots. The Read Me can be accessed via the Help menu options.
Features for Recent Tasks:
  • Same features as Usage Stats with the addition of the recent images and snapshot fields. Pressing on the images will show them in your system's default image viewer. HTML reporting include images as well.

Repository and installer

To get the scripts go to the following repository:
The installer is in the same repository in the release tab. 

As said at the beginning of the post I am indebted to Jessica Hyde for doing the original research and to Chris Weber for putting all work an effort to maximize the use of the parsing scripts by making an awesome graphical interface for them.

As always I can be reached on twitter @AlexisBrignoni and email 4n6[at]abrignoni[dot]com.

Saturday, March 2, 2019

iOS Bplist Inception

Update 03/21/2019:
Script now decodes NSdata contents. See details at

Short version:

Python 3 script that export compound bplists from a specific field on a iOS knowledgeC database, extracts the internal bplist and creates a triage html report of its contents. Two versions are provided, for iOS 11 and iOS 12, due to a slight difference on how the internal bplist is referenced within the external that holds it.

The scripts can be found in the following location:
It's recommended that you load these plists into your viewer of choice to examine them directly.

Long version:

Like most DFIR things lately this one also started with Phill Moore. He reached out to the community on the following:

Since I've been on a data parsing binge of lately I was happy to try and assist. As I was reading the replies to Phill's tweet I was reminded of how, of all the data structures utilized by Apple products, bplists are one of the most prevalent. So prevalent that these can be contained within SQLite databases and they themselves contain other bplists within them. Total data storage inception. At this point there was no doubt...

Thanks to kind souls like @i_am_the_gia@ScottVance, and others who will remain anonymous; we got test data to see if we could do the following:

  1. Export the bplists intact from the SQLite DB.
  2. Extract a bplist (clean) from the bplist that holds it (dirty.)
  3. Access the clean bplist and create a file that could be used in forensic tools for analysis.
  4. Generate a triage report of clean bplist data contents to easily evaluate relevance before importing to forensic tools.
There are many tools that let us view the contents of bplists but when these are nested in such a way getting to the internal content requires some manual work. Like any and all examiners the world over manual work is just the universe telling you there is a need to automate and scale. 

The database selected for our testing was the iOS knowledgeC database. I highly recommed everyone reads Sarah Edwards' article on it, THE article on it. By looking at the Z_DKINTENTMETADATAKEY__SERIALIZEDINTERACTION field within the ZSTRUCTUREDMETADATA table can see how these bplists look when nested.

Notice how there are two bplist headers in the same SQLite database content. 


Exporting the data was a straight forward action. Regular SELECT and assigning the content of the field to a variable that would be written to a file. For this to work the receiving file has to expect binary content. As seen in the next image the extracted bplist are named in the following convention:
  • D/C = Dirty or clean There is nothing wrong or dirty about the shell bplist. It is a shorthand in opposite to the internal bplist which I called clean after extraction due to a lack of its bplist shell. 
  • Z_PK = The field name in the table that contained the primary key for the row that contained the exported bplist.
  • Numeric value = Integer contained in the Z_PK field for the row that contained the exported bplist.

By establishing this filename convention the examiner can easily backtrack to the proper row from the target table if additional fields are of interest or if there is a question on the validity of the exported bplist.


Now that we had exported the bplist we had to get to the clean one in a automated way. Thanks to @firmsky I was reminded of an article by Sarah Edwards on the use of ccl_bplist for the parsing of NSKeyedArchiver bplists in Python. These bplist objects are beyond the scope of this blog but just know that I am grateful that Alex Caithness came up with this module that saved me from experiencing a painful headache. You can find this great module here:
With this module in hand and some test data we figured out that:
  1. In iOS 11 one has only to deserialize the bplist at the root which gives you the clean bplist.
  2. In iOS 12 one has to desiralize the bplist at the level since the clean bplist is contained within it.
The previous was a long way of saying that in iOS 11 the following key piece of cll_bplist function
CleanBplistFile = ccl_bplist.deserialise_NsKeyedArchiver(DirtyBplistFile)
would give you the clean bplist ready to write out where as the following code
ns_keyed_archiver_objg = ccl_bplist.deserialise_NsKeyedArchiver(DirtyBplistFile)
CleanBplistFile = (ns_keyed_archiver_objg[""])
would give you the clean bplist after accessing the portion. It would be good to have further confirmation that these type of incepted bplist truly vary per iOS version and that is not only a crazy coincidence of the the data sets we had available.

Originally the purpose of this exercise was to find a way to easily extract the clean bplists in order to import them into forensic tools with minimum effort and no manual extraction. It became clear that a triage report was needed when one of my data sets contained 1565 extracted bplists. Be aware that the script developed will keep both the dirty and clean bplists in separate folders within a timestamped directory. In this way one can backtrack the whole process for validation purposes.


With a triage report that shows the content one can decide which set of bplists should be drilled down more or just retained due to work or case relevance. The fields on the html formatted report are the following:
  • Filename = Same format as stated before.
  • Intent Class = This is a value taken from a field in the table where the dirty bplists where stored in the knowledgeC database. This value is key cause it gives you a clue of the purpose of the contents of the bplist.
  •  Intent Verb = Another value taken from one of the table fields. Further description of bplist purpose and/or type of content.
  • NSstartDate = Time stamp.
  • NSsendDate = Time stamp.
  • NSduration = Float value.
  • NSdata = Binary data store of activity.
Since the report is a triage report the NSdata values are just a string representation of the binary values in it. Although it contains many non human readable characters it is pretty easy to key in on those ASCII values that one can easily read. The report is a testament to my ignorance on how to convert these values to something more pleasing to the eyes, but for triage purposes that help the examiner decide what to process with a forensic tool further it is perfect. Some of the values can cleaned up a little with UTF-8 decoding but many, especially those that contain a lot of data, are not.

The next picture is an example of the report format. The particular data in the report was shared with the condition that it would not be share hence the redaction of it.

It is up to the reader to test it out and discover for herself what awesome data resides in these structures. Things that are, things that were in one form and changed to another, and things that are no more.

Future work

I was surprised by the amount of data contained in just one field from one table in one database. I can only imagine what relevant data resides in incepted SQLite held bplists in other tables and other databases. The next step is to evolve the script so it can extract any bplist blob from any SQLite table and generate dirty and clean instances as needed with complementing reports for triage. A key part is to better better understand how the NSdata fields work to see if anyone in the community knows how to parse them.  If only the days had more hours and our bodies less need for sleep.

As always I can be reached on twitter @AlexisBrignoni and email 4n6[at]abrignoni[dot]com.

Tuesday, February 19, 2019

Android Recent Tasks XML Parser

This post is a continuation of my last blog post where I introduced a simple parser for the Android usagestats XML files.
In this entry I am introducing a parser for the Android recent tasks XML files. Like the previous parser it is based on the research done by Jessica Hyde that she presented at the SANS DFIR Summit 2018. You can see her excellent presentation here:
YouTube: Every Step You Take: Application and Network Usage in Android 
The presentation slides, in PDF format, can be found here:
PDF Slides: Every Step You Take: Application and Network Usage in Android 
As explained in the presentation the Recent Tasks XML files record the following activities for recently used apps:

  • Task ID number = Used to correlate snapshot and recent image files.
  • Effective UID = App identifier.
  • First active time = Timestamp in millisecond epoch time.
  • Last active time= Timestamp in millisecond epoch time.
  • Last time moved = Timestamp in millisecond epoch time. 
  • Affinity = Bundle ID name.
  • Calling package = Bundle ID or process that called the referenced recent task.
  • Real activity = Gives information on app usage at time of recording and snapshot creation.
These XML files are located in the following directory:
In addition to these XML files, recent tasks can produce snapshot images as well as recent images. Details about these are contained in the previously referenced presentation. These images can be found in the corresponding directories:

In order to leverage the data contained in these XML files and images I made a parser in Python 3 that takes the XML information and puts it in a SQLite database for ease of querying. The script can be found here:

The script has been tested and found to be accurate on my own data sets. Not all recent tasks will contain all data events or related images. Additional testing and validation of the script is humbly requested and more than welcomed.

Script usage

1. Extract from your Android source device the three directories mentioned previously. Extraction should be logical and not contained forensic tool generated recovered items like deleted and/or file slack files.

2. Place the script and the noimage.jpg files from the repository in the same root directory as the extracted directories.

Have this before running script.
3. Run the script with no arguments.

Script is done.
4. When completed the script will generate two files, a SQLite database named RecentAct.db and a report file named Recent_Activity.html.

What you should see after a successful run of the script.

Note that the RecentAct.db SQLite file will contain two fields populated with all the XML attributes in JSON format. The analyst can run a query using JSON_extract to custom generate queries with any of the attributes within the XML.

5. Open the Recent_Activity.html report.

Sample report entry.
For every recent task there will be a table with pertinent information as well as the snapshot and recent image files that correspond to it. To view the images full size just click on them. Be aware of the importance of the creation times of these image files within the source media. For details see the presentation previously mentioned.

It is of note that not all recent tasks, in some of my test data samples, had corresponding images or full sets of attributes. When a recent task lacks corresponding images the script will reference the file.

Missing image and missing attributes.
For missing attributes the report will state 'NO DATA' and/or 'NO IMAGE' in the Key and Values columns as needed. Be aware that the SQLite database has all attributes in JSON format for custom query generation.


I want to thank again Jessica Hyde for her research and for making the community aware of these artifacts. Hopefully this script can make it easier to give much needed context to these images and apps whose value might not be found anywhere else on the source device.

As always I can be reached on twitter @AlexisBrignoni and email 4n6[at]abrignoni[dot]com.

Sunday, February 17, 2019

Android Usagestats XML Parser

As I've been testing and using Sarah Edwards' excellent APOLLO pattern of life framework for iOS I reminded myself of the great work done by Jessica Hyde on a similar set of files for Android called usagestats. These files provide insight on what apps where being used, if they were in the foreground or background, and how long have the apps been active among many other forensically interesting data points.

In her presentation for the SANS DFIR Summit 2018, Jessica Hyde explains how the usagestats XML files record the following activity from Android devices:
  • User interaction
  • Move to foreground
  • Move to background
  • Configuration changes
I highly recommend the reader check out her Every Step you Take DFIR Summit video and presentation slides on PDF format. The rest of the blog post will make more sense after the viewing and/or reading of her work.

In order to leverage the data contained in these XML files I made a parser in Python 3 that takes the XML information and puts it in a SQLite database for ease of querying. The script can be found here:
The script has been tested and found to be accurate on my own data sets. Additional testing and validation of the script is humbly requested and more than welcomed.

Script usage

Extract from your source Android device the following directory:
Export the usagestats directory
 Place the script at the in the same root directory as the just extracted usagestats directory.

Side by side as such.
Run the script with no arguments from the root directory that contains both the script and the usagestats directory. The script will parse all the internal directories and files for you.

No arguments are needed.
The script will also alert you of files whose content is not XML that can be parsed. After the script ends a sqlite database named usagestats.db is generated.

Data is served.
Use your favorite SQLite application to view the parsed data. Notice the timestamps are in epoch time.

Notice the fields.
The generated database contains a table named data with the following fields:


Each XML file contains a description of what type of data is recording. The values can be event-log, configuration, and packages.


Records when an app (package) was last active or when a configuration took place. The XMLs themselves keep track of these time in two ways. Most usage events maintain a count of how many milliseconds have passed since the creation of the XML file and the occurrence of the event. To calculate the timestamp of the event the script takes the XML filename, which is the epoch time of the file itself in milliseconds, and adds to it the milliseconds the event took to occur. This provides the event time as en epoch timestamp. For events that are not millisecond offsets from the epoch time filename of the XML file, they keep the time as en epoch timestamp preceded by a minus sign. The script eliminates the minus sign to keep the epoch timestamp. My testing has shown this way of calculating times to be accurate to activity I have taken on the device.

The following image was included in an app review I did in November for the TikTok Android application. Notice the time some of the chat activity took place.

Notice the created time
As seen in the image above the TikTok app is sending and receiving chat messages. The parsed XML SQLite database shows the same activity at the same time being totally consistent with TikTok chatting.

Notice the classs values
Shortly I will provide a SQL query that will format the dates and type values in the same human readable format seen above.

Certain events keep track of their length in milliseconds.

Application name.

Activity types as integer values. These represent activity like move to background or move to foreground. The list of interactions can be found here:
Application name and corresponding modules in use.

Usagestat originating XML category. They are daily, weekly, monthly, and yearly.

Contains the full attributes for the XML event, in other words all the data for the even in JSON format. With the data in this field the analyst can easily select any key:value pair and make it its own column in a SQL query by the use of JSON_Extract. For an example on how this SQL query function works see here:
SQL query

The following query can be run against the script generated database to format the timestamps from UTC to local time, add a field for time_active in seconds, and changes the types integer values to readable activity descriptions. Be aware that I have not added all case types per the link provided previously in the Types section. Add as needed.

  datetime(lastime/1000, 'UNIXEPOCH', 'localtime') as lasttimeactive,
  timeactive as time_Active_in_msecs,
  timeactive/1000 as timeactive_in_secs,
CASE types
     ELSE types
END types,
FROM data
ORDER BY lasttimeactive DESC


My hope with this script is to make accessible the data contained in the usagestats xml files for digital forensic case work. Additional testing of the script and suggestions on how to optimize it are welcomed. I hope to create additional scripts that will parse the Android battery status and recent tasks XML files as show by Jessica Hyde in her presentation.

As always I can be reached on twitter @AlexisBrignoni and email 4n6[at]abrignoni[dot]com.

Wednesday, January 16, 2019

QuickPic for Android - Don't forget external/emulated storage!

QuickPic Gallery for Android

QuickPic is an image gallery app for Android devices that used to be fairly popular before it was taken down from the Google Play Store. Since the app is still available via third-party APK repositories we might still come across it in our case work.


The main reason for this blog post on the QuickPic app is that it illustrates the value of checking related external, emulated, and adoptable storage in Android devices. In this particular app all the databases that track user generated activity were kept outside of the main app directory.

QuickPic app keeps relevant databases in the following directory:


SQLite Queries

Queries that can be used as to extract pertinent data can be found at:

The following queries are provided:
Quick analysis of SQLite databases

The app provides thumbnails for all the images it scans as seen in the next image.
These thumbnails are kept in a database titled thumbs_numeric-value.db where the words in red represent a series of numbers as seen below.

Notice the multiple thumbnail databases
In the image we can see that the databases of interest are named thumb_123904.db and thumb_220900.db. It appears that when one database reaches a particular point (size? directory paths? amount of records? year?) it continues registering thumbnail data in a second database.

The thumbnail databases contains a table named thumbs with the following columns: path, thumb, and modified.

Thumbnail database content
The columns provide the path where the original image is located on the device, a modified timestamp, and a thumbnail of the original image. To view the image export the blob within the thumb column and rename it with their proper extensions as seen in the path column. The usefulness of having a copy of the data in thumbnail form is apparent. This is even more so when the examiner has access to multiple data backups, be it by Android itself or by third party backup apps, of the same device.

When the user presses one of the thumbnails images in the application, in order to view a larger version of the image, the app keeps track of such activity in the preview database. The database can be seen in the cache directory image seen above. The preview database contains a table named cache with the following columns: document_id, _data, _size, accessed_time, and last_modified.

Preview database
As seen previous image the document_id columns keeps the location path for the original image. the _data column keeps the location path for a copy of the original image that was selected by the user. These images are kept, as seen in the path, in the .preview directory. The preview images themselves have alphanumeric names with no extensions. Just like the images in the thumbnails database one can add the proper extension to the preview files to view them. The rest of the fields are for preview image size, accessed time and last modified time. 

It is of note that files in the .preview directory tell us that the user had to select/press the thumbnail of a media item of interest in order to view a large version of it. It requires user interaction. 


This quick analysis made me think of how many times we can be in a hurry and overlook related app directories that are contained in SD cards or emulated storage space. Databases that tell us about user intent, what was selected for viewing, and what was actually seen can be missed if we don't make it a habit to always look for unfamiliar app ids in app directories AND emulated/external storage. A one minute check can provide us with amazing returns we did not expect. 

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Sunday, January 6, 2019

iOS Mobile Installation Logs Parser

In the last two blog posts I wrote about ways of obtaining a list of currently installed apps and their corresponding app directories from an iOS file system extraction. My usual method is to query the contents of the applicationState.db file to find the app bundle id and what directory GUID like name corresponds to it. By finding the proper directory one can focus on the data stores it contains for parsing of user generated data when our forensic tools are not aware of them.

On my second post I received great feed back from Sarah Edwards. She pointed me to the contents of the mobile installation logs in iOS.

Cool stuff!
I immediately wondered if there was a script that could parse those logs for the data I was looking for. After asking Sarah Edwards and looking online I didn't find any.

I'll do it.
The link below is for a python script I made that parses the mobile installation logs.

These logs contain a lot of information. Currently the script only extracts the following events:
  • App install successful with date and time.
  • App container made live with date, time, and path.
  • App container moved with date, time, and path..
  • App destroying container with date, time, and path.
Here is a sample screen of how the logs look as taken from the device. The image has been zoomed out which could make it harder to read unless the preview image is clicked.

Lots of data.
The script is really simple to use.
  1. Have Python 3.6.4 or newer installed.
  2. Extract the logs from the /private/var/installd/Library/Logs/MobileInstallation/ directory.
  3. Place the script in the same directory as the extracted logs.
  4. Execute the script via CMD.
The screen, after done, should look something like this:

Run complete. Some stats.

The script will produce one SQLite database called mib.db, a directory named Apps_State, and a  directory named Apps_Historical.

Script with generated items.

The SQLite database holds the extracted information from the lines of log data. The script queries the database to produce the contents within the two directories.

The Apps_State directory can have two files within it. These are named InstalledApps.txt and UninstalledApps.txt. The contents reflect the name of the text files. Here is a sample image of InstalledApps.txt content:

List of installed apps.
Having this list handy is really useful since it can be used to compare the currently installed apps within the file system image that might have been missed by our third party forensic tool of choice. 

If one would like more context in regards to when the app was installed and where the app directory is located the Apps_Historical directory has all that information per app.

A txt file for every app.
Here is a sample of the historical information regarding an installed app.

Historical events for
Notice the report has a timestamp for every event. The scrip puts the most recent events at the top so the current path for the application directory can be at or near the top.

Here is a sample of historical information regarding an uninstalled app.

Historical event for org.videolan.vlc-ios
 Like the previous report there is a timestamp for every entry and events start with the most recent at the top. These report are useful if one wants to determine when an app was uninstalled or if a current app was uninstalled and then reinstalled multiple times.

Historical events for org.coolstar.electra1131
Notice the multiple 'Destroying', 'Made', and 'Install Successful' entries in the report. Again the most recent ones are at the top.

As seen above the script's output is responsive to the original requests of installed apps with corresponding app directory paths. It goes further by identifying uninstalled apps and by providing timestamps and historical app event aggregation.

By looking at the logs there seems to be further areas where the script can be improved. This is my soon to-do list:
  • Report on 'detected reboot' and other log entries that indicate system state as opposed to particular app states.
  • Add 'updated bundle entries' for 'container made live' context in the historical reports.
  • Add 'attempting delta patch update' and app version information in the historical reports.
  • Add 'uninstall requested' and 'uninstalling identifier' in the historical reports.
I can't thank enough my colleague @i_am_the_gia for testing out the script on her data sets and Sarah Edwards for making me aware of the logs.

If anyone gives the logs a look and finds further items to report on or wants to give other feedback I can, as always, be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Sunday, December 30, 2018

Update on identifying installed and uninstalled apps in iOS

In my last post  I asked the following regarding the values within applicationState.db:
Are the key_tab table values the same for all iOS devices? In another phone, would ID 1 still stand for compatabilityInfo and ID 13 stand for _UninstallDate data? If every phone has different key_tab relationships there would not be a way to scale this analysis using a universal SQL query for it.
 I'm am happy to say the community came to my rescue. Among the great multiple responses I got the ones from the awesome Sarah Edwards stand out.

As seen in the screenshot above the ID to key relationship within the key_tab table can, and many times is, different on each iOS device. Also, as seen on the screenshot, her suggestion of identifying the ID by key name instead was totally on point. With that in mind I remade the query as seen here:

The fact that the uninstalledapp key is so random that it did not appear in many of the data sets from folks that reached out to me tells me that the query for unistalled apps is not useful at all, hence I nixed it.

As of tonight this is where I stand:

1) The applicationState.db is a good way of getting a current list of installed apps with their corresponding app directory locations and names. It is super useful for the purposes of validating a list of installed apps that a mobile forensic tool tells you. Any app in the applicationState.db list that is not in the installed app list from the tool requires a look at that app directory. Since it is a simple SQL query it is fast and simple to implement.

Sarah did not limit herself to that. She pointed me to another location rich in app installation data.

2) The MobileInstallation logs are fantastic! They have all that and more. Still, to pry a list of current installed apps and directory names out of these logs requires some work. There is no script that automates it. Time permitting I will try and take a crack at it. If the reader hasn't looked at those logs before then they (you!) definitely should.

Christopher Vance chimed in with another set of forensically relevant items.

Great info for future use.

3) Last but not least, Sarah provided a way to track uninstalled apps.
Good stuff all around.


My original requirement was to produce a list of installed apps and their corresponding app directory names and locations in order to identify any apps that would require parsing when a commercial tool misses them. As of today the applicationState.db method from my previous post seems to fulfill that requirement.

If a historical look at installed apps is required the MobileInstallation logs provide a full picture. Sadly until a script is made to parse it getting that information out will require some grepping/lots-of-eyeballing-the-logs to get what is needed.

For network usage Netusage and datausage analysis fills that gap. 

UninstalledApplications.plist rounds out the picture as the name of the plist clearly indicates.

I can't thank enough all the folks who took time out of their weekends to respond and share their knowledge. Super grateful for all of you. I'm sure glad to be part of the digital forensic community.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Saturday, December 29, 2018

Identifying installed and uninstalled apps in iOS

Short Version

In this post I look at the applicationState.db SQLite database in detail and ask for help on testing/validating some of the findings.

Long Version

As stated in previous blog posts the applicationState.db located at
keeps track of app bundle IDs as well as the path and long alphanumeric folder name ID where the app keeps its data. For an example of how this looks in practice see the previous post here.

As I was bouncing around some concepts with Phill Moore regarding my upcoming Magnet User Summit he asked me what would happen to the data in the applicationState.db if an app was deleted. In all my iOS third party app research I have used the database to find the app data directory names but I never thought of finding out what happened to it when an app was deleted. This post is me attempting to find out. Thanks Phill. Here we go.

For an example on how to obtain a file system extraction from a jailbroken iOS device see here.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
Testing Procedure

  1. Connect to jailbroken device via SSH and extract a copy of the applicationState.db file.
  2. Deleted three apps from the device.
  3. Connect again and extract a copy of the database after deletions.
  4. Compare the databases to see what changes, if any, took place after the deletions.

The applicationState.db SQLite database has 3 tables of interest. The first one is the application_identifier_tab table.

It contains the bundle IDs for the apps and a unique ID for each one. I have always used the ID value here to identify where the path and app data folder name in the second table of interest named kvs.

Note how each value in the application_identifier column is the foreign key of the ID column values in  application_identifier_tab table. For example when the value for the kvs.application_identifier filed is 110 the bundle ID name in the application_identifier_tab table is com.spotify.client.

Look at highlighted area

If you look back at the kvs table notice there is also a column named key. For the 110 ID there are multiple key values like 2, 9, 8 and others. What are these? These values map to the third table of interest, key_tab.

This is where it gets interesting and questions arise. On my test device the blobs contained in the value field within the kvs table tell me where the app keeps its data as long as the key = 1. For the com.spotify.client it would look like so:

Selected blob where key value equal 1

Blob content
Notice the blob content has the Spotify app name and the path for the application directory to include the long alphanumeric name of the folder. The blob is a binary plist that can be exported and view more cleanly with a binary plist viewer like Sanderson Forensics BPlister.

What then do the other blobs contain when the key =/= 1? The key_tab table mentioned previously is the guide. It maps the values to what data it contains. If one looks at the contents of the table, value 1 stands for compatabilityInfo while 13 represents _UnistallDate.

In my test data application ID 112 maps to the com.valvesoftware.steam bundle ID and it has a a key value of 13 which indicates a deletion date binary plist. The extracted bplist blob looks like this:

Deletion date. Nice.

Easy right? The bundle ID (app) in question does not have a key value of 1 which tells me that it has no associate app directory folder which is consistent with the app being deleted. So logic follows that if we identify which apps (bundle IDs) don't have a key value of 1 (indicating deletion) and extracting the key value of 13 (giving us the deletion date) we should have a list of deleted apps with the deletion date of each. Neat right? Well the devil is in the details. Of my three deleted apps one (com.microsft.rdc.ios) did not have a key value of 13 hence no deletion date for it. I knew it was deleted since there was no ID value of 1 (and I made the deletion myself). No ID value of 1 then no app directory (I checked and it was gone) for the bundle ID which does remain in the database. When I thought how could I automate this analysis the following questions surfaced:

  1. Are the key_tab table values the same for all iOS devices? In another phone, would ID 1 still stand for compatabilityInfo and ID 13 stand for _UninstallDate data? If every phone has different key_tab relationships there would not be a way to scale this analysis using a universal SQL query for it.
  2. Why out of the three deleted apps one had no deletion date recorded?

I made the following queries that identify installed applications (the ones with ID value of 1) and uninstalled applications (the ones without the required ID value if 1 entry and/or the additional ID value of 13). The problem on these queries ties back to question #1. Would the queries still work in another iOS device? My guess is that no, most likely it would not. This is something a reader could test and validate. I plan on doing validation on another iOS device in the near future as well. Here are the queries as stored in the DFIR SQL Query Repository:

  • iOS Installed Apps
  • iOS Uninstalled Apps
  • iOS Uninstalled Apps (That have deletion dates)
Updated query:


The applicationState.db SQLite database is super useful when one wants to list installed applications and find out which obscure alphanumeric app directory name matches an app of interest in the list. This is really important if one is to identify any apps that automated vendor tools do not parse and to locate the corresponding app data stores in the app directory. 

This database seems to track app deletions in some form. For some apps it will have a deletion date and for others it might not. I have yet to find out why.

It is still pending to validate if the ID values for compatabilityInfo, _UnistallDate and others are the same across iOS devices. 

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.


My apologies to Jessica Hyde for my half baked emails on the topic before the creation of this blog post. Sorry...

Tuesday, December 4, 2018

Profiling user activity in Dropbox for iOS

Dropbox for iOS

Dropbox is one of the most well known cloud storage services in the planet. It needs little to no introduction. In this post I look into what relevant digital forensic artifacts can be found for Dropbox in iOS.

This post will differ from previous ones in that I will not discuss how to locate the application data directories or how to extract them. For an example on how to go about doing so follow the steps (which are for the Discord app but work for any app) here.

Short Summary
Dropbox SQLite databases contain information regarding files stored in the cloud even when the files themselves are not downloaded to the device. Two of my favorite findings are:
  1.  The ability of matching app generated thumbnails on the device to the full metadata information from the original remotely stored files.
  2. Database tracking of how many times a file was viewed via the app even if the file wasn't downloaded or synced.

Additional data was contained within the app directories outside of SQLite databases. If the account in use is linked to a third party provider, like Google, a JSON file is generated with all your contacts and any Dropbox interaction that took place with the target user account. Some of the data in the JSON file are names, usernames, profile pics URLs, and timestamp of the latest Dropbox interaction like the sharing of a folder. In addition the Dropbox target user account information can be found in one of these JSON files.

Important Information 

All the queries and analysis in this blog post is provided as a guide for the reader's own testing and validation.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
  • Magnet Forensics Axiom 2.71.12070 with custom queries made by the author.

SQL Queries

Queries that can can be used as templates to extract pertinent data can be found at:

Here is a list of the individual queries in the aforementioned repository:
Analysis of SQLite databases

The Dropbox app has the following file directory structure:

Most of the databases of interest are in the /Documents  and /Documents/Users/UserID directories where UserID is the numeric representation of the user account. The contents of the /Documents directory are as seen in the next image.

First database of interest.

The first database of interest, as seen above, is spotlight.db. This database seems to be the partial aggregation of the data contained in two other databases within the /Documents/Users/UserID directory. These will be discussed shortly. After running the query above for this database (Spotlight - recent actions) we get the following:

Spotlight.db selection.

Salient items:
  • Path: Path and filename of remotely stored file.
  • Source: From where the activity information came from. I found no files named as such in the Dropbox app directories.
  • Title: Filename.
  • Last action time: Timestamp. For files that end in .remote in the source field these timestamps match when the app was opened. For files that end in .local in the source field these timestamps match when the user had interaction last with those files.

Based on the files names it seemed to me that this list is composed of the items one sees in the recents screen after login into the app. Older files that I did not scroll down to are not in the list. The content of Spotlight.db mirrors the content in two other databases called recent_actions_local.db and recent_actions_server.db. These databases are contained in the following location:



This database contains the same entries that end in .local within the spotlight.db source column. The only addition is the user id column. Here is how running the query above (Recent actions local) looks like:

Salient items:
  • Path: Path and filename of remotely stored file.
  • Timestamp: Time user interacted with the file last. I clicked on the thumbnails of these items in the app to view them. One of them I made available offline. The times are the same ones as contained in spotlight.db.
  • User ID: User identifier.
There might be more user actions that will flag a file as local recent actions. The databases do not specify what that action was or could be. So far I have only been able to flag the files by the actions stated previously.


This database contains the same entries that end in .remote within the spotlight.db source column. The only addition is the action column. The timestamp is different from the spotlight.db time. Here is how running the query above (Recent actions server) looks like:

Salient items:
  • Path: Path and filename of Dropbox stored file. Not local.
  • Action: Human readable message.
  • Timestamp: The times in this column follow closely the time the image was taken (see next section for details.) By comparing the filenames with the timestamp column I concluded that the timestamp reflect when the image was added to Dropbox. I have my Dropbox set up to only upload images when connected to WiFi.  
Camera uploads and naming convention

My testing showed the following regarding file naming convention and timestamps for the camera uploads directory:

  • Images taken by the mobile device camera and uploaded automatically by the Dropbox app are named in Dropbox as the creation date of the image on the device.
Sample image and metadata on the device. Notice the original filename and creation timestamp.
Now notice the filename Dropbox gives it after it uploads it.

  • If the user herself uploads files and images to the Camera Uploads folder, as opposed to the app itself, these will retain their original names.
  • Files can come from different devices and land in the same Dropbox remote storage account. These databases do not tell us what device produced or uploaded the images originally.
Be aware that by default the app will create thumbnails for the items on the list at the following location:


The directory has a collection of folders named in a simple pattern of lowercase letter p followed by a number.

Inside these folders thumbnail files will be stored with the following filenames:
  • 256x256_fit_one_bestfit
  • 960x640_bestfit
If one of the images was downloaded to the device or placed as available offline the folder will contain an additional image named:
  • original.png
The following image shows the contents when all three are present for the p5 folder.

The obvious question becomes, how are these images matched to the items previewed, viewed, or downloaded by the user in the Spotlight database? The answer is that there is no link to the thumbnails in that database. The link is found in a different database called Dropbox.sqlite.


The Dropbox.sqlite database has metadata on all cached files. Here is how running the query above (All cached files metadata) looks like:

The column named Cached File ID has the number needed in order to match the thumbnail to the full size image stored within Dropbox. It is as simple as putting a p in front of the number and looking for it in the thumbnails directory.

Salient items:
  • Cached File ID: Number that corresponds to the thumbnails folder. Just add a 'p' in front of the number.
  • Path: Path and filename of Dropbox stored file. Not local.
  • Cached File Size: In bytes.
  • File size: Actual file size. Item not local.
  • Times viewed: How many times the user click on that thumbnail to view a larger version of it.
  • Last time viewed: Just what it means.
In order to isolate the viewed files and their times a query (Viewed files only)
was generated that looks as so:

This is one of the most direct ways of demonstrating user activity tied to specific items.

Additional databases of interest in the /Documents/Users/UserID/ directory are metadata.db, offline.db, and starred_infos_local.db. 


This database is interesting because the contents mirror user activity when files are browsed through the Files menu in the app. It will record folders and file names. A query was generated (Browsed files via the 'Files' menu option) to show these contents.

Salient items:
  • Path: Path and filename of Dropbox stored file. Not local per testing conditions.
  • Last Modified Date: Notice how the root folder and Dropbox generated directories (Camera Uploads, Public, etc...) do not have a Last Modified timestamp while user generated directories and uploaded files do. My testing shows that the last modification timestamp reflects when the files was placed in its current Dropbox location. By interacting with the files via the app there are no timestamp changes. Further testing is needed to see if any changes are reflected from outside the app interaction with the files in the remote storage location.
  • Client Modified Date: Time when the file was created. Dropbox will use the file creation time at upload as the Client Modified Date. If the files are in the Camera Uploads folder and follow the naming convention discussed in a previous section, the Client Modified Date is the creation date of the image in real time as long as both values match. This might seem like a distinction with no difference but there is and it is a key one. A creation date for a file is not necessarily the same as when the image was taken in real time. Depending on where the file is in the remote storage location and how it is named one can then determine if the file creation time is the same as when the image was possibly taken in real time. 
  • Shared Folder ID: One of the folders was shared by me and another shared to me. I find no way to determine directionality by looking at the contents of the database.
I want to emphasize how useful it is to understand the differences in meaning regarding Client Modified Date and Last Modified Date in the previous database. That same understanding will apply to the next database we will analyze. The next database is key because it contains metadata on a large number of images and videos uploaded to the Dropbox account no matter if the files where browsed or accessed by the user via the application on the mobile device.


As stated previously this database contains information on a large number of multimedia items in the remote storage location. A query was generated (Images and videos metadata) to show the contents of the /Library/Application Support/Dropbox/alphanumeric sequence/Files/cache.db database.

The explanations for the Path, Bytes (for file size), Client Modified Date, and Last Modified Date columns are the same as the ones used in the previous database, metadata.db. As seen in the image above some Client Modified Date data might not be moved over. If one accesses the Dropbox storage location via the web interface those dates can be seen. This means that the modified date in the web interface for the file is the Client Modified Date in the database.

My shorthand, that is not so short, is as follows:
  • If file is in the Camera Uploads folder, and it is date timestamp named, and the Client Modified Date is equal to the date timestamp name then the Client Modified date IS the time the picture was taken via a mobile device camera and would reflect the time given by such device. All conditions are necessary for this assumption to be true based on my testing. The underlined word in the last sentence is important because it assumes the mobile device that generated the image or video is keeping time accurately in relation to real time. It is true that science doesn't work in assumptions but it is guided and informed by them. Always do your own tests on your own particular case scenario. 
  • For all other files the Client Modified Date is the creation date of the file without any assumptions on when the picture or video was taken in real time.
  • Last Modified Date is when the files were uploaded to remote storage absent some yet unknown mechanism that affects this timestamp after file upload.
What happens if a file is downloaded from Dropbox to Windows using a browser? Are the Client Modified Date values carried over? If so, where? On Windows systems, and using Chrome as the browser, the following occurs:

  • When multiple files are selected for download a zipped files is downloaded to the system containing the selected items. After decompression the files created and accessed times are the same as the Client Modified Date in the database.
  • If you download a single file all modified, accessed and created times will be the same and will reflect the moment the file was downloaded from cloud storage locally.
The file properties on the left are from a zipped file whereas the file properties on the right are from a single file download. Both done via Chrome browser on Windows 10.

Here are the values for the same file as recorded in the database.

The difference in hours and minutes is due to setting the time values in the database viewer (Axiom) to UTC-5:00. Notice how time recorded in the database is exactly the same as the created and accessed dates in the unzipped files.


Database records items selected for offline viewing. A query was generated (Files available offline) to show the contents. The database is located in the /Documents/Users/UserID/ directory.


Database records starred items. A query was generated (Starred files) to show the contents. The database is located in the /Documents/Users/UserID/ directory.

Cache.db / User notifications

Database records user notifications. A query was generated (User notifications) to show the contents. The database is located in the /Library/Application Support/Dropbox/Alphanumeric string/notifications directory.

Notice the feed timestamp on the left as well as the content of the notification in JSON format. Since the JSON notifications in the database did not follow a consistent key:value pair pattern they are presented as is. They are small enough for any examiner to read and figure out what they are about.

JSON Files

Dropbox keeps data in JSON files in addition to SQLite databases. The following two files are of great interest. They are located in the /Library/Application Support/Dropbox/Alphanumeric string/Account/contact_cache directory.


This JSON file contained all the user Gmail contacts for the Dropbox account user. For my sample data I am using my own Dropbox account which is tied to my Gmail. Some of the data kept for the user contacts in the JSON file are:

  • Email address
  • Interaction info
    • Last used time
    • Total interactions
    • Use type
  • Last used time
  • Name
  • Service types
  • Total interactions
  • Dropbox profile pic URL if any
Here is a sample contact from the list.

The JSON file was processed for viewing using a python script located at:


This JSON file contained the Dropbox user information. Some of the data kept for the user in the JSON file are:

  • Email address
  • Name
  • Dropbox profile pic URL
  • Is_me field with a value of True.


Due to now being able to obtain file system extractions from devices we couldn't in the past it is important we revisit our analysis of what we might think are already well known applications. This is even more relevant when the data we are examining is multi-platform and sometimes we might have to look at it from different vantage points, for example mobile device data in contrast to the same data viewed through a desktop browser.

The eternal caveat still applies. Always test, test and test. And when done, test some more.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.