[syslog-ng] Issues with sql driver

Wed Jan 16 20:35:59 UTC 2019

Hello,

I tried your config on ubuntu:16.04 where I've found 3.5.6-2.1 [@416d315].
The mysql was the latest mysql docker image in dockerhub.

It create a new table, and push the message into the table; but not at the
first time. When it tried to create a table instrumenting with index,
"Error running SQL query; type='mysql', host='127.0.0.1', port='',
user='root', database='syslog', error='1170: BLOB/TEXT column \'date\' used
in key specification without a key length', query='CREATE INDEX
messages_peterkokai_work_date_idx ON messages_peterkokai_work (date)'"

It creates the fields of the table as TEXT, which cannot be index by
default.

What distro are you using ? Where do you get the syslog-ng ? Would it be
possible to try with the latest ?

--
Kokan

On Wed, Jan 16, 2019 at 5:27 PM N. Max Pierson <nmaxpierson at gmail.com>
wrote:

> 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
>>
>>
> ______________________________________________________________________________
> 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/a4cb4efe/attachment.html>