The Windows event logs store a huge amount of data. Scrolling down lists of events for specific information can be burdensome, and most administrators probably review the logs only when something bad happens or when something is broken.
In this month's Toolbox, I'll show you a method for extracting interesting data from event logs—such as security events, application misconfigurations, or application usage—and parsing the data by using tools I've talked about in previous Toolbox articles. By combining LogParser and Sed into a single solution, we can extract this valuable information and tighten it into an extremely usable format.
POP3/IMAP Usage Audit
For the purpose of this article, I've chosen to audit POP3/IMAP usage in Microsoft Exchange Server. If you're trying to eliminate POP3/IMAP usage for security reasons, or if you want to lock down POP3/IMAP with Secure Password Authentication or Secure Sockets Layer (SSL), you'll find this article's script useful.
Before you can use LogParser to extract any event log data, however, you need to know what you're looking for. For example, IMAP/POP3 connections create an EventID 1010 in the Application event log, so this is the data that we'll configure LogParser to extract. Looking for EventID 1010 will tell us who is using either protocol, and then we can take subsequent action. Knowing who uses your applications is an important first step toward securing them.
Use LogParser to Extract Data
We'll use LogParser to export the target data from the Windows event log into a text file. (For information about this tool, check out "LogParser," InstantDoc ID 42174.) Using SQL syntax, we'll extract only the fields we're interested in from the POP3 and IMAP4 events, then use I/O streams to parse the data. See "Toolbox: Grep," InstantDoc ID 46869, for a description of how you can pipe (|) data from one program to another. Let's look at the full command, then break it down.
logparser -i evt "SELECT
TimeGenerated,SourceName,
EventID,Message FROM
Application WHERE EventID
=1010 AND TimeGenerated > SUB
(SYSTEM_TIMESTAMP(),
TIMESTAMP( '08', 'dd' ) )" -
q | grep 1010 | sed -r
"\{s/\Client //; s/\suc.\{0,\}
box // ; s/\. For.\{0,\}$//"\}|
cscript mail.js admin@mydomain
.com -s "Pop3/ IMAP Users" -
smtp smtp.mydomain.local -p
The LogParser command comes first. The -i evt switch specifies the input as the event log. This switch is optional, but it ensures that LogParser doesn't get confused. Next is a SQL statement that uses a SELECT statement to define the fields to return with the query. Running
logparser -h -i:evt
reminds us that the event log includes the fields EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, SourceName, Strings, ComputerName, SID, and Message. In our example, we're interested only in returning the TimeGenerated, SourceName, EventID, and Message fields, so we've tailored the SELECT statement to match.
Security events for your applications might fall in the Security, Application, or System event logs. Every application stores events differently. It's important to identify all possible sources of pertinent information. In our case, because IMAP/POP3 connections create events with ID 1010 in the Application event log, we've configured our Select statement's FROM and WHERE clauses to find these specific events.
Grep Lends Help
Before piping the LogParser command into other tools, you should first test it to ensure that it returns the data you seek by displaying the output to the console, as Figure 1 shows. You'll notice that a lot of data is returned, so you'll want to clean it up. In this example, the message body is long. Ideally, we'd like to extract just the username (e.g., [email protected]) and possibly the client address. The command-line tool Grep can help with this task.
Grep lets us simply remove the lines of data that don't contain the actual events. To do this, we pipe the LogParser output into the Grep command and look for Event ID 1010. The following command begins the data distillation and strips the headers and footers:
logparser -i evt "select TimeGenerated,SourceName,Event ID,Message FROM application where EventID=1010 OR EventID= 101 7" -q | grep 1010
Sed Cleans It Up
Figure 1 also shows a lot of redundant data. It would be nice to strip that out so that we get only unique data for the event, so let's use Sed to eliminate everything in the message except the client and the username. A quick inspection reveals that we can easily remove some strings (e.g., substitute them with a value of nothing) by having the tool identify matches, as follows:
- Remove the word "Client" and the space that follows it
- Remove the string of text beginning with "successfully" and ending with "mailbox," as well as the extra spaces before and after
- Remove the text that follows the mailbox name, beginning with the period and leading into "For more information ..."
When you're using Sed to find matches, it doesn't matter exactly what strings you look for. For example, you could look for "mailbox," or even "box." It's merely important to pick a string with enough characters so that Sed can match the string in exactly the right place and in no other.
Now, build these matches as regular expressions. The trickiest regular expression is the final one, in which we remove the tail of the message ID to get rid of the "For more information" text, as well as the punctuation around it. The regular expression
"s/\. For.\{0,\}$//"
matches the string beginning with ". For" and continuing to the end of the line (denoted by the regular expression character $) and substitutes the matching string with nothing, effectively eliminating the text. The period (.) is a wildcard, so don't forget to escape the character with a backslash (\). Also, because we're looking for a space, we need to enclose the entire command in quotes.
The other two regular expressions are easy. You could pipe the output from one Sed command into another, but it's more elegant to combine your substitutions using braces and separating with semicolons, as follows:
sed -r "\{s/\Client //;
s/\suc.\{0,\}box // ; s/\.
For.\{0,\}$//"\}
Running this through the entire command, we get much tighter output as Figure 2 shows.
Automate!
Now that we've got the output pared down to something useful, we can configure the command to run weekly and mail the results to an administrator. First, we instruct LogParser to obtain only the previous week's data. To do so, we modify the WHERE clause so that the SELECT statement looks for TimeGenerated values that are less than 1 week old, as follows:
WHERE TimeGenerated > SUB(SYSTEM_ TIMESTAMP(), TIMESTAMP( '08', 'dd' ) )
This function returns any events that are newer than (i.e., greater than) the present time minus (SUB) 8 days (dd). Now, go to Control Panel and create a new scheduled task by opening the Scheduled Tasks applet and choosing Add Scheduled Task. Save the entire command into a batch file and specify that it run once per week.
Finally, edit the batch file to email the results to an administrator. You can write your own email script, using Collaboration Data Objects (CDO) to generate an email message, or you can use a tool such as Blat, which uses a Win32 program to send an SMTP message for you. If you choose to script it yourself, include an option to accept data from a stream so that you can pipe the output to the script.
Now, Tweak It
I've shared one example of distilling a common event into usable data and exporting it to another system. Even if you don't use POP3/IMAP, you can easily tweak aspects of this example for any event that's important to your systems.
