Web Development

Log Parser Studio provides a great interface to Microsoft Log Parser

Log Parser Studio simplifies using the features of the versatile Microsoft Log Parser. Here's a look at how to use the tools together.

A recent call from a client revealed worrisome activity on their ecommerce site. The problem was ultimately traced to a vindictive former employee, but we spent quite a few hours investigating the situation, with the web server logs being the biggest help during this time (you can never have too many logs). These log files contain lots of valuable information, but combing through them can be mind-numbing. Thankfully, there are plenty of tools available to provide assistance; Microsoft Log Parser is my favorite, and the Log Parser Studio provides a great interface. Here's a look at both tools used together.

Microsoft does something right

Log Parser was developed more than a decade ago. While I have worked with other similar tools during that time, I repeatedly return to Log Parser due to its broad support for file types and the flexibility afforded by its SQL-like language. Log Parser is freely available from the Microsoft Download Center -- it shows up as Log Parser 2.2 under Program Files once it is installed (its help file provides great information on usage).

By default, Log Parser offers a command-line interface that lets you do anything and everything needed. While it has log in its name, it can work with a vast number of file types including, but not limited to, web server logs, Windows Event logs, CSV files, XML, Windows registry, Active Directory, and on and on. My usage is usually restricted to processing web server logs, but I know IT professionals who use it with other file types.

The command-line interface is powerful, but Log Parser Studio greatly simplifies using the many features of the Log Parser tool. Figure A shows Log Parser Studio opened on my development machine with a test library created for this article. Figure B shows a simple query executed against web server logs on my server (it returns the top 10 rows from a specific log file).

Figure A

MSLogParser_FigA_102513.jpg
The basic Log Parser Studio interface

Figure B

MSLogParser_FigB_102513.jpg
Running a basic query against a standard web server log file

The Log Parser Studio interface is intuitive, with ribbon buttons (or icons) for standard action along the top as shown in Figures A and B. The red exclamation mark runs the current open query with the results shown in the middle window as Figure B displays. There is one results area per query tab. The gray icon with double exclamation marks (!!) allows you to run multiple items as a batch, so you are not restricted to one query at a time. You can save data in a number of formats including CSV, tab-delimited, XML, IIS logs, and even as a chart image.

SQL-esque syntax

One of Log Parser's best features is its usage of a SQL-like language for querying and working with data, which is a great feature for developers who get ultimate control over their interaction with the data. As shown earlier, Log Parser Studio provides an easy-to-use interface for writing and executing these queries. In Figure B, I returned the first 10 rows of a log file on my local development server -- it used the following basic query.

SELECT TOP 10 * FROM 'C:\inetpub\logs\LogFiles\W3SVC1\u_ex130813.log'

If you're familiar with using SQL to query databases, you might recognize this syntax.

The next example returns the number of requests per hour from all log files (it uses asterisks as wildcards) in a certain directory.

SELECT 
QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour Of Day, 
COUNT(*) AS Total Hits
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\u_ex*.log'
GROUP BY Hour 
ORDER BY Hour

You may choose to save the results of your query in a comma-separated file (CSV), which is easily accomplished via the INTO statement as the following query demonstrates.

SELECT 
QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour Of Day, 
COUNT(*) AS Total Hits
INTO ‘C:\output.csv’
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\u_ex*.log'
GROUP BY Hour 
ORDER BY Hour
Just to prove that it can do more than process web server logs, here is a query that returns all of the words in text files in the current directory ordered by the number of times they appear.

SELECT Text, COUNT(*) AS Total
FROM *.txt
GROUP BY Text
ORDER BY Total DESC

The previous example may not be very useful, but it does demonstrate the tool's flexibility, as well as showing what is available on the web. The longevity of the tool means there are countless others out there using it, and most of the queries or issues that come up have been written or solved by one or more of them. A simple web search yields a mountain of data on usage, and there are plenty of queries available for your use (I found the last example via a Google search). If you're more inclined to traditional learning methods, you might check out the book Microsoft Log Parser Toolkit.

Easily parse data to get what you need

Log Parser has been around for years, and I am always surprised that many developers and administrators are unaware of its existence. It easily integrates with Microsoft environments, but it can process files from other environments as well. Its versatility and flexibility with supporting numerous file formats and ease-of-use offered by the Log Parser Studio client makes it a great tool when researching an issue or just handling daily chores. This article offers a brief peek at its features, but I hope it makes you consider using Log Parser the next time you need to quickly locate information within a pile of log or other data files.

 

About

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

0 comments