Corey Harrell from Journey Into IR posted a great article on using Excel filtering and advanced filters to drill down into the timeline for relevant or key information, and there are a few other posts out there discussing a similar approach.  There are surely many ways that an examiner can filter or otherwise eliminate irrelevant data from the timeline, but I would like to discuss one in particular here.

For those who haven’t used it, Log Parser is a free tool published by Microsoft (written by Gabriele Giuseppini) capable of interpreting data files as SQL records that can be readily queried by Log Parser using SQL commands.  Although Log Parser is capable of interpreting many types of logs and other data files, what specifically interests me in the realm of timeline analysis is the ability to query csv files.  Given the ability to process a timeline as a SQL database, a forensic examiner that has an idea of what they’re targeting can easily, and more importantly, quickly drill down to the essential timeline information that is relevant to the case.

For those of you who may not be very well versed when it comes to SQL, rest assured that by no means do I consider myself an expert in constructing SQL queries.  That’s the beauty here – you don’t have to be.  You do need a basic understanding of building SQL queries and the fundamentals of using Log Parser, but in the end we’re just performing basic queries.  If you need to gain a better understanding of SQL, there are tons of resources on the web (not to mention several books on the topic).  There’s also plenty of resources on Log Parser (and at least one book that I know of).

One nuance that you have to deal with when using Log Parser on a csv created by mactime is the issue of column header naming.  On a side note, Log Parser creates two virtual columns that can be used if needed – “FileName” and “RowNumber”.  “FileName” refers to the csv file that is taken as input and processed by LogParser, while “RowNumber” refers to the actual row number within the csv file that a match is found.  Mactime creates a column header titled “File Name”.  The space between “File” and “Name” can be a nuisance to deal with in SQL, so I’ll simply open the csv file in Notepad++ and change “File Name” to “File_Name”.  This will make the queries easier and a bit cleaner.

Let’s move into an example.  We’ll assume that our timeline is already in csv format and that we used mactime against a bodyfile for the conversion (as opposed to the csv output module of log2timeline).  Note that the timeline used in this example is only a file system dump.  Suppose you were interested in files referencing CCleaner from any time in 2011.  By running the following query, you could view the matching rows in a table (defined by Log Parser as a datagrid).  Note that I specified to only display four columns in order to make the screenshot easier to read.

logparser -o datagrid “SELECT file_name, date, size, type FROM timeline.csv WHERE file_name LIKE ‘%ccleaner% AND date LIKE ‘%2011%””

logparser “SELECT * INTO C:timelineDataCCleaner2011.csv FROM timeline.csv WHERE file_name LIKE ‘%ccleaner%’ AND date LIKE ‘%2011%'”

I will often start by viewing the results in the datagrid provided by Log Parser, and then export the rows to a separate csv file after I have verified that the query does indeed return the data that I’m interested in.  The ability to run a SQL query against my timeline often greatly reduces the time and effort that I need to find relevant information.

While timeline analysis and the use of Log Parser is nothing new, I think that the two coupled together pose for an efficient means of analyzing timeline data.  This of course assumes that the examiner has an idea of what they’re targeting.  So the next time you’re working with a timeline, consider running a few queries on it using Log Parser.