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
My wife is always quick to reprimand me when I bring home geek speak. She'll
say, "You don't interface with people, you talk to them!" Or, "You're
not parsing your mail, you're reading your mail!"
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 =
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
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"
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 =
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
Greg Shields is Author Evangelist with PluralSight, and is a globally-recognized expert on systems management, virtualization, and cloud technologies. A multiple-year recipient of the Microsoft MVP, VMware vExpert, and Citrix CTP awards, Greg is a contributing editor for Redmond Magazine and Virtualization Review Magazine, and is a frequent speaker at IT conferences worldwide. Reach him on Twitter at @concentratedgreg.