I didn't notice that you've specified the wrong receiver class. There is a
DBReceiver, which supports a pre-defined schema (to be used with the log4j 1.3
DBAppender). You're using CustomSQLDBReceiver, so modify the receiver class to
org.apache.log4j.db.CustomSQLDBReceiver. I've made that change to the XML
below.
Once you get the concat syntax fixed (results are returned in toad) and you
have the properties format displaying correctly {{key1,value1,key2,value2}},
you should be fine.
As for reserved keywords, you should be fine. Again, if the results come back
in toad, Chainsaw can process them. You may need to wrap alias names in single
quotes or brackets, if you can't get the SQL to return with the column aliases
that are required by Chainsaw.
Are you using the Web Start version of Chainsaw? You need to add your jdbc
drivers and the log4j-1.3-db jar to the user.dir/.chainsaw/plugins directory,
and you need to go to the application-wide preferences and check the 'ok to
remove security manager box'. You can find more info at the Chainsaw page:
http://logging.apache.org/log4j/docs/chainsaw.html - click on the 'important
distribution notes' link.
The application-wide preferences screen is also where you specify the URL to
your config file, so that it loads on startup (provide the URL in the
'automatic configuration URL' box).
When I load your config file, I see refreshMillis set to 3000.
Connectionsource will be blank - it's not a simple name/value pair like 'user'
or 'url'. What do you see for refreshMillis?
Here's a version with the receiver class corrected, but still needs to have
valid SQL provided.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration >
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";>
<plugin name="MyDBReceiver" class="org.apache.log4j.db.CustomSQLDBReceiver">
<connectionSource
class="org.apache.log4j.db.DriverManagerConnectionSource">
<param name="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<param name="password" value="blah"/>
<param name="user" value="blah"/>
<param name="url" value="jdbc:oracle:thin:@blah:1521:brewutcp"/>
</connectionSource>
<param name="IDField" value="log_id"/>
<param name="refreshMillis" value="3000"/>
<param name="sql" value="SELECT line as LINE, application as
APPLICATION, log_level as LEVEL, message as MESSAGE, hostname as HOSTNAME,
src_file as FILE, stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS
MDC, '' AS CLASS, '' AS METHOD,
CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, ''
AS EXCEPTION, '' AS LOGGER from bu_log"/>
</plugin>
<root>
<level value="debug"/>
</root>
</log4j:configuration>
Scott
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Fri 9/8/2006 7:21 AM
To: Log4J Users List
Subject: RE: chainsaw with DBReceiver
Scott,
Thanks so much for taking time to help me. I really appreciate it.
I made the modifications you suggested, but still no luck. Forgive me
for asked what seems like a silly question; however ....
level and file are key words in oracle, and putting the below sql in toad
and executing it results in errors. In addition, it doesn't like the
syntax for the concat.
When I launch chainsaw, I tell it I want to select my own config file and
point it to the file where the information below is stored. The name of
the receiver then shows up in chainsaw, but nothing happens and I get no
errors. When I look at the properties through chainsaw, the refreshMilis
is not the same as what is shown in the information below and the
connectionSource is blank. Any thoughts?
Also, is there a way in chainsaw to have it default to a specific config
file on start up?
Below is my modified config file.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration >
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";>
<plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver">
<connectionSource
class="org.apache.log4j.db.DriverManagerConnectionSource">
<param name="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<param name="password" value="blah"/>
<param name="user" value="blah"/>
<param name="url" value="jdbc:oracle:thin:@blah:1521:brewutcp"/>
</connectionSource>
<param name="IDField" value="log_id"/>
<param name="refreshMillis" value="3000"/>
<param name="sql" value="SELECT line as LINE, application as
APPLICATION, log_level as LEVEL, message as MESSAGE, hostname as HOSTNAME,
src_file as FILE, stack as THREAD, log_time as TIMESTAMP, '' AS NDC, '' AS
MDC, '' AS CLASS, '' AS METHOD,
CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, ''
AS EXCEPTION, '' AS LOGGER from bu_log"/>
</plugin>
<root>
<level value="debug"/>
</root>
</log4j:configuration>
"Scott Deboy" <[EMAIL PROTECTED]>
09/08/2006 12:52 AM
Please respond to "Log4J Users List"
To: "Log4J Users List" <[email protected]>
cc:
Subject: RE: chainsaw with DBReceiver
I think this xml (or this with a few tweaks) may work for you..Note, I
don't know if you have a column that would make sense as the 'logger'
column, so in this example I'm using the string 'logger'. I've added some
explanation below the xml.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration >
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";>
<plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver">
<connectionSource
class="org.apache.log4j.db.DriverManagerConnectionSource">
<param name="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<param name="password" value="blah"/>
<param name="user" value="blah"/>
<param name="url" value="jdbc:oracle:thin:@blah:1521:blah"/>
</connectionSource>
<param name="IDField" value="log_id"/>
<param name="refreshMillis" value="3000"/>
<param name="sql" value="SELECT 'logger' as LOGGER, log_time as
TIMESTAMP, log_level as LEVEL, stack as THREAD, message as MESSAGE, '' as
NDC, '' as MDC, '' as CLASS, '' as METHOD, src_file as FILE, line as LINE,
CONCAT('{{USERID'||USER_ID||',log4jid,'||log_id||'}}') AS PROPERTIES, ''
AS
EXCEPTION from bu_log"/>
</plugin>
<root>
<level value="debug"/>
</root>
</log4j:configuration>
All columns are required - you're missing the LOGGER column. Also, your
'properties' column is missing the double-braces at each end as well as
the comma separation between each name and value. Also, leave out the
order by - it breaks the ability to use IDField which is needed to
retrieve new events every x milliseconds (the receiver is blindly adding a
where clause in order to avoid retrieving the same events twice).
You also need an IDField param in the receiver config if you want the
query to be ran more than once. Does your table use log_id as the (int)
unique identifier? If so, use log_id as your IDField param in the xml and
leave it out of the SQL.
Your resulting 'properties' column should look like this - note the braces
and commas: {{name1,value1,name2,value2}}
Run your SQL in a query analyzer and make sure you have each required
column, and that the properties column looks correct.
It takes a bit of work to get set up, but it works!
Good luck!
Scott
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thu 9/7/2006 2:26 PM
To: [email protected]
Subject: chainsaw with DBReceiver
Hi,
I am trying to using chainsaw to monitor logs that we insert into our
database (oracle db). I created the file below and launched chainsaw. I
manually pointed chainsaw to this config file. The receiver shows up;
however, nothing happens. I get no errors and I get no tab for the
receiver. I did notice that the refreshMillis is different in the
properties in the chainsaw gui than in the file below. In addition, the
connectionSource is blank.
Can you give me an idea of what I am doing wrong?
In addition, the sql below has issues in oracle because level is a key
word in oracle. Does DBReceiver require the field be aliased as LEVEL? If
so, how would I get around the key word issue.
Also, is there a way that upon start up, this receiver would get loaded
automatically?
Thanks.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration >
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/";>
<plugin name="MyDBReceiver" class="org.apache.log4j.db.DBReceiver">
<connectionSource
class="org.apache.log4j.db.DriverManagerConnectionSource">
<param name="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<param name="password" value="blah"/>
<param name="user" value="blah"/>
<param name="url" value="jdbc:oracle:thin:@blah:1521:blah"/>
</connectionSource>
<param name="refreshMillis" value="3000"/>
<param name="sql" value="SELECT log_id as ID, line as LINE,
application as APPLICATION, log_level as LEVEL, message as MESSAGE,
hostname as HOSTNAME, src_file as FILE, stack as THREAD, log_time as
TIMESTAMP, '' AS NDC, '' AS MDC, '' AS CLASS, '' AS METHOD,
CONCAT(USER_ID||' '||CATEGORY||' '||log4jid) AS PROPERTIES, '' AS
EXCEPTION from bu_log order by TIMESTAMP desc, APPLICATION"/>
</plugin>
<root>
<level value="debug"/>
</root>
</log4j:configuration>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
