Converting File Formats

Converting a log file from one format to another can be easily accomplished with Log Parser by executing a command with the following characteristics:

• The input format chosen for the command should match the conversion source format;
• The output format chosen for the command should match the conversion target format;
• The query should contain a SELECT clause that performs the necessary modifications on the input format field names and values in order to match the requirements of the target format.

When using Log Parser to convert one log file format to another, we should pay close attention to the order and names of the fields in the input and output formats. Some output formats, such as the IIS output format, have fixed fields. When converting to IIS log format, input format fields should be selected to match the IIS format exactly. For example, when converting a W3C Extended log file to IIS log format, we should select the client IP address first, the user name next, and so on.

In addition, we might want to change the name of the fields that we extract from the input format. For example, when writing to a W3C Extended format log file, Log Parser retrieves the names to be written to the "#Fields" directive from the SELECT clause. If we retrieve data from an IIS log format file, these names are not the same as those used by the W3C Extended format, so we must alias every field in order to get the correct field name.

As an example, consider the following SELECT clause that converts IIS log format files to IIS W3C Extended log format:

SELECT TO_DATE(TO_UTCTIME(TO_TIMESTAMP(Date, Time))) AS date, 
TO_TIME( TO_UTCTIME( TO_TIMESTAMP(Date, Time))) AS time, 
ServiceInstance AS s-sitename, 
HostName AS s-computername, 
ServerIP AS s-ip, 
RequestType AS cs-method, 
REPLACE_CHR(Target, ' \u0009\u000a\u000d', '+') AS cs-uri-stem, 
Parameters AS cs-uri-query, 
UserName AS cs-username, 
UserIP AS c-ip, 
StatusCode AS sc-status, 
Win32StatusCode AS sc-win32-status, 
BytesSent AS sc-bytes, 
BytesReceived AS cs-bytes, 
TimeTaken AS time-taken

The command-line Log Parser executable can be used to run built-in queries that perform conversions between the following formats:

• BIN to W3C
• IIS to W3C
• BIN to IIS
• IISW3C to IIS

© 2004 Microsoft Corporation. All rights reserved.