Microsoft Log Parser Studio (LPS) has become one of my favourite (free) tools whenever it comes time to work with log files of any significant size. In the past I have always used one of the pre-define log type formats when working with log files. Recently I needed to work with SQL Server error logs and was a little concerned initially that there was no pre-defined format for these logs and trying one of the standard log types didn’t give me the output I expected (or needed).

With a little bit of effort I managed to figure out what was required, it turned out to be fairly simple. The trick was using the TSV format and then customising the settings.

 

Now that TSV has been selected, we need to customise the settings.

The TSV input format customisation window is now shows. We need to make changes as follows for it to work. Modify your settings so they match the ones below.

Basically, it is configured to skip the first 5 lines, to use SPACE as the separator (instead of TAB), and then to have four fields. The number of fields was key to making this work as the main text line of the error log entries contains spaces, so it must be the last of the four fields which then still allows it to contain the spaces.

Configure as above if we run the default LPS query against our error log we receive the following.

The fields we now have to work with are the filename and line number (added in by LPS) and then the date, time, process and error text. I have built some queries which suited my purpose and which allowed quick analysis and review of the error logs. I will share these in my next post.

For those curious as to the performance of LPS in working with large log files. I was running this on a Dell Latitude E5540 with i7 and 16 gig of ram.

The log file covers about a 5 week period, over 2.2 million entries, is about 250meg, and can be summarised in around 5 (five) seconds. That makes working with the file quite feasible and a whole lot better than trying to do fancy footwork with a text editor or Excel.

I will share some of the queries I have built as an LPS XML file in a (hopefully to be soon) future post. This provides some custom splitting of the main error message text into more specific fields and some easy ways to summarise the data in looking for anomalies.

Tags: Logs, LPS

Categories: Security

---