Managing the many logs generated in a Windows environment is a time-consuming—though necessary—part of systems administration. Each workstation, member server, and domain controller (DC) has a Security, Application, and System log, all of which contain valuable security and system information. Depending on your environment and which Windows components you use, you might also have logs generated by Internet Authentication Service (IAS), Microsoft IIS, RRAS, and URLScan, not to mention the logs generated by application servers such as Microsoft Exchange Server and Microsoft SQL Server. Each of these logs has a different format and structure as well as a hefty amount of noise—activity that you must filter out before you can find important events. Wouldn't it be great to have a tool that could read and execute SQL-like queries against any type
of log? This dream is reality in the form of LogParser, a command-line utility that you can use with Windows 2000 and later. LogParser gives you the data-mining power of a SQL database such as Microsoft Access, and you can use the tool to automatically process the megabytes of data that your network's diverse logs generate every day. As I write this article, the most recent version of the tool, LogParser 2.1, is available for download as part of the IIS 6.0 Resource Kit Tools (http://www.microsoft.com/downloads/details.aspx?familyid=56fc92ee-a71a-4c73-b628-ade629c89499&
displaylang=en).
How It Works
LogParser has three main parts: input processor, data engine, and output processor. The input processor supports native log formats such as IIS logs and Windows log (.evt) files. LogParser can also read comma-delimited (.csv) files, ODBC databases, and text files delimited by carriage returns. The input processor converts each log type into a uniform format, which the LogParser data engine can then process in much the same way that a database processes tables.
If you're familiar with SQL SELECT statements, you'll probably have little trouble writing LogParser commands. Within a LogParser command, you can specify which log fields you want to include in the tool's output, which event records to include or exclude (according to field-value comparisons that you specify), and how you want LogParser to sort output records. But you can also do much more. The data engine supports advanced query functions such as record counts, averages, and top X events. You can also manipulate text fields and perform date and numeric calculations to customize your output or refine your selection criteria.
After the data engine massages the input data and produces a result set, the output processor takes over and formats the result set into an output table. Like the input processor, the output processor supports many file types, so you can format the output table any way you like—from plain text files to SQL databases to XML files.
A Simple Query
To give you a taste of what you can accomplish with LogParser, let's begin with a sample command and its output. The following command queries the local system's Security log and produces a report of all locked-out accounts.
logparser "SELECT DISTINCT SID FROM security WHERE EventID = 644"
The above command produces the output that Figure 1 shows. (This output supplies only the SIDs of the user accounts that have been locked out; later I show you how the tool can resolve SIDs to user names.) As you can see, LogParser is powerful and its syntax is straightforward. The only mandatory argument is the SELECT statement, which you must enclose in quotes. (Also be aware that LogParser is case sensitive when reading most input information, so, for example, the tool considers "eventid" as different from "EventID".) Depending on the type of logs you're working with, you might also need to include parameters about the input logs or the format of your output files.
Formulating Queries
To get the most out of LogParser, you must understand how to formulate your queries. Because LogParser natively supports the Windows event logs, writing queries for the Security log is simple and doesn't require you to give LogParser any additional instruction regarding how to parse the log. Therefore, I use the Security log to show you how to write effective queries. You can then apply that knowledge to other types of logs.
LogParser's SELECT statement comprises two mandatory clauses—SELECT and FROM—and several optional clauses:
SELECT clause FROM clause [TO clause] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause]
The SELECT clause specifies the fields to include in each record of the query's result set. The FROM clause tells LogParser which log or logs to use as input for the query. The TO clause tells LogParser where to direct the output. The WHERE clause lets you specify criteria for filtering records into or out of the query. The GROUP BY and HAVING clauses are advanced clauses that let you analyze groups of similar records, calculate aggregate functions on those groups, and specify criteria for filtering groups into or out of the query. The ORDER BY clause lets you sort the result set by specified fields. Let's take a closer look at the FROM, SELECT, and WHERE clauses.
FROM. To execute a query against the Windows Security log, use the FROM clause
FROM security
You can replace "security" with "application" or "system" to query the other two standard Windows event logs. To query other types of logs, you must specify the log filename in the FROM clause. (I'll discuss this technique in greater depth in an upcoming article.)
SELECT. After you've decided which logs to query, the next step in building a LogParser command is to write your SELECT clause. This clause specifies a comma-delimited list of the fields from the input log that you want to appear in the query's output.