Solved

Log4j Database related

Posted on 2006-07-21
23
1,296 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:geek4u
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +3
23 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 150 total points
ID: 17154370
0
 
LVL 23

Assisted Solution

by:Ajay-Singh
Ajay-Singh earned 125 total points
ID: 17154377
try using JDBC appender. You don't have to change the code.

http://logging.apache.org/log4j/docs/api/org/apache/log4j/jdbc/JDBCAppender.html
0
 

Author Comment

by:geek4u
ID: 17154393
I dont want to change the source code but i need to externally send the log to Databases. The above links dont explain how to do it.

Please suggest
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 86

Expert Comment

by:CEHJ
ID: 17154417
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17154438
0
 

Author Comment

by:geek4u
ID: 17154575
please can you be more clear!! Just explain the steps that need to be done.

Thanks,
0
 
LVL 30

Assisted Solution

by:Mayank S
Mayank S earned 100 total points
ID: 17154643
>> I dont want to change the source code but i need to externally send the log to Databases

You don't have to. You can change the configuration from the configuration file (do you have a properties file or an XML file?). You can configure new appenders simply from the configuration-file (like a JDBC appender) and make it subscribe to certain log-types (like say Info and above). What does your config XML/ properties file look like?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17154649
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
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17155157
You have add an appender to log category for this and add the insert query for this appender in the config file.
0
 
LVL 2

Assisted Solution

by:RoyalNepal
RoyalNepal earned 75 total points
ID: 17155666
Hi,
 
 The best way to do is..use JDBCAppender so that you dont have to change the configuration file..

there are lots of examples avialable online..

Thanks,
Roy.

0
 

Author Comment

by:geek4u
ID: 17156885
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.apache.log4j.RollingFileAppender
log4j.appender.A1.Threshold=WARN
# log4j.appender.A1.File=/usr/kasenna/tomcat/logs/osf.log
log4j.appender.A1.File=/usr/kasenna/LivingRoom/tomcat/logs/osf.log

# Set File Size and How many backup copies to keep
log4j.appender.A1.MaxFileSize=5MB
log4j.appender.A1.MaxBackupIndex=10
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%d %p [%t] %c{2} (%M:%L) - %m%n

log4j.appender.A1X=org.apache.log4j.CompositeRollingAppender
log4j.appender.A1X.RollingStyle=3
log4j.appender.A1X.Threshold=WARN
log4j.appender.A1X.File=/usr/kasenna/LivingRoom/tomcat/logs/osfx.log4j
log4j.appender.A1X.datePattern='.'yyyy-MM-dd
log4j.appender.A1X.dateExpireInterval=5 Days
log4j.appender.A1X.MaxSizeRollBackups=2
log4j.appender.A1X.layout=org.apache.log4j.xml.XMLLayout

log4j.appender.DB=org.apache.log4j.jdbcplus.JDBCAppender
log4j.appender.DB.Threshold=WARN
log4j.appender.DB.dbclass=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
log4j.appender.DB.url=jdbc:mysql://172.31.254.103:3306/osfdb
log4j.appender.DB.username=IPTVuser
log4j.appender.DB.password=1ptvus3r
log4j.appender.DB.layout=org.apache.log4j.PatternLayout
log4j.appender.DB.layout.ConversionPattern=%d %p [%t] %c{2} (%M:%L) - %m%n
log4j.appender.DB.sql=INSERT 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=INFO, AL, ADB
#ALX
log4j.appender.AL=org.apache.log4j.RollingFileAppender
log4j.appender.AL.File=/usr/kasenna/LivingRoom/osf/logs/audit.log

log4j.appender.AL.MaxFileSize=5MB
log4j.appender.AL.MaxBackupIndex=10
log4j.appender.AL.layout=com.kasenna.osf.core.AuditLogPatternLayout
log4j.appender.AL.layout.ConversionPattern="%d{ISO8601}" %m%n

#log4j.appender.ALX=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.ALX.File=/usr/kasenna/LivingRoom/osf/logs/audit.log4j
#log4j.appender.ALX.DatePattern=yyyyMMdd-HH'.log4j'
#log4j.appender.ALX.layout=org.apache.log4j.xml.XMLLayout


