The 3rd party app SystemPanel2 keeps timestamped system wide app usage statistics in the following database:
userdata/data/nextapp.sp/databases/SystemRecord.dbThe following queries at https://github.com/abrignoni/DFIR-SQL-Query-Repo can be used as templates to extract information from the SystemPanel2 database:
- History Master
- Timestamps start & end, battery data, CPU usage, screen usage, cell and WiFi signal strength, CPU clock, and load information.
- History Process
- Timestamps start & end, PID, process name, type, CPU time, total CPU time, and foreground time.
- History Foreground
- Timestamps start & end, interval, process name, type, battery usage, and CPU usage
- History Power
- Timestamps start & end, name, power use, flags, wake count, and wake time.
Long version
Since Sarah Edwards came out with her earth shattering iOS analysis of the KnowledgeC database and then her industry leading APOLLO framework I have been obsessed with finding ways of getting similar data sets from Android devices.
To this end one of the most enjoyable research projects I embarked this year involved working on some scripts that parse the UsageStats and Recent Tasks XML data stores in Android. These scripts took as foundation the incredible research done by Jessica Hyde. They were usability enhanced by the magical python GUI work of Chris Weber. This particular work for Android is of importance due to the fact that these XML files reside in most, if not all, Android devices. Still I knew there had to be better captures of app usage via third party apps due to some work I had done last year on the Android Ccleaner app.
This blog post will be the first of a series that will look into pattern of life, app usage and system data that can be extracted from Android third-party apps. Some of these apps are user installed via the Play Store but others will come by default on branded devices. Some of these apps will require root access to extract the data while others will spill the usage secrets via plain and simple ADB. Here is the first of these...
Testing Platform
For my testing platform see here.
For this particular post a rooted Samsung S7 Edge was used.
Analysis
The best explanation of what data this app records can be found in their Google Play store description. It read as such:
SystemPanel is a tool to let you view and manage just about everything possible about the goings-on of your device and visualize it in an easy-to-understand graphical format.It goes without saying that the amount of data this app tracks is incredible. The data is kept in the sqlite SystemRecord.db file located in the userdata/data/nextapp.sp/databases/ directory. Within the database there were 4 tables housing the data of interest.
Features include:
* Show active apps, record app battery, CPU, and wake lock usage over time to show potential battery drain issues
* Draw plots showing how you used your phone over time and how much battery disappeared as a result
* Analyze recent battery consumption and device wakeups (wakelocks), showing potential problem apps
* Manage installed apps, backup app APKs, uninstall apps, and re-install archived versions
* View apps categorized by the permissions they require
* Disable system packages [ROOT required]
* Disable individual services of apps (e.g. OTA updates) [ROOT required]
* Browse all the technical nitty-gritty about your phone
This app uses Accessibility services. SystemPanel's "Usage" feature can optionally use an "accessibility service" to show you how much time you're spending in each app on your phone, and when you use them throughout the day. This is useful for those with addiction disorders (and/or their parents or legal guardians) to avoid addictive use of the device/specific applications. Use of this service is optional, and like the rest of SystemPanel, no collected data is sent from the device, it is only displayed to the user.
History_Master Table
This table contains information on timestamps, battery data, CPU usage, screen usage, cell and WiFi signal strength, CPU clock, and load information.
A simple scroll down the results shows how when a device is in use the screen usage, load, and CPU stats then to be high while battery charge goes down (assuming is not plugged in.) In my test data it was easy to see how most heavy usage took place during the daytime hours. It would be trivial to export the data into Excel to create usage graphs for all the parameters being tracked. This is true of the data in all the tables. Each row of data in the table has a 7 second interval between the start and end timestamps.
History_Process
In this table we get out first set of data that is related to both app bundle ids and Android OS processes.
As seen on the previous screenshot the bundle ids have a type classification of 2 where as non reverse URL designations have a type classification of 1. Each row of data in the table has a 3 second interval between the start and end timestamps.
History_Foreground
The table name is self explanatory.
Is is of note that the time interval between the start and end timestamps is variable and it is contained in the interval row. The interval values are in seconds.
History_Power
Power usage aggregation by app / process.
Each row of data in the table has a 7 second interval between the start and end timestamps.
So what?
What use would such data have? For starters it can tell us not only when a device was in use but also what apps where the ones and and which ones weren't in 3 & 7 second time-frames. Was the screen on (higher energy consumption) or not? Was the phone being charged at a particular time? Was the device on or off? How long was the app used for? The best part is the knowledge of when all of these events were happening. The forensic applicability of such data cannot be overstated. To that end I will be submitting these queries as custom artifacts to the Magnet Forensics Artifact Exchange.
Conclusion
The preceding analysis of a third-party app demonstrates again the need to always manually review the app directories for items our forensic tools might not be aware of yet. A few extra minutes of work can yield incalculable benefits.
As always I can be reached on Twitter @AlexisBrignoni and email 4n6[at]abrignoni[dot]com.
