A
Log File Types Supported by Clickstream Intelligence

Oracle9iAS Clickstream Intelligence supports the collection and analysis of data from the following standard log file types:

• Apache Log Format

• W3C Extended Log File Format

• Microsoft IIS Extended Log File Format

The log formats above contain some fields that are case-insensitive. Case-insensitive log fields are converted to lower-case when loaded into the Clickstream Intelligence database. Case-insensitive log fields include the following:

• Client IP Address

• Client Hostname

• Server IP Address

• Server Hostname

• Server Name

• The "Hostname" portion of the Referrer field

All other log fields are treated in a case-sensitive manner - that is, the original case of the field is not changed as it is loaded into the database.

This appendix provides information about each log file type supported by Oracle9iAS Clickstream Intelligence. Log format fields are defined in a summary table at the end of each section.

Apache Log Format

Apache is an open-source Web server supported by the Apache Software Foundation. The Apache HTTP Server Project develops and maintains the Apache HTTP server, one of the most popular servers on the internet. The two formats used most often with Apache are Common and Combined.

Common Log File Format

Apache Common format displays each request as a separate line in the Web log. Fields are separated with a space, and may be enclosed in quotation marks (" "), as with the "Request Line" field in the Apache Log File Format Example that follows. Time in the "Date and Time" field includes an offset from Greenwich Mean Time (GMT). Null values for log fields are represented by a dash ( - ).

Apache Common Log Format contains all basic Web log parameters (see Apache Log Format Details), but does not include information about the Referrer, Agent, Time to Serve (transfer time), Domain Name, or Cookie string.

Combined Log File Format

Apache Combined format is simply an extension of Common Log Format. It contains the same fields as Apache Common Log Format, with the addition of two fields: Referrer and User Agent. Apache Combined Log Format does not provide Domain Name, Time to Serve (transfer time), or Cookie information.

Apache Log File Format Example

Consider the following example of a Web log entry in the Apache Combined Log Format:

203.93.245.97 - oracleuser [28/Sep/2000:23:59:07 -0700] "GET 
/files/search/search.jsp?s=driver&a=10 HTTP/1.0" 200 2374 
"http://datawarehouse.us.oracle.com/datamining/contents.htm" "Mozilla/4.7 [en] 
(WinNT; I)"

In the entry above, the following parameters can be identified:

• Client IP Address: 203.93.245.97

• Remote Logname: - (null; value not logged by server)

• Authenticated Username: oracleuser

• Request Date and Time: [28/Sep/2000:23:59:07 -0700]

• Request Line (from client to server): GET/files/search/search.jsp?s=driver&a=10 HTTP/1.0

• Status Code (also called the Server Response Code): 200

• Bytes Sent from Server to Client: 2374

• Referrer: http://datawarehouse.us.oracle.com/datamining/contents.htm

• User Agent: Mozilla/4.7 [en] (WinNT; I)

  The User Agent field is comprised of the following data:

  • Browser: Mozilla (code name for Netscape)

  • Browser Version: 4.7

  • Browser Language: en (English)

  • Browser Operating System: WinNT

• Filename: search.jsp

• Request Method: GET

• Transport Protocol (and version): HTTP/1.0

• Request Stem (path of requested document relative to its root directory on the Web server): /files/search/search.jsp

• Request Query String: ?s=driver&a=10

Apache Log Format Details

Apache log files may contain some or all of the following fields.

Table A-1 Apache Log Format details

