Webserver Logfile Analysis with Oracle APEX

Recently I wanted to set up better monitoring for our several FOEX webservers and decided not to use an off-the-shelf solution, since I wanted to have the analysis results in my Oracle Database, ready to be used by whatever internal tools we have.

Immediately I thought of an old blogpost by Carsten Czarski (him and Tim Hall seem to have all Oracle answers present on their respective blogs), but decided against using remote tables spread over several servers.

Instead I did setup a new datamodel to import the webserver logfile data into. Check out my QuickSQL definition:

 host_name /nn
 alias vc100
 ip_address vc16

 request_string /nn

 user_agent_string /nn
 alias vc100
 is_bot yn
 agent_type vc2000
 agent_name vc2000
 agent_version vc2000
 os_type vc2000
 os_name vc2000
 os_version_name vc2000
 os_version_number vc2000
 os_producer vc2000
 os_producer_url vc2000
 linux_distribution vc2000
 agent_language vc2000
 agent_language_tag vc2000

 referrer_string /nn
 alias vc100

 http_status /nn /unique
 alias vc100
 is_used yn

 host_id /nn
 remote_ip vc16
 access_time timestamp /nn
 http_status number
 bytes number
 hash number /index /nn

# settings = { prefix: "AWL", PK: "TRIG", drop: true, language: "EN" }

Using this data model it was very easy to create some charts based on analytic queries to visualize the data of interest:

Even before that we had to solve the issue of how to transport the logfile data (in almost real-time) from the webservers to the database.

Keep on reading how to stream the logfile data into the database in the next post.

One thought on “Webserver Logfile Analysis with Oracle APEX

  1. Pingback: Building a monitoring dashboard with Oracle APEX JET Charts

Leave a Reply

Your email address will not be published. Required fields are marked *