Log Parser (Microsoft)
The "Swiss Army Knife" for Intrusion Investigators and Computer Forensics Examiners
Backgrou nd and resources:
nd and resources:
Microsoft has, for quite some time, produced a log parsing tool known as "Log Parser". It's current release (as of June 2006) was version 2.2. It is available as a free download from Microsoft at: http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en .
Microsoft doesn't officially support this tool, dubbing it internally as "skunkware". Microsoft has actually placed this utility on some of its resource disks in the past, but doesn't really promote its use. This is most unfortunate as this is a utility that really works and is the "Swiss Army Knife" of the log parsing utilities that are available. It is incredibly versatile and is also very fast at getting the job done and done well.
As Microsoft doesn't really do this tool justice, a log parser support site has been formed to promote its use and growth, which is at: http://www.logparser.com/ and is dubbed the "The Unofficial Log Parser Support Site". Additionally, the Microsoft engineer who developed this tool, Gabriele Giuseppini, has partnered with Syngress to write a book entitled "Microsoft Log Parser Toolkit". If you register the book with Syngress, you may download a zip file from them that contains every query detailed in the book.
When you consider that the tool is free from Microsoft and you can purchase a toolkit (book and queries) for under $40.00, you have a powerful tool at a bargain price.
The only downside to this tool is a front-end learning curve. If you are accustomed to working with SQL queries, there is none, but if you aren't you will have to learn some basic SQL queries to effectively work with this tool. Giuseppini's book will get you over that hurdle and if give it a day of your time, you'll master this tool and SQL queries.
Log parser consists of three components, which are: 1) input engine, 2) SQL query engine, and 3) output engine. The input engine and output engines are truly incredible and, combined, make this tool shine. When investigating network intrusions, you are faced with analyzing logs from many sources, none of them being compatible with the other. Log parser can accept most any common log format and output it into one of many formats of your choosing. When you are done, you can combine all your disparate logs into one common format for analysis.
At any point in the process you can subject your logs to a query so that you narrow down the data to that which is relevant. While many GUI tools are out there that provide filters, even those that allow the user to build custom filters can't compare with the power of writing a custom SQL query in Log Parser.
As an intrusion investigator / forensic examiner, you are tasked with mastering many tools to get your work done. It would be nice if we only had to master a couple of tools, but such will never be the case. We can however, limit the number of tools we have to use if we make careful selections. Whenever you can use one tool that will handle multiple tasks instead for multiple tools for the same number of tasks, that should be your tool of choice. Log parser fits this criteria as it can process and query all the common logs formats and can address your file system and your registry as well, including those of remote systems.
The best way to get to know this tool is to use it daily in the administration of your systems. You can create batch files to run your SQL queries against your logs, place them in your scheduler, and have critical log reports sitting on your desktop each day when you come to work. By getting to know this tool and its capabilities in this manner, you can apply those acquired skills to forensic applications of this tool. In the end, you'll have better management of your systems and have a forensic tool that you'll find new uses for with every case you process.
This section will begin with a basic query so the user can begin the process of using log parser. As time permits, other useful queries will be added.
A basic SQL query must have, at a minimum, the following:
In other words, you must tell it what to "SELECT" and "FROM" which source or location. After that, everything else is "optional" and the query can get quite detailed and complex, but at a minimum, every query must have those two items.
To continue, you must have installed log parser. Open the command (cmd.exe) line interface in the root of the folder "C:\Program Files\Log Parser 2.2" wherein lies the executable "logparser.exe". From the command line interface, type in the following:
logparser.exe -i:EVT -o:NAT "SELECT TimeGenerated, EventID FROM System"
Press enter and you should see:
The "-i:EVT" is the input engine argument telling log parser that the format is coming from the Windows Event Log format. Log Parser is smart enough to know this information in many cases and it is not necessary always to enter this argument. Similarly, the "-o:NAT" is the output engine argument telling log parser to format the output into the NAT or natural format. This will happen by default and thus using the output argument is not necessary if you want NAT output (to screen).
Thus entering logparser.exe "SELECT TimeGenerated, EventID FROM System" would yield the same result as above.
Another output feature of log parser is its "DATAGRID" output. Instead of dumping the query to a screen, you can send it to a GUI interface. To send it to a datagrid, enter the following:
logparser.exe -i:EVT -o:DATAGRID "SELECT TimeGenerated, EventID, message FROM System" and you should see the following:
The types of inputs are, as mentioned, very extensive as are the types of output, which can be CVS, graphics, syslog, etc. Let's turn our attention away from input and output and to the SQL query contained in the quotes, which is "SELECT TimeGenerated, EventID, message FROM System". In this case, we are telling the query to return or "SELECT" the following fields from the event log database: TimeGenerated, EventID, and message. We could have chosen others or all. The FROM argument tells log parser the source for the log. For Windows event logs, it could be the standard event log names "System", "Security", "Application" or others. It can also point to a specific file to process by its full path, which is useful for forensic applications.
We can also place conditions on the records to display based on "WHERE" statements. If we wanted only only a certain event, we could create a "WHERE" statement such as: WHERE EventID=520 (Shows when system time was changed!).
Enter the following:
logparser.exe -o:DATAGRID "SELECT TimeGenerated, EventID, message FROM Security WHERE EventID=520"
Press enter and you should see, assuming your are logging system events, the following:
Note in this log you can distinguish between time changes created by the ATC program and those by the user (rundll32.exe) and you can also determine which user did so. We also told log parser to SELECT message, giving us the event log message.
Command line commands are great, but you can automate this whole process into SQL query files and launch them with batch files, making this powerful tool a "one-button" operation. There is also a GUI on the support site, but even that tool requires some SQL knowledge to use it effectively. Everything up until now was intended as a primer for those new to log parser and SQL in general. Our next step is advanced and for those wishing to take the next step.
Remote Desktop Logins:
Let's create a SQL query file that captures Remote Desktop connects and reconnects (XP / Win2003) and then launch it with a batch file. In notepad, type in the following:
The "EXTRACT_TOKEN" is a log parser function whose explanation far exceeds our purpose in this tutorial. In short, it is pulling out numbered and delimited fields within a field called "Strings" and is assigning them a field name for use by the query. The "fields" within "Strings" vary by EventID and you need to research them and create the queries as you go along.
The %Source% following FROM creates a parameter or custom variable that will be specified later in the batch file and passed to the query. The ORDER BY timegenerated tells the query to be sorted by the "timegenerated" field.
Save this file in the folder "C:\Program Files\Log Parser 2.2" wherein lies the executable "logparser.exe". I have called it "TSLoginsDetails_More.sql" and it is available for download by following the link.
Next, let's build a batch file to run this query that specifies the output type and passes the variable. Open notepad and type or paste in the following:
Save this file and name it "TSLoginsDetails_More.bat" or download it at the link, placing it also in the folder "C:\Program Files\Log Parser 2.2" wherein lies the executable "logparser.exe".
Note the format for executing log parser. Immediately following logparser.exe is the argument file:TSLoginsDetails_More.sql , which tells log parser which SQL query file to run. Immediately following the file argument (no spaces) begins the parameter or variables to pass to the query. In this case we pass ?source=Security to the query file, telling the query that the variable named "source" is to use "Security". We could change our batch file and specify a path to a log file as well. Finally, we tell the log parser to use the DATAGRID for output.
When we run this batch file by clicking on it, we see the following:
You can see above that there is considerable information returned by this query that can be quite useful in any network investigation in which remote desktop is an issue.
As time permits, more queries will be posted and if you have any you wish to share, send them along and credit will be given with the posting.