Ever need to know what words were in what emails? Ever need a cross reference for those words to what emails they came from. Don't want to spend a lot of money to get this done but want to be able to do this with many mailbox types and do it quickly? Well do I have some good news, with some Perl scripting, a sqlite database (I told you I love databases) and 2 programs from Fookes Software, Aid4mail and MailBag Assistant (both are also part of Paraben's email examiner).

So here is what you need to do. I will use a Outlook pst as an example. First open up Aid4Mail and export your pst file to a directory into eml format (make sure you recreate the directory structure of the mailbox). Next open up Mailbag Assistant and import all the eml files including the subdirectories. You will need to create the following script and template to use (I will put all the files in a zip archive and put them on my webserver for you).

Script: Save_Body_As_Text

IfEmpty End
MergeData Save_Body_As_Text

Template: Save_Body_As_Text

>>>Files ?\{Mailbox}\{Subject}.txt
{Body}

The script will take all the selected emails (alt-a) from the "Grid View - Main" and run the template unless no emails were selected. The template will save the text body of the eml file to a directory you will be prompted for with a structure of <Directory Specified>\<Mail Box, IE: Inbox, Deleted, etc..>\<subject line>.txt. Once all the files have been extracted, run the get-word.pl Perl program passing the top level directory of where the email bodies were extracted to you will extract all the words and put them into the database ( I am not include a listing of the program but will have it available for download). Now you can run sql against the database to find the keywords that you want, you can also run the following sql against the database to create copy statements for you so that you can copy the emails you want out to another directory (If you want to get even fancier then include a table with the keywords you are looking for and add a subselect to the query, if you don't know what that is email me and I will explain it further)

select 'copy "'||directory_found_in||'/'||filename_found_in||
'" "c:/stuff/test/test/'||filename_found_in||'"'
from word_file_xref a, words b
where b.word_seq_num = a.word_seq_num and word = 'Oracle';

You can also make a slight modification and add a table with words you do not want to see (IE: and, if, or, not, etc..).

I will package all the code and database create statements up and also include a exe of the Perl program in case you do not have Perl but still want to test out the program (I know the code is not the neatest but it is functional). It can be found here.

One interesting thing to note is that this could be the beginning of an open source e-discovery email production package. Any takers for a project like this?

Questions/comments/suggestions?

Incident Response Hash Set Creation....

I use x-ways forensics as my main tool and I am pretty impressed with the product and support you get from the vendor. One of the things that I have been doing is creating my own hash sets. X-ways allows you to create the hash sets using many different methods (sha1, md5, sha256, etc..). Since x-ways is very light I thought I would try a little experiment. Using version 13.0 I installed it on my Hard Drive (no registry settings needed and weighs just over 4M with the external viewer and hash database). I then RDP'd to a QA server and mapped a drive back to my machine. I then fired up x-ways and examined the drives on the QA machine. I was then able to create a sha256 hashset of each drive of the server (4 seperate hashsets at this point for 4 drives). I then exported the 4 hashsets into a directory and reimported the directory naming the hashset the same name as the server (aprox 78,000 hashs created). I then waited 4 hours and rehashed all the drives on the QA server and compared it to what I created earlier. I was left with aprox 150 files that I had to look at, makes life a lot easier during a incident response. This is one of the many features X-ways has that can be used to help during Incident response.

Posting of Sample Notes

As requested I am putting up a sample of the information I have (it has been sanitized) of some notes I recently took during an investigation. The file is here. In the future when you leave comments if you can let me know who you are I would greatly appreciate it. If you don't feel comfortable leaving your name then just shoot me an email Mark.McKinnon@sbcglobal.net, I like to know who is requesting things and commenting.

I know I have not blogged lately and I am getting some stuff ready to share with everyone so be patient. If anyone has something they want passed along let me know and I will pass it along. You can contact me at the above email address. Make sure you put something in the subject relating to the blog.

Anyone will to share any file hashs that they have built? I have some hashs that I am putting together and will try and get them out within the Month.

Sorry this is short but more will be comming.

Notes During the Investigation....

So I am just sitting down to start an investigation and get out my notebook and pencil so I can jot down any notes when suddenly the lightbulb goes off and I wonder why not try TiddlyWiki. I blogged about this a little while ago about using it to keep specific information in so you would not have to search for it later, I was not thinking about using it during an investigation to keep my notes in.

For the reports I write I have 4 sections: Results/Things found, Opinion, Steps Taken and Technical Explainations. So what I started to do was instead of writing things down in my notebook I started typing in TiddlyWiki. I make each thing I found it's own tiddler with a tag of what section in the report I would put it in. As I started to do this I found out how much simpler it is then writing it down in a notebook. I can easily cut and past things as well as keep everything in a time line so I know when I ran accross it. I can search and make references to other sections as well. Also I can use this as a template for the next investigation that I have, it can be a start of a very detailed and comprehensive checklist.

Now I know some people will argue the need for a check list but I think it is a good idea to have one, I don't know about you but there is so much information out there, and to remember it all I think is just too much sometimes. I think it would be better to have tiddlers of things to look at and if it does not apply to the case then say so and move on (with the many flavors of OS's there will be things that you will do for some OS's and not others), if a lawyer wants to know why you did not do a specific step then your notes should say why (The OS was win98 so that is why I did not search the restore Point directory). In a former life when I had some pretty big system implementations to do I always had a checklist to follow, it made me make sure I did not forget anything and also I could use it for documentation then next time I did an upgrade to the system since upgrades were a few years apart.

Questions/Comments/Suggestions???

To DB or Not To DB The Report

As requested I have uploaded a sample of a program (create_report.zip) that will create a comma delimited file from executing a sql statement. The program expects an argument of the a ini type file to be passed to it. The program create-report.pl is the program and the sql-report.txt is the ini file. The create-report.pl program reads the file (sql-report.txt) that is passed to it, each line has 3 parameters in it (database file, output file and sql text). Each sql statement gets parsed and executed and written to a file. I chose to create a comma delimited file because that is the easiest, you could create any type of output you would want. By using this program all you have to do is edit/create new ini files for each database you have.

Questions/Comments/Suggestions?

To DB or Not To DB.........

Man do I really love to use databases. When you have a decent database and a good design there is nothing that you can not accomplish. Now when I say databases you are probably thinking Oracle, DB2, Sql Server, Mysql, etc... Those are all great databases with rich features but I am thinking more along the lines of an embedded database. What I usually use is Sqlite, it is a embedded relational database that is small and fast and supports most of SQL92. By combining Sqlite and perl I can do many things. Some examples of what I can do are as follows:

Store data from log files and report on them based on different criteria.

Load data and use sql to generate commands, ie: load up file names and then use sql to generate rename commands for the files.

Load multiple log files and types and correlate the data into a comprehensive report.

I will now show you what I am talking about. I will use one of Harlan Carveys   Cpan scripts that reads the event logs. I will use the lsevt3.pl program and make a few modifications to insert the records into a Sqlite database. The initial program looks like this:

#! c:\perl\bin\perl.exe

use strict;
use File::ReadEvt;

my $file = shift || die "You must enter a filename.\n";
die "$file not found.\n" unless (-e $file);

my $evt = File::ReadEvt::new($file);
my %hdr = ();
if (%hdr = $evt->parseHeader()) {
# no need to do anything...
}
else {
print "Error : ".$evt->getError()."\n";
die;
}

my $ofs = $evt->getFirstRecordOffset();

while ($ofs) {

my %record = $evt->readEventRecord($ofs);
print "Record Number : ".$record{rec_num}."\n";
print "Source : ".$record{source}."\n";
print "Computer Name : ".$record{computername}."\n";
print "Event ID : ".$record{evt_id}."\n";
print "Event Type : ".$record{evt_type}."\n";
print "Time Generated: ".gmtime($record{time_gen})."\n";
print "Time Written : ".gmtime($record{time_wrt})."\n";
print "SID : ".$record{sid}."\n" if ($record{sid_len} > 0);
print "Message Str : ".$record{strings}."\n" if ($record{num_str} > 0);
print "Message Data : ".$record{data}."\n" if ($record{data_len} > 0);
print "\n";

# length of record is $record{length}...skip forward that far
$ofs = $evt->locateNextRecord($record{length});
# printf "Current Offset = 0x%x\n",$evt->getCurrOfs();
}
$evt->close();

One of the programs I use to create the database is SqliteSpy. This is a nice gui to create and view the data that you load into the database. What I did was create a table with the following definition:

CREATE TABLE events
( file_name text,
Record_Number number,
Source text,
Computer_Name text,
Event_ID number,
Event_Type text,
Time_Generated text,
time_generated_unix number,
Time_Written text,
time_written_unix number,
SID text,
Message_Str text,
Message_Data text);

You can compare this definition to the $record in the lsevt3.pl script. I have added 3 extra columns to make the table more flexible, they are:

file_name which is the event file name that is being loaded, this allows for multiple event logs to be inserted into the database.

time_generated_unix and time_written_unix were added to allow for easier selecting and sorting of timestamps.

The following is the changed lsevt3 program that does the inserts into the database (Added lines in Bold):

#! c:\perl\bin\perl.exe

use strict;
use File::ReadEvt;

use DBD::SQLite;

# Attributes to pass to DBI to manually check for errors
my %attr = (
PrintError => 0,
RaiseError => 0
);

# Create the connecton to the database
my $dbh = DBI->connect("dbi:SQLite:events.db3","","",\%attr);


my $file = shift || die "You must enter a filename.\n";
die "$file not found.\n" unless (-e $file);

my $evt = File::ReadEvt::new($file);
my %hdr = ();

my $sid = "";
my $message = "";
my $data = "";

if (%hdr = $evt->parseHeader()) {
# no need to do anything...
}
else {
print "Error : ".$evt->getError()."\n";
die;
}

my $ofs = $evt->getFirstRecordOffset();

# Make it so Inserts run in a batch mode
$dbh->do("Begin Transaction");

while ($ofs) {

my %record = $evt->readEventRecord($ofs);

# Convert data and check type to be inserted
if ($record{sid_len} > 0) {
$sid = $record{sid};
} else {
$sid = "";
}
if ($record{num_str} > 0) {
$message = $record{strings};
} else {
$message = "";
}
if ($record{data_str} > 0) {
$data = $record{data};
} else {
$data = "";
}

# Insert statement for the data into the events tables Use prepate and execute to handle quotes in the string fields
my $sql_stmt = qq{Insert into events values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)};
my $sth = $dbh->prepare( $sql_stmt);
$sth-> execute( $file, $record{rec_num}, $record{source}, $record{computername},
$record{evt_id}, $record{evt_type}, $time_gen, $record{time_gen},
$time_wrt, $record{time_wrt}, $sid, $message, $data);

# Check for any errors in the insert statement
my $err_desc = $dbh->errstr();
if (($err_desc =~ m/not\sunique/) || ($err_desc eq "")) {
} else {
print "Error in Database $err_desc\n";
print "loading Record ".$record{rec_num}."\n";
}



# length of record is $record{length}...skip forward that far
$ofs = $evt->locateNextRecord($record{length});
# printf "Current Offset = 0x%x\n",$evt->getCurrOfs();
}

