Larkware
We get up early so that you don't have to.

Log Parser 2.1 FAQ

By Mike Gunderloy
Saturday, March 08, 2003

For updated information and support, please visit the Unofficial Log Parser Support Site.

Because I've written about Log Parser, I get a bunch of e-mail on the subject. Since Google loves my site for this, I'm going to maintain this page with information for other would-be Log Parser users.

UnansweredQuestions

First, though, here's your opportunity to help your peers. From time to time, I get e-mail from people who have Log Parser issues that I can't solve. I'll post those questions here. If you have an answer or a suggestion, please add it to the comments page, or just e-mail me directly. Thanks!

Q. One of the things I'd like to do with logparser is to calculate the number of visits to a site. One convention I use is: if requests come from the same ip, but they are more than 15 minutes apart, they constitute 2 different visits. Do you know how to differentiate between visitors using the sql language of logparser? What would be nice is to create a new field that is something like 'visit_nbr' that assigns a unique and incremented number to the requests from each distinct visit...

Answered Questions

And now on to some more currently-helpful information...

Q, What is Log Parser?
A. It's a command-line tool from Microsoft that lets you run SQL queries against almost any sort of Windows log file, and get the results out to an array of destinations, from SQL tables to CSV files.

Q. Where can I get Log Parser?
A. To get the current version, download and install the IIS 6.0 Resource Kit Tools . This will give you Log Parser 2.1. If you want the older version 2.0 for some reason, you can download it from the Windows 2000 Tools site.

Q. What is the current version of Log Parser?
A. Log Parser 2.1 is currently available as part of the IIS 6.0 Resource Kit.

Q. What's new in Log Parser 2.1?
A. Besides bug fixes, look for these new features:

  • The DATAGRID output type (which displays results in a Windows dialog box as shown below)
  • Support for comparison of two expressions (rather than just an expression and a value) in the SQL grammar
  • The EXTRACT_TOKEN and RESOLVE_SID functions
  • The resolveSIDs switch for the EVT input format
  • The fixedFields, nFields, and comment switches for the CSV input format
  • The encodeDelim switch for the W3C output format
  • The transactionrowcount switch for the SQL output format
  • The encodeDelim switch for the CSV output format
  • The noemptyfile switch for the XML output format
  • The MSUtil.LogQuery.DataGridOutputFormat COM object

[Log Parser Datagrid screenshot]

Q. What files can be used as input to Log Parser queries?
A. Any type of IIS log file, URLScan log files, HTTP error logs, Windows NT event logs and EVT backup log files, generic CSV or text files, generic W3C files (such as personal firewall log files, Windows Media Services log files, and Exchange Tracking log files), or file and directory structure information.

Q. What output formats are supported by Log Parser?
A. W3C-formatted text files, IIS log format text files, direct output to SQL tables, XML files, CSV files, text files with user-defined templates, on-screen datagrid, and native Log Parser format.

Q. What operating systems will Log Parser run on?
A. Windows 2000, Windows XP, or Windows Server 2003.

Q. How do I write a query that only returns records in a file during a certain period of time?
A. 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.

In Log Parser 2.0, date arithmetic was not supported in the WHERE clause. In some circumstances you can work around this. Here's a 2.0 query that returns event log records in the last 24 hours:

SELECT EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName, 

EventCategory, SourceName, ComputerName, Message, TO_STRING(SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), 

TimeGenerated), 'MM-dd-yyyy') AS Delta FROM System, Application, Security TO 

tblEvents WHERE (Delta ='12-01-')" -o:SQL -server:BEARSPAW -driver:"SQL Server" 

-database:Monitor -username:sa -password:xxxxx -createtable:ON

Phil Baldyga found a way to extend this 2.0syntax to do ranges. Here's a sample query he supplied for a 4-day range: 

SELECT TimeGenerated, Message, TO_STRING(SUB(TO_LOCALTIME(SYSTEM_DATE()), 

TimeGenerated), 'MM-dd-yyyy') AS deltastart, TO_STRING(SUB(TO_LOCALTIME(SYSTEM_DATE()), TimeGenerated), 

'MM-dd-yyyy') as deltastop FROM C:\eventlogname.evt 

WHERE ((deltastop <='12-05-') AND(deltastart >='12-01-')) ORDER BY TimeGenerated DESC

Phil was also kind enough to send along a sample ASP page to analyze event logs with LogParser.

You should only use this delta technique if you're stuck with version 2.0. If possible, upgrade to 2.1 and use date arithmetic.

Mike Dixson sent along another script to handle date ranges. He writes: "I was hit with the problem of how to view only X amount of days logs still as I do not run IIS6. I managed to find a work around for this in the shape of some date trickery and a rather more complex script. However after much toiling it is finished and so in order to save others having to work this out for themselves I'm more than happy for you to post this script on your site.

