Windows Insider

Why Read When You Can Parse?

Microsoft's Log Parser might be the one of the most flexible tools you never knew about.

• By Greg Shields
• 04/01/2007

But truth be told, there really is a difference between reading your mail and parsing it. I mean, do you really read through the 18 credit-card solicitations you get every day? Probably not. You instead parse them to find just the mail that's truly important to you.

In the Windows world, Microsoft's often-overlooked Log Parser tool does the same thing. Initially built to handle searching IIS logs, Log Parser's current version is capable of querying and converting over 20 different log file formats into 13 different textual and graphical outputs. It uses a dialect of SQL to provide rich search, aggregation and sorting capabilities limited only by the scripting ability of the user. Though some predefined log formats like Windows Event, IIS and NetMon logs are available by default, virtually any well-formed data structure in a text file can be manipulated using this command-line tool.

To use Log Parser, download it from the Microsoft Web site here. After installation, check out the command logparser -h to learn more about the syntax and file formats supported.

[Click on image for larger view.]

Figure 1. Using -queryinfo exposes the fields Log Parser can query against.

Parse Like a Pro
Let's look at a couple of examples of how Log Parser can enhance your vision into your systems' log files. One very simple query is to check the last 100 entries of your Windows System Event Log to see if any NETLOGON errors have appeared. If you find any, report the error message to the screen:

LogParser.exe "SELECT TOP 100 message FROM system WHERE sourcename = 'NETLOGON'"

As you can see above, using Log Parser most effectively means knowing a little about SQL querying.

Reporting information to the screen is only so useful, because it can scroll by you far too fast to read. If you want to store the results in a text file called results.txt, you make the following change to your select statement:

LogParser.exe "SELECT TOP 100 message INTO results.txt FROM system WHERE sourcename = 'NETLOGON'"

Whether it's the event log or any other input stream you're pulling information from, you can always add the -queryinfo switch to return information about what fields can be queried against. Interested in the fields available for the Security Event Log? Use this syntax:

LogParser.exe "SELECT * FROM security" -queryinfo

You'll get back the list of the fields that can be queried from the Security Event Log, as well as each field's associated data type including integer, string, time, and so on.

We all know it's a good practice to enable security logging, but getting useful data from that security log can sometimes be almost impossible. If we want to find out if the user "bgates" has been attempting to read files for which he has no access, we can use the -resolveSIDs switch to resolve user SID information and query against his username:

LogParser.exe "SELECT * INTO results.txt FROM security WHERE sid LIKE '%bgates%'" -resolveSIDs:ON

Another useful query involves aggregating folder sizes for a list of folders. Interested in finding out the top 10 consumers of disk space on your company's home drives? The syntax below assumes that home folders are rooted at H:\home with the user's name as the subfolder name:

LogParser.exe "SELECT TOP 10 folder path, sum(size) USING EXTRACT_PREFIX(EXTRACT_PATH (path), 2, '\\') AS folderpath FROM H:\home\* GROUP BY folderpath ORDER BY sum(size) DESC" -i:fs

As you can see, this sort of request involves a bit more SQL experience to sum all the subfolders of each user's first level folder. It uses the extract_prefix function to grab the folder name of each folder below H:\home and sum the contents. The top 10 folders by size are returned to the command window.

Log Parser can handle chart creation as well. If you've installed the Microsoft Office Web Components to the machine where Log Parser is running, you can format the results of this or any numerically valued query to a chart graphic. Though the Microsoft Office Web Components are not a part of Office 2007, they're still available for Office 2003 and can be downloaded from Microsoft's Web site.

To change the output format of your folder size query in order to create a chart, change the syntax to include the -o:chart switch and a pointer to the chart's filename, FolderChart.gif:

LogParser.exe "SELECT TOP 10 folder path, sum(size) USING EXTRACT_PREFIX(EXTRACT_PATH (path), 2, '\\') AS folderpath INTO Folder Chart.gif FROM H:\home\* GROUP BY folderpath ORDER BY sum(size) DESC" -i:fs -o:chart

Log Parser's real strength lies in its extensibility. Not limited to just preconfigured Windows logs, Log Parser can search and aggregate data in any comma- or tab-delineated file with a header row. This means that any vendor's log file format can potentially be parsed. Check out the .TSV file shown here:

Month User Sales
Jan   Dan  100
Jan   Mark 235
Jan   Mark 210
Feb   Dan  50
Feb   Dan  520
Feb   Joe  445
Mar   Mark 320
Mar   Lee  420
Mar   Dan  110
Apr   Lee  120
Apr   Lee  60
Apr   Mark 510

Using this syntax we can use the SQL query capabilities of Log Parser to find out Dan's sales for each month:

LogParser.exe "SELECT month, user, sales FROM sales.tsv WHERE user = 'Dan'"

A Question of Support
Having been built as a "skunk works" project by one of Microsoft's developers, all of this nifty functionality comes with virtually no Microsoft support. At least that's the word from the tool's unofficial Web site at www.logparser.com. This Web site sports a few useful parsing recipes in its repository and a lightly attended forum for questions. There's even a book available, written by the tool's creator, which details even more creative solutions one can build using the Log Parser tool.

In any case, if you've got an interesting problem you've solved using Log Parser, let me know. The boundaries of this extensible tool are limited only by your imagination.