[syslog-ng] Issues with sql driver

N. Max Pierson nmaxpierson at gmail.com
Fri Jan 18 20:14:35 UTC 2019 

Since I was able to upgrade to 3.19, I will certainly try the sanitize
template function to see if that fixes our problem. Thank you for the
suggestion. I will hold off on the bug report until I can try this method
but I will respond back if it doesn't work out for me.

Regards,
Max

On Fri, Jan 18, 2019 at 12:20 PM Scheidler, Balázs <
balazs.scheidler at oneidentity.com> wrote:

> The problem is that as far as I know different sql implementations escape
> differently, mysql would need apostrophes (or backticks) around table
> names, and the same will not work with other sql implementations.
>
> You can however escape from syslog-ng explicitly using the $(sanitize)
> template function, at least as far as I remember.
>
> Template functions however are not yet in 3.5.
>
> If sanitize does not work, I would be willing to add support for this use
> case.
>
> On Fri, Jan 18, 2019, 18:48 N. Max Pierson <nmaxpierson at gmail.com wrote:
>
>> Hi Balazs,
>>
>> I finally resolved all of my issues and as you stated I found (although
>> not explicitly state in the docs) a small config section in the docs that
>> had the column types defined and I was able to correctly define them and
>> index them properly.
>>
>> I did however uncover what I think is a bug while doing this. When you
>> have use_dns (no); configured and you are dynamically inserting tables with
>> a macro that references the IP of the host, the query to create the table
>> fails with the error below. I was able to overcome this issue with manually
>> creating entries in the /etc/hosts file and turning use_dns (yes); but I do
>> not really want to have to maintain entries for everything we log to this
>> server. I also tested this on version 3.5.6 and version 3.19.1 so
>> presumably all versions of syslog-ng are affected. It seems as though the
>> sql driver doesn't properly escape the dots in the IP address of the host
>> macro per the error below. I am now going to report this as a bug on the
>> github.com repo for syslog-ng for those that care.
>>
>> Jan 18 11:44:59 syslog syslog-ng[23770]: Error running SQL query;
>> type='mysql', host='127.0.0.1', port='', user='syslog-ng',
>> database='syslog', error='1064: You have an error in your SQL syntax; check
>> the manual that corresponds to your MariaDB server version for the right
>> syntax to use near \'.11.100 (date text, host text, level text, message
>> text)\' at line 1', query='CREATE TABLE messages_10.251.11.100 (date text,
>> host text, level text, message text)'
>> Jan 18 11:44:59 syslog syslog-ng[23770]: Error creating table, giving up;
>> table='messages_10.251.11.100'
>> Jan 18 11:44:59 syslog syslog-ng[23770]: Error checking table,
>> disconnecting from database, trying again shortly; time_reopen='10'
>> Jan 18 11:44:59 syslog syslog-ng[23770]: g_string_free: assertion 'string
>> != NULL' failed
>>
>> Regards,
>> Max
>>
>> On Fri, Jan 18, 2019 at 2:57 AM Scheidler, Balázs <
>> balazs.scheidler at oneidentity.com> wrote:
>>
>>> You can also specify a type in the column specification, e.g.
>>> columns('date varchar(64)')  or something like that. With that change, the
>>> CREATE INDEX should work.
>>>
>>> those debug messages should be emitted from the internal() source or
>>> printed to stderr if running in the foreground.
>>>
>>> On Wed, Jan 16, 2019 at 10:41 PM N. Max Pierson <nmaxpierson at gmail.com>
>>> wrote:
>>>
>>>> I am using the version supplied in the epel repo on CentOS 7.6.1810
>>>> which is syslog-ng version 3.5.6. I can certainly try a newer version if
>>>> you think that would be beneficial, but I will need to build a RPM from the
>>>> source as I use yum to mange all applications and libraries on the system.
>>>> I'll try and build a new RPM with the latest version and see if that fixes
>>>> my issue. I'll also try and create the tables manually using TEXT types to
>>>> see if I can at least get the records into the database but ultimately I
>>>> would like to be able to dynamically insert records with syslog-ng creating
>>>> tables and columns on demand.
>>>>
>>>> One last question, how were you able to see the sql debug that showed
>>>> you what statements/queries were being used? I was unable to locate that
>>>> info in any log files I have.
>>>>
>>>> Thanks again for all of your help!
>>>>
>>>> Regards,
>>>> Max
>>>>
>>>> On Wed, Jan 16, 2019 at 2:36 PM Péter, Kókai <
>>>> peter.kokai at oneidentity.com> wrote:
>>>>
>>>>> 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
>>>>>>
>>>>>>
>>>>> ______________________________________________________________________________
>>>>> 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/20190118/e780e60e/attachment-0001.html>

More information about the syslog-ng mailing list