Get a Load of Log Parser

Does having access to a variety of valuable Exchange Server-related data sources, including the Windows event log, the system registry, Active Directory (AD), and Exchange Server message-tracking logs sound like a wish come true? Then Log Parser is the tool for you. This free Microsoft utility can quickly interrogate those data sources and output results in a variety of formats, including plain text, HTML, charts, or data that you can easily import into a Microsoft SQL Server database. Read on to find out how you can use this fast, powerful, and flexible utility to extract information about your Exchange servers' messaging traffic.

Using Log Parser
You can download the Log Parser kit from the Microsoft Web site (http:// www.microsoft.com/downloads/ details.aspx?familyid=890cd06b-abf8-4c25-91b2-f8d975cf8c07&display lang=en). The rudimentary installation procedure, which typically takes less than a minute, creates the Log Parser 2.2 directory under program files. This directory contains all the Log Parser files, including the logparser .exe executable. If you've ever used SQL, Log Parser will be familiar. For example, to count all the messages received on the local server, I used the following command:

logparser "SELECT COUNT(*) FROM C:tempserver.log WHERE [Event-ID] = 1028" -i:W3C 

This command generated the output that Figure 1 shows. All I asked Log Parser to do is to count the number of records in the log file (i.e., C:tempserver.log) to which I pointed the utility. You can pass parameters such as c:logs*.log to instruct Log Parser to process all the message-tracking logs in a directory. In the previous example, Log Parser looks for records with an event ID of 1028, which is the identifier that Exchange writes into the message-tracking log when it delivers a message to a local mailbox on a server. The -i:W3C parameter tells Log Parser that the input file is in World Wide Web Consortium (W3C) format. This speedy utility can process even very large message-tracking logs in a flash: When I ran this sample command, Log Parser processed more than 1,000,000 records in 16.5 seconds.

Task 1: Who Gets the Most Mail?
Let's execute a more sophisticated (and useful) query. You can use Log Parser to look for the top 10 recipients of messages delivered to mailboxes on the server.

First, you need to know which fields exist in the source logs. You can ask Log Parser to parse a file and report what it finds:

logparser -h -i:w3c C:temptracking.log 

Log Parser treats fields in message-tracking logs as either strings (S) or integers (I). Listing 1 contains a query that collects 1028 events and groups those events by recipient. That query is obviously more complex than the others and wraps over several lines in a command window. For the purpose of debugging and ease of use, it's more convenient to create such queries in a text file that you can then feed to Log Parser by passing the name of the text file as a parameter. Notice also that in the query that Listing 1 shows, I've taken the liberty of including a logs parameter that lets me specify the location of the input logs (i.e., C:temp). So, to simplify the process, I can save the complete query into a file called recipients.sql, then execute the query by running the following command:

logparser file:recipients.sql?logs= c:temp*.log -i:w3c 

Figure 2 shows the resulting output.

By default, Log Parser outputs results to the screen, pausing output after every 10 lines. You can pipe the output to a text file by including the following syntax at the end of the query command:

C:tempoutput.txt 

Note, however, that when you send the output for this query to a file, there's no point in specifying anything other than "TOP 10" in your query as Log Parser pauses after every 10 lines and the command will never finish.

Let's refine things a little more. Take another look at Figure 2. Notice the entry for [email protected]; this is the address of the local public folder replication agent. I might want to know how many incoming replication messages arrived on the server, but I'm more interested in the activity of real people, so I need to exclude public folder replication messages. To do so, I can tell Log Parser to exclude any message sent to an address that includes the string -IS@, as callout A in Listing 2 does.

Now that we know who our top recipients are and how many messages they each receive, what other information do we need? How about the total size of those messages? We can tweak our query to compute the size of the total bytes field that Exchange logs for each message. Reporting the size in megabytes is more useful than reporting it in bytes; we can use a combination of Log Parser's DIV and SUM commands, then divide the computed value by 1048756 to get the result in megabytes, as Listing 3 shows. Figure 3 shows the results.

Looking at the output, we can see that one recipient—[email protected]— is receiving a much smaller total message size than the others. This recipient is likely a special mailbox used to receive notifications from an administrative process. Exchange generates a separate 1028 event for each recipient of a message; an individual message might have 20 or more recipients on a server, all of which result in separate 1028 events in the log. The fact that an individual message can feature in multiple events isn't a problem when we analyze message recipients, but it can be when we want to analyze message senders. We can use Log Parser to tackle that issue as well.

Task 2: Who Sends the Most Mail?
We have a couple of problems to solve when we want to use messagetracking logs to determine which users with mailboxes on a server send the most messages. The first problem is which event ID to look for. Messages submitted by local users have an event ID of 1027, which looks promising, but Exchange hasn't yet fully resolved the Sender-Address field when that event is recorded, so the field contains the sender's X.500-like distinguished name (DN—e.g., EX:/ O=XYZ/OU=EMEA/CN=RECIPIENTS/ CN=AJR), instead of the display name. Event ID 1031 is a better option because the Sender-Address field in that event contains the sender's resolved SMTP name. However, if you select only 1031 events, you ignore any message a local user sends for delivery to a recipient on the same server. We're forced to use event ID 1027.

The next problem is that Exchange creates a 1027 event for each addressee on a message—including each recipient in a Distribution Group (DG). To establish who the top message senders are, we therefore need to look for unique message-tracking log entries such that each message is included only once before we start analyzing the data. There isn't an elegant way to do this kind of two-step process in Log Parser; the best advice is to use a temporary file. First, use a query such as the one that Listing 4 shows to reduce the data down to individual messages by using the unique message identifier field, and write the message ID and sender address to a temporary file. Second, use a query such as the one that Listing 5 shows to extract the count of unique message IDs. Note that this query trims the leading EX: from the DNs, making the resulting output (which Figure 4 shows) a little easier to read.

Unfortunately, Log Parser lacks the ability to join queries, which would let you compare the Exchange DNs with AD (using the Legacy-ExchangeDN field) and output something more user-friendly, such as the user display name or SMTP address. This functionality might appear in a future version of Log Parser, but for now, gaps such as this demonstrate the advantage of commercial reporting packages. (See the sidebar "Commercial Alternatives" for more information about third-party options.)

Task 3: Where's the Mail Coming From?
Sometimes you want to know where the messages that end up in your users' mailboxes originated. Listing 6 shows a Log Parser query that examines event ID 1019, which marks the arrival of a message onto a server. (A query to examine domain traffic for outgoing messages would simply look for event ID 1027 instead of event ID 1019.) Figure 5 shows the query's output.

Looking at the output, we can see that the vast bulk of messages come from servers in our Exchange organization (i.e., xyz.com), but there are also many messages from sybari.com. This doesn't necessarily indicate that users receive many messages from users in sybari.com. Examining the messagetracking logs themselves might reveal that the bulk of these messages are generated by Sybari Antigen when its scanner restarts after loading new virus-pattern files, at which time Antigen sends a message from me@sybari .com to [email protected] on the local server. To eliminate these messages, you can use the same technique we used to remove public folder replication messages from the count.

Free and Easy
Log Parser has a lot more functionality than I have room to explore in this article. For example, if Microsoft Excel is installed on the system from which you run Log Parser, you can output data in chart format or write data into a SQL Server database so that you can perform more sophisticated queries, combine message-tracking data into other reports, or analyze information from Microsoft IIS logs to find out more about Microsoft Outlook Web Access (OWA) or Microsoft SharePoint Portal Server activity. If you're interested in expanding your use of Log Parser, check out http://www.log parser.com or http://www.microsoft .com/technet/scriptcenter/tools/log parser/default.mspx. You can also find out more about the tool by reading "Log Parser," May 2004, InstantDoc ID 42174; "Troubleshooter: Troubleshooting Message-Tracking Logs," May 2002, InstantDoc ID 24449; or "Mining the Depths of Exchange Tracking Logs," July 2000, InstantDoc ID 8903.

As with any free utility, you have to do some work to make Log Parser reveal what you are interested in, but using the tool to generate basic reports is a relatively easy process. After you begin to discover the wealth of information that the messagetracking logs hold, you might decide to move on to a more robust commercial product—or you might let the inexpensive combination of Log Parser, SQL Server, and Excel chart your way to Exchange admin nirvana.