Log Parser Lizard

View and analyze your Log Files

 

This FREE Log Parser GUI for Microsoft Log Parser 2.2 is a versatile tool that provides quick access to log files, XML files, CSV files, as well as data sources on the Microsoft Windows operating system. Now, businesses running Log Parser can use this query software to find exactly what they need exactly when they need it.

 

By trying Log Parser Lizard for free, you’ll see first-hand how easy it is to find needed information in your log files, XML files and CSV files. You’ll no longer have to use an unintuitive interface to search, and your queries are stored for future use.

 

Read More...

 

 

 

Tutorial for Log Parser Lizard GUI

 

Here is a simple guide for using Log Parser Lizard GUI query software successfully to view and anlyze your IIS log files:

  1. Run Log Parser Lizard
  2. Create a new query by clicking on the “New query” button on the toolbar
  3. From the drop down list in the toolbar select your file format (for example 'W3C IIS file format')
  4. Check properties (the button next to the drop down list) – these are equivalent to MS LogParser command line switches.
  5. Enter a simple query (as you would in MS LogParser command line) in a query text box in the bottom of the window (ex. select top 1 * from <1>) to see if it works.
  6. Click Generate
  7. See results
  8. In the “Queries” dialog box you can organize your queries in separate groups (the groups are on the left of the main window in the so-called-outlook-bar)
  9. In the “Options” dialog box you can set options/macros that would be replaced in every query.
  10. In the Export dialog box, you can configure options for exporting data with queries.
  11. You can always save results in Microsoft Excel file for future analysis.
  12. From the Chart drop down menu, you can select your preferred chart type (column, pie, 2D, 3D) and/or save it to graphic file (jpg, gif)

Basics of writing a Logparser SQL Query

 

A basic SQL query must have, at a minimum two basic building blocks: the SELECT clause, and the FROM clause. For starters: start Log Parser Lizard, click on the “New Query” button on the toolbar, from a drop down list select “Windows Event Log” and in the Query text box in the bottom of the window write the following command:

 

SELECT * FROM System

 

The SELECT clause is used to specify which input record fields we want to appear in the output. The FROM clause is used to specify which specific data source we want the Input Format to process. Different Input Formats interpret the value of the FROM clause in different ways; for instance, the EVT Input Format requires the value of the FROM clause to be the name of a Windows Event Log, which in our example is the "System" Event Log.

 

The special "*" wildcard after a SELECT keyword means "all the fields" (like in standard SQL). Most of the times, an output of all of the fields of the log records might not be desired. You might only want to see only the fields that are of your interest. To accomplish this, instead of the "*" wildcard in the SELECT clause, you will have to write a comma-separated list of the names of the fields you wish to be displayed.

 

SELECT TimeGenerated, EventTypeName, SourceName FROM System.

 

The Log Parser SQL-Like language also supports a wide variety of functions, including arithmetical functions (e.g. ADD, SUB, MUL, DIV, MOD, QUANTIZE, etc.), string manipulation functions (e.g. SUBSTR, STRCAT, STRLEN, EXTRACT_TOKEN, etc.), and timestamp manipulation functions (e.g. TO_DATE, TO_TIME, TO_UTCTIME, etc.). Functions can also appear as arguments of other functions.

 

SELECT TO_DATE(TimeGenerated), TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ), SourceName FROM System

 

То change the name of a field-expression in the SELECT clause by using an alias you can use the AS keyword followed by the new name of the field.

 

SELECT TO_DATE(TimeGenerated) AS DateGenerated, TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ) AS TypeName, SourceName FROM System

 

When retrieving data from an Input Format, it is often needed to filter out unneeded records and only keep those that match specific criteria. To accomplish this task, you can use another basic building block of the Log Parser SQL language: the WHERE clause which is used to specify a Boolean expression that must be satisfied by an input record for that record to be listed in the output. Input records that do not satisfy the condition will be discarded. Conditions specified in the WHERE clause can be more complex, making use of comparison operators (such as ">", "<=", "<>", "LIKE", "BETWEEN", etc.) and boolean operators (such as "AND", "OR", "NOT"). The WHERE clause must immediately follow the FROM clause.

 

SELECT TimeGenerated, EventTypeName, SourceName FROM System WHERE ( SourceName = 'Service Control Manager' AND EventID >= 7024)

 

The ORDER BY clause can be used to specify that the output records should be sorted according to the values of selected fields. By default, output records are sorted according to ascending values. We can change the sort direction by appending the DESC (for descending) or ASC (for ascending) keywords to the ORDER BY clause.

 

SELECT SourceName, EventID, TimeGenerated FROM System ORDER BY TimeGenerated

 

