[syslog-ng] Issues with sql driver

Wed Jan 16 16:26:45 UTC 2019

Thanks for all of the feedback Peter.

I have resolved all of the issues I was having and it turns out I did not
have the specific mysql libdbi driver installed which was causing the
error. Now that it is resolved, I am having one last issue. When I enable
the log statement with the sql destination in it, nothing is being written
to the database. I'm not getting any errors as to why and I know the source
and filter/rewrite is working because if I log it to a flat file it works
correctly. My config for the sql destination is below, so my questions are
....

The docs state that the tables and columns can be dynamically created if I
use macros, but that doesn't happen with the config below. Is that correct
for version 3.5 that I am using? Is this config correct and is there any
logs or flags I can use to see why the tables and columns aren't being
created dynamically? I also created them manually and it still doesn't
insert the record either.

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

filter f_nexus { host("10.251.11.241"); };

rewrite r_nexus{ subst("^[a-z]+ [0-9]+ [0-9]+:[0-9]+:[0-9]+ [a-z]+: ", "",
value("MESSAGE"), type("posix"), flags("ignore-case"),
condition(filter(f_nexus))); };

destination d_mysql {
    sql(type(mysql)
    host("127.0.0.1")
    username("syslog-ng")
    password("password")
    database("syslog")
    table("messages_${HOST}")
    columns("date", "host", "level", "message")
    values("${R_DATE}", "${HOST}", "${LEVEL}", "${MESSAGE}")
    indexes("date", "host", "level") );
};

log { source(s_network); rewrite(r_nexus); destination(d_mysql); };

Thanks again for the help!!

Regards,
Max

On Wed, Jan 16, 2019 at 12:18 AM Péter, Kókai <peter.kokai at oneidentity.com>
wrote:

> Hello,
>
> Please do that :) I was not on board of the project at version 3.5
>
> Well a macro itself also a template, a template somewhat more generic as
> that include string literals, template functions and of course macros, and
> those combination.
>
> In order to cut the date part; there was just recently a nice patch that
> did similar thing for websense-parser:
> https://github.com/balabit/syslog-ng/pull/2471/commits/a725a578b06459e96a3bc85812e12a71d3f0a3b4
>
> Also for example the cisco-parser has tricks you can learn from:
> https://github.com/balabit/syslog-ng/blob/master/scl/cisco/plugin.conf
>
>
> --
> Kokan
>
> On Tue, Jan 15, 2019 at 8:16 PM N. Max Pierson <nmaxpierson at gmail.com>
> wrote:
>
>> Thanks for the reply.
>>
>> I am using version 3.5, so I am reading the admin guide for 3.5 now to
>> see if I have something configured that isn't available in this version.
>>
>> As far as the template, I thought the ${R_DATE} was a macro. Maybe I am
>> misunderstanding then. What I need is to take a part of the log that comes
>> in and remove it. Here is a sample of the message I have below. What is the
>> best way to remove the date portion that isn't part of the standard syslog
>> message ( the part delimited by ***).
>>
>> Jan 15 13:12:35 10.251.11.241 ***2019 Jan 15 13:12:35 CST:***
>> %DAEMON-3-SYSTEM_MSG: NTP Receive dropping message: Received NTP control
>> mode packet. Drop count:147908  - ntpd[15029]
>>
>> Regards,
>> Max
>>
>> On Tue, Jan 15, 2019 at 12:03 AM Péter, Kókai <
>> peter.kokai at oneidentity.com> wrote:
>>
>>> Hello,
>>>
>>> As the *--syntax-only* suggest, it only does check for syntactic errors.
>>> A common way to find such issues to start the process in foreground:
>>> * stop syslog-ng systemd service (so it won't get in the way)
>>> * start syslog-ng as the systemd would do, plus include the -F
>>> (foreground) option and -e (print internal logs to the stderr); optionally
>>> you may also use -d (debug) -v (verbose); but in this case probably the -Fe
>>> would suffice
>>>
>>> I just tried your config (with additional @version: 3.18), and it
>>> started just fine.
>>>
>>> About the second part. You already using template in your configuration
>>> for the date column ( ${R_DATE} ); in the values you should be able to use
>>> any template (not template function due).
>>>
>>> --
>>> Kokan
>>>
>>> On Mon, Jan 14, 2019 at 10:54 PM N. Max Pierson <nmaxpierson at gmail.com>
>>> wrote:
>>>
>>>> Hi List,
>>>>
>>>> I have 2 questions about the sql driver. First, I am trying to get
>>>> messages into sql using the sql driver but I get an error when I try and
>>>> restart syslog-ng when I enable the log statement with the sql destination.
>>>> The syslog-ng --syntax-only command runs without any issues but systemd
>>>> throws and error that it cannot restart the service but doesn't give a
>>>> clear reason. My config is below, doesn't anyone know where in a log I can
>>>> see why it won't restart??
>>>>
>>>> source s_network { udp(ip(0.0.0.0) port(514)); };
>>>>
>>>> destination d_mysql {
>>>>     sql(type(mysql)
>>>>     host("127.0.0.1")
>>>>     username("syslog-ng")
>>>>     password("password")
>>>>     database("syslog")
>>>>     table("messages_${HOST}")
>>>>     columns("date", "host", "message")
>>>>     values("${R_DATE}", "${HOST}", "${MESSAGE}")
>>>>     indexes("date", "host") );
>>>> };
>>>>
>>>> log { source(s_network); destination(d_mysql); };
>>>>
>>>>
>>>> My second question is can you use a template with the sql destination
>>>> driver? I need to reformat some Cisco Nexus logs because of how it formats
>>>> the date (looks to be non RFC compliant) and if so, can someone give me a
>>>> sample of config with the template in the sql destination driver? I cannot
>>>> seem to find in the docs if this is even possible much less and example of
>>>> how to do it.
>>>>
>>>> TIA,
>>>> Max
>>>>
>>>> ______________________________________________________________________________
>>>> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>>>> Documentation:
>>>> http://www.balabit.com/support/documentation/?product=syslog-ng
>>>> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>>>>
>>>>
>>> ______________________________________________________________________________
>>> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>>> Documentation:
>>> http://www.balabit.com/support/documentation/?product=syslog-ng
>>> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>>>
>>>
>> ______________________________________________________________________________
>> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>> Documentation:
>> http://www.balabit.com/support/documentation/?product=syslog-ng
>> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>>
>>
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation:
> http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20190116/f0c712ee/attachment.html>