Field Name	Description	Field Directive	Example Values
Client IP Address	IP address of the host making the request	%a	123.1.2.3
Remote Logname	Remote logname (from identd). This field is almost always null ("-")	%l	-
Authenticated User Name	Name or identifier of the authenticated user	%u	j.user
Request Date and Time	Date and time at which the request was received by the server (in Common Log time format)	%t	[14/Jan/2001 23:59:09 -0800]
Request Line	Line in an HTTP request that contains the Method, Request-URI, and Protocol ending with <CR><LF>	%r	GET /path/to/resource?query+string HTTP/1.1
Status Code	The server HTTP response code	%s	200, 304, 404
Bytes Sent	Number of bytes transferred from the server to the client	%b, %B	14378
Referrer	URI of the resource (typically a web site) from which the requested URI was obtained	"%{Referrer"	http://www.google.com/search?q=oracle
User Agent	Information about the browser that made the request	"%{User-Agent}i"	Mozilla/4.51 [en] (WinNT; U)
Cookie String	Cookie name-value pairs separated by a semicolon and space character	"%{Cookie}i"	COOKIE1=value; COOKIE2=value
Client Hostname	DNS hostname of the host making the request	%h	client-123lp.domain.net
Server IP Address	IP address of the host fulfilling the request	%A	123.1.2.3
Filename	Filename of the requested URI	%f	index.html
Request Method	HTTP method of the request	%m	GET, POST
Transport Protocol	HTTP protocol version string	%H	HTTP/1.1
Server Port	Port number of the listener fulfilling the request	%p	80
Server Process ID	Identifier of the process that fulfilled the request	%P	4971
Request Stem	Stem (path) component of the requested URI	%U	/path/to/resource
Request Query String	Query component of the requested URI	%q	?page=catalog&x=100&y=0
Time to Serve	Time taken to serve the request (in seconds)	%T	0, 1, 2, 802
Server Name	Server name of the host fulfilling the request	%v, %V	server1
Session Identifier Field	Session identifier as a separate field		1227584, E034080020CB1B7C
Visitor Identifier Field	Visitor identifier (such as a cookie) as a separate field		710EA25716662CACE0
General Purpose Fields 1-10	Users may define (customize) up to ten log fields		Any value(s)

W3C Extended Log File Format

W3C Extended is a flexible, highly configurable Web log format developed by the World Wide Web Consortium (W3C) as a common standard to support the needs of servers, clients, and proxies. Each W3C Extended format log file is self-identifying -- it displays at the beginning of each log file a header containing information about the data types recorded, as well as the version of the extended log file format used. As with Microsoft IIS Extended Log File Format, field directives may begin with:

• c (client)

• s (server)

• cs (client to server)

• sc (server to client)

W3C Extended log fields are separated by a whitespace (although tabs are most commonly used and are encouraged by the W3C specification). A null value for a field is typically represented by a dash ( - ); the "Date" and "Time" fields are expressed as GMT time.

W3C Extended Log File Format Example

Consider the following example of a Web log entry generated in W3C Extended Log File Format:

#Version: 1.0
#GMT-Offset: -0800
#Software: Oracle9iAS Web Cache/2.0.0.2.0
#Start-Date: 2001-10-31 00:00:18
#Fields:  c-ip  c-dns  c-auth-id  date  time  cs-method  cs-uri  sc-status  
bytes  cs(Cookie)  cs(Referrer)  time-taken  cs(User-Agent)  
#Date: 2001-10-31 00:00:18
64.103.37.2      client_joaz7      DMS.user      2001-10-31     00:00:18      
GET      /admin/images/oc_bottomleft.gif    200     350    "BIGipServerwww_
webcache_pool=1443321748.19460.0000;ORA_UCM_
AGID=%2fMP%2f8M7%3etSHPV%40%2fS%3f%3fDh3VHO"         
"http://www.oracle.com/nl/partner/content.html"      370879      "Mozilla/4.5 
[en] (WinNT; I)"

In the entry above, the following parameters can be identified. The #Fields header indicates the order of fields that appear in the log file entry.

• Client IP Address: 64.103.37.2

• Client Resolved IP Address: client_joaz7

• Authenticated Username: DMS.user

• Request Date: 2001-10-31

• Request Time: 00:00:18

• Client to Server Request Method: GET

• Request URI: /admin/images/oc_bottomleft.gif

• Status Code (or Server Response Code): 200

• Bytes Sent from Server to Client: 350

