Writing logs into an external DB — log4j2 & WSO2 products

Hello dears ….

I return with another new story describing how to write logs into an external/custom DB using log4j2 configurations on WSO2 products.

Ref: https://www.polygon.com/reviews/23799502/barbie-movie-review-margot-robbie-ryan-gosling

Five years ago, I wrote a medium with log4j and wso2 products for the same topic. Now WSO2 products use log4j2. Therefore, let's see how we can achieve this with log4j2.

Recently, I had a requirement to print and persist API request and response details(Example: API name, context, version, request payload, response payload, http method, status and time), which deployed on WSO2 EI 6.6.0 product.

With this requirement, I decided to write a custom global synapse handler to capture API request and response information. It prints logs as follows.

[2023-08-10 17:36:55,211] [-1234] DEBUG {org.wso2.sample.synapse.handler.BasicHandler} - transactionId=135f4884-eeb4-4834-9606-0f0873cf9837 | calledMethod=POST | requestURI=/hello | requestTime=2023-08-10 17:36:55.176 | RequestPayload=<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><jsonObject><Hi>Nadee</Hi></jsonObject></soapenv:Body>
[2023-08-10 17:36:55,651] [-1234] DEBUG {org.wso2.sample.synapse.handler.BasicHandler} - transactionId=135f4884-eeb4-4834-9606-0f0873cf9837 | calledMethod=null | requestURI=/hello | responseTime=2023-08-10 17:36:55.650 | responseCode=200 | response MessageOK| errorMessage=No Error | response Message=<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><jsonObject><hello>world</hello></jsonObject></soapenv:Body>

Now I need to save those logs to a Database.

Let's see how to do that step by step.

Prerequisites

1. WSO2 EI 6.6.0 or Any other WSO2 product with log4j2 (Example: APIM 3x, APIM 4x, IS 5.9.0, IS 5.10.0, IS 5.11.0, IS 6.x, EI 7.x, MI 4.x)
2. MySQL 8.0 or any other DB server (Example: PostgreSQL, Oracle, MS SQL…etc.)

• Note: My Sample uses EI 6.6.0 and MySQL 8.0

Here I am going to save the logs, which write through my custom syanpse handler. Instead, you can use WSO2 carbon logs, audit logs, or other default patterns on the product.

I used the following appender and logger to write those logs to a log file.

appender.CUSTOM_LOGGER_APPENDER.type = RollingFile
appender.CUSTOM_LOGGER_APPENDER.name = CUSTOM_LOGGER_APPENDER
appender.CUSTOM_LOGGER_APPENDER.fileName = ${sys:carbon.home}/repository/logs/wso2-custom-log.log
appender.CUSTOM_LOGGER_APPENDER.filePattern = ${sys:carbon.home}/repository/logs/wso2-custom-log-%d{MM-dd-yyyy}.log
appender.CUSTOM_LOGGER_APPENDER.layout.type = PatternLayout
appender.CUSTOM_LOGGER_APPENDER.layout.pattern = [%d] [%tenantId] %5p {%c} - %m%ex%n
appender.CUSTOM_LOGGER_APPENDER.policies.type = Policies
appender.CUSTOM_LOGGER_APPENDER.policies.time.type = TimeBasedTriggeringPolicy
appender.CUSTOM_LOGGER_APPENDER.policies.time.interval = 1
appender.CUSTOM_LOGGER_APPENDER.policies.time.modulate = true
appender.CUSTOM_LOGGER_APPENDER.policies.size.type = SizeBasedTriggeringPolicy
appender.CUSTOM_LOGGER_APPENDER.policies.size.size = 10MB
appender.CUSTOM_LOGGER_APPENDER.strategy.type = DefaultRolloverStrategy
appender.CUSTOM_LOGGER_APPENDER.strategy.max = 20

logger.BasicHandler.name = org.wso2.sample.synapse.handler.BasicHandler
logger.BasicHandler.level = DEBUG
logger.BasicHandler.appenderRef.CUSTOM_LOGGER_APPENDER.ref = CUSTOM_LOGGER_APPENDER
logger.BasicHandler.additivity = false

loggers = AUDIT_LOG, ..., BasicHandler

According to the log pattern(`appender.CUSTOM_LOGGER_APPENDER.layout.pattern = [%d] [%tenantId] %5p {%c} — %m%ex%n`), we have five parts to save. There are,