Sometimes we might need to aggregate multiple input records together and perform some operation on groups of input records. To accomplish this, the Log Parser SQL like language has a set of aggregate functions (also referred to as "SQL functions") that can be used to perform basic calculations on multiple records. These functions include SUM, COUNT, MAX, MIN, and AVG. The GROUP BY clause is used to specify which fields we want the group subdivision to be based on. After the input records have been divided into these groups, all the aggregate functions in the SELECT clause will be calculated separately on each of these groups, and the query will return an output record for each group created.

 

SELECT EventTypeName, Count(*) FROM system GROUP BY EventTypeName

 

For filtering results from groups you can use the HAVING clause. The HAVING clause works just like the WHERE clause, with the only difference being that the HAVING clause is evaluated after groups have been created, which makes it possible for the HAVING clause to specify aggregate functions.

 

SELECT EventTypeName, Count(*) from system group by EventTypeName HAVING EventTypeName =’Error event'

 

The DISTINCT keyword is used to indicate that the output of a query should consist of unique records. Duplicate output records are discarded. It is also possible to use the DISTINCT keyword inside the COUNT aggregate function, in order to retrieve the total number of different values appearing in the data.

 

SELECT DISTINCT SourceName from System

 

SELECT COUNT( DISTINCT SourceName) from System

 

Use the TOP keyword in the SELECT clause to return only a few records at the top of the ordered output.

 

SELECT TOP 10 SourceName, Count(*) as Total FROM System GROUP BY SourceName ORDER BY Total DESC

 

These are simple queries, but they are good example that this log tool is more powerful for analyzing syslog events than any other event log viewer. For more samples, you can always look in examples provided with the program. They don’t all work out-of-a-box but can be very helpful.

 

There are many additional resources for learning about and using Log Parser on the Internet. Please check the following links.

  1. Logparser Download
  2. Logparser Forums
  3. Using Log Parser Lizard with SharePoint
  4. Examples (SQL) queries for IIS Analysis
  5. Under the hood of Logparser
  6. Microsoft Script Center page - The Microsoft logparser overview page
  7. Forensic IIS log exploration with LogParser
  8. Using the Logparser Utility to Analyze Exchange/IIS Logs
  9. LogParser 2.2 and ASP.NET
  10. Auditing the Event Logs
  11. Log Parser Plus
  12. Aggressive Virus Defense

 

Using Regular Expression and log4net input formats with Log Parser Lizard.

 

Both these formats are based on regular expressions to parse the log lines but the difference is that RegEx input format is parsing the log files line by line (if there is some line that doesn’t match the regular expression, it will be marked as error). Log4Net input format also uses regular expressions to parse the log file but the logged messages is spread in more than one line. Field “Full Message” is what goes after the first line that matches the regular expression and “Exception” field isn’t null only if the “Full Message” begins with “Exception:” word (this was made for my own needs but maybe someone else will find it useful). Regex and Log4Net input formats are not the ultimate solution for every text-log-file-format but they are flexible enough to meet most of your needs.

 

Here is a step by step guide for using RegExp and log4net input formats:

  1. In Log Parser Lizard install path create XML file which defines regular expression and its fields and their data types. Something like this:

    <?xml version="1.0" encoding="UTF-8"?>
    <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\src\LogParserCSWebServiceInputFormat\LogParserRegexInputFormat.xsd">
     <regex>^(?&lt;DateTime&gt;(?:\d{4})-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2},\d{3})\s+(?&lt;ThreadID&gt;\d*)\s*\[(?&lt;ProcessID&gt;\d+)\]\s+(?&lt;LogType&gt;\w+)\s+(?&lt;Loger&gt;\w+)\s+-\s+(?&lt;Message&gt;.*)$</regex>
     <fields>
                <field name="DateTime" type="Timestamp" format="yyyy-MM-dd HH:mm:ss,fff"/>
                <field name="ThreadID" type="Integer"/>
                <field name="ProcessID" type="Integer"/>
                <field name="LogType" type="String"/>
                <field name="Loger" type="String"/>
                <field name="Message" type="String"/>
     </fields>
    </config>


    For writing regular expression you can use Expresso, excellent and free tool for building regular expressions and for editing XML files you can use XML Notepad from Microsoft.
  2. Create a new query
  3. From a drop down list select “Regular expression input format” or “log4net input format”
  4. Click on properties button (next to the drop down list) and set “config file” property to configuration file name that you have created in step 1.
  5. Test created the query against your text file (ex. select * from c:\mylog.txt) and if you have some troubles try to fix the config file.

You can look at log4net examples provided in installation directory of LogParser Lizard. And remember, the difference between RegEx input format and log4net input format is that RegEx log files are read as one record per text line. In log4net log files, one record can be in more text lines (for instance when exception is logged).