log4j.appender.ADB=org.apache.log4j.jdbcplus.JDBCAppender
log4j.appender.ADB.dbclass=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
log4j.appender.ADB.url=jdbc:mysql://172.31.254.103:3306/osfdb
log4j.appender.ADB.username=IPTVuser
log4j.appender.ADB.password=1ptvus3r
log4j.appender.ADB.sql=INSERT 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.kasenna.osf.core.AuditLogPatternLayout
log4j.appender.ADB.layout.ConversionPattern="%d{ISO8601}" %m%n

# Set the SNMP Log Appender
log4j.category.com.kasenna.osf.core.SNMPLog=INFO, TRAP
#,SNMPDB

log4j.appender.TRAP=org.apache.log4j.RollingFileAppender
log4j.appender.TRAP.File=/usr/kasenna/LivingRoom/osf/logs/SNMPTrap.log

log4j.appender.TRAP.MaxFileSize=5MB
log4j.appender.TRAP.MaxBackupIndex=10
log4j.appender.TRAP.layout=org.apache.log4j.PatternLayout
log4j.appender.TRAP.layout.ConversionPattern=%d %p [%t] %c{2} (%M:%L) - %m%n


# Control logging for Jakarta packages classes
log4j.logger.org.apache.commons=ERROR
log4j.logger.org.apache.struts=WARN

# Control logging for Tomcat packages classes
# Comment the following line to activate looging for catalina classes
log4j.logger.org.apache.catalina=ERROR


#log4j.appender.SNMPDB=org.apache.log4j.jdbcplus.JDBCAppender
#log4j.appender.SNMPDB.dbclass=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
#log4j.appender.SNMPDB.url=jdbc:mysql://localhost:3306/osfdb
#log4j.appender.SNMPDB.username=posuser
#log4j.appender.SNMPDB.password=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.layout=org.apache.log4j.PatternLayout
#log4j.appender.SNMPDB.layout.ConversionPattern=%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.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 17159965
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.
0
 

Author Comment

by:geek4u
ID: 17168031
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
0
 

Expert Comment

by:firas981
ID: 17170117
Try to use
log4j.appender.db.Driver="full qualified driver classname" instead of
log4j.appender.DB.dbclass=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
0
 

Author Comment

by:geek4u
ID: 17170748
I added this line instead of the line mentioned above:

log4j.appender.db.Driver=com.mysql.jdbc.Driver

It still does not work. Please anyone suggest a way out.

Thanks
0
 

Expert Comment

by:firas981
ID: 17170899
try use:
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
instead of
log4j.appender.DB=org.apache.log4j.jdbcplus.JDBCAppender
0
 

Author Comment

by:geek4u
ID: 17170969
That does not work either.

This is how the changes look like:

log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.Threshold=DEBUG
#log4j.appender.DB.dbclass=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
log4j.appender.DB.dbclass=com.mysql.jdbc.Driver
log4j.appender.DB.url=jdbc:mysql://172.31.254.103:3306/osfdb
log4j.appender.DB.username=IPTVuser
log4j.appender.DB.password=1ptvus3r
log4j.appender.DB.layout=org.apache.log4j.PatternLayout
log4j.appender.DB.layout.ConversionPattern=%d %p [%t] %c{2} (%M:%L) - %m%n
log4j.appender.DB.sql=INSERT 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
0
 

Expert Comment

by:firas981
ID: 17171211
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.

0
 
LVL 2

Expert Comment

by:RoyalNepal
ID: 17171257
Try create a small table and try to insert some values and see if it works with the same configuration.
0
 

Author Comment

by:geek4u
ID: 17171296
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.
0
 

Assisted Solution

by:firas981
firas981 earned 50 total points
ID: 17171434
If you have Oracle you may try this (they working for me):

log4j.appender.db=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.db.Driver=oracle.jdbc.driver.OracleDriver
log4j.appender.db.URL=jdbc:oracle:thin:@localhost:1521:your_db_name
log4j.appender.db.user=your_user
log4j.appender.db.password=your_pass
log4j.appender.db.sql=INSERT INTO LOG VALUES ('%d %-5p -%F %m')
log4j.appender.db.layout=org.apache.log4j.PatternLayout
log4j.appender.db.layout.ConversionPattern=%d %-5p - %m
log4j.appender.db.Threshold=debug

where log table has just a msg field.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question