1. Date: [%d] — [2023–08–10 17:36:55,211]
2. Tenant ID: [%tenantId] — [-1234]
3. Log level: %5p — DEBUG
4. Logging component: {%c} — {org.wso2.sample.synapse.handler.BasicHandler}
5. Logging details: %m%ex%n — transactionId=135f4884-eeb4–4834–9606–0f0873cf9837 | calledMethod=POST | requestURI=/hello | requestTime=2023–08–10 17:36:55.176 | RequestPayload=<soapenv:Body xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/"><jsonObject><Hi>Nadee</Hi></jsonObject></soapenv:Body>

Therefore, we need to create a table inside your custom database with five columns. You can decide the number of columns on your table based on your login pattern.

So I used the following queries to create DB and table on my MySQL server.

CREATE DATABASE Log_DB;

USE Log_DB;

CREATE TABLE LOGS(DATE VARCHAR(200) NOT NULL, TENANT_ID VARCHAR(20) NOT NULL, LEVEL VARCHAR(10) NOT NULL, LOGGER VARCHAR(200) NOT NULL,MESSAGE VARCHAR(2000) NOT NULL);

Now you need to add the following JDBC appender and logger to the "log4j2.properties" file, which resides in the "<MI/EI_HOME>/conf" or "<APIM/IS_HOME>/repository/conf" directory.

#External DB details
appender.jdbc.type = JDBC
appender.jdbc.name = jdbc
appender.jdbc.connectionSource.driverClassName = com.mysql.cj.jdbc.Driver
appender.jdbc.connectionSource.type = DriverManager
appender.jdbc.connectionSource.connectionString = jdbc:mysql://<MYSQL_Server_hostname>:<MYSQL_server_port>/Log_DB?useSSL=false
appender.jdbc.connectionSource.userName=********
appender.jdbc.connectionSource.password=********
appender.jdbc.tableName=LOGS
appender.jdbc.ignoreExceptions=false

# map of column and relevant log part needs to save
appender.jdbc.columnConfigs[0].type = COLUMN #column type
appender.jdbc.columnConfigs[0].name = DATE # column name 
appender.jdbc.columnConfigs[0].pattern = %d # log part that need to persist; example: date
appender.jdbc.columnConfigs[0].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.

appender.jdbc.columnConfigs[1].type = COLUMN
appender.jdbc.columnConfigs[1].name = TENANT_ID
appender.jdbc.columnConfigs[1].pattern =%tenantId
appender.jdbc.columnConfigs[1].isUnicode =false

appender.jdbc.columnConfigs[2].type = COLUMN
appender.jdbc.columnConfigs[2].name = LEVEL
appender.jdbc.columnConfigs[2].pattern =%5p
appender.jdbc.columnConfigs[2].isUnicode =false

appender.jdbc.columnConfigs[3].type = COLUMN
appender.jdbc.columnConfigs[3].name = LOGGER
appender.jdbc.columnConfigs[3].pattern =%c
appender.jdbc.columnConfigs[3].isUnicode =false

appender.jdbc.columnConfigs[4].type = COLUMN
appender.jdbc.columnConfigs[4].name = MESSAGE
appender.jdbc.columnConfigs[4].pattern =%mm%ex%n
appender.jdbc.columnConfigs[4].isUnicode =false

Through this JDBC appender, you need to specify the external DB connect URL, DB user & password, driver name, table name, column set and their mapping with the log pattern.

I have five columns on my table so you can see each column's details on the above appender configuration.

Finally, To set the logs to a specific appender, we also need to add a configuration to the respective logger.
For example, I use “BasicHandler” as my logger for this custom class. Therefore, I set this “jdbc” appender with my logger as follows.

#logger reference
logger.BasicHandler.appenderRef.jdbc.ref = jdbc

Now, all set; you need to add this “jdbc” appender to the “appender” list of the “log4j2.properties” file to activate this task. It shows below.

appenders = CARBON_CONSOLE, CARBON_LOGFILE, ..., CUSTOM_LOGGER_APPENDER, jdbc

All steps are done. You can save the changes on the “log4j2.properties” file and test the scenario.

Let’s test and see the result.

Ref: https://www.kansascity.com/opinion/letters-to-the-editor/article277669513.html

Test and Outcome

Now you can invoke API or service on your EI and see logs are printing as expected. Then if you query the LOGS” table, you will see the persisted records in the following screenshot.

So that’s all for today's story. I think this will be useful for your task with WSO2 products. Let’s give it a try, and let me know your feedback.

Appriciate your claps and shares :-) ❤

bye bye … see you soon … ❤ ❤

Ref: https://www.kqed.org/arts/13931753/allan-doll-michael-cera-greta-gerwig-barbie-movie-review