Server logs to Postgresql

[houshmandX]

Hi,

I found the information in log4j about how to write server logs into the database but pictured some issues. Is there anyone has done this before? sending server logs to Postgres?
https://www.tutorialspoint.com/log4j/log4j_logging_database.htm
https://forums.mirthproject.io/forum/mirth-connect/support/11487-log-info

[jonbartels]

A few things to be aware of:

• MC uses Log4j 1.x (log4j vulnerablity - is Mirth affected? #4892) - When you're reading about Log4J configuration, remember to make sure you're reading the 1.x documentation and not the 2.x documentation
• https://github.com/odoodo/Mirth-MetaAppender is a great custom logger for MC. It does not log to the database but it is a good reference for how logging in MC works and some powerful things you can do with it

I would also be curious about what happens in Log4J if the DB connection is lost or the DB runs out of space. What happens to the logs and the application?

What problem are you trying to solve? Do you have large log files (logging less data might be better, log4j and logrotate also have compression options)? Do you need to preserve log files for a long time (is a DB a good choice or a tool like Graylog or some other log archiver)?

[ab-20-m]

The regular server-lib directory is not loaded into the classpath until after logging is initialized. You will need to copy the Postgres JDBC JAR into the server-launcher-lib folder or use the exe4j .vmoptions -classpath/p setting to prepend the JDBC driver at the start of the classpath for Mirth. A symlink to the JAR in the server-launcher-lib may also work but I seem to recall Java can have issues loading JARs that way.

The code where the classpath is built up before logging initialization is in MirthLauncher.java L54-L61.

[houshmandX]

here is where I create my table:
CREATE TABLE LOGS (USER_ID VARCHAR(20) NOT NULL, DATED DATE NOT NULL, LOGGER VARCHAR(50) NOT NULL, LEVEL VARCHAR(10) NOT NULL, MESSAGE VARCHAR(1000) NOT NULL );

here is where I insert to table:

# Set the SQL statement to be executed.
log4j.appender.DB.sql=INSERT INTO LOGS VALUES('%x','%d','%C','%p','%m')
# Define the layout for file appender
log4j.appender.DB.layout=org.apache.log4j.PatternLayout

and here is the error I get:

ERROR 2022-05-16 11:20:49,953 [Main Server Thread] Server: log4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute

how to fix the issue?

[jonbartels]

ERROR 2022-05-16 11:20:49,953 [Main Server Thread] Server: log4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute

Is there any more context to this error? Is there a stack trace from MC in the console or in mirth.log?

Any errors in your postgres.log for Postgres?

[houshmandX]

nothing beside bunch of same error:

ERROR 2022-05-16 14:16:21,892 [Main Server Thread] Server: log4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute sqllog4j:ERROR Failed to excute

[houshmandX]

FYI. I could able to fix the issues and write them into database. thanks for all help and hint.

[houshmandX]

here is the solution from my end:

1. for creating a table increase the varchar for logger and change the d(data type), so create the table as below:

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

2. add/copy postgres_jdbc.jar from the database folder in MC, and past/place it in server-launcher-lib folder in MC(mirth connect).
3. change date format '%d' to '%d{yyyy-MM-dd}'

here is my final log4j.properties that saves server logs to Postgres db:

# sample properties to initialize log4j
log4j.rootLogger = ERROR,stdout,fout,DB

# stdout appender
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = %-5p %d [%t] %c: %m%n


# file appender
dir.logs = C:\\Program Files\\Mirth Connect\\logs
log4j.appender.fout = org.apache.log4j.RollingFileAppender
log4j.appender.fout.File = ${dir.logs}/mirth.log
log4j.appender.fout.MaxFileSize = 500KB
log4j.appender.fout.MaxBackupIndex = 20
log4j.appender.fout.layout = org.apache.log4j.PatternLayout
log4j.appender.fout.layout.ConversionPattern = %-5p %d [%t] %c: %m%n

# Define the DB appender
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender

# Set JDBC URL
log4j.appender.DB.URL=jdbc:postgresql:https://XXX:5432/DBNAME

# Set Database Driver
log4j.appender.DB.driver=org.postgresql.Driver
log4j.appender.DB.user=USERNAME
log4j.appender.DB.password=PASSWORD

# Set the SQL statement to be executed.
log4j.appender.DB.sql=INSERT INTO LOGS VALUES('%x','%d{yyyy-MM-dd}','%C','%p','%m')
# Define the layout for file appender
log4j.appender.DB.layout=org.apache.log4j.PatternLayout


# splash screen
log4j.logger.com.mirth.connect.server.Mirth = INFO

# Mirth Connect server logging
log4j.logger.com.mirth.connect.server.controllers.DonkeyEngineController = INFO
log4j.logger.com.mirth.connect.donkey.server.channel.RecoveryTask = INFO
log4j.logger.com.mirth.connect.connectors.file.FileReceiver = WARN

# Mirth Connect channel logging
log4j.logger.transformer = INFO
log4j.logger.preprocessor = INFO
log4j.logger.postprocessor = INFO
log4j.logger.deploy = INFO
log4j.logger.undeploy = INFO
log4j.logger.filter = INFO
log4j.logger.db-connector = INFO
log4j.logger.js-connector = INFO
log4j.logger.attachment = INFO
log4j.logger.batch = INFO
log4j.logger.response = INFO

# SQL Logging
log4j.logger.java.sql = ERROR

[1jon11]

I just tried this and not working to me. I'm using Mirth Connect 4.4.2.

I edited log4j2.properties , on /conf directory

Any idea?

[pacmano1]

Mirth 4.4.2 uses log4j 2 not 1. Also, create a new discussion thread. Also don't post "it does not work for me" without thorough details.