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., joe@blackstatic.com) 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
Previous
Register
