Oracle external organized Table for Tomcat accesslog analysis

This posting shows by example how to query a tomcat access log via SQL. This works only with an Oracle DB. Key feature for this is the "external Table" support. Tabledata is populated with sqlloader internally, this allows nearly every fileformat to be processed. SQL Loader Syntax ist documented in the "utility" manual (Oracle9i Database Utilities). this example works with Version )9i Release 2 (called 9.2 but exactly 9.0.2.x).

A tomcat accesslog has the following lines:

10.19.33.229 - - [18/May/2004:08:28:57 1000] "POST /kat3/servlet/kat3/action/Stammdaten.Artikel.Detail HTTP/1.1" 200 690

you need an oracle account and the rights to create/query a table & directory. the first step is to create an directory object so that an access is possible. Syntax:

create directory ext_dir_tomcatlogs as  '/export/home/tomcat/logs';

specify your filesystem directory where your logfile of interest exists. Next step is to create the table:

create table ext_accesslog 

	   ( ip varchar2(30), 

	   	 c2 varchar2(30), 

		 c3 varchar2(30), 

	     accessdate varchar2(100), 

		 c1000 varchar2(20), 

		 url varchar2(1000), 

		 c6 varchar2(40), 

		 c7 varchar2(50)

	   )

organization external 

( 

  type oracle_loader

  default directory ext_dir_tomcatlogs

  access parameters (

   fields terminated by WHITESPACE

   OPTIONALLY enclosed by '"'

   (

    ip, c2, c3, accessdate, c1000, url, c6, c7

   ))

   location ('localhost_access_log.2004-05-13.txt')

)

reject limit unlimited

;

Now some analysis can be done e.g.

select * from ext_accesslog where url not like '%.gif HTTP/1.1' order by ip, accessdate 

select ip, min(accessdate), max(accessdate), count(*) from ext_accesslog group by ip