geek4u
asked on
Log4j Database related
I have an existing code which used log4j to log to a file. I need to log to Databases. I want basic guidance of what to do and how to do?
What needs to be changed without changing the source code so that the statements are logged into database.
Please help.
Thanks
What needs to be changed without changing the source code so that the statements are logged into database.
Please help.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's an e.g. using the properties file:
http://www.dankomannhaupt.de/projects/jdbcappender/src/org/apache/log4j/jdbcplus/examples/test/log4j.properties
http://www.dankomannhaupt.de/projects/jdbcappender/src/org/apache/log4j/jdbcplus/examples/test/log4j.properties
ASKER
please can you be more clear!! Just explain the steps that need to be done.
Thanks,
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to set up the db to take the fields you want to log and then configure log4j.properties. I would follow
# JDBC appender directly, 2b)
as per my last link
# JDBC appender directly, 2b)
as per my last link
You have add an appender to log category for this and add the insert query for this appender in the config file.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My log4j.properties looks like this:
# Attach appender A1 to root. Set root level to Level.DEBUG.
log4j.rootLogger=DEBUG, A1, A1X, DB
# A1 is set to be a FileAppender sending its output to file
# However, only warning messages and above will be printed
# in A1 because A1's threshold is set to Level.WARN.
# The fact that the root level is set to Prority.DEBUG only influences
# log requests made to the root logger. It has no influence on the
# *appenders* attached to root.
# Valid Log levels DEBUG, INFO, WARN, ERROR
log4j.appender.A1=org.apac he.log4j.R ollingFile Appender
log4j.appender.A1.Threshol d=WARN
# log4j.appender.A1.File=/us r/kasenna/ tomcat/log s/osf.log
log4j.appender.A1.File=/us r/kasenna/ LivingRoom /tomcat/lo gs/osf.log
# Set File Size and How many backup copies to keep
log4j.appender.A1.MaxFileS ize=5MB
log4j.appender.A1.MaxBacku pIndex=10
log4j.appender.A1.layout=o rg.apache. log4j.Patt ernLayout
log4j.appender.A1.layout.C onversionP attern=%d %p [%t] %c{2} (%M:%L) - %m%n
log4j.appender.A1X=org.apa che.log4j. CompositeR ollingAppe nder
log4j.appender.A1X.Rolling Style=3
log4j.appender.A1X.Thresho ld=WARN
log4j.appender.A1X.File=/u sr/kasenna /LivingRoo m/tomcat/l ogs/osfx.l og4j
log4j.appender.A1X.datePat tern='.'yy yy-MM-dd
log4j.appender.A1X.dateExp ireInterva l=5 Days
log4j.appender.A1X.MaxSize RollBackup s=2
log4j.appender.A1X.layout= org.apache .log4j.xml .XMLLayout
log4j.appender.DB=org.apac he.log4j.j dbcplus.JD BCAppender
log4j.appender.DB.Threshol d=WARN
log4j.appender.DB.dbclass= com.mysql. jdbc.jdbc2 .optional. MysqlConne ctionPoolD ataSource
log4j.appender.DB.url=jdbc :mysql://1 72.31.254. 103:3306/o sfdb
log4j.appender.DB.username =IPTVuser
log4j.appender.DB.password =1ptvus3r
log4j.appender.DB.layout=o rg.apache. log4j.Patt ernLayout
log4j.appender.DB.layout.C onversionP attern=%d %p [%t] %c{2} (%M:%L) - %m%n
log4j.appender.DB.sql=INSE RT INTO osfLog (id, prio, cat, thread, msg, layout_msg, throwable, ndc, mdc, mdc2, info, addon, created_by) VALUES (@INC@, '@PRIO@', '@CAT@', '@THREAD@', '@MSG@', '@LAYOUT:1@', '@THROWABLE@', '@NDC@', '@MDC:MyMDC@', '@MDC:MyMDC2@', '@TIMESTAMP@', '@LAYOUT@','log4j')
# Set the Audit Log Appender
log4j.category.com.kasenna .osf.core. AuditLog=I NFO, AL, ADB
#ALX
log4j.appender.AL=org.apac he.log4j.R ollingFile Appender
log4j.appender.AL.File=/us r/kasenna/ LivingRoom /osf/logs/ audit.log
log4j.appender.AL.MaxFileS ize=5MB
log4j.appender.AL.MaxBacku pIndex=10
log4j.appender.AL.layout=c om.kasenna .osf.core. AuditLogPa tternLayou t
log4j.appender.AL.layout.C onversionP attern="%d {ISO8601}" %m%n
#log4j.appender.ALX=org.ap ache.log4j .DailyRoll ingFileApp ender
#log4j.appender.ALX.File=/ usr/kasenn a/LivingRo om/osf/log s/audit.lo g4j
#log4j.appender.ALX.DatePa ttern=yyyy MMdd-HH'.l og4j'
#log4j.appender.ALX.layout =org.apach e.log4j.xm l.XMLLayou t
log4j.appender.ADB=org.apa che.log4j. jdbcplus.J DBCAppende r
log4j.appender.ADB.dbclass =com.mysql .jdbc.jdbc 2.optional .MysqlConn ectionPool DataSource
log4j.appender.ADB.url=jdb c:mysql:// 172.31.254 .103:3306/ osfdb
log4j.appender.ADB.usernam e=IPTVuser
log4j.appender.ADB.passwor d=1ptvus3r
log4j.appender.ADB.sql=INS ERT INTO auditLog (id, prio, cat, thread, msg, layout_msg, throwable, ndc, mdc, mdc2, info, addon,created_by) VALUES (@INC@, '@PRIO@', '@CAT@', '@THREAD@', '@MSG@', '@LAYOUT:1@', '@THROWABLE@', '@NDC@', '@MDC:MyMDC@', '@MDC:MyMDC2@', '@TIMESTAMP@', '@LAYOUT@','log4j')
log4j.appender.ADB.layout= com.kasenn a.osf.core .AuditLogP atternLayo ut
log4j.appender.ADB.layout. Conversion Pattern="% d{ISO8601} " %m%n
# Set the SNMP Log Appender
log4j.category.com.kasenna .osf.core. SNMPLog=IN FO, TRAP
#,SNMPDB
log4j.appender.TRAP=org.ap ache.log4j .RollingFi leAppender
log4j.appender.TRAP.File=/ usr/kasenn a/LivingRo om/osf/log s/SNMPTrap .log
log4j.appender.TRAP.MaxFil eSize=5MB
log4j.appender.TRAP.MaxBac kupIndex=1 0
log4j.appender.TRAP.layout =org.apach e.log4j.Pa tternLayou t
log4j.appender.TRAP.layout .Conversio nPattern=% d %p [%t] %c{2} (%M:%L) - %m%n
# Control logging for Jakarta packages classes
log4j.logger.org.apache.co mmons=ERRO R
log4j.logger.org.apache.st ruts=WARN
# Control logging for Tomcat packages classes
# Comment the following line to activate looging for catalina classes
log4j.logger.org.apache.ca talina=ERR OR
#log4j.appender.SNMPDB=org .apache.lo g4j.jdbcpl us.JDBCApp ender
#log4j.appender.SNMPDB.dbc lass=com.m ysql.jdbc. jdbc2.opti onal.Mysql Connection PoolDataSo urce
#log4j.appender.SNMPDB.url =jdbc:mysq l://localh ost:3306/o sfdb
#log4j.appender.SNMPDB.use rname=posu ser
#log4j.appender.SNMPDB.pas sword=pos- user
#log4j.appender.SNMPDB.sql =INSERT INTO snmpLog (id, prio, cat, thread, msg, layout_msg, throwable, ndc, mdc, mdc2, info, addon,created_by) VALUES (@INC@, '@PRIO@', '@CAT@', '@THREAD@', '@MSG@', '@LAYOUT:1@', '@THROWABLE@', '@NDC@', '@MDC:MyMDC@', '@MDC:MyMDC2@', '@TIMESTAMP@', '@LAYOUT@','log4j')
#log4j.appender.SNMPDB.lay out=org.ap ache.log4j .PatternLa yout
#log4j.appender.SNMPDB.lay out.Conver sionPatter n=%d %p [%t] %c{2} (%M:%L) - %m%n
My Database table is:
mysql> desc auditLog;
+------------+------------ --+------+ -----+---- ---------- -----+---- ---+
| Field | Type | Null | Key | Default | Extra |
+------------+------------ --+------+ -----+---- ---------- -----+---- ---+
| id | varchar(255) | YES | | NULL | |
| prio | varchar(10) | YES | | NULL | |
| cat | varchar(255) | YES | | NULL | |
| thread | varchar(64) | YES | | NULL | |
| msg | varchar(255) | YES | | NULL | |
| layout_msg | varchar(255) | YES | | NULL | |
| throwable | varchar(255) | YES | | NULL | |
| ndc | varchar(10) | YES | | NULL | |
| mdc | varchar(10) | YES | | NULL | |
| mdc2 | varchar(10) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| addon | varchar(255) | YES | | NULL | |
| created_by | varchar(64) | YES | | NULL | |
| time | timestamp | YES | | CURRENT_TIMESTAMP | |
+------------+------------ --+------+ -----+---- ---------- -----+---- ---+
14 rows in set (0.00 sec)
Its not working. When i do some activity it gets logged into audit.log file but it does not get logged in the Database. Please look at the configuration whether it is correct.
Urgent response awaited.
Thanks in advance.
# Attach appender A1 to root. Set root level to Level.DEBUG.
log4j.rootLogger=DEBUG, A1, A1X, DB
# A1 is set to be a FileAppender sending its output to file
# However, only warning messages and above will be printed
# in A1 because A1's threshold is set to Level.WARN.
# The fact that the root level is set to Prority.DEBUG only influences
# log requests made to the root logger. It has no influence on the
# *appenders* attached to root.
# Valid Log levels DEBUG, INFO, WARN, ERROR
log4j.appender.A1=org.apac
log4j.appender.A1.Threshol
# log4j.appender.A1.File=/us
log4j.appender.A1.File=/us
# Set File Size and How many backup copies to keep
log4j.appender.A1.MaxFileS
log4j.appender.A1.MaxBacku
log4j.appender.A1.layout=o
log4j.appender.A1.layout.C
log4j.appender.A1X=org.apa
log4j.appender.A1X.Rolling
log4j.appender.A1X.Thresho
log4j.appender.A1X.File=/u
log4j.appender.A1X.datePat
log4j.appender.A1X.dateExp
log4j.appender.A1X.MaxSize
log4j.appender.A1X.layout=
log4j.appender.DB=org.apac
log4j.appender.DB.Threshol
log4j.appender.DB.dbclass=
log4j.appender.DB.url=jdbc
log4j.appender.DB.username
log4j.appender.DB.password
log4j.appender.DB.layout=o
log4j.appender.DB.layout.C
log4j.appender.DB.sql=INSE
# Set the Audit Log Appender
log4j.category.com.kasenna
#ALX
log4j.appender.AL=org.apac
log4j.appender.AL.File=/us
log4j.appender.AL.MaxFileS
log4j.appender.AL.MaxBacku
log4j.appender.AL.layout=c
log4j.appender.AL.layout.C
#log4j.appender.ALX=org.ap
#log4j.appender.ALX.File=/
#log4j.appender.ALX.DatePa
#log4j.appender.ALX.layout
log4j.appender.ADB=org.apa
log4j.appender.ADB.dbclass
log4j.appender.ADB.url=jdb
log4j.appender.ADB.usernam
log4j.appender.ADB.passwor
log4j.appender.ADB.sql=INS
log4j.appender.ADB.layout=
log4j.appender.ADB.layout.
# Set the SNMP Log Appender
log4j.category.com.kasenna
#,SNMPDB
log4j.appender.TRAP=org.ap
log4j.appender.TRAP.File=/
log4j.appender.TRAP.MaxFil
log4j.appender.TRAP.MaxBac
log4j.appender.TRAP.layout
log4j.appender.TRAP.layout
# Control logging for Jakarta packages classes
log4j.logger.org.apache.co
log4j.logger.org.apache.st
# Control logging for Tomcat packages classes
# Comment the following line to activate looging for catalina classes
log4j.logger.org.apache.ca
#log4j.appender.SNMPDB=org
#log4j.appender.SNMPDB.dbc
#log4j.appender.SNMPDB.url
#log4j.appender.SNMPDB.use
#log4j.appender.SNMPDB.pas
#log4j.appender.SNMPDB.sql
#log4j.appender.SNMPDB.lay
#log4j.appender.SNMPDB.lay
My Database table is:
mysql> desc auditLog;
+------------+------------
| Field | Type | Null | Key | Default | Extra |
+------------+------------
| id | varchar(255) | YES | | NULL | |
| prio | varchar(10) | YES | | NULL | |
| cat | varchar(255) | YES | | NULL | |
| thread | varchar(64) | YES | | NULL | |
| msg | varchar(255) | YES | | NULL | |
| layout_msg | varchar(255) | YES | | NULL | |
| throwable | varchar(255) | YES | | NULL | |
| ndc | varchar(10) | YES | | NULL | |
| mdc | varchar(10) | YES | | NULL | |
| mdc2 | varchar(10) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| addon | varchar(255) | YES | | NULL | |
| created_by | varchar(64) | YES | | NULL | |
| time | timestamp | YES | | CURRENT_TIMESTAMP | |
+------------+------------
14 rows in set (0.00 sec)
Its not working. When i do some activity it gets logged into audit.log file but it does not get logged in the Database. Please look at the configuration whether it is correct.
Urgent response awaited.
Thanks in advance.
Either the URL could be wrong. Or the driver class might not be in the classpath. Or it could be that you are logging only debug/ info messages - you have set the JDBC logger to log only warn and more severe messages.
ASKER
The URL is correct. The driver is in the class path as well. I changed the WARN to DEBUG. Please Advice what to do? Its still not working
Try to use
log4j.appender.db.Driver=" full qualified driver classname" instead of
log4j.appender.DB.dbclass= com.mysql. jdbc.jdbc2 .optional. MysqlConne ctionPoolD ataSource
log4j.appender.db.Driver="
log4j.appender.DB.dbclass=
ASKER
I added this line instead of the line mentioned above:
log4j.appender.db.Driver=c om.mysql.j dbc.Driver
It still does not work. Please anyone suggest a way out.
Thanks
log4j.appender.db.Driver=c
It still does not work. Please anyone suggest a way out.
Thanks
try use:
log4j.appender.DB=org.apac he.log4j.j dbc.JDBCAp pender
instead of
log4j.appender.DB=org.apac he.log4j.j dbcplus.JD BCAppender
log4j.appender.DB=org.apac
instead of
log4j.appender.DB=org.apac
ASKER
That does not work either.
This is how the changes look like:
log4j.appender.DB=org.apac he.log4j.j dbc.JDBCAp pender
log4j.appender.DB.Threshol d=DEBUG
#log4j.appender.DB.dbclass =com.mysql .jdbc.jdbc 2.optional .MysqlConn ectionPool DataSource
log4j.appender.DB.dbclass= com.mysql. jdbc.Drive r
log4j.appender.DB.url=jdbc :mysql://1 72.31.254. 103:3306/o sfdb
log4j.appender.DB.username =IPTVuser
log4j.appender.DB.password =1ptvus3r
log4j.appender.DB.layout=o rg.apache. log4j.Patt ernLayout
log4j.appender.DB.layout.C onversionP attern=%d %p [%t] %c{2} (%M:%L) - %m%n
log4j.appender.DB.sql=INSE RT INTO osfLog (id, prio, cat, thread, msg, layout_msg, throwable, ndc, mdc, mdc2, info, addon, created_by) VALUES (@INC@, '@PRIO@', '@CAT@', '@THREAD@', '@MSG@', '@LAYOUT:1@', '@THROWABLE@', '@NDC@', '@MDC:MyMDC@', '@MDC:MyMDC2@', '@TIMESTAMP@', '@LAYOUT@','log4j')
I tried DB.Driver as well before changing to DB.dbclass.
Please look into it.
Thanks
This is how the changes look like:
log4j.appender.DB=org.apac
log4j.appender.DB.Threshol
#log4j.appender.DB.dbclass
log4j.appender.DB.dbclass=
log4j.appender.DB.url=jdbc
log4j.appender.DB.username
log4j.appender.DB.password
log4j.appender.DB.layout=o
log4j.appender.DB.layout.C
log4j.appender.DB.sql=INSE
I tried DB.Driver as well before changing to DB.dbclass.
Please look into it.
Thanks
Strange! I have the same configuration (but with oracle) and they are working.
Why you don't try a simple table (say INSERT INTO Test (LoggingDate) VALUES ('%d'))
and without MDC and NDC.
Why you don't try a simple table (say INSERT INTO Test (LoggingDate) VALUES ('%d'))
and without MDC and NDC.
Try create a small table and try to insert some values and see if it works with the same configuration.
ASKER
Please provide a sample. It will help me a lot.
Thanks, I appreciate it. I have been stuck in this problem for 4 days now.
Thanks, I appreciate it. I have been stuck in this problem for 4 days now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please suggest