s How to Use Log Parser to Query Event Log Data

Hi Everyone! While I primarily enjoy deep-diving into SQL Server techniques and troubleshooting, I also troubleshoot issues on Windows servers in general. One place I always look as a starting point for troubleshooting is the Event Log. It contains a wealth of information, however, it isn’t always easy to find. One tool that can be used to narrow the search is Log Parser.

What Is Log Parser?

Log Parser is a useful tool for querying large amounts of data for specific information. I won’t go into detail about the syntax, but here are some links to get started:

In a nutshell, Log Parser provides the ability to extract a subset of data from text-based files such as log, XML and CSV files in an organized and readable manner. It will also extract information from important data sources on the Windows operating system such as the Event Log, the Registry, and the file system. While the Event Log has a wealth of information, it isn’t always easy to read and it can be cumbersome to find specific information.

Below are four examples to extract Event Log data:
 

Example #1:

This example queries the event logs over a specific time and then exports the data to a .csv file. It uses a SQL SELECT statement to gather specific fields from the event logs. It filters them by EventType (EventTypes 1 and 2 are Error events and Warning events; EventType 4 are Informational events). It then puts the extracted data into a CSV file (c:\temp\Events.csv). This will allow us to view the most recent Warning and Error messages to resolve any issues. This example is great to use if a web application began returning error messages and we want to gather the errors from the Event Logs. 

logparser.exe -i:EVT “SELECT TimeGenerated,EventID,EventType,EventTypeName,EventCategory,EventCategoryName,SourceName,
Strings,ComputerName,SID,Message FROM \\servername\Application WHERE TimeGenerated > ‘2012-07-12 00:00:00’ AND EventType IN (1;2) ORDER BY TimeGenerated DESC” -o:CSV -q:ON -stats:OFF >> c:\temp\Events.csv

*Be sure to replace the server name (\\servername\Application) with the correct server name and the event log you want to search (Application, Security, or System).

If we have an application name, we could then narrow our search by including it.

logparser.exe -i:EVT “SELECT TimeGenerated,EventID,EventType,EventTypeName,EventCategory,EventCategoryName,SourceName,
Strings,ComputerName,SID,Message FROM \\servername\Application WHERE TimeGenerated > ‘2012-07-12 00:00:00’ AND EventType IN (1;2) AND Message like ‘%ApplicationName%‘ ORDER BY TimeGenerated DESC” -o:CSV -q:ON -stats:OFF >> c:\temp\Events.csv

*Be sure to replace the application name (ApplicationName) with the correct name.
 

Example #2:

This example searches the event log for WAS log entries and then writes the output to an HTML file that is generated using a template file. It also uses the Message field to narrow the search to a specific web application pool. This is a great way to find out the details of an application pool and how often it may be recycling and/or failing.

logparser.exe “SELECT TimeGenerated,EventTypeName,Strings,Message INTO c:\temp\logparse_file.html FROM System WHERE SourceName = ‘WAS’ and Message like ‘%ApplicationName%’” -tpl:c:\temp\iis_event_log_entries.tpl

*Be sure to replace the application name (ApplicationName) with the correct name.

The template file contains the following code:

<LPHEADER>
<HTML>
<HEAD>
  <STYLE>
TD { font-family: Arial };
TH { font-family: Arial };
  </STYLE>
</HEAD>
<BODY>
<TABLE BORDERCOLOR=”BLACK” BORDER=”1″ CELLPADDING=”2″ CELLSPACING=”2″>
<TR>
  <TH COLSPAN=4 BGCOLOR=”BLACK”><FONT COLOR=WHITE>New W3SVC Messages in System Event Log</FONT></TH>
</TR>
<TR>
  <TH ALIGN=LEFT BGCOLOR=”#C0C0C0″>Time Generated</TH>
  <TH ALIGN=LEFT BGCOLOR=”#C0C0C0″>Event Type</TH>
  <TH ALIGN=LEFT BGCOLOR=”#C0C0C0″>Strings</TH>
  <TH ALIGN=LEFT BGCOLOR=”#C0C0C0″>Message</TH>
</TR>
</LPHEADER>
<LPBODY>
<TR>
  <TD>%TimeGenerated%</TD>
  <TD>%EventTypeName%</TD>
  <TD>%Strings%</TD>
  <TD>%Message%</TD>
</TR>
</LPBODY>
</TABLE>
</BODY>
</HTML>

 

Example #3:

The Log Parser queries below are designed for Windows Server 2008. These examples are great for auditing purposes. They will allow visibility into who is accessing the server and who is attempting to access the server. The most valuable fields are the USER and Source-IP. By viewing the USER field, we can decide if it is necessary to block the user’s IP.

The following example will provide a list of successful logon attempts:

logparser.exe “SELECT timegenerated, EXTRACT_TOKEN(Strings,3,’|’) AS LogonID, EXTRACT_TOKEN(Strings,5,’|’) AS USER, EXTRACT_TOKEN(Strings,8,’|’) AS LogonTYPE, EXTRACT_TOKEN(Strings,11,’|’) AS WorkstationName, EXTRACT_TOKEN(Strings,18,’|’) AS Source-IP, EventID FROM Security WHERE EventID=4624 ORDER BY timegenerated DESC” -o:DATAGRID

 

Example #4:

This example will provide a list of failed logon attempts:

logparser.exe “SELECT timegenerated, EXTRACT_TOKEN(Strings,5,’|’) AS USER, EXTRACT_TOKEN(Strings,10,’|’) AS LogonTYPE, EXTRACT_TOKEN(Strings,13,’|’) AS WorkstationName, EXTRACT_TOKEN(Strings,18,’|’) AS ProcessName, EXTRACT_TOKEN(Strings,19,’|’) AS Source-IP FROM Security WHERE EventID=4625 ORDER BY timegenerated DESC” -o:DATAGRID

A list of Logon Types and its corresponding description can be found here, http://technet.microsoft.com/en-us/library/cc787567%28v=ws.10%29.aspx.

 
So there you have it, these 4 examples should give you some flexibility depending on the type of data you are looking to gather.

Written by Desiree Harris Employee @ SherWeb

Loading