An easy way to quickly redirect Apache's access log to a database with absolutely no scripting.

With ApacheLogToDB, you will be required to perform a few simple configuration tasks once and then everything will be automated. You can choose Apache's log format that best suits your preferences, and ApacheLogToDB will create the appropriate database table for you. ApacheLogToDB uses native database drivers to archieve optimum performance and meet high security standards.
  • Microsoft SQL Server (including free Express Edition) and MySQL databases are currently supported.
  • ApacheLogToDB requires .NET Framework to be intalled.

Install And Configuration

Apache Configuration

  • Deploy ApacheLogToDB's files in the \bin subfolder of your Apache server installation.
  • Choose Apache's log format that best suits your preferences. You can find some common formats and individual arguments description at this page. To conform to ApacheLogToDB's requirements, log format string must begin with "|" character and format arguments must be delimited by "|" character. Here you can see some example strings:
|%h|%l|%u|%t|%r|%>s|%b
|%h|%l|%t|%r|%s|%b|%{Referer}i|%{User-agent}i
|%t|%v|%{REMOTE_USER}e|%a|%{X-Forwarded-For}i|%m|%U|%q|%s|%B|%D|%{dds_auth}C
  • Open Apache's configuration file "httpd.conf" and make a few modifications in it. Firstly enable log_config Apache's module by uncommenting or adding the following line:
LoadModule log_config_module modules/mod_log_config.so
  • Define your log format, that was discussed above, by adding the following line (insert your own log format string into this line):
LogFormat "|%h|%l|%t|%r|%s|%b|%{Referer}i|%{User-agent}i" db_log
  • Make ApacheLogToDb handle the specified log format by adding the following line (correct the path as necessary):
CustomLog "|C:/Apache/bin/ApacheLogToDb.exe" db_log

ApacheLogToDB Configuration

  • Open "ApacheLogToDB.conf.xml" configuration file and edit it. It's XML file. The following configuration settings are availlable:
    • dbType - Database system. Supported values are sqlsrv (Microsoft SQL Server) or mysql (MySQL). Default is sqlsrv.
    • dbHost - Address or name of the database instance to which to connect. Default is localhost. Please, note that if SQL Server was installed as a named instance, dbHost must be specified as .\<sqlServerName> or <machineName>\<sqlServerName>. This particularly concerns SQL Server Express instances. Port can be specified after a colon like this: localhost:3306 or 192.168.0.14:8306
    • dbName - Name of the database in which log records must be stored.
    • dbTable - Name of the table in which log records must be stored.
    • dbUser - Login name of the database user. This user must have sufficient rights to create table, to write data into that table and (for SQL Server users) to use "dbo" scheme. To utilize Windows authentication, please, use the following format <myDomain>\<myUsername>;Integrated Security=SSPI
    • dbPassword - Database user's password.
    • apacheLogFormat - Log format string like |%h|%l|%u|%t|%r|%>s|%b, that was specified in Apache's configuration file.
    • maxParamLength - If any log record's parameter exceeds this number of characters, the overhead will be cut off. Default is 255. If maxParamLength is set to 0, no cutting occurs.
  • The following optional parameters are also available:
    • apacheTimestampFormat (optional) - The format of timestamp %t, that Apache uses in it's log. Default is [dd/MMM/yyyy:HH:mm:ss zz00] and normally it doesn't change unless a user does change it explicitly in Apache's configuration file. Use .NET's standard date and time format strings to define new timestamp if required.
    • dbConnectTimeout (optional) - Number of seconds to wait while trying to establish a connection before terminating the attempt and generating an error. Default is 10.
    • errorFile (optional) - The path to and the name of the file, to which ApacheLogToDB will record it's internal errors, e.g. C:\Logs\ApacheLogToDb.error.log. If it's not specified, the file ApacheLogToDb.error.log will be automatically created in Apache's \bin subdirectory or Apache's main directory.
    • longQueriesFile (optional) - If maxParamLength limit is specified, every log record's parameter exceeding that limit will be truncated. Such truncated records can be saved into file.
    • dumpFile (optional) - The path to and the name of the file, to which ApacheLogToDB will dump all records received from Apache, e.g. C:\Logs\ApacheLogToDb.dump.log. If it's not specified, no dumping will occur. It's useful for debugging purposes only.
    • profilingFile (optional) - The path to and the name of the file, to which ApacheLogToDB will record profiling information. It's useful for debugging purposes only.
  • Save the configuration file. If some configuration settings contain non-latin characters, make sure that configuration file is encoded using UTF-8 encoding, without byte-order mark.

Database Configuration

  • After saving ApacheLogToDB's configuration file, open your database management program. If that database doesn't exist, create the database with the name exactly as specified in dbName configuration setting. Create a user which was specified in dbUser setting and grant him necessary permissions. Example scripts are available for SQL Server and MySQL.
  • Launch the following commands from the Windows command line (correct the path as necessary):
cd C:\Apache\bin
ApacheLogToDB.exe -createTableAndExit

This will create a database table according to the log format, which was specified in apacheLogFormat configuration setting. Review the table and tune it if necessary. No indexes are created by default.
  • Restart Apache HTTP Server. That's all, enjoy!

Troubleshooting

  • If anything goes wrong, see ApacheLogToDB's error log file in first place. ApacheLogToDB's error messages are quite informative, you won't have much trouble finding the error's cause.
  • Most SQL Server connection issues are explained in this SQL Server Protocols Blog post.
  • Common problems with MySQL are explained in this section of MySQL documentation.
  • If "Authentication with old password no longer supported, use 4.1 style passwords." error arises when trying to connect to MySQL Server, the following commands should be launched in MySQL console (insert your username and password):
SET SESSION old_passwords=0;
SET PASSWORD FOR my_user=PASSWORD('my_password');
  • If Apache service doesn't start, it's typically a configuration problem. Try launching Apache as a program (not a Windows service) to see error description. Some configuration errors are also logged into Apache's error log file, that is usually called "error.log" and is usually located in Apache's \logs subfolder.

Last edited Aug 23, 2013 at 9:21 AM by CodeDriller, version 1

Comments

No comments yet.