Log4j2 — How to write the Logs into an External Database

https://en.wikipedia.org/wiki/Log4j https://pixabay.com/images/search/database/

Logs are the way of showing the state of any software system. Also, Users can use the logs to troubleshoot the applications very easily.

What is Log4j?

Apache Log4j is a Java-based logging utility and it is part of the Apache Logging Services, a project of the Apache Software Foundation.
— wikipedia

The Log4j version is upgraded in all WSO2 products to Log4j2 version 2.17.0 (apache-log4j-2.17.0) as a response to Log4j2 Zero-Day Vulnerability recently.
You can find more information on the WSO2 action response to this vulnerability here.

In this article, Let’s discuss how we can write the logs into an external database in Log4j2.

1. Create a database and a table. Here I will name the DB auditDB and database table as LOGS .
   This table has three columns as LOG_TIME, LOG_LEVEL, MESSAGE.

CREATE DATABASE auditdb;
USE auditdb;
CREATE TABLE LOGS(LOG_TIME DATETIME NOT NULL, LOG_LEVEL VARCHAR(20) NOT NULL, MESSAGE VARCHAR(1000) NOT NULL);

2. Add the relevant JDBC driver jar file for a particular Database to the repository/components/lib directory.

If you are using PostgreSQL you can download the driver from here. 
If you are using MYSQL you can download the driver from here. 
If you are using ORACLE you can download the driver from here.

3. Configure the log4j2.properties file resides in /repository/conf directory.

• Add the “jdbc” appender to the appender list as follows.

appenders = CARBON_CONSOLE, CARBON_LOGFILE, ATOMIKOS_LOGFILE, CARBON_TRACE_LOGFILE, DELETE_EVENT_LOGFILE, TRANSACTION_LOGFILE, osgi, jdbc

• Then add the following configuration to the log4j2.properties file to configure jdbc appender.

appender.jdbc.type = JDBC
appender.jdbc.name=jdbc
appender.jdbc.connectionSource.driverClassName=<database_driver_name_as_the_package>
appender.jdbc.connectionSource.type= DriverManager
appender.jdbc.connectionSource.connectionString=jdbc:postgresql://<host>:<port>/auditdb
appender.jdbc.connectionSource.userName=<username_of_the_db_user>
appender.jdbc.connectionSource.password=<password>
appender.jdbc.tableName=logs
appender.jdbc.ignoreExceptions=false
appender.jdbc.columnConfigs[0].type = COLUMN
appender.jdbc.columnConfigs[0].name = LOG_TIME
appender.jdbc.columnConfigs[0].pattern =%d
appender.jdbc.columnConfigs[0].isUnicode =false
appender.jdbc.columnConfigs[1].type = COLUMN
appender.jdbc.columnConfigs[1].name = LOG_LEVEL
appender.jdbc.columnConfigs[1].pattern =%5p
appender.jdbc.columnConfigs[1].isUnicode =false
appender.jdbc.columnConfigs[2].type = COLUMN
appender.jdbc.columnConfigs[2].name = MESSAGE
appender.jdbc.columnConfigs[2].pattern =%mm%ex%n
appender.jdbc.columnConfigs[2].isUnicode =false

i. appender.jdbc.connectionSource.driverClassName
Here you need to provide the driver class name
Eg :

For Postgresql=> 
appender.jdbc.connectionSource.driverClassName=org.postgresql.Driver
For MySQL =>
appender.jdbc.connectionSource.driverClassName=com.mysql.cj.jdbc.Driver

ii. appender.jdbc.ignoreExceptions=false

You can set this to false to see any errors that occurred in the terminal. This is very useful for troubleshooting.

iii. columnConfigs

In Log4j2, we can use the columnConfigs array to specify the columns and the data to be inserted.

type = COLUMN # set the type as COLUMN to set the column configurations
name = LOG_TIME # column name in the database
pattern =%d # get the log display time from the application and set it to the database.
isUnicode =false # we need to set this as false otherwise the log4j2 library will choose setNString method to set the value. If the paticular datatype of the column is Nvarchar, you can ignore this property.

• To set the logs to a specific appender, we also need to add a configuration to the respective logger.
  For example, if you need to write the logs audit logs to the database, you need to add the below configuration.

logger.AUDIT_LOG.name = AUDIT_LOG
logger.AUDIT_LOG.level = INFO
logger.AUDIT_LOG.appenderRef.AUDIT_LOGFILE.ref = AUDIT_LOGFILE
logger.AUDIT_LOG.appenderRef.jdbc.ref = jdbc
logger.AUDIT_LOG.additivity = false

Additional Configurations

• With the above configurations, we can write the logs to the database directly. But, when considering the performance, writing logs directly to the database is not a good practice.
  As a best practice, we can write them batch-wise by configuring the “bufferSize” property in jdbc appender.

appender.jdbc.bufferSize=1000

• We should provide an integer value to bufferSize and this value refers to the number of records/logs. If the bufferSize value is greater than 0, it will wait until the buffer reaches the mentioned size and after that, it will flush the data(write the data)to the DB. With this config, you can avoid logs being written to the database each time a log entry is generated, and this will help to improve the performance of your system.
• Also, if your requirement is to write only the selected/filtered logs, you can use filter property in the jdbc appender. You can find more information about log4j2 filters here.
• Following is the sample configuration with RegexFilter.

appender.jdbc.type = JDBC
appender.jdbc.name=jdbc
appender.jdbc.connectionSource.driverClassName=org.postgresql.Driver
appender.jdbc.connectionSource.type= DriverManager
appender.jdbc.connectionSource.connectionString=jdbc:postgresql://127.0.0.1:5432/auditdb
appender.jdbc.connectionSource.userName=dimuthuk
appender.jdbc.connectionSource.password=****
appender.jdbc.tableName=logs
appender.jdbc.filter.1.type=RegexFilter
appender.jdbc.filter.1.regex="<regex-pattern>"
appender.jdbc.filter.1.onMatch=ACCEPT
appender.jdbc.filter.1.onMismatch=DENY

4. Restart the server. Please note that we need to restart the server because we have added the relevant driver to the repository/components/lib directory. You don’t need to restart the server after every time you change the configurations in log4j2.properties file.

Hope you got an idea about how we can write the logs to an external DB using log4j2.

Thank you for reading this article!