Write Logs into the External Database — WSO2 Products

When considering the wso2 servers, there are log messages to inform the state of the system. As well users can use the Log mediator for their mediation flow to verify their works are done correctly. However, some time for the data mining purpose, we need to store them in an external database. To store them, we can modify the log4j file in wso2 products and write them into an external database. To achieve this, we need to follow the below steps.

• First, need to create the database and table, which we need to configure that logs

CREATE DATABASE Log_DB;
USE Log_DB;
CREATE TABLE LOGS( USER_ID VARCHAR(20) NOT NULL, DATED DATETIME NOT NULL, LOGGER VARCHAR(200) NOT NULL, LEVEL VARCHAR(10) NOT NULL,MESSAGE VARCHAR(1000) NOT NULL);

• Then need to configure the log4jproperty file(/repository/conf) as follows.

i. Add the “sql” appender for the log4j.rootLogger as follows.

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

ii Then add the following configuration to the log4jproperty file

log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.sql.URL=jdbc:mysql://localhost:3306/LOG_DB #changed as your IP and port
# Set Database Driver
log4j.appender.sql.driver=com.mysql.jdbc.Driver
# Set database user name and password
log4j.appender.sql.user=root #Changed as your sql user name
log4j.appender.sql.password=root #Changed as your sql password
# 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

By this method, we can able to write the logs to the database in a direct manner. But, it may be affected by the performance of the system. Then as a solution for that, we can write them in batch-wise. for that, there is a log4j method as “bufferSize“. We can use it as follows.

log4j.appender.sql.bufferSize=1000

Here, the bufferSize value should be an integer and its default value is 1. This value refers the number of records. Then if we set as 1000, JDBC appender will wait until completing the 1000 records on the log file. After completing that value, that 1000 log records will write to the database. By doing this, you can avoid logs being written to the database each time a log entry is generated. Then it helps to improve the performance of your system.

Furthermore, when writing each logs also not good for the performance. For that, we can use the “filter” method and selected out the only required logs. Hence we can modify it as follows.

log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.sql.URL=jdbc:mysql://localhost:3306/LOG_DB
# Set Database Driver
log4j.appender.sql.driver=com.mysql.jdbc.Driver
# Set database user name and password
log4j.appender.sql.user=nadeepoornima
log4j.appender.sql.password=nadee
#Set the bufferSize
log4j.appender.sql.bufferSize=1000
#filter set of logs
log4j.appender.sql.filter.01=org.apache.log4j.varia.StringMatchFilter
log4j.appender.sql.filter.01.StringToMatch=message
log4j.appender.sql.filter.01.AcceptOnMatch=true
log4j.appender.sql.filter.02=org.apache.log4j.varia.DenyAllFilter
# 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

• Makesure add the JDBC driver to the lib folder(/reposotory/components/lib).
• Restart the server.

I hope, this will use full and please leave me the feedback.

Thank you :)

Reference

[1]. http://vanjikumaran.blogspot.com/2014/07/write-logs-into-external-database-in.html

[2]. https://logging.apache.org/log4j/log4j-2.2/manual/filters.html