hello, we are currently setting up a new syslog server and switching from a sun fire v880 with syslog-ng and plain files to a new linux based (centos 5.3) syslog server with oracle as logging backend. we do not want to use pipes to write to the database, so we compiled libdbi-dbd-oracle with the oracle instantclient 10.2.0.4-1. syslog-ng is starting up fine (not complaining about the oracle backend), but we do have some problem with the inserts to the database. our trial syslog-ng configuration: destination d_sql_fwint { sql(type(oracle) username("xxx") password("xxx") database("dashb") table("SYSLOG_FWINT") columns("MSG_RCV_TIME varchar2(16)", "HOSTNAME varchar2(256)", "MESSAGE varchar2(2048)") values("$R_DATE", "$HOST", "$MSGONLY") }; when running in debug mode we get the following output: Running SQL query; query='INSERT INTO SYSLOG_FWINT (MSG_RCV_TIME, HOSTNAME, MESSAGE) VALUES (\'Aug 11 21:24:14\', \'gw20\', \'Built outbound TCP connection 144547326429398234 for LAN230:10.20.30.17/43825 (10.20.30.17/43825) to outside:10.10.10.102/80 (10.10.10.102/80)\')' this cant work sind oracle is not accepting \ as escape char, and in this case we do not need an escape at all. the sql syntax is right without the \. since templates are not working when using the sql driver, does anyone has an idea how to fix that? are we missing something? oracle setup is right, tnsnames or works and we can connect to the database with sqlplus. the syslog-ng init script as the environment variables set (ORACLE_HOME, LD_LIBRARY_PATH) thanks for any help -andy
On Tue, 2009-08-11 at 22:33 +0200, Andreas Sartori wrote:
hello,
we are currently setting up a new syslog server and switching from a sun fire v880 with syslog-ng and plain files to a new linux based (centos 5.3) syslog server with oracle as logging backend.
we do not want to use pipes to write to the database, so we compiled libdbi-dbd-oracle with the oracle instantclient 10.2.0.4-1.
syslog-ng is starting up fine (not complaining about the oracle backend), but we do have some problem with the inserts to the database.
our trial syslog-ng configuration:
destination d_sql_fwint { sql(type(oracle) username("xxx") password("xxx") database("dashb") table("SYSLOG_FWINT") columns("MSG_RCV_TIME varchar2(16)", "HOSTNAME varchar2(256)", "MESSAGE varchar2(2048)") values("$R_DATE", "$HOST", "$MSGONLY") };
when running in debug mode we get the following output: Running SQL query; query='INSERT INTO SYSLOG_FWINT (MSG_RCV_TIME, HOSTNAME, MESSAGE) VALUES (\'Aug 11 21:24:14\', \'gw20\', \'Built outbound TCP connection 144547326429398234 for LAN230:10.20.30.17/43825 (10.20.30.17/43825) to outside:10.10.10.102/80 (10.10.10.102/80)\')'
the backslash only comes to the console log (to escape the starting/ending quotes of the query='' tag of the log message) and is not included in the query as posted to Oracle. Hmm.. hmm, now as I think of it I had to patch the libdbi Oracle driver to make that work to auto-commit INSERTs, because by default oracle does not commit automatically. I've submitted those patches to upstream libdbi-drivers and seem to be included in the CVS repository, although I'm not sure if there was a release since then. Which libdbi-drivers version are you using? Can you try using their CVS version?
this cant work sind oracle is not accepting \ as escape char, and in this case we do not need an escape at all. the sql syntax is right without the \.
since templates are not working when using the sql driver, does anyone has an idea how to fix that? are we missing something?
oracle setup is right, tnsnames or works and we can connect to the database with sqlplus. the syslog-ng init script as the environment variables set (ORACLE_HOME, LD_LIBRARY_PATH)
thanks for any help
-andy
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- Bazsi
hi balazs, thanks for your response. i recompiled the centos 5.3 libdbi-drivers package with oracle support. version is libdbi-drivers-0.8.1a-1.2.2 i will try the cvs version. -andy
the backslash only comes to the console log (to escape the starting/ending quotes of the query='' tag of the log message) and is not included in the query as posted to Oracle.
Hmm.. hmm, now as I think of it I had to patch the libdbi Oracle driver to make that work to auto-commit INSERTs, because by default oracle does not commit automatically.
I've submitted those patches to upstream libdbi-drivers and seem to be included in the CVS repository, although I'm not sure if there was a release since then.
Which libdbi-drivers version are you using? Can you try using their CVS version?
this cant work sind oracle is not accepting \ as escape char, and in this case we do not need an escape at all. the sql syntax is right without the \.
since templates are not working when using the sql driver, does anyone has an idea how to fix that? are we missing something?
oracle setup is right, tnsnames or works and we can connect to the database with sqlplus. the syslog-ng init script as the environment variables set (ORACLE_HOME, LD_LIBRARY_PATH)
thanks for any help
-andy
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
problem is solved. at the end of the post there are the spec files linked. if you like you can put them on your website. ----------------- i didnt want to use the cvs version from libdbi-drivers, but the latest stable one. (0.8.3-1). 2 patches are missing in the 0.8.3-1 release: a patch so the oracle stuff compiles and your commit patch. (they are included in the src.rpm files and on my website). i changed the spec files from libdbi and libdbi-drivers so everything compiles smooth under rhel 5.3 and centos 5.3. ================= compile instructions for syslogng + libdbi + oracle on centos/rhel 5.3 x86_64 - download latest (0.8.3) libdbi + libdbi-drivers from sourceforge - download instantclient (10.2.0.4) from oracle website (http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...) - download syslog-ng_3.0.4.tar.gz (http://www.balabit.com/) 1) install oracleinstantclient (basic+sdk). 2) compile libdbi 3) compile libdbi-drivers (./configure --with-dbi-incdir=/usr/include/dbi --with-dbi-libdir=/usr/lib64 --libdir=/usr/lib64 ... oracle, mysql,...) patch libdbi with "compile patch" and "oracle commit patch" (in this order) 4) compile syslog-ng with --enable-sql (in spec file you can enable the --enable-debug for more debug information) 5) put the following into /etc/init.d/syslog-ng (somewhere at the top) export ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client64 export PATH=${PATH}:${ORACLE_HOME}/bin export LD_LIBRARY_PATH=${ORACLE_HOME}/lib 6) you need a tnsnames.ora file (please read the syslog-ng documentation) source rpm installation method: 1) rpmbuild --rebuild libdbi-0.8.3-1.src.rpm 2) rpm -ihv http://www.users.fh-salzburg.ac.at/~asartori/aconet/ /usr/src/redhat/RPMS/x86_64/libdbi-devel-0.8.3-1.x86_64.rpm 3) rpmbuild --rebuild libdbi-drivers-0.8.3-1.src.rpm 4) rpm -ihv libdbi-drivers-0.8.3-1.x86_64.rpm libdbi-dbd-oracle-0.8.3-1.x86_64.rpm 5) rpmbuild --rebuild syslog-ng-3.0.4-1.src.rpm 6) rpm -ihv syslog-ng-3.0.4-1_fhs3.x86_64.rpm 7) change your init script and add the environment variables thanks to balazs for giving me the final hint and for the "commit patch". src.rpms, patches, and rpms are at http://www.users.fh-salzburg.ac.at/~asartori/aconet/syslog-ng/ -- ___________________________________________ FACHHOCHSCHULE SALZBURG GmbH Salzburg University of Applied Sciences Andreas Sartori Systems Engineer IS - Information Services Urstein Süd 1 | 5412 Puch/Salzburg | Austria fon: +43 (0)50-2211-1655 | fax: -1699 web: www.fh-salzburg.ac.at Gerichtsstand Salzburg | FN166054y WELCOME TO YOUR FUTURE! ___________________________________________
Hi, I'd prefer that you host these files, but can I reference them in a blog post? On Wed, 2009-08-12 at 13:35 +0200, Andreas Sartori wrote:
problem is solved. at the end of the post there are the spec files linked. if you like you can put them on your website. ----------------- i didnt want to use the cvs version from libdbi-drivers, but the latest stable one. (0.8.3-1). 2 patches are missing in the 0.8.3-1 release: a patch so the oracle stuff compiles and your commit patch. (they are included in the src.rpm files and on my website). i changed the spec files from libdbi and libdbi-drivers so everything compiles smooth under rhel 5.3 and centos 5.3.
================= compile instructions for syslogng + libdbi + oracle on centos/rhel 5.3 x86_64
- download latest (0.8.3) libdbi + libdbi-drivers from sourceforge - download instantclient (10.2.0.4) from oracle website (http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...) - download syslog-ng_3.0.4.tar.gz (http://www.balabit.com/)
1) install oracleinstantclient (basic+sdk). 2) compile libdbi 3) compile libdbi-drivers (./configure --with-dbi-incdir=/usr/include/dbi --with-dbi-libdir=/usr/lib64 --libdir=/usr/lib64 ... oracle, mysql,...) patch libdbi with "compile patch" and "oracle commit patch" (in this order) 4) compile syslog-ng with --enable-sql (in spec file you can enable the --enable-debug for more debug information) 5) put the following into /etc/init.d/syslog-ng (somewhere at the top) export ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client64 export PATH=${PATH}:${ORACLE_HOME}/bin export LD_LIBRARY_PATH=${ORACLE_HOME}/lib 6) you need a tnsnames.ora file (please read the syslog-ng documentation)
source rpm installation method: 1) rpmbuild --rebuild libdbi-0.8.3-1.src.rpm 2) rpm -ihv http://www.users.fh-salzburg.ac.at/~asartori/aconet/ /usr/src/redhat/RPMS/x86_64/libdbi-devel-0.8.3-1.x86_64.rpm 3) rpmbuild --rebuild libdbi-drivers-0.8.3-1.src.rpm 4) rpm -ihv libdbi-drivers-0.8.3-1.x86_64.rpm libdbi-dbd-oracle-0.8.3-1.x86_64.rpm 5) rpmbuild --rebuild syslog-ng-3.0.4-1.src.rpm 6) rpm -ihv syslog-ng-3.0.4-1_fhs3.x86_64.rpm 7) change your init script and add the environment variables
thanks to balazs for giving me the final hint and for the "commit patch".
src.rpms, patches, and rpms are at http://www.users.fh-salzburg.ac.at/~asartori/aconet/syslog-ng/
-- Bazsi
hi balazs, thats fine with me and thanks for the reference :) -andy On 08/12/2009 02:27 PM, Balazs Scheidler wrote:
Hi,
I'd prefer that you host these files, but can I reference them in a blog post?
On Wed, 2009-08-12 at 13:35 +0200, Andreas Sartori wrote:
problem is solved. at the end of the post there are the spec files linked. if you like you can put them on your website. ----------------- i didnt want to use the cvs version from libdbi-drivers, but the latest stable one. (0.8.3-1). 2 patches are missing in the 0.8.3-1 release: a patch so the oracle stuff compiles and your commit patch. (they are included in the src.rpm files and on my website). i changed the spec files from libdbi and libdbi-drivers so everything compiles smooth under rhel 5.3 and centos 5.3.
================= compile instructions for syslogng + libdbi + oracle on centos/rhel 5.3 x86_64
- download latest (0.8.3) libdbi + libdbi-drivers from sourceforge - download instantclient (10.2.0.4) from oracle website (http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linu...) - download syslog-ng_3.0.4.tar.gz (http://www.balabit.com/)
1) install oracleinstantclient (basic+sdk). 2) compile libdbi 3) compile libdbi-drivers (./configure --with-dbi-incdir=/usr/include/dbi --with-dbi-libdir=/usr/lib64 --libdir=/usr/lib64 ... oracle, mysql,...) patch libdbi with "compile patch" and "oracle commit patch" (in this order) 4) compile syslog-ng with --enable-sql (in spec file you can enable the --enable-debug for more debug information) 5) put the following into /etc/init.d/syslog-ng (somewhere at the top) export ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client64 export PATH=${PATH}:${ORACLE_HOME}/bin export LD_LIBRARY_PATH=${ORACLE_HOME}/lib 6) you need a tnsnames.ora file (please read the syslog-ng documentation)
source rpm installation method: 1) rpmbuild --rebuild libdbi-0.8.3-1.src.rpm 2) rpm -ihv http://www.users.fh-salzburg.ac.at/~asartori/aconet/ /usr/src/redhat/RPMS/x86_64/libdbi-devel-0.8.3-1.x86_64.rpm 3) rpmbuild --rebuild libdbi-drivers-0.8.3-1.src.rpm 4) rpm -ihv libdbi-drivers-0.8.3-1.x86_64.rpm libdbi-dbd-oracle-0.8.3-1.x86_64.rpm 5) rpmbuild --rebuild syslog-ng-3.0.4-1.src.rpm 6) rpm -ihv syslog-ng-3.0.4-1_fhs3.x86_64.rpm 7) change your init script and add the environment variables
thanks to balazs for giving me the final hint and for the "commit patch".
src.rpms, patches, and rpms are at http://www.users.fh-salzburg.ac.at/~asartori/aconet/syslog-ng/
-- ___________________________________________ FACHHOCHSCHULE SALZBURG GmbH Salzburg University of Applied Sciences Andreas Sartori Systems Engineer IS - Information Services Urstein Süd 1 | 5412 Puch/Salzburg | Austria fon: +43 (0)50-2211-1655 | fax: -1699 web: www.fh-salzburg.ac.at Gerichtsstand Salzburg | FN166054y WELCOME TO YOUR FUTURE! ___________________________________________
participants (2)
-
Andreas Sartori
-
Balazs Scheidler