[syslog-ng] SQL destination function issue

17 Dec 2009

      Hi,
I am trying to log messages from a Cisco ASA into a MySQL database
through syslog-ng  3.0.4.  I have the whole thing working except I
cannot import data if I want to use a function.  Here is an example:

Here is the log message that is coming into the syslog server from the
ASA device:

Dec 04 2009 10:22:23: %ASA-5-305012: Teardown dynamic TCP translation
from inside:10.6.42.166/2617 to outside:10.16.19.17/61631 duration 0:01:00

Here is what is logged by syslog-ng:
Running SQL query; query='INSERT INTO stop (s_time, e_time, proto,
in_ip, in_port, out_ip, out_port) VALUES (\'DATE_SUB(\"2009-12-04
10:22:23\", INTERVAL \"0:01:00\" HOUR_SECOND)\', \'2009-12-04
10:22:23\', \'TCP\', \'10.6.42.166\', \'2617\', \'10.16.19.17\', \'61631\')'

Here is what is recorded in the database:
+-----+---------------------+---------------------+-------+-------------+---------+---------------+----------+
| idx | s_time              | e_time              | proto | in_ip
| in_port | out_ip        | out_port |
+-----+---------------------+---------------------+-------+-------------+---------+---------------+----------+
| 166 | 0000-00-00 00:00:00 | 2009-12-04 10:22:23 | TCP   | 10.6.42.166
| 2617    | 10.16.19.17 | 61631    |

Every field is recorded properly aside from the one that I am trying to
use a function in.  The purpose of the function is to record the start
time of a translation.  I get the end time from the syslog timestamp and
the duration from the syslog message itself.  I am trying to use a
builtin MySQL function to record the start time in s_time.  I think  the
problme is syslog-ng is putting single quotes around the whole function
so it is being treated like a string literal by MySQL instead of
variable data.  Is that right?  Is there a way to remedy this?

Here is my syslog-ng.conf configuration if it helps:

@version: 3.0
# $Header:
/var/cvsroot/gentoo-x86/app-admin/syslog-ng/files/syslog-ng.conf.gentoo,v
1.7 2007/08/02 04:52:18 mr_bones_ Exp $
#
# Syslog-ng default configuration file for Gentoo Linux
# contributed by Michael Sterrett

options {
        chain_hostnames(no);

        # The default action of syslog-ng 1.6.0 is to log a STATS line
        # to the file every 10 minutes.  That's pretty ugly after a while.
        # Change it to every 12 hours so you get a nice daily update of
        # how many messages syslog-ng missed (0).
        stats_freq(43200);
};

source src {
    unix-stream("/dev/log" max-connections(256));
    internal();
    file("/proc/kmsg");
};

source s_xlate {
    udp(ip(0.0.0.0) port(514));
};

filter f_xlate_start { host(10.16.19.33) and
                       program("%ASA-5-305011") ; };
filter f_xlate_stop  { host(10.16.19.33) and
                       program("%ASA-5-305012") ; };

parser p_xlate { csv-parser(columns("LOG.STATUS", "LOG.TYPE",
                                    "LOG.PROTOCOL", "LOG.XLATE",
                                    "LOG.FROM","LOG.INSIDE",
                                    "LOG.IN_IP", "LOG.IN_PORT",
                                    "LOG.TO","LOG.OUTSIDE",
                                    "LOG.OUT_IP", "LOG.OUT_PORT",
                                    "LOG.DURATION","LOG.HR",
                                    "LOG.MIN", "LOG.SEC")
                            delimiters("/: ")
                            flags(escape-none)
                            template("${MSGONLY}"));
};

destination messages { file("/var/log/messages"); };

destination d_xlate_start { sql(type(mysql)
                            username("user") password("pass")
                            database("nat")
                            table("start")
                            columns("s_time DATETIME",
                                    "proto VARCHAR(4)",
                                    "in_ip VARCHAR(15)",
                                    "in_port VARCHAR(5)",
                                    "out_ip VARCHAR(15)",
                                    "out_port VARCHAR(5)")
                            values("$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC",
                                   "${LOG.PROTOCOL}", "${LOG.IN_IP}",
                                   "${LOG.IN_PORT}", "${LOG.OUT_IP}",
                                   "${LOG.OUT_PORT}"));
                          };

destination d_xlate_stop  { sql(type(mysql)
                            username("user") password("pass")
                            database("nat")
                            table("stop")
                            columns("s_time DATETIME",
                                    "e_time DATETIME",
                                    "proto VARCHAR(4)",
                                    "in_ip VARCHAR(15)",
                                    "in_port VARCHAR(5)",
                                    "out_ip VARCHAR(15)",
                                    "out_port VARCHAR(5)")
                            values("DATE_SUB(\"$YEAR-$MONTH-$DAY
                                    $HOUR:$MIN:$SEC\", INTERVAL
                                   \"${LOG.HR}:${LOG.MIN}:${LOG.SEC}\"
                                    HOUR_SECOND)",
                                   "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC",
                                   "${LOG.PROTOCOL}", "${LOG.IN_IP}",
                                   "${LOG.IN_PORT}",
                                   "${LOG.OUT_IP}", "${LOG.OUT_PORT}"));
                          };

destination console_all { file("/dev/tty12"); };
log { source(s_xlate); filter(f_xlate_start); parser(p_xlate);
      destination(d_xlate_start); flags(final); };
log { source(s_xlate); filter(f_xlate_stop);  parser(p_xlate);
      destination(d_xlate_stop); flags(final); };
log { source(src); destination(messages); };
log { source(src); destination(console_all); };

[syslog-ng] SQL destination function issue

Christopher O'Brien