| |
Log Parser Lizard GUI
Short introduction to Log Parser Lizard
Log Parser is a very powerful, free and versatile tool that provides universal query access to text-based data, such as log files, XML files, and CSV files, as well as key data sources on the Microsoft Windows operating system, such as the event log, IIS log, the registry, the file system, and the Active Directory services.
Because the command-line interface is not very intuitive, I have created Log Parser Lizard, a FREE GUI tool for managing queries, exporting results to Excel, charts, etc… In addition I have added input filters for RegEx and log4net input log formats (with support for regular expressions) and SQL server T-SQL queries. There are some helpful examples included in installation package to help you start using Log Parser Lizard (and it’s SQL like syntax) for a web log analyzer and system log analyzer.
For unofficial Web page for Log Parser follow this link.
Pre requirements for installation are Microsoft Log Parser 2.2 (you can find it here) and Microsoft.Net 2.0. Download and install both of them before installing Log Parse Lizard.
Enjoy using Log Parser and Log Parser Lizard GUI.
The simplest guide for using this Log Parser GUI
- Run Log Parser Lizard
- Create a new query (click on “New query” button on the toolbar)
- From the drop down list in the toolbar select your file format (for instance 'W3C IIS file format')
- Check properties (the button next to the drop down list) – these are equivalent to MS LogParser command line switches.
- Enter simple query (as you would in MS LogParser command line) in a query text box in the bottom of the window (ex. select * from <1>) to see if it works.
- Click Generate
- See results :)
- 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)
- In the “Options” dialog box you can set options/macros that would be replaced in every query. For instance, I put here some date-time ranges (for monthly log reports and log statistics) and some paths, server names, so I can use the same queries on a different domains.
- In the Export dialog box, you can configure options for exporting data with queries.
- You can always save results in Excel file for future analysis.
- From the Chart drop down menu, you can select type of chart (column, pie, 2D, 3D…) and/or save it to graphic file (jpg, gif).
- That would be it. :)
About Microsoft LogParser
Microsoft has produced a “Log Parser” as a log parsing and log analyzing command line tool. It's current release is version 2.2. It is available as a free download from Microsoft at this address:
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en.
Microsoft doesn't officially support Log Parser but has placed this utility on some of the Internet Information Services (IIS) resource kits, and later made it available as a separate download, but didn’t promote its use very much. This is most unfortunate as this is a utility that really works great and between the members of the community known as "Swiss Army Knife" of the log parsing utilities for MS Windows. It is very fast and reliable. It is well known as one of the best log analysis software available on the market.
With LogParser you use Queries written in a dialect of the SQL language to specify the operations that transform input records generated by an Input Format into output records that are delivered to an Output Format. While many GUI tools are out there that provide filters, even those that allow the user to build custom filters can't compare with the power of writing a custom SQL query in Log Parser. Log parser can accept most common log formats and output it into one of many formats of your choosing for analysis of relevant data.
Log Parser is made up of three components
- Input Formats are generic record provider (records are equivalent to rows in a SQL table. Log Parser's built-in Input Formats can retrieve data from the following sources:
- IIS log files (W3C, IIS, NCSA, Centralized Binary Logs, HTTP Error logs, URLScan logs, ODBC logs)
- Windows Event Log
- Generic XML, CSV, TSV and W3C - formatted text files (e.g. Exchange Tracking log files, Personal Firewall logfiles, Windows Media Services logfiles, FTP log files, SMTP log files, apache log files etc.)
- Windows Registry
- Active Directory Objects
- File and Directory information
- NetMon .cap capture files
- Extended/Combined NCSA log files
- ETW traces
- Custom plugins (through a public COM interface)
- In addition to these input formats Log Parser Lizard GUI have added input filters for:
- Parsing text based log files line-by-line with Regular Expressions (RegEx Input Format)
- Log4net and log4j file format (also with support for regular expressions). This input format is also used for parsing multiline text log files (one record is spread through one or more text lines) from various sources
- SQL server T-SQL queries, retrieves data from SQL server tables and views. For a quick way to export some data from SQL Server to Excel file or to a chart image.
- A SQL-Like Engine Core processes the records generated by an Input Format, using a dialect of the SQL language that includes common SQL clauses (SELECT, WHERE, GROUP BY, HAVING, ORDER BY), aggregate functions (SUM, COUNT, AVG, MAX, MIN), and a rich set of functions (e.g. SUBSTR, CASE, COALESCE, REVERSEDNS, etc.); the resulting records are then sent to an Output Format.
- Output Formats are generic consumers of records; they can be thought of as SQL tables that receive the results of the data processing.
Log Parser's built-in Output Formats can:
- Write data to text files in different formats (CSV, TSV, XML, W3C, user-defined, etc.)
- Send data to a SQL database
- Send data to a SYSLOG server
- Create charts and save them in either GIF or JPG image files
- Display data to the console or to the screen
Using Log Parser
Let see an example of using Log Parser from a command line. Run windows command prompt, set current directory path to the directory wherein lies the executable "logparser.exe" (default "C:\Program Files\Log Parser 2.2") and enter the following command line:
LogParser -i:EVT -fullText:OFF -o:CSV -tabs:OFF "SELECT * INTO output.csv FROM SYSTEM"
This will save all records from System Event Log on the local system in a comma separated text file “output.csv”. This example show the Log Parser command is made up of the SQL query and the Input and Output formats options. These kinds of commands are very powerful in scripts for automatic execution and monitoring of the servers. For more information about using Log Parser from a command line, please refer to the help file or look at resources paragraph on this page.
If you are comfortable and familiar with SQL queries and command prompt commands and switches, there would be no problem using the Log Parser, but if you aren't you will have to learn basics of SQL to effectively work with this tool.
Although LogParser is awesome, there are a few things that some users didn't like about it:
- Command line interface can be difficult to learn and adopt for new users.
- The graphing output and charts are good, but it's also a MS Office dependency. You can’t graph something on a machine without Office installed (like most of production servers)
- No support for a custom text file formats and multiline text log files
This is why I have decided to develop a new Log Parser GUI and share it with you and hoping to become your log reader of choice.
In short, if you keep and analyze any type of log this will make your life easier and can save you major head hakes. By getting to know these tools and its capabilities you'll have better management of your systems, improved development process and have a forensic tool that you'll find new uses for with every time you use it. This is a must have for any systems engineer who needs to take a proactive approach in system monitoring.
Basics of writing a 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.
More resources for learning to use MS LogParser Toolkit
The developer of this tool, Gabriele Giuseppini, has writen a book entitled "Microsoft Log Parser Toolkit". This is a complete reference for utilizing the Microsoft Log Parser in real world scenarios. The authors did an outstanding job of bringing you from the basics of Log Parser through advanced techniques and tricks. Regardless of whether you are new to Log Parser, or if you are an experienced user, this book will give you new ideas and discover a few new tricks that you never thought of before. I would definitely recommend this book to any IT professional.
Also you can find a lot of additional resources on the internet. Please check the following links.
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:
- 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>^(?<DateTime>(?:\d{4})-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2},\d{3})\s+(?<ThreadID>\d*)\s*\[(?<ProcessID>\d+)\]\s+(?<LogType>\w+)\s+(?<Loger>\w+)\s+-\s+(?<Message>.*)$</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.
- Create a new query
- From a drop down list select “Regular expression input format” or “log4net input format”
- 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.
- 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 (ex. when exception is logged).
A word or two from the author
First of all, thank you very much for all your kind words that some of you have sent. This is very flattering, worms my hart and itt really means a lot to me. I’m always glad to hear feedbacks from users.
So, where was I? Ooh-yeah... I’ve made LPL for my personal use in summer 2006 when I was working as a system administrator in one of the largest banks in Republic of Macedonia, to monitor a lot of servers and produce all kind of reports for system and security audits (today most of the time I’m working as a solution developer and freelance problem solver :). The goal was to help me write and organize my MS Log Parser (according to me, the best free or commercial web log analyzer tool for Windows on the market. In that time I’ve tried a lot of others too.) queries and to help me generate some reports for the managers and business users. In that time I planned to publish it on Internet but I did not have much time to work on it till recently. So this version is as-it-is with a little modification from the original one and no warranty.
If there is interest in using a Log Parser Lizard, I’m planning to release next version and to add some new features like better GUI, query parameters, query builder, maybe a web interface, log index and search (Google-like text search), etc... It will probably still be free because I’m trying to support my products trough Google Adds (for hosting expenses and some bear for the effort) and over a donations.
For now I’m just thinking for adding some new features and upgrades for all of my products, and all of your suggestions for LPL are very useful.
A-word-or-two about documentation, ‘couse there was some of you demanding one, but... I really, really, hate writing docs, I’m planning to add more help tips built in intuitive user interface (for instance more yellow balloon tips). Writing docs in English really bothers me ‘cause I’m not a native English speaker (I work and leave in Macedonia and my mother language is, you guess, Macedonian). Reading these words you’l probably find a lot of broken phrases and I believe that these kinds of docs will have a bad impact on the overall product value. I hope that for the next releases, Log Parser Lizard will provide me with enough revenue over an ads and donations so I can pay for a professional help writer. Till that time, if you are new user please read the simplest guide section for new LPL user (this is like a hello-world program for a novice programmer :) I will provide soma FAQ on this site too.
If you have more suggestions (or special needs), feel free to write me and I will see what I can do about it.
Last-last words: please donate.
Although similar free and commercial products are available on the market, Log Parser Lizard is published as donationware. Donationware is a software that can be freely used but for which monetary donations are requested. Or put differently, a Donationware author is a freeware author who needs a little help paying the bills. If Log Parser Lizard has benefited you in some way (especially If you use it in corporate environment as I do), please consider a monetary donation. For more information on how to support this product and site, please visit a How-to-help page on this site.
Best regards, Dimce Kuzmanov, the author.
|