Tip

Log Parser examples: Using the free log analysis tool

Sooner or later -- when applications crash, servers fail or suspected hacking activity appears -- the IT department will need to sort through logs to find critical information; this includes application logs, operating system logs, security logs and many others.

There are dozens of commercial products -- from companies such as Splunk Inc., LogLogic Inc., Q1 Labs, ArcSight (now part of Hewlett-Packard Co.), RSA, the security division of EMC, and so forth -- intended to make this task easier, but most are expensive and require substantial effort to implement correctly. These products are most useful when log analysis is a sustained, ongoing and funded task.

So, what options exist for companies where log analysis is more tactical in nature, not a sustained IT project, and has zero budget? Previously, I've described how Microsoft

    Requires Free Membership to View

Excel is frequently overlooked when it comes to analyzing logs. Today, I will examine Log Parser, a free Microsoft log analysis tool, with some in-depth Log Parser examples.

Microsoft describes Log Parser as providing "universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system and Active Directory." So, what does this really mean? With Log Parser, it's possible to take most log files and perform SQL searches against them. If you are familiar with SQL, you know how valuable this capability is. If you aren't familiar, then this tip will show you why it's worth it to gain fundamental SQL query skills.

To illustrate one use of this powerful free log analysis tool, let's pretend we want to know if the Time service (W32Time) on our Windows computer is failing to synchronize time. We could open the Event Viewer, view the System Log and look for those messages, or we could use Log Parser.

 

C:\Program Files\Log Parser 2.2>LogParser "SELECT TOP 3 TimeGenerated, SourceName, Message FROM System WHERE SourceName='W32Time' AND Message NOT LIKE '%is now synchronizing%' ORDER BY TimeGenerated DESC" -q:ON
2010-12-22 09:58:31 W32Time The time provider NtpClient is configured to acquire time from one or more time sources, however none of the sources are currently accessible. No attempt to contact a source will be made for 14 minutes. NtpClient has no source of accurate time.

2010-12-22 09:58:31 W32Time Time Provider NtpClient: An error occurred during DNS lookup of the manually configured peer 'pool.ntp.org'. NtpClient will try the DNS lookup again in 15 minutes. The error was: A socket operation was attempted to an unreachable host. (0x80072751)

2010-12-22 09:58:29 W32Time The time provider NtpClient is configured to acquire time from one or more time sources, however none of the sources are currently accessible. No attempt to contact a source will be made for 14 minutes. NtpClient has no source of accurate time.

Figure 1 - Query for Time Synchronization Errors

The command in Figure 1 instructed Log Parser to examine the System event log, show events from the source W32Time, and only show events with a message that did not contain the text "is now synchronizing." I further limited this to the three most recent events with the query parameters "TOP 3" and "ORDER BY TimeGenerated DESC." The optional "–q:ON" enables quiet mode. If we wanted all of the failed messages and the output to go directly to a CSV file, we could modify it as shown in Figure 2.

 

C:\Program Files\Log Parser 2.2>LogParser "SELECT TimeGenerated, SourceName, Message INTO time_errors.csv FROM System WHERE SourceName='W32Time' AND Message NOT LIKE '%is now synchronizing%' ORDER BY TimeGenerated DESC" -q:ON -o CSV.

Figure 2 - Generating CSV output

To make this even more useful, let's additionally look at the event log on another computer ("myserver") by adding "\\myserver\" before the log source ("FROM system") as shown in Figure 3. This use case of Log Parser requires that whoever is executing the command has privileges to view the System event log on the destination computer. You can even specify multiple computers with the syntax "\\computer1\system, \\computer2\system."

 

C:\Program Files\Log Parser 2.2>LogParser "SELECT TOP 3 TimeGenerated, SourceName, Message FROM \\myserver\System WHERE SourceName='W32Time' AND Message NOT LIKE '%is now synchronizing%' ORDER BY TimeGenerated DESC" -q:ON

Figure 3 - Searching a remote computer

Now that we've been introduced to Log Parser, let's use it to examine a log file. The following is a log file from a Web proxy which shows websites accessed by a specific user, along with the category of the website (shopping, gambling, news, miscellaneous, etc). The format of the log, as opened in Excel, is shown in Figure 4.


Click to enlarge
Figure 4 – Web sense Log Format

Since this file is a CSV file with a header row defining names for each column, Log Parser will automatically detect the column names and allow you to query those names. In order to see which users (field "User") showed up in the logs the most, use the command shown in Figure 5.

 

C:\Program Files\Log Parser 2.2>LogParser "SELECT User, Count(*) as user_count FROM websense.csv GROUP BY User ORDER BY user_count DESC"
User user_count
------------------ ----------
Doe, Jon 1099
Adams, John 989
Washington, George 871

Figure 5 - Top Users from Websense log

Log Parser can output results in several possible ways: straight to screen (stdout), to a text file of various formats, to a GUI "datagrid," to a syslog server and to a chart.

The possibilities for this tool are limited only by your imagination, data and time. SQL queries allow you to sort, filter and aggregate your data in many different ways. The bundled help file (logparser.chm) provides many examples, a SQL reference and documentation on how to use the various log sources. You can create batch files to process large numbers of files and then process the output of those original sources. You can also output the results into a database rather than a file of some sort.

In summary: If your job ever requires the analysis of logs, this often overlooked (and free) tool is well worth your time.

About the author:
Tom Chmielarski is a former senior consultant at GlassHouse Technologies.

This was first published in March 2011

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.