Custom logging to a database with PHP and Apache

My organization needed a way to create several types of persistent database-stored logs for our PHP website, where each log entry would record which user was requesting the resource.  We decided we would try for a solution that did not require us to do a database insert for every page view, for obvious reasons.  Here is what we came up with.  Our environment is PHP5 and Apache 2.2.

Apache’s custom log format shows how to log variables from the Apache environment.  If there’s an environment variable called FOOBAR, you log it with the format string %{FOOBAR}e.  This is great because PHP can inject data into the Apache environment, meaning we can get more or less any data we like into an Apache custom log.

The first Apache configuration step was to create a custom log format that we can then apply to a logfile.  We create the log format in httpd.conf.  Find the log_config_module section and add a LogFormat directive there:

<IfModule log_config_module>
LogFormat "%t %U %D %a %{MY_UID}e" mystats
</IfModule>

In this example, we add a log format named “mystats” which logs these datapoints:  date/time the request was received, URL path requested, time taken to serve the request, remote client IP address, and the environment variable MY_UID.  For our purposes, we are logging these data because our aim is to keep a 7-day rolling log of webserver performance – how long it took to process each request – to monitor our servers more effectively.

Then we set up a custom log for our site’s vhost in httpd-vhosts.conf:

<VirtualHhost 192.168.100.1:80>
...
CustomLog logs/mysite_custom_log mystats
...
</VirtualHost>

This makes Apache start logging every request to that host, in the format defined by our LogFormat directive named “mystats”.

The first step in PHP code, then, is to simply inject the MY_UID environment variable containing the user ID of whoever is logged in.  In our master controller there’s a check to see if the client has an authenticated session, and if they do we just add a quick call to set the environment var:

apache_setenv('MY_UID', $the_current_user_id);

Once this in all in place, you should be able to tail the logfile mysite_custom_log and see that requests are being logged with the requesting client’s user ID, or “-” if no user is authenticated.

We rotate these logs daily.  Our final step is a cron’d PHP script that runs in the wee hours, taking yesterday’s log file, iterating each line, inserting the data into a table… and we’re done!  We logged each request with how long our server processed it, and who was signed in for that request, and we did it without adding any overhead to our application.