Spreading Out My Skills:
Fun with Spreadsheets

I had an opportunity to improve my spreadsheet skills last night while helping my wife on a project.  It's not hard for me to improve in this area because up until yesterday, spreadsheets were just a convenient way to open and sort CSV documents from forensics tools.  But, I learned some averaging and summing techniques, and more importantly, conditional statements and conditional formatting.  What's that got to do with forensics, you might ask?

I've written and/or used plenty of tools that produce CSV output.  Let's take a an SMS output, as an example.  Often, a particular phone number is the target of an investigation, and spreadsheets make it quite easy to sort on a column of data, such as the phone number.  So, in a few clicks, you've got the target number nicely grouped for review.

But, the more investigations I do, the more I've come to realise that good intelligence and investigation reads between the lines--not in a 'make up your own interpretation' sort of way, but looking to see what else was going on in the phone, computer, browsing session, etc., to give the target data proper context.  Conditional formatting can really help here.  It allows you to easily visualise the target data while at the same time seeing it in context.

OK, now I have your interest, but you really don't know what I mean by 'conditional formatting.'  Simply put, conditional formatting changes the look of a spreadsheet cell based on the content of the cell.  It is automated, rules based process; you set the rules, the spreadsheet formats the cells according to the rules.  Taking our cell phone SMS output as an example, you could create a rule  that changes the color of a cell based on the the phone number in the cell.  Thus, you can easily find your target, but still see it in context.

I'll use the spreadsheet in Google Docs as an illustration for setting up a conditional format:

1. Sweep the cells or select the column to which you wish to apply the condition.
2. Right-click in the selected area and choose (you guessed it) 'Conditional formatting...'
3. Set the rule according your your specifications.  That's it... really!

Your options may not seem like much at first, but you can specify more than one rule for the cell selection.  If the condition for one or more of the rules is met, then the text and background color selections your make are applied to the cell.  Conditional operators are:

Now, I also mentioned conditional statements.  These are statements that act on the data itself, not the cell format.  When would you want to change the data in a forensics investigation?  Well, how about this:

You are not a SQLite giant, but you know how to use your favorite GUI SQLite browser to export a table as CSV.  The SQLite table represents 'Sent' messages as '0' and received as '!'.  You'd like to render those values in their text equivalent for easy reading.  Sound like a possible scenario, yet?

OK, you've bought into the idea, but how do you do it?  Well, spreadsheets offer and 'if' statment that takes three arguments, and if, then, else clause if you will.  In our case, we would want the expression to read "If the value is zero, replace it with 'SENT', otherwise replace it with 'RECEIVED.'"  The expression looks like:

IF(test, then_value, otherwise_value)

The formula for our example in your spreadsheet might look like this, then:

=IF(B2=0, "SENT", "RECIEVED")

You can easily apply this formula to each successive cell, automatically changing the cell address for the appropriate row, by clicking the cell with the formula, grabbing the handle on the lower right corner of the selection box, and dragging to to the end of your column.  If statements can even be nested to make more that two possible outcomes:

=IF(B2=0, "SENT",(IF B2=1, "RECEIVED", "UNKNOWN"))

In the statement above, cell B2 is tested for 0, if the condition is met, then it is replaced with "SENT."  If it fails the text, then the "otherwise" value is another IF statement: if B2 is 1, then replace it with "RECEIVED", otherwise replace it with "UNKNOWN."   It is possible to have multiple nested if statements.

Who knew spreadsheets could be so much fun?  I even hear they do math!

SINF Structure

I spent some time decoding the SINF files that I discussed here, thanks in great part to a link sent to me by a colleague (Thanks, Derrick).  Here are my findings, to date:

Unlike iTunes Purchased MP4 media files, the SINF does not contain the iTunes user account name, which is most often their email address and most useful for contacting owners of stolen devices.  Instead, you are limited to the iTunes user's name and iTunes ID#.  Short of a search warrant or subpoena, Apple is not going to reveal the owner's personal information, though they have contacted owners on my behalf in the past.

Please review the data in the table and compare with your findings, if you are so inclined.  Post a comment if you have more insight, find an error, or can confirm any of the information.

iOS .sinf Name Calling

In my ever present quest to identify the true owners of stolen iPods, I made discovery in iOS while examining a Touch that may be probative: the app .sinf files found in the /private/var2/Applications sub folders.  According to File-Extensions.org:

The SINF file extension is associated with applications for Apple iOS operating system that is used in Apple iPhone, iPad and iPod Touch. File contains information about digital rights that are applied in application. The SINF file is stored in an IPA iOS application archive.

I found that by searching the ../Applications directory for .sinf files, and then grepping for the term "name", the Apple Store real name associated with the app can be discovered.  On the Linux command line, this can be accomplished very quickly with:

$ find private/var2/Applications -name "*.sinf" -exec strings -f {} \; | grep name

Modification dates for the files can be used to create a timeline of activity for the device and perhaps demonstrate when new residents moved in, so to speak.  The find command can by used with stat to quickly provide a list of date stamps:

$ find private/var2/Applications -name "*.sinf" -exec stat {} \;

But, even better, you can put it all together in a fairly simple command and create csv output for examination and sorting:

$ find private/ -name "*.sinf" | while read i; do name=$(strings "$i" | grep name); date=$(stat -c %y "$i"); echo -e "$i,$name,$date"; done

It appears from content that I have uncovered in a suspected stolen device, that the real name of the Apple Store account used to install the app is embedded in the .sinf file at the time of installation.  If this is the case, a stolen device, though it have the device name changed and the true owner's data deleted, may still have applications that were installed with the owners Apple Store account! 
 
Testing still needs to be done for verification, and I don't currently have any test devices to properly test.  If you are able to conduct any validation studies, please comment on this post with your findings.  I'll amend this post once I'm able to conduct my own studies or receive reliable findings from others.