"It is best run from a DC logged in with a Domain Admin account. The only real part of the script that will need changing it the end section where the machine name is set. It's presently set to give approximately 7 days login successes and failures. I say approximately because it will be plus a day or two or three when months are shorter as it doesn't have any proper date calculation just scripting to fake date calculation. This could be altered to allow for more or less days results.

"I hope this is of help to all those who are stuck with Log Parser 2.0"

Q. How do I write a query that only returns records that aren't already in the output file?
A. You can't do this directly; 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)

Q. I'm trying to use Log Parser with a template, but the process fails when the template exceeds 2047 bytes. How can I work around this limitation?
A. In Log Parser 2.0, save the template as Unicode text. Or, upgrade to Log Parser 2.1, where this bug is fixed.

Q. Can I use Windows authentication when using SQL Server as the output target for Log Parser?
A. Yes. Just leave out the username and password parameters in your Log Parser command line, and it will default to integrated authentication.

Q. Where can I find more Log Parser query and template samples?
A. Visit the Log Parser section of the TechNet Script Center. If you have any cool query or template techniques to share, you can send them to me and I'll see that they get forwarded to the right person for inclusion. Also 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.

Q. I am struggling to work out how to use the logparser to output to an Access database on the workstation on which I am running the analysis. Is that possible? Can you (anyone?) provide an exampleof the command line I should use, please?
A. Here's a worked example:

  1. Create your Access database.
  2. Create the target table in your Access database. I've found that the -createtable option doesn't work well with Access (but see the next question for a workaround). 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).
  3. 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).
  4. 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

Q. When I attempt to use the -createtable switch with an Access database, Log Parser fails with an error such as this:

SQL State: 42000
Native Error: -1506
Error Message: [Microsoft][ODBC Microsoft Access Driver]
Size of field
'FName' is too long.
Most likely you're attempting to create a table with 
invalid column names,
such as names containing the '-' character. Rename the 
columns in the
SELECT clause using the AS statement.

Renaming the column doesn't help. What can I do to fix this problem?
A. The problem is that Log Parser 2.0 attempts to create 256-character wide text fields in Access, but Access has a maximum column width of 255 characters. There are three ways to resolve this. The first is to upgrade to Log Parser 2.1, where the default is 255 columns. The second is to create the table in advance, using Access, rather than using the -createtable switch. The third is to create the Registry key HKCU\Software\Microsoft\Log Parser\SQLOutputStringWidth as a DWORD key, and to set its value to 255.

Note that using the Registry workaround is undocumented and not guaranteed to work in future versions. I recommend that you upgrade to Log Parser 2.1

Q. I am trying to figure out how to use your Log Parser 2.0 to read Windows 2000 event logs. All the examples that I find are quite advanced. I need a simple example that shows the whole command, not just the query part of it. I have saved the security events from the event log on my server as a .csv file and now I'd like to pull out events by event ID numbers such as 560.

A. 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

Q. 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. How can I work around this?

A. 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, '"')), ..."

Better yet, upgrade to version 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 '+'.

Q. How do I include a column name that contains a space in a SELECT statement?

A. Use single quotes around field names that contain spaces. For example:

logparser "SELECT 'Virus Type', 'Virus Name' FROM log.csv" -i:CSV -headerRow:ON

Q. I want to use Log Parser 2.1 on a Windows 2000 server, but the setup file tells me that it's only supported on Windows 2003 and Windows XP. What can I do?

A. 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.

Q. I'm trying to retrieve all the data from Event Viewer to A SQL table. I can pull in the records for System, Application, and Security Events. But on the DC, there are events like Directory Service, DNS Server or File Replication Service. I'm having trouble getting this data in.

A. You need to do two things to make this work:

(1) Specify the -i EVT switch to tell LogParser specifically that this is an Event Log
(2) 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"

Q. I use this command line to extract events to a templated output file:

logparser "Select top 3 * From Application to c:\Temp\a.txt" -o:TPL -tpl:EventLogTemplate.txt

The template looks like this:

time=%TimeGenerated%, %EventID%, %EventCategory%, %SourceName%, %ComputerName%, %Message%

But the output has strange characters in the time field. How do I fix this?

A. There's a bug in LogParser 2.1 that causes TIMESTAMP fields to output garbage for the seconds when formated with a template. The workaround is to use the TO_STRING() function to impose your own formatting on the field. Modify the template to assign your own name for the TIMESTAMP field:

time=%TimeGeneratedFixed%, %EventID%, %EventCategory%, %SourceName%, %ComputerName%, %Message%

and then use this query instead:

logparser "SELECT TOP 3 EventID, EventCategory, SourceName, ComputerName, Message,

TO_STRING(TimeGenerated, 'yyyy-MM-dd hh:mm:ss') AS TimeGeneratedFixed

