The benefits of using a file for Log Parser SQL queries

  • September 26, 2010
  • James Skemp
One of the things I always forget about when I'm using Microsoft Log Parser is that you can define your SQL query in a file and run it, instead of putting it in the command line. For example, the following will run whatever SQL is defined in strivinglife.robots.sql. logparser -i:w3c -o:xml file:strivinglife.robots.sql Where this comes in handy is for something like the Log Parser query I defined today, that parses IIS logs for requests to the robots.

Read More

Log Parser Plus - version 0.3 released

  • November 7, 2009
  • James Skemp
Version 0.3.1 of Log Parser Plus now features a custom LocalTime variable for IISW3C queries, and outputs the final query (for copying). The scratch pad (the unlabeled text box) will no longer clear when a new query is performed. Currently under development, Log Parser Plus (not be confused with the Web site LogParserPlus.com) is a tool to (hopefully) help work with Microsoft Log Parser. Download Log Parser Plus 0.3 BETA.

Read More

Log Parser Plus - version 0.2.1 released

  • November 2, 2009
  • James Skemp
This is no longer the current version. View information about the current version of Log Parser Plus. Microsoft Log Parser is a command line-based utility useful to parse IIS logs and more. While a number of applications exist to help use Log Parser, as well as a number of sites containing Log Parser queries, I haven’t been very happy with them. In an attempt to make it even easier to work with Log Parser, I present version 0.

Read More

Microsoft Log Parser custom brush for Syntax Highlighter

  • October 18, 2009
  • James Skemp
It still needs a touch of work, but a basic custom brush for Syntax Highlighter is available for download. Download the Microsoft Log Parser custom brush for Syntax Highlighter 2.0.320. Example included below and at LogParserPlus.com: logparser -rtp:-1 "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 200sReport.txt FROM ex0902*.log WHERE (sc-status >= 200 AND sc-status < 300) ORDER BY sc-status, date, cs-uri-stem, cs-uri-query" Comments and suggestions are welcomed.

Read More

IIS Logs to SQLite - version 0.1 beta released

  • September 24, 2009
  • James Skemp
Unfortunately, Microsoft Log Parser is unable to convert logs into SQLite. To work around this limitation I've created IIS Logs to SQLite, which will parse IIS logs and import them into the SQLite database and table of your choice. The first beta version of this application is now available. Download IIS Logs to SQLite version 0.1. This application requires the .NET Framework version 3.5 (check your version of .NET Framework).

Read More

Microsoft Log Parser timestamp formats

  • February 6, 2009
  • James Skemp
The following article covers the timestamp format specifiers accepted by Microsoft Log Parser. Date Year y, yy, yyy, yyyy = last 1, 2, 3, or 4 digits, relative to 2000 Month M = no leading zero MM = leading zero MP = leading space MX = no leading zero, or with or without zero when parsing MMM = 3-character abbreviation MMMM = full name of month Day d = no leading zero dd = leading zero dp = leading space dx = no leading zero, or with or without zero when parsing ddd = 3-character abbreviation dddd = full name of day Time Hour h or H = no leading zero hh or HH = leading zero hp or HP = leading space hx or HX = leading zero, or with or without leading zero when parsing Minute m = no leading zero mm = leading zero mp = leading space mx = leading zero, or with or without leading zero when parsing Second s = no leading zero ss = leading zero sp = leading space sx = leading zero, or with or without leading zero when parsing Millisecond l = no leading zero ll = leading zero lp = leading space lx = leading zero, or with or without leading zero when parsing Nanosecond n = no leading zero nn = leading zero np = leading space nx = leading zero, or with or without leading zero when parsing Other tt = AM/PM ?

Read More

Parse FileZilla Server logs with Log Parser

  • November 23, 2008
  • James Skemp
While FileZilla Server is one of the best FTP servers available, it's logging leaves much to be desired. However, after a couple of hours, I've created a script for Log Parser that will generate a W3C log from FileZilla Server's logs. Save the following to a file, for example, FileZillaServer.sql: SELECT  SUBSTR(Text, 1, SUB(INDEX_OF(Text, ')'), 1)) AS RequestNumber  , TO_TIMESTAMP(   TRIM(    SUBSTR(     Text     , ADD(INDEX_OF(Text, ')'), 1)

Read More

How to get started with Microsoft Log Parser

  • November 7, 2008
  • James Skemp
I've written about Microsoft Log Parser before, and even started a site around it. But, I noticed the other day that I hadn't written on how to get started with this powerful tool. Getting Log Parser The current version of Log Parser is version 2.2, which you can download from Microsoft. Installing Log Parser While it says supported operating systems are "Windows 2000; Windows Server 2003; Windows XP Professional Edition," it appears to run fine on Windows XP Home Edition and Windows Vista (at least Ultimate has been personally confirmed).

