Using Log Parser to Read Log FilesLog Parser COM ArchitectureWhat to do with Log Parser?
Log Parser is a tool available from Microsoft that allows you to run SQL queries against log files and send the information to a SQL Database or other file formats. In this article I describe the capabilities of Log Parser and how you can use it as a set of COM interfaces from your application.
Using Log Parser to Read Log Files
Microsoft has a slick tool tucked away in the IIS 6.0 Resource Kit called the Log Parser 2.1. An earlier 2.0 version of the tool is also available for Windows 2000. Mike Gunderloy has a nice FAQ on how to use the Log Parser tool from the command line and an Unofficial Support Site.
Log Parser is a very slick way to extract and analyze the data in your IIS, NCSA and W3C Log files as well as the Windows Event Log, CSV files and file information from a directory. You can write queries on the data directly from the files using SQL.
Here’s a few example queries from the documentation.
The first runs against a W3C Extended Log File:
SELECT time, REVERSEDNS(c-ip), cs-uri-stem, cs-uri-query, sc-status FROM ex*.log TO MyTable WHERE (sc-status > 200 AND sc-status <> 404) OR time-taken > 30 ORDER BY time
A query against the Windows Event Log:
SELECT Message, COUNT(*) AS TotalCount FROM Application GROUP BY Message HAVING TotalCount > 2
A query that retrieves a list of large files on Drive D:
SELECT Path, QUANTIZE(Size, 1000000) AS Megs FROM D:\*.* WHERE Megs > 0 ORDER BY Megs DESC
Dan Bright has a write-up that lists the various input formats available for Log Parser.
Log Parser also has output targets. You can use the utility to query from, for example, the IIS log files and insert the results into a SQL Database. You can also accomplish things like splitting your log files into multiple files based on a field.
This query from the documentation will split the log files. One file will be created for each IP address occurring in the logs:
SELECT c-ip, date, time, cs-uri-stem, cs-uri-query FROM ex*.log TO exclient*.log
You can output to a SQL table, CSV files, XML files or text documents based on a template. There are a few others as well.
Log Parser COM Architecture
One of the cooler things about the Log Parser is that it is exposed via COM interfaces so that you can script the tool or call it from your application. I found this very useful a few weeks ago to create a batch process that downloads log files and imports them into a database, doing a little work on the data as it gets moved around.
I was able to reference the Log Parser type library in my .NET project and utilize it though the provided interfaces. Once you get use to creating SQL queries on your log data using the command line interface, using the COM interfaces is fairly easy:
- Set up a reference to the Log Parser component library in your VS.NET project.
- Create an Input Context compatible with the format of your input files.
- If you want the data outputted to another format, create an Output Context.
- Use the LogQueryClass interface to execute a query using the Input context and the optional Output Context.
To reference the Log Parser, look for the “MS Utility 1.0 Type Library – LogParser Interfaces collection” COM component in the Add Reference dialog.
Pick an Input Context that is appropriate for the format of the log files you want to query. My log files were in the IIS W3C format, so I used the COMIISW3CInputContextClass interface.
It’s useful to note that the Log Parser has support for an IIS format, W3C format and an IIS W3C format. There are differences between each. As far as I can tell, the difference between W3C and IIS W3C formats in Log Parser is the data types. The W3C format uses the string data type for each field whereas the IIS W3C format maps fields to appropriate integer, string and timestamp data types.
Create an instance of the Input Context class:
MSUtil.COMIISW3CInputContextClass inputContext =
new MSUtil.COMIISW3CInputContextClassClass();
Choose the Output Context according to where you want to send the results of the query. I used the SQL Output Target. The SQL Output Target will send the results to a SQL table. If the table does not exist, this output target can create the table for you with the fields used in the query.
Create an instance of the Output Context class and set properties for the context:
MSUtil.COMSQLOutputContextClass outputContext =
new MSUtil.COMSQLOutputContextClassClass();
outputContext.clearTable = false;
outputContext.createTable = true;
outputContext.database = "DBNAME";
outputContext.server = "SERVERNAME";
outputContext.username = "USERNAME";
outputContext.password = "PASSWORD";
outputContext.driver = "SQL Server";
Create a new instance of the LogQueryClass:
MSUtil.LogQueryClass logQuery = new MSUtil.LogQueryClassClass();
Now execute a batch query against the log files and output the results to the database:
String query =
"SELECT TO_TIMESTAMP(date, time) as dateTime, c-ip, cs-username, " +
"s-sitename, s-computername, s-ip, s-port, cs-method, cs-uri-stem, " +
"cs-uri-query, sc-status, sc-win32-status, sc-bytes, cs-bytes, " +
"time-taken, cs-version, cs-host, cs(User-Agent), cs(Cookie), " +
"cs(Referer) FROM C:\\Log\u005cu0020Files\\ex*.log TO Hits";
logQuery.ExecuteBatch(query, outputContext, inputContext);
UPDATE: The above line should read:
logQuery.ExecuteBatch(query, inputContext, outputContext);
If you don’t want to send the results to an output context, you can call the Execute method. This returns an ILogRecordset that you can iterate through and work with each result row.
What to do with Log Parser?
So what other things might you do with Log Parser? I can think of a few, but you can probably think of more.
- Wrap the Log Parser in a DataReader class to easily read Event Logs or CSV files into a DataGrid.
- Build a monitor application that queries the Event Log for critical events.
- Build a query to summarize log files into an XML Document for display as a report on your Intranet.
Updates
01/13/2003 Roy Osherove says that the next version of LogParser will allow parsing of multiple line log files.
01/13/2003 Steve Makofsky will use LogParser to extract referrers for his blog. Good idea.
01/15/2003 Steve Makofsky shows another method for invoking the LogParser COM library.