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
 host_name /nn
 alias vc100
 ip_address vc16

request
 request_string /nn

user_agent
 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
 referrer_string /nn
 alias vc100

http_status
 http_status /nn /unique
 alias vc100
 is_used yn

access_log
 host_id /nn
 remote_ip vc16
 access_time timestamp /nn
 request_id
 http_status number
 bytes number
 referrer_id
 user_agent_id
 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.