The following information was submitted in the past by customers. I'm reposting it here as-is. It contains some general informaiton and some troubleshooting information.
Can't install Log Parser 2.1 on Windows 2000
Version: 2.1
The procedure for getting Log Parser 2.1 on to a Windows 2000 box is non-obvious. You can't just run the setup, because it will fail on any operating system other than Windows XP and Windows 2003.
Though the IIS 6.0 Resource Kit itself is limited to Windows 2003 and Windows XP, Log Parser 2.1 runs fine on Windows 2000. The trick is getting it there. You have two options to do this. The first is to install to a Windows 2003 or Windows XP box, and then copy the Program Files\IIS Resources\Log Parser folder to your Windows 2000 box. If that's not an option, you can still get the files by performing an administrative installation directly to your Windows 2000 computer. Download the iis60rkt.exe file to your computer and open a command prompt window to the directory where you saved the file. Then execute this command line:
iis60rkt.exe /V/a
The InstallShield Wizard will appear and ask you where to place the files for the administrative install. Specify a directory such as c:\Temp, and then you'll find the Log Parser 2.1 files in c:\Temp\program files\IIS Resources\Log Parser.
What is the current version of Log Parser?
The current version of Log Parser is Log Parser 2.2, which is available directly from the Microsoft Download site.
What operating systems will Log Parser run on?
Log Parser runs on Windows 2000, Windows XP, Windows Server 2003, or Windows Server 2008.
Date Arithmetic (2.1)
Starting in version 2.1, you can perform date arithmetic in the WHERE clause. Here's an example:
LogParser "SELECT EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName, EventCategory, SourceName, ComputerName, Message FROM System, Application, Security WHERE TO_DATE(TimeGenerated) = TO_DATE(SUB(SYSTEM_TIMESTAMP(), TO_TIMESTAMP('01-02', 'MM-dd')))" -o:DATAGRID
The origin date for timestamp values is 0000-01-01 00:00:00, so 0000-01-02 00:00:00 represents a span of exactly one day.
Write a query that returns records that are not already in the output file
Note: In version 2.2, you can use the new Checkpoint feature to solve this problem in many cases. See the help topic "Parsing Input Incrementally".
Versions: 2.0, 2.1
You can't directly write a query that returns only records that are not already in the output file; that would require joining data from two different sources (the input file and the output file). Log Parser is planned to support multiple inputs in the same query in version 3.0, which will ship at some unknown point in the future. However, you may be able to get the required results with one of two different hacks. First, if the output is a SQL table, you can set a unique index on the table and just let inserts that violate the uniqueness constraint fail. Second, you may be able to use a sub-select if you have a different file that already contains the records that were previously inserted:
SELECT * FROM NewLog.LOG
TO Dest
WHERE (datetime field) > (SELECT Max(datetime field) FROM OldLog.log)
Use Windows authentication with SQL Server ouput
To use Windows authentication when using SQL Server as the output target for Log Parser, just leave out the username and password parameters in the Log Parser command line. Log Parser will default to integrated authentication.
Source of Query and Template Samples
Worth reading is Mark Burnett's article for SecurityFocus, Forensic Log Parsing with Microsoft's Log Parser, showing how to use Log Parser to hunt through IIS logs looking for suspicious patterns.
Use an Access Database for Log Parser output
Here's a full example of how to use a Microsoft Access database as the target for Log Parser output:
-
Create your Access database.
-
Create the target table in your Access database. In Log Parser 2.0, the -createtable switch has a problem with Access databases (see the related article)You may need to play around some to get the data types right, but fortunately the error messages if you get it wrong are pretty clear. For this example, I created a table named Files with three fields: FName (Text, 255), Size (Number, Long Integer), and Attributes (Text, 255).
-
Create an ODBC DSN to point to the database. On Windows 2000, you do this from Programs, Administrative Tools, Data Sources (ODBC). I created a System DSN named LPtest that refers to my Access database. You'll find when you create the DSN that you get to select a driver (Microsoft Access, of course) and then browse to the database (the "Select" button on the setup dialog box will do this).
-
Now you can run a Log Parser query that uses the DSN to get to the database. For this example, I used:
logparser "SELECT Name AS FName, Size, Attributes
FROM c:\winnt\system32\*.* TO Files" -i:FS -o:SQL -dsn:LPtest
Escape commas in CSV output
When outputting query results to -o:CSV format, any commas that are embedded in the data are not escaped. Consequently, reading the results into Excel will be done improperly.
Workaround for 2.0:
You can use the REPLACE_CHR function, as in:
"SELECT ...., REPLACE_CHR(cs-uri-query, ',', '+'), ..."
which will automatically replace any occurrence of ',' with '+'.
If double quotes are preferred, you can use the STRCAT function:
"SELECT ...., STRCAT('"', STRCAT(cs-uri-query, '"')), ..."
New switch in 2.1:
In version 2.1, the CSV and W3C output formats have a new "encodeDelim" option (default=false), which will automatically encode ',' (for CSV) and space (for W3C) as '+'.
Include a column name that contains a space in the SELECT statement
Versions: 2.0, 2.1
Including column names containing spaces in your SQL statements requires special syntax. In Log Parser 2.0, you can use \u0020 to represent a space in Unicode:
logparser "SELECT Virus\u0020Type, Virus\u0020Name
FROM log.csv" -i:CSV -headerRow:ON
In Log Parser 2.1, you can surround field names with the SQL quoting square brackets instead:
logparser "SELECT [Virus Type], [Virus Name]
FROM log.csv" -i:CSV -headerRow:ON
Use event logs with spaces in their names
Some event logs (such as Directory Service, DNS Server, and File Replication Service) have spaces in their names. To get Log Parser to recognize these files, you need to do two things:
- Specify the -i EVT switch to tell LogParser specifically that this is an Event Log
- Replace spaces in the log name with the Unicode-escaped space \u0020
So, for example, this query will give you the EventIDs for ten FRS events:
logparser -i EVT "SELECT TOP 10 EventID
FROM File\u0020Replication\u0020Service"
Use remote event logs with spaces in their names
Version: 2.1
Fixed in: 2.2
Attempting to retrieve information from an event log such as File Replication Services on a remote server may fail. For instance, this command line:
LogParser -i:EVT -o:CSV
"SELECT * FROM \\myNetworkServer\File\u0020Replication\u0020Service
TO test.csv"
may return the following error:
ERROR: Error while looking for files: Error searching for files in folder \\myNetworkServer\File Replication Service: The network path was not found.
The problem isn't actually the spaces in the name. Log Parser checks the registry to verify event log names before issuing the query. There's a bug in all versions of Log Parser through 2.1 that causes it to incorrectly check the local registry rather than the remote registry, so if the event log you're trying to retrieve doesn't exist on the local computer, the query will fail. The workaround is to create a registry key for the desired event log in HKLM\System\CurrentControlSet\Services\EventLog. To make the example work, just add a File Replication Services subkey on your local machine.
There's an additional problem if your local computer has a different %systemroot% than the remote computer; in this case, it may not be able to retrieve message descriptions for remote event log entries. The only workaround for this is to copy the EventMessageFile registry keys, and the files they point to, from the remote computer to your own computer, placing the files in a known location.
Read Windows 2000 Event logs saved as CSV
You may encounter difficulties when trying to retrieve information from Windows 2000 event logs saved as CSV files. Here's an example to show how to pull out events by ID number from such files.
The first hurdle when using a CSV generated from an event log is that it doesn't include field names -- LogParser will simply call them Field1, Field2, and so on. I took a look at the standard format for an exported event log and it looks like this to me:
Field1 - Date
Field2 - Time
Field3 - Source
Field4 - Type
Field5 - Category
Field6 - Event
Field7 - User
Field8 - Computer
Field9 - Description
So, given a file named security.csv, you could extract all of the 560 Events with this line:
logparser "SELECT Field1 AS Date, Field2 AS Time, Field7 AS User, Field9 AS Description
FROM c:\Temp\Securiy.csv WHERE Field6=560" -i:csv -headerRow:Off
Cannot Find 'Fields' Directive when parsing IIS log file
Attempting to run a query against an IIS log file may return an unexpected error. For example, you attempt to run this query:
logparser -i:IISW3C -o:CSV "SELECT cs-uri-stem FROM iislog.log"
You receive this error:
Cannot Find 'Fields' directive
This indicates that the files you are trying to parse are in NCSA format, rather than native IIS format. Use the -i:NCSA switch with the query. Change the field names into those displayed by running
logparser -h -i:NCSA
Timestamp basics
Timestamp is a Log Parser data type that can contain a date, a time, or both. Timestamps have two basic functions. Firtst, they can hold an entire date and time. For example, returning the current system time as a timestamp:
SYSTEM_TIMESTAMP()
Note that SYSTEM_TIMESTAMP() returns UTC time.
You can create such a timestamp by combining two timestamps, one of which contains a date and one of which contains a time. For example, you can get the current system date as a timestamp with SYSTEM_DATE() and the current system time as a timestamp with SYSTEM_TIME(), so a longwinded replacement for SYSTEM_TIMESTAMP() is
TO_TIMESTAMP(SYSTEM_DATE(), SYSTEM_TIME())
Timestamps can also be used to hold a span of time. For this, you can use the second form of TO_TIMESTAMP(), which takes a string value indicating a date or time (or both), and a formatting string. You can also use the equivalent TIMESTAMP() data type, which is constructed the same way. For example, returning a 12-hour timestamp (holds twelve hours in the first day of the year zero):
TIMESTAMP('12:00:00', 'hh:mm:ss')
Returning a 24-hour timestamp:
TIMESTAMP('2', 'd')
(Why not TIMESTAMP('1', 'd')? Because the origin of the date values is day 1 of year zero. So you need to ask for the start of the second day to get 24 hours of time.)
Returning a 48-hour timestamp (2 days in year zero):
TIMESTAMP('3', 'd')
You can use timestamp math to move around timezones. For example, to move a time from GMT to GMT-6, you'd subtract a six-hour timestamp:
SUB( FieldWithGMTTimestamp, TIMESTAMP('06:00:00', 'hh:mm:ss'))
If you're trying to go from GMT to your computer's local time, there's a shortcut TO_LOCALTIME(). The reverse shortcut is TO_UTCTIME().
You can also use timestamp math in WHERE clauses to get recent events. For example, to get events in the last two hours, you'd look to see whether a particular time was more recent than a time created by subtracting a two-hour timestamp fro the current time:
[.....] WHERE FieldWithGMTTimestamp >= SUB(SYSTEM_TIMESTAMP(), TIMESTAMP('02:00:00', 'hh:mm:ss'))
To extract just the date from a timestamp into another timestamp, use TO_DATE(). To extract just the time from a timestamp into another timestamp, use TO_TIME().
Retrieve data from file with a space in the filename
Version: 2.1
If you have a filename such as "Copy of MyFile.csv", you may have trouble retrieving data from the file. A simple query such as "SELECT * FROM Copy of MyFile.csv" will fail with a syntax error.
The solution is to Unicode-encode the space characters: "SELECT * FROM Copy\u0020of\u0020MyFile.csv".
In general, you can use \u followed by hexadecimal numbering to represent any arbitrary characters in Log Parser queries.
Note: In Log Parser 2.2, you can also use single quotes to delimit the FROM clause. So beginning in 2.2 you can say "SELECT * FROM 'Copy of MyFile.csv'"
Failure to parse comma-separated FROM clause
Version: 2.2
Log Parser 2.2 may fail to correctly parse a comma-separated list of elements in the FROM clause. If a comma character in the FROM clause is followed by whitespace (space/tab/carriage-return/line-feed), parsing will fail.
Workaround: Remove all spaces after commas in the FROM clause.
Can't direct output to database other than SQL Server
You may encounter difficulties exporting to an ODBC data source that doesn't support the SQL datatypes that LogParser is using. One possible workaround (reported to be successful with Oracle) is to convert all columns to strings with the TO_STRING function, and to provide a table in your datasource whose columns are predefined as varchar2 columns.
Another (and usually superior, if you have direct administrative access to the database) is to create the target table yourself before you perform the export. That way you can use any data type that LogParser itself can handle in updating an existing table, without being limited to string fields.
Retrieve events in the last calendar month
There's no clean way to write SQL to retrieve only log entries for the last calendar month - that is, from today minus the number of day's in today's month, so if today is March 4 you want to see events from February 5 to March 4. Here's a hack suggested by Gabriele Giuseppini:
SELECT ....
USING TO_TIMESTAMP([your stuff]) AS Now,
TO_TIMESTAMP(
CASE TO_STRING(Now, 'M')
WHEN '1' THEN '02-01' // 31 days
WHEN '2' THEN '01-29' // Broken on leap years
WHEN '3' THEN '02-01' // 31 days
WHEN '4' THEN '01-31' // 30 days
......
END, 'MM-dd') AS DaysInMonth
... WHERE TimeGenerated >= SUB( Now, DaysInMonth)
What's new in Log Parser 2.2
Changes to Log Parser 2.2 include:
- New input formats (XML, TSV, ADS, COM, REG, NETMON, and ETW)
- New output formats (CHART, TSV, SYSLOG)
- GROUP BY WITH ROLLUP
- DISTINCT in aggregate functions
- PROPSUM and PROPCOUNT
- A whole bunch of new functions including MOD, ROUND, FLOOR, EXTRACT_FILENAME, HEX_TO_ASC, and more
- USING clause for temporary field expressions
- BETWEEN in WHERE or HAVING clauses
- SELECT CASE
- New date/time formats: milliseconds, nanoseconds, AM/PM
- Many new parameters for input and output formats
- Parameters in stored SQL files
- Permanent overrides to default global options
These are just the highlights. See the Log Parser 2.2 help file for full details.
Strange results when inserting data into MySQL database
When inserting data into a MySQL database, you may find strange or nonsensical data in the resulting table. It doesn't matter whether you let Log Parser create the table, or whether you create it yourself. This is due to a bug in the MyODBC driver that occurs in (at least) versions 3.51.04 through 3.51.11.
Currently there is no resolution or workaround.