FROM Application TO c:\Temp\a.txt" -o:TPL -tpl:EventLogTemplate.txt

Q. I'm having trouble using TO_TIMESTAMP to combine a date and a time into a value. When I use TO_TIMESTAMP('12/01/2002', '00:00:00') AS TS it only returns a NULL.

A. TO_TIMESTAMP does two things. First, it combines two of LogParser's internal Timestamp values to a combined timestamp - this is useful when a data source gives back a date and time separately. Second, it formats a date as a timestamp, according to a format string. Your attempted use doesn't fit into either of these categories. I think this does what you're trying to do:

TO_TIMESTAMP('12/01/2002 0:00:00', 'MM/dd/yyyy h:mm:ss') AS TS

Q. I am trying to access the "DNS Server", "Directory Service", and "File Replication Service" Event Logs on a remote W2K system. I can access the System, Security and Application Event logs remotely with no problem.

ie. (This works well)

LogParser -i:EVT -o:CSV "SELECT * FROM \\MyNetworkServer\Application TO test.csv"

If I run the LogParser on the system locally, I can access the other logs with no problem.

ie. (This works from the command line on MyNetworkServer)

LogParser -i:EVT -o:CSV "SELECT * FROM File\u0020Replication\u0020Service TO test.csv"

I can not, however, figure out the syntax to do the same on a remote server.

ie. (this doesn't work)

LogParser -i:EVT -o:CSV "SELECT * FROM \\myNetworkServer\File\u0020Replication\u0020Service TO test.csv"

I get the following error no matter what I try:

ERROR: Error while looking for files: 

Error searching for files in folder 

\\myNetworkServer\File Replication Service: 

The network path was not found.

A. 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 your 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.

Q. Whenever I attempt to load less than 100 and more than 40 log records from a CSV file to a SQL 2000 (fully patched) DB (either master or one that I created), I get consistent, but incorrect results.

For example, using a CVS file with 70 records, 60 items are entered in the DB, and the data in the table is items 41-70, and then 41-70 repeated again (items 41-70 are entered twice) - there is no sign of items 1-40. If I try a smaller CSV file (greater than 40 entries), then I get the same pattern. If I create a 40 row CSV file, it works fine. If I create a 41 row CSV file, only item 41 is in the DB (it is in there twice - there are two rows total in the DB after this test).

What is strange is that if I output the same 70 row CSV file to the DATAGRID, all rows are present. I believe that the problems are with the DB import section of Logparser, or perhaps with the ODBC bulk load process?

A. This is a bug in Log Parser 2.0 and 2.1, which will be fixed in the upcoming 2.2 release.

For the time being, there is a workaround that you can try. Set the following registry key to 0 (zero):

HKCU\Software\Microsoft\Log Parser\SQLOutputMultiThreaded [DWORD] 0

(please note that use of this registry key is not guaranteed to work on future versions of Log Parser).

The above registry key will disable multi-threading in the SQL output, and you might experience some performance degradation in Log Parser. However, it should make sure that the problem doesn't happen.

Q. I can run a Log Parser query that includes multiple source files fine from the command line:

logparser "SELECT DISTINCT c-ip FROM \\logcabin\LogFiles\W3SVC1\ex031204.log, 

\\logcabin\LogFiles\W3SVC1\ex031203.log WHERE sc-bytes=9436"

This gives back the merged results from the two files. But when I create a file named Test.qry containing the same query:

SELECT DISTINCT c-ip


FROM \\logcabin\LogFiles\W3SVC1\ex031204.log, \\logcabin\LogFiles\W3SVC1\ex031203.log


WHERE sc-bytes=9436

and run the same query from the file:

logparser file:Test.qry

I get an error that "The filename, directory name, or volume label syntax is incorect." How can I fix this?

A. There's a bug in Log Parser through 2.1 (fixed in 2.2) that causes this problem when there are multiple filenames and the last filename is at the end of a line in the query file. To work around this bug, pull the WHERE keyword up so that the filename is not the last thing on the line:

SELECT DISTINCT c-ip


FROM \\logcabin\LogFiles\W3SVC1\ex031204.log, \\logcabin\LogFiles\W3SVC1\ex031203.log WHERE 

sc-bytes=9436

Q. I'm trying to use the -createtable switch with SQL Server 7.0, and I keep getting an error:

Task aborted.

Error while preparing ODBC statement

SQL State: 42S02

Native Error: 208

Error Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid

object name 'myTable'.

What can I do?

A. Unfortunately, this is a bug in all versions of Log Parser through 2.1 (fixed in 2.0). There's no workaround. You can either use SQL Server 2000, or create the table before running the Log Parser query (you may find the command-line SQL Server utility isql useful for this).

Mike Gunderloy is the lead developer for Larkware and author of numerous books and articles on programming topics.

Home