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:
- Sweep the cells or select the column to which you wish to apply the condition.
- Right-click in the selected area and choose (you guessed it) 'Conditional formatting...'
- Set the rule according your your specifications. That's it... really!
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!
