Weblogic aceess.log into Oracle DB via SQL Loader

User access statistics help a lot to see how an application is being utilized by its user community. However querying on user statistics become lot easier if they are loaded into a relational DB such as Oracle. Below is an example of how to load access.log written at weblogic using "common" format. This is the default one. Below scripts can be easily modified for extended or combined formats as well. These can also be used to load apache access_log files:

create table weblogic_access_log (

    ip_address     char(20)     ,

    user_inf       char(20)     ,

    user_id        char(100)     ,

    req_date       char(60)     ,

    request        varchar2(4000),

    status_cd      char(10)     ,

    res_size       char(10)

  )



Install an oracle client for SQLLDR and TNS utilities on your desktop. Create a connection to your db. For example, here i created a connection called KPSOFT_DB1. Schema name is weblogic_logs.


Download weblogic access.log to a directory called weblogic_logs on your desktop. 

Below is the control file, weblogic_control_file.ctl 
save this file to the same folder where the logs are downloaded on your desktop

load data                           

infile  'access.log'

infile  'access.log00001'

infile  'access.log00002'

infile  'access.log00003'

infile  'access.log00004'

infile  'access.log00005'

infile  'access.log00006'

infile  'access.log00007'

infile  'access.log00008'

badfile 'accesslog.bad'

discardfile 'accesslog.dsc'

append                              

-- insert, APPEND,REPLACE,TRUNCATE

into table weblogic_logs.weblogic_access_log    --

(  ip_address   position(*) char terminated by whitespace                 

 , user_inf     position(*) char terminated by whitespace

 , user_id      position(*) char terminated by whitespace 

 , req_date     position(*) char enclosed by '[' and ']'        

 , request      position(*) char enclosed by '"' and '"'

 , status_cd    position(*) char terminated by whitespace

 , res_size     position(*) char terminated by whitespace

)

Open a command prompt on your desktop and cd to the folder where logs and control file are downloaded, make sure your paths are set correctly to access sqlldr utility. run the following command:


sqlldr weblogic_logs/welcome1@KPSOFT_DB1 control=weblogic_control_file.ctl errors=500


errors could be resulted in case the data int eh file exceeds the delimitation or the width specified in the table.


Files are now loaded into the table for easier querying and even joining with your identity management tables.