[syslog-ng] Configuration question, logging to db not working.

13 Feb 2009

      Hi --

I am /almost/ there, logging to Postgres database.  However, I've
discovered a puzzling and problematic behavior.This is probably just
some simple misunderstanding on my part, since this is my first foray
into syslog-ng.
I am logging to two different db tables.  Which table I log to is
determined by a regexp filter. The value is either root.ut_access or
root.geocode.
I can get either one to work, but not both at the same time.
If I comment out the log entry for the geocode, then ut_access works. 
However, if both log entries exist, only the gecocode_access_log table
gets a new row.  Nothing is logged to the ut_access_log table!  (Both
messages are logged to d_obsidian destination file, however.)
I've attached my config file.

Any tips would be greatly appreciated!!!

Liam
----------------------------------
@version:3.0
# syslog-ng configuration file.

options {
    flush_lines (0);
    time_reopen (10);
    log_fifo_size (1000);
    long_hostnames (off);
    use_dns (no);
    use_fqdn (no);
    create_dirs (yes);
    keep_hostname (yes);
};

source s_sys {
    file ("/proc/kmsg" program_override("kernel: "));
    unix-stream ("/dev/log");
    internal();
    udp(ip(0.0.0.0) port(514));
    tcp(ip(0.0.0.0) port(5000) max-connections(1000));
};

destination d_cons { file("/dev/console"); };
destination d_mesg { file("/var/log/messages"); };
destination d_auth { file("/var/log/secure"); };
destination d_mail { file("/var/log/maillog" flush_lines(10)); };
destination d_spol { file("/var/log/spooler"); };
destination d_boot { file("/var/log/boot.log"); };
destination d_cron { file("/var/log/cron"); };
destination d_mlal { usertty("*"); };
destination d_obsidian {
file("/var/log/django/$PROGRAM/$R_YEAR$R_MONTH$R_DAY.log"); };

destination d_ut_access {
    sql(
       type(pgsql)
       host("localhost") username("postgres") password("xxxxxx")
       database("prodlogs")
       table("ut_access_log")
       columns("datetime", "query_time", "host", "program", "pid",
"request_id",
           "level", "ip", "phone_id", "phone_type", "software_version",
       "client_version", "query_string", "art_id", "session_id",
       "lat", "lng")
       values("$R_ISODATE", "${UT.QTIME}", "$HOST", "$PROGRAM", "$PID",
           "${UT.REQUEST_ID}", "$LEVEL", "${UT.IP}", "${UT.PHONE_ID}",
       "${UT.PHONE_TYPE}", "${UT.SOFTWARE_VERSION}", "${UT.CLIENT_VERSION}",
       "${UT.QUERY_STRING}", "${UT.ART_ID}", "${UT.SESSION_ID}",
       "${UT.LAT}", "${UT.LNG}")
       indexes("datetime", "host", "program", "ip", "phone_id")
    );
};

destination d_geocode {
    sql(
       type(pgsql)
       host("localhost") username("postgres") password("xxxxxx")
       database("prodlogs")
       table("geocode_access_log")
       columns("datetime", "querytime", "host", "program", "pid",
"request_id",
           "level", "ip", "name", "place", "lat", "lng")
       values("$R_ISODATE", "${GEO.QTIME}", "$HOST", "$PROGRAM", "$PID",
           "${GEO.REQUEST_ID}", "$LEVEL", "${GEO.IP}", "${GEO.NAME}",
       "${GEO.PLACE}", "${GEO.LAT}", "${GEO.LNG}")
       indexes("datetime", "host", "program", "pid", "ip", "name", "place")
    );
};

parser p_ut_access {
    csv-parser(
        columns("UT.QTIME",
        "UT.IP",
        "UT.REQUEST_ID",
        "UT.CATEGORY",
        "UT.MYLEVEL",
        "UT.PHONE_ID",
        "UT.PHONE_TYPE",
        "UT.SOFTWARE_VERSION",
        "UT.CLIENT_VERSION",
        "UT.QUERY_STRING",
        "UT.ART_ID",
        "UT.SESSION_ID",
        "UT.LAT",
        "UT.LNG")
        delimiters(",")
        quote-pairs('""')
        flags(escape-double-char, strip-whitespace)
    );
};

parser p_geocode {
    csv-parser(
        columns("GEO.QTIME",
        "GEO.IP",
        "GEO.REQUEST_ID",
        "GEO.CATEGORY",
        "GEO.MYLEVEL",
        "GEO.NAME",
        "GEO.PLACE",
        "GEO.LAT",
        "GEO.LNG")
        delimiters(",")
        quote-pairs('""')
        flags(escape-double-char, strip-whitespace)
    );
};
#filter f_filter1   { facility(kern); };
filter f_filter2   { level(info..emerg) and
                     not facility(mail,authpriv,cron); };
filter f_filter3   { facility(authpriv); };
filter f_filter4   { facility(mail); };
filter f_filter5   { level(emerg); };
filter f_filter6   { facility(uucp) or
                     (facility(news) and level(crit..emerg)); };
filter f_filter7   { facility(local7); };
filter f_filter8   { facility(cron); };
filter f_obsidian  { program("^obsidian$") and level(info); };

filter f_ut_access { filter(f_obsidian) and message("root\.ut_access"); };
filter f_geocode   { filter(f_obsidian) and message("root\.geocode"); };

#log { source(s_sys); filter(f_filter1); destination(d_cons); };
log { source(s_sys); filter(f_filter2); destination(d_mesg); };
log { source(s_sys); filter(f_filter3); destination(d_auth); };
log { source(s_sys); filter(f_filter4); destination(d_mail); };
log { source(s_sys); filter(f_filter5); destination(d_mlal); };
log { source(s_sys); filter(f_filter6); destination(d_spol); };
log { source(s_sys); filter(f_filter7); destination(d_boot); };
log { source(s_sys); filter(f_filter8); destination(d_cron); };
log {
    source(s_sys);
    filter(f_ut_access);
    parser(p_ut_access);
    destination(d_ut_access);
};
### With this log entry commented out, logging to d_ut_access works. But
if I uncomment it, nothing is logged to d_ut_access!
### What am I missing?
#log {
    #source(s_sys);
    #filter(f_geocode);
    #parser(p_geocode);
    #destination(d_geocode);
#};
log { source(s_sys); filter(f_obsidian);  destination(d_obsidian); };

-- 
Liam Kirsher
PGP: http://liam.numenet.com/pgp/