This is a complementary post to Mari DeGrazia's post here about what to do when your tools don't quite cut the mustard. In today's post, I'll show how we can write a Perl script to retrieve the contents of an iPhone's voicemail database and then display those contents in a nice HTML table.
The first thing I *should* have done was Google it and see if anyone had written a similar script ... D'Oh!
But due to my keen-ness, I dived right in and using iPhone and IOS Forensics by Hoog and Strzempka (2011) plus some previous code I'd written, it took me a couple of days (at a leisurely end of year pace) to write this script.
Soon after I wrote this script, I learned that John Lehr had already written a bunch of similar iPhone scripts in Python in 2011. So while it looks like this monkey was a little late to the party, I still had fun learning and creating something.
You can view John's iPhone Voicemail script here.
My Python skills are pretty limited but it looks like my script is very similar to John's (except for the HMTL generation part). So I guess that's comforting - ie I didn't miss out on some obsure Apple incantation to Lord Jobs (I'm joking OK? Please don't sue me LOL).
Writing the script
First we use the DBI Perl package to read "voicemail.db". Next, we use the HTML::QuickTable package to print out the HTML table.
We've used both of these packages before (see exif2map.pl and squirrelgripper.pl posts), so it should be pretty straight-forward. Not being able to think of a clever and punny name, I'm just calling this script "vmail-db-2-html.pl". Catchy huh?
You can download the script from here. I'll spare you the agony of a line-by-line commentary and just delve into the most interesting parts.
So this is what the voicemail.db schema looks like (via the sqlite command line interface):
sqlite> .schema
CREATE TABLE _SqliteDatabaseProperties (key TEXT, value TEXT, UNIQUE(key));
CREATE TABLE voicemail (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, remote_uid INTEGER, date INTEGER, token TEXT, sender TEXT, callback_num TEXT, duration INTEGER, expiration INTEGER, trashed_date INTEGER, flags INTEGER);
CREATE INDEX date_index on voicemail(date);
CREATE INDEX remote_uid_index on voicemail(remote_uid);
Using iPhone and IOS Forensics by Hoog and Strzempka (2011) pp. 193, 194 - the important bits (for us anyway) are located in the "voicemail" table. These are the:
ROWID = Unique index number for each voicemail entry. Each entry's voicemail file uses the format "ROWID.amr" for the voicemail's filename. ROWID increments by 1 so if voicemails are deleted there will be discrepancies between the ROWID numbers and the current number of voicemail entries.
date = Date and time relative to the Unix epoch (ie seconds since 1 Jan 1970).
sender = Phone number of person who left the voicemail. Can be "null" presumably if number is witheld.
duration = Duration of voicemail in seconds.
trashed_date = Time when the user placed the voicemail in the "Deleted" folder or "0" if not deleted. This field is a Mac "CF Absolute Time" = number of seconds since 1 JAN 2001 (Thanks to Mari for pointing this out!). Consequently, we have to add 978307200 to our "trashed_date" before we can use it with any Unix epoch date functions (eg "gmtime"). Note: 978307200 is the number of seconds between 1 JAN 1970 and 1 JAN 2001.
Once we know the schema we can formulate our SQLite query (see line 74's "$db->prepare" argument):
"SELECT rowid as Rowid, sender as Sender, datetime(date, 'unixepoch') AS Date, duration as 'Duration (secs)', rowid as Filename, trashed_date as 'Deleted Date' from voicemail ORDER BY rowid ASC"
We're using the SQLite "as" functionality to create pretty alias names for the table headings. We're also using the SQLite "datetime" function to convert the Unix epoch "date" field into a YYYY-MM-DD HH:MM:SS string. The "trashed_date" will be handled later via the script's "printCFTime" function. For the moment, we will just retrieve the raw Mac "CF Absolute time" value.
The query results will be returned in order of ascending "rowid" and subsequently processed via the "PrintResults" function.
Once we have the results from the database, we then store them in a variable (imaginatively) called "results_hash".
The "results_hash" variable is set from within the "PrintResults" function and involves some mucking around to get the required table fields (eg human readable trash date, HTML link to .amr files). Essentially, each entry of the "results_hash" has a key (the rowid) and an associated array of values (eg From, Date, Duration, Filename, Deleted Date).
Once we've got the "results_hash" all set up, we can then call HTML::QuickTable's "render" function to do the actual HTML table generation and then add in some of our own text for the number of rows returned.
The resultant HTML file will be called "vmail-db-2-html-output-X.html" where X represents a timestamp of the number of seconds since 1 Jan 1970.
Note: Due how the HTML::QuickTable renders hashes, the HTML table "rowid" entries are printed in textual rowid order (eg 1, 10, 2, 3).
Running the script
I tested the script on SIFT v2.14 with Perl v5.10 and also on Win 7 Pro-64 with ActiveState Perl v5.16.1.
Here are the Perl package dependencies:
DBI
HTML::QuickTable
Getopt::Long
File::Spec
If you run the script and it doesn't work, it's probably complaining that it can't find one of those packages.
To install a package X on SIFT you can use:
"sudo cpan X"
eg1 "sudo cpan HTML::QuickTable"
eg2 "sudo cpan DBI"
The 2 examples shown above will probably be the most likely culprits.
Also, after downloading the script on SIFT, you should ensure that it is executable by typing something like:
"sudo chmod a+x vmail-db-2-html.pl"
If you're using ActiveState Perl, just use the Perl Package Manager to install the relevant packages.
And here's the help text - if I've written it right, it should be all you need (Ha!)
sansforensics@SIFT-Workstation:~$ ./vmail-db-2-html.pl -h
vmail-db-2-html.pl v2012.12.28
Perl script to conjure up an HTML table from the contents of an iPhone's voicemail.db SQLite database.
Usage: vmail-db-2-html.pl [-h|help] [-db database] [-f folder]
-h|help ........ Help (print this information). Does not run anything else.
-db database ... SQLite database to extract voicemail data from.
-f folder ...... Optional foldername containing the .amr files for linking. If not specified,
the script assumes the .amr files are in the current directory.
Example: vmail-db-2-html.pl -f heavy-breather/vmails -db voicemail.db
The script will extract the voicemail data from voicemail.db and then
write HTML links to the relevant .amr using the nominated directory (eg "heavy-breather/vmails/1.amr")
The .amr files must be copied to the nominated directory before the link(s) will work.
Script Output
The script was tested using data from an iPhone 4S running iOS 6. Unfortunately, I cannot show you any actual case output and I also do not have any iPhone data of my own - so here's some fictional output just so you can see how purdy everything is ...
Example of command line ouput:
sansforensics@SIFT-Workstation:~$ ./vmail-db-2-html.pl -f heavy-breather/vmails -db voicemail.db
Now Retrieving Voicemail data ...
Rowid | Sender | Date | Duration (secs) | Filename | Deleted Date
1 | +12005551234 | 2013-01-01 00:00:01 | 25 | 1.amr | 2013-01-01 12:00:01
2 | +12005552468 | 2013-01-01 01:00:01 | 10 | 2.amr | 0
3 | +12005551357 | 2013-01-01 02:00:01 | 28 | 3.amr | 0
4 | +12005551123 | 2013-01-01 03:00:01 | 30 | 4.amr | 0
5 | +12005554321 | 2013-01-01 04:00:01 | 19 | 5.amr | 0
6 | +12005558642 | 2013-01-01 05:00:01 | 17 | 6.amr | 0
7 | +12005557531 | 2013-01-01 06:00:01 | 26 | 7.amr | 0
8 | +12005551234 | 2013-01-01 07:00:01 | 51 | 8.amr | 0
9 | | 2013-01-01 08:00:01 | 41 | 9.amr | 2013-01-01 12:01:01
10 | +12005551234 | 2013-01-01 10:00:01 | 15 | 10.amr | 0
10 Rows returned
Please refer to "vmail-db-2-html-output-1357011655.html" for a clickable link output table
sansforensics@SIFT-Workstation:~$
Note1: Rows are printed in numerical rowid order for the command line output.
Note2: Null value for rowid 9 is left as a blank.
Here's the corresponding HTML generated file output example:
Note1: Rows are printed in textual rowid order for the HTML table (due to how the HTML::QuickTable renders)
Note2: Null values (eg for rowid 9) are displayed as a "-".
Note3: The HTML link to Filename will assume the user has copied the .amr files into the user specified folder (eg heavy-breather/vmails/1.amr). If no folder argument is given, the script will assume the .amr files are in the current local directory and link accordingly (eg 1.amr).
Final Thoughts
Mari's "Swiss Army Knife A $$$$$" tool did not process iPhone voicemail deleted dates or indicate if the voicemails were deleted. By writing this Perl script we were able to obtain this extra information that otherwise may have been missed.
By writing this script I also feel like I:
- Helped a friend and by sharing the solution, potentially helped other DFIRers.
- Improved my knowledge of iPhone voicemail. I had skim read iPhone and IOS Forensics by Hoog and Strzempka about 6 months ago but writing this script provided some much needed reinforcement. Additionally, I also learned how to handle yet another time format - the Mac "CF Absolute Time".
- Exercised my Perl coding skills. Like any language, skills atrophy if you don't use them regularly. This exercise also showed me the benefit of building up your own code library - I was able to "cut and paste" parts of my previous scripts into this new script thus saving time.
I'm not really bothered that I re-invented the wheel for this script. While John Lehr's script already provides the trashed date information - if I hadn't tried writing this, I would have missed out on a great learning opportunity.
I think in my case, "learning by doing" sticks in my brain better than learning exclusively via reading someone else's work. "Having a go" at something doesn't mean it has to be original or even successful so long as you are able to learn something from it. Sharing what you've learnt/helping others is just an added bonus.
Finally, one helpful tool for converting different date formats is the free "DCode" Windows exe from www.digital-detective.co.uk.
I used this tool to verify my script's arithmetic in converting "CF Absolute time" to a human readable time but it will also do a bunch of other conversions.
So thats about it for my first post of 2013. Any comments/suggestions are welcome.
