Write the logs into External database in WSO2 Servers

Some time data mining purpose storing the logs in the database important and it is possible to do that with wso2 carbon products as well.

To achieve above task follow the steps that mention below. I have used mysql for demonstrate this task and it is possible to use and other RDBMS for this.

1. If the server is already running, stop the server.

2. Configure the database (say, LOG_DB) and create the following table (LOGS)

CREATE TABLE LOGS( USER_ID VARCHAR(20) NOT NULL, DATED   DATETIME NOT NULL, LOGGER  VARCHAR(50) NOT NULL, LEVEL   VARCHAR(10) NOT NULL,MESSAGE VARCHAR(1000) NOT NULL);

3. Configure the log4j.properties in the /repository/conf/

Since log4j.rootLogger is already defined append “sql” in it as follows.

log4j.rootLogger=ERROR, CARBON_CONSOLE, CARBON_LOGFILE, CARBON_MEMORY, CARBON_SYS_LOG, ERROR_LOGFILE, sql

Add the following,

log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender

log4j.appender.sql.URL=jdbc:mysql://localhost/LOG_DB

# Set Database Driver

log4j.appender.sql.driver=com.mysql.jdbc.Driver

# Set database user name and password

log4j.appender.sql.user=root

log4j.appender.sql.password=root

# Set the SQL statement to be executed.

log4j.appender.sql.sql=INSERT INTO LOGS VALUES ('%x', now() ,'%C','%p','%m')

# Define the xml layout for file appender

log4j.appender.sql.layout=org.apache.log4j.PatternLayout

4. Download the mysql driver from, http://dev.mysql.com/downloads/connector/j/5.0.html and place the jar (mysql-connector-java-5.1.31-bin) inside /repository/components/lib/

5. Start the server, you will be getting the logs in the LOGS table as well.