• Cookie String: BIGipServerwww_webcache_pool=1443321748.19460.0000;ORA_UCM_AGID=%2fMP%2f8M7%3etSHPV%40%2fS%3f%3fDh3VHO

• Referrer: http://www.oracle.com/nl/partner/content.html

• Time Taken (to serve the request): 370879 (seconds)

• User Agent: Mozilla/4.5 [en] (WinNT; I)

  The User Agent field is comprised of the following data:

  • Browser: Mozilla (

  • Browser Version: 4.5

  • Browser Language: en (English)

  • Browser Operating System: WinNT

WC3 Extended Log File Format Details

W3C Extended Format log files may contain the following fields:

Table A-2 WC3 Extended Log File Format details

Field Name	Description	Field Directive	Example Values
Request Date	Date on which the request was received by the server	date	2001-01-14
Request Time	Time at which the request was received by the server	time	23:59:09
Request Date and Time	Date and time at which the request was received by the server (separated by a space)	date time	2001-01-14 23:59:09
Time to Serve	Time taken to complete the request (in seconds)	time-taken	0.062, 0.392, 2, 802.1
Bytes Sent	Number of bytes transferred from the server to the client	bytes	14378
Cache Hit	Indicates if the request was fulfilled using cached content (0 = cache miss, 1 = cache hit)	cached	0, 1
Client IP Address	IP address of the host (client) making the request	c-ip	123.1.2.3
Server IP Address	IP address of the host (server) fulfilling the request	s-ip	123.1.2.3
Client Hostname	Resolved DNS hostname of the host (client) making the request	c-dns	client-123lp.domain.net
Server Hostname	Resolved DNS hostname of the host fulfilling the request	s-dns	server1, server1.domain.com
Server Port	Port number of the listener fulfilling the request	s-port	80
Status Code	HTTP response code returned from the server	sc-status	200, 304, 404
Status Comment	Comment that defines the server status code	sc-comment	OK, Not Found
Authenticated User Name	Name or identifier of the authenticated user	c-auth-id	j.user
Request Method	HTTP method of the request	cs-method	GET, POST
Transport Protocol	HTTP protcol version string	cs- protocol	HTTP/1.1
Request URI	Requested Uniform Resource Identifier (Request-URI)	cs-uri	/path/to/resource?page=catalog&x=100&y=0
Request Stem	Stem (path) component of the requested URI	cs-uri- stem	/path/to/resource
Request Query String	Query component of the requested URI	cs-uri- query	?page=catalog&x=100&y=0
User Agent	Information about the user agent (browser) originating the request	cs (User-Agent)	Mozilla/4.51 [en] (WinNT; U)
Cookie String	Cookie name-value pairs separated by a semicolon and space character	cs (Cookie)	COOKIE1=value; COOKIE2=value
Referrer	URI of the resource (typically a web site) from which the requested URI was obtained	cs (Referrer)	http://www.google.com/search?q=oracle
Session Identifier Field	Session identifier as a separate field		1227584, E034080020CB1B7C
Visitor Identifier Field	Visitor identifier (such as a cookie) as a separate field		710EA25716662CACE0
General Purpose Fields 1-10	Users may define up to ten log fields		Any value(s)

Microsoft IIS Extended Log File Format

Microsoft IIS Extended Web logs are loosely based upon W3C Web log format. Microsoft IIS Extended logs contain the basic information found in both Apache and W3C format Web logs, in addition to other unique fields (such as "Win32 Status"). The beginning of every Microsoft IIS Extended log file (IIS 4.0 and higher) displays a header indicating the fields in the order that they were recorded by the Web server. Most field indentifiers begin with one of the following prefixes:

• c (client)

• s (server)

• cs (client to server)

• sc (server to client)

Fields in Microsoft IIS Extended format logs can be separated by a space. To ensure that spaces contained in the data are not misinterpreted as field delimiters, fields are URL-encoded. Null values are displayed as a dash ( - ). The "Date" field is expressed in local time.

Microsoft IIS Extended Log File Format Example

Consider the following example of a Web log entry generated in Microsoft IIS Extended Log File Format:

2000-09-28 06:59:07 203.93.245.97 oracleuser W3SVC1 DATAWAREHOUSE
144.25.86.192 GET /files/search/search.jsp s=driver&a=10 200 0 2374 369
2938 80 HTTP/1.0 Mozilla/4.7+[en]+(WinNT;+I) -
http://datawarehouse.us.oracle.com/datamining/contents.htm

In the entry above, the following parameters can be identified:

• Request Date: 2000-09-28

• Request Time: 06:59:07

• Client IP Address: 203.93.245.97

• Authenticated User Name: oracleuser

• Site Name: W3SVC1

• Web Server Name: DATAWAREHOUSE

• Web Server IP Address: 144.25.86.192

• Request Method: GET

• Request Stem (relative to root directory on Web server): /files/search/search.jsp

• Request Query String: s=driver&a=10

• Status Code (or Server Response Code): 200

• Win 32 Status: 0 (internal status code specific to Microsoft IIS Web server)

• Bytes Sent (from server to client): 2374

• Bytes Received (from client to server): 369

• Time to Serve the Request: 2938 (milliseconds)

• Server Port: 80

• Transport Protocol (and version): HTTP/1.0

• User Agent: Mozilla/4.7+[en]+(WinNT;+1)

  The User Agent field is comprised of the following data:

  • Browser: Mozilla (Netscape)

  • Browser Version: 4.7

  • Browser Language: en (English)

  • Browser Operating System: WinNT

• Cookie String: - (null; no value recorded by server)

• Referrer: http://datawarehouse.us.oracle.com/datamining/contents.htm

Microsoft IIS Extended Format Details

Microsoft IIS Extended Format log files may contain the following fields:

Table A-3 Microsoft IIS Extended Format details

Field Name	Description	Field Directive	Example Values
Request Date	Date at which the request was received by the server	date	2001-01-14
Request Time	Time at which the request was received by the server	time	23:59:09
Client IP Address	IP address of the host making the request	c-ip	123.1.2.3
Authenticated User Name	Name or identifier of the authenticated user	c-auth-id	d.smith
Site Name	Name of the server that MS IIS sets up	s-sitename	MYSITE
Server Name	Server name of the host fulfilling the request	%v, %V	server1
Server IP Address	IP address of the host fulfilling the request	s-ip	123.1.2.3
Request Method	HTTP method of the request	cs-method	GET, POST
Request Stem	Stem (path) component of the URI requested by the client	cs-uri- stem	/path/to/resource
Request Query String	Query component of the URI requested by the client	cs-uri- query	?page=catalog&x=100&y=0
Status Code	HTTP status code returned from the server	sc-status	200, 304, 404
Win32 Status	Debug status code - an internal status code specific to Microsoft IIS Web server	sc-win32-status	Any number from zero to infinity.
Bytes Sent	Number of bytes transferred from the server to the client	sc-bytes	14378
Bytes Received	Number of bytes transferred from the client to the server	cs-bytes	14378
Time to Serve	Time taken to serve the request (in milliseconds)	time-taken	0.062, 0.392, 2, 802.1
Server Port	Port number of the listener fulfilling the request	s-port	80
Transport Protocol	HTTP protcol version string	cs- protocol	HTTP/1.1
User Agent	Information about the user agent (browser) originating the request	cs (User-Agent)	Mozilla/4.51 [en] (WinNT; U)
Cookie String	Cookie name-value pairs separated by a semicolon and space character	cs (Cookie)	COOKIE1=value; COOKIE2=value
Referrer	URI of the resource (typically a web site) from which the requested URI was obtained	cs (Referrer)	http://www.google.com/search?q=oracle
Session Identifier Field	Session identifier as a separate field		1227584, E034080020CB1B7C
Visitor Identifier Field	Visitor identifier (such as a cookie) as a separate field		710EA25716662CACE0
General Purpose Fields 1-10	Users may define (customize) up to ten log fields		Any value(s)