Two iOS databases that I’ve always found interesting (and probably should test more) are netusage.sqlite and DataUsage.sqlite. These two databases contain very similar information – one is available in a backup (and file system dumps) the other only in file system dumps. These databases are excellent at tracking application and process network usage.
These databases can provide answers to investigative questions such as:
What apps were being used?
What apps were used more than others?
Did the device communicate over cellular or wi-fi more often and when?
What apps were used that are no longer on the device?
A recent conversation a few of us ( @AlexisBrignoni and @cScottVance) have had on Twitter.
These databases are located in the following locations depending on the type of acquisition available.
/private/var/networkd/netusage.sqlite
Available in File System dumps only.
Backup: /wireless/Library/Databases/
DataUsage.sqlite
DataUsage-watch.sqlite (yes, there is one just for the Apple Watch!)
File System: /private/var/wireless/Library/Databases/DataUsage.sqlite
I’ve created modules for these databases in APOLLO, but you can also use the SQL queries in a standalone environment. I’m still working on how best to represent the timestamp keys and may alter the APOLLO code to accept multiple timestamp keys. This will help some other modules I’ve been working on as well so keep an eye out for that. I also need to work on acceptance of multiple database names, thanks to DataUsage-watch.sqlite.
The first set of modules are netusage_zprocess and datausage_zprocess. These two use the same SQL query as it is the same table, just different databases. These query extracts the process and/or the application bundle id. This query will show two timestamps:
TIMESTAMP – I believe this is the most recent timestamp for the process/application.
PROCESS FIRST TIMESTAMP – This appears to be the first usage of the process/application.
The first example comes from netusage.sqlite, the second from DataUsage.sqlite. It is notable to show that more information is available potentially from DataUsage.sqlite. Since this database is backed up it has the potential to have very historical data. These examples come from my iOS 11.1.2 dump. NetUsage goes back to November 4,2017 when I first setup iOS 11 on the device. The DataUsage database on the same device goes all the way back to 2013! This was from my iPhoneX which certainly did not exist in 2013. I restore backups onto new devices. I also get many more records from the DataUsage database.
netusage_zprocess
datausage_zprocess
The next set of queries are netusage_zliveproces and datausage_zliveprocess. These modules technically have a copy of the ZPROCESS data so they may be redundant if you are running APOLLO. Again, this is the same query for each database. DataUsage will again have many more entries. The added value from the ZPROCESS queries is the added network data information, Wi-Fi In/Out and WWAN In/Out. I will assume this value is stored in bytes until I can test further.
The major difference that I can tell between the two databases (apart from number of records), is that the DataUsage database does not record Wi-Fi network data. I know for sure I was on Wi-Fi at some point in the last six years! (It shows in NetUsage – remember it is the same device. Check out my Twitter data, its almost horrifying! 🤭)
netusage_zliveprocess
datausage_zliveprocess
Finally, we have an additional query only for the netusage.sqlite database, netusage_zliverouteperf. This query extracts lots of information, some of which I have no idea what it is. The first step into determining this is creating the query! In addition to some timestamps that appear to be stored on a per-hour basis we have the type of network traffic (Cellular or Wi-Fi), bytes and packings coming and going, connection information.
A second screenshot is required to show the rest of the extracted data. Some sort of cellular network identifier (any ideas?) or Wi-Fi (SSID/BSSID) are provided, with additional network-based information.
There is a lot of data going through these pipes!