Read More

LogParserPlus.com - Updates for 2008.11.04

  • November 5, 2008
  • James Skemp
Log Parser Plus was updated last night, during the election coverage, with the following changes: All functions now have descriptions (pulled almost completely from the included help file). Expressions have been added. Visit LogParserPlus.com for all of this information, and more, as it becomes available. (Next addition will be samples for the functions, and the start of a fleshed-out queries listing.

Read More

LogParserPlus.com launched

  • September 26, 2008
  • James Skemp
Since I now have actual content, there can be more visitors to Log Parser Plus, outside of the government. In additon to posting the articles here, I'll be posting a number of articles to Log Parser Plus in an attempt to make Microsoft's Log Parser utility that much easier. The initial focus will be on IIS-related queries, but who knows where it'll end up.

Read More

A brief overview of IIS 6.0 and the W3C extended log format

  • November 3, 2007
  • James Skemp
In Microsoft Internet Information Services (IIS) 6.0, you can choose to use a number of different formats for your access logs. Access logs contain information about what files are requested, whether the request was successful or not. Of all the available formats, the World Wide Web Consortium (W3C) extended log file format is by far the most flexible format available, as you can select the information that you would like to log.

Read More

Log Parser SQL query syntax (high-level)

  • October 25, 2007
  • James Skemp
The following is a high-level look at Log Parser's (version 2.2) SQL syntax. This does assume that you understand the specifics of each part, but for those that do not, this will be covered in a future tutorial. (This is more for my use.)  Bolded elements are required for any valid query, whereas the other elements are optional. SELECT USING INTO FROM WHERE GROUP BY HAVING ORDER BY Again, details about the specific parts, with real-world examples, will be covered in a future tutorial.

Read More

Log Parser script: Percent of status codes across all hits/requests

  • October 1, 2007
  • James Skemp
Note: This article was written using Log Parser 2.2. Therefore, while it may work for a different version, it may not. The following SQL can be used by Log Parser to generate a chart with the total requests (for a day, month, or year) and what percent each status code is of those requests. An example chart can be found at the end of this article. First, I assume that the below is put in the same directory as the logs you would like to parse.

Read More

How to ensure that performance counters continue after a restart

  • September 19, 2007
  • James Skemp
A few days ago my server was restarted in the early morning hours. I had been logging network and processor usage using Windows built-in performance monitoring, but when the server restarted, the logs did not. A bit of research later, it appears that there is a way to have Windows restart the logging after a system restart. To enable this, it seems you just need to have the log stop after a certain amount of time (for example, after x hours, or x days).

Read More

Using Log Parser to find users accounts used to log into an FTP site

  • September 13, 2007
  • James Skemp
The following Log Parser query can be used on FTP log files in order to determine what user names were used to login, or attempt to login, to an FTP site. logparser "select cs-uri-stem, count(cs-method) from ex*.log where cs-method like '%USER' group by cs-uri-stem order by count(cs-method),cs-uri-stem"  This assumes that you've added Log Parser to your path, and that you're running this from your log file directory.

Read More

Log Parser queries to find 400 and 500 http status codes

  • September 9, 2007
  • James Skemp
Note: This article was written using Log Parser 2.2. Therefore, while it may work for a different version, it may not. In a previous article, I discussed how to use Log Parser to find 404 errors in IIS log files. This time, I've made it a little broader, by giving some queries to find all 400 codes, and all 500 codes, through log files. There may be a better way to find these codes (instead of my IN statement), but a standard LIKE doesn't seem to work.

Read More

Find 404 errors using Log Parser

  • August 4, 2007
  • James Skemp
Note: This article was written using Log Parser 2.2. Therefore, while it may work for a different version, it may not. The following code will generate a listing of calls that generated a 404 error. logparser "SELECT cs-uri-stem, cs-uri-query, date, sc-status, cs(Referer) INTO 404report.txt FROM ex.log WHERE sc-status = 404 ORDER BY date, cs-uri-stem, cs-uri-query" This assumes that you've currently got a command line open in the folder containing your IIS logs, and that you are logging the above information, as well as that logparser is setup in your PATH line.

Read More

Microsoft's Log Parser 2.2

  • July 29, 2007
  • James Skemp
Year’s ago, Microsoft released Log Parser 2.2, which is a tool for analyzing IIS log files, event viewer exports, and etcetera. There’s a Professor Windows article from May 2005 which covers Log Parser, to some extent, and the download is available from Microsoft. Unfortunately, it’s all command line, but it seems interesting enough, nonetheless. I would have liked to have used AWStats (and probably still will), but I just didn’t want to bother with the config file today.

Read More