# Commit the Batch
$dbh->do("Commit");

$evt->close();


By running this program from the command line, lsevt3_db.pl Sysevent.evt, the events will now be loaded into the Sqlite database. You can then load multiple event logs into the table and report on them through sqlite.

The following is an example of a query to show when the Removable Storage Service wrote to the event log:

select * from events where source like 'Remov%';

or

To show the when the computer was started and stopped.

select * from events where event_id in (6009, 6006) order by time_generated_unix desc;

If you were to add the application events then you can see everything that happened during a specific time period as well (now you will see why the unix time is important to have since it is much easier to use and sort by).

select * from events where time_generated_unix between 1168484317 and 1168516719 order by time_generated_unix desc;


Now if you use x-ways forensics you can define the perl script under the external viewer programs and when you select a file you can have it run this program and it will load up the database as if you were running the program from the command line.

If there is interest I can post a generic perl script to print out reports from the database, just leave some comments and I will put one out there.

Hopefully I did not confuse you to much, if I did then let me know and I will try and make it less confusing.

A Tiddly Wiki Travel Notebook

How many times have you been on site somewhere and not had access to the Internet and wanted to get some small piece of information that you can't quite remember but know where to look for it on the net. Well TiddlyWiki can come to the rescue. Here is a excerpt from there website:

a free MicroContent WikiWikiWeb created by JeremyRuston and a busy Community of independent developers. It's written in HTML, CSS and JavaScript to run on any modern browser without needing any ServerSide logic. It allows anyone to create personal SelfContained hypertext documents that can be posted to a WebServer, sent by email or kept on a USB thumb drive to make a WikiOnAStick. Because it doesn't need to be installed and configured it makes a great GuerillaWiki. This is lastest version is 2.1.3, and is published under an OpenSourceLicense.

I have added some information to a TiddlyWiki to get anyone who downloads it started. I tried to enter some tiddlers (name given to a unit of microcontent) with examples of how you can use it to try and give you a leg up. It can be saved from the following link

http://RedWolfComputerForensics.com/downloads/Computer_Forensic_Tiddly_Wiki.htm

Now for the challenge. How much information do you think that we can put in this wiki and help spread our knowledge to each other. If you would like to help out on this little project you can email me @ Mark.McKinnon@sbcglobal.net (Put "Forensic Wiki" in the subject) with your entries and I will put them in the wiki with the proper credit to you.

Questions/Comments/Suggestions/Help?