[syslog-ng] MS-SQL Question

[Harridan]

Thank you for your message and suggestions.  I’ll check out elasticsearch.  I was using sql as it’s what we have in place already.  I appreciate it!

> On Feb 6, 2019, at 3:56 PM, Scheidler, Balázs <balazs.scheidler at oneidentity.com> wrote:
> 
> I vaguely remember that the mssql driver in libdbi that we use had bugs with multiple connections. Maybe others in the team have more information on that. But mssql is not the database that is often used with syslog-ng, at least with the open source variant.
> 
> Also, you could use template strings in the table name, that way you won't need a separate sql destination.
> 
> Something like table("syslog_$SOURCEIP") where the sourceip would get substituted. Tables for different IP addresses get created automatically.
> 
> If you seriously want to throw logs in a database i would probably try elasticsearch instead of an sql database.
> 
>> On Wed, Feb 6, 2019, 19:55 Garridan <garridan at gmail.com wrote:
>> It's logs from Cisco ASA firewalls.  It doesn't seem to get hung up on any particular message it just starts when I add a device that matches a different filter.   A representative config is below...
>> 
>> So if I write using devices that match filter1, it works fine.  When I increase the load and start adding in the devices that match filter2, the service starts restarting and eventually the SQL message is thrown.
>> 
>> Again, I'm a noob who's still learning and not a dev, just a firewall guy.  
>> 
>> Thanks for any help!
>> 
>> source s_source1 {
>>     network(
>>     ip("192.168.100.1")
>>     transport("udp")
>>     port(514)
>>     );
>> };
>> 
>> destination d_device1 {
>>         sql(type(mssql)
>>         host("dbserver")
>>         port("1433")
>>         username("syslogng")
>>         password("syslogng")
>>         database("syslogng")
>>         table("device1")
>>         columns("Date varchar(10)", "Time varchar(8)", "Priority varchar(30)", "Hostname varchar(255)", "Text varchar(4096)")
>>         values("${R_MONTH}-${R_DAY}-${R_YEAR}", "${R_HOUR}:${R_MIN}:${R_SEC}", "${LEVEL_NUM}", "${HOST}", "${MSGHDR} ${MESSAGE}"));
>> };
>> 
>> destination d_device2 {
>>         sql(type(mssql)
>>         host("dbserver")
>>         port("1433")
>>         username("syslogng")
>>         password("syslogng")
>>         database("syslogng")
>>         table("device2")
>>         columns("Date varchar(10)", "Time varchar(8)", "Priority varchar(30)", "Hostname varchar(255)", "Text varchar(4096)")
>>         values("${R_MONTH}-${R_DAY}-${R_YEAR}", "${R_HOUR}:${R_MIN}:${R_SEC}", "${LEVEL_NUM}", "${HOST}", "${MSGHDR} ${MESSAGE}"));
>> };
>> 
>> filter f_device1 {
>>         host("192.168.1.1") or host("192.168.1.2");
>> };
>> 
>> filter f_device2 {
>>         host("192.168.2.1") or host("192.168.2.2");
>> };
>> 
>> log {
>>     source(s_source1);
>>     filter(f_device1);
>>     destination(d_device1);
>>     };
>> 
>> log {
>>     source(s_source1);
>>     filter(f_device2);
>>     destination(d_device2);
>>     };
>> 
>> 
>> 
>>> On Feb 6, 2019, at 1:01 PM, Alicia Smith <asmith at mozilla.com> wrote:
>>> 
>>> You'll want to tune your config according to the resources available and the throughput it requires.
>>> 
>>> I can follow up with a link on how to do that.
>>> 
>>> Are you using json format from syslog-ng? 
>>> Can you provide an example event that it's getting hung up on?
>>> 
>>> Alicia
>>> 
>>> 
>>>> On Wed, Feb 6, 2019, 11:42 AM Garridan <garridan at gmail.com> wrote:
>>>> Hello!   I'm a new syslog-ng user, so please be gentle with me.  :)
>>>> 
>>>> I'm attempting to log to an MS-SQL database and would like to send to different tables in the same DB based on the source IP - for example device A to its own table, device B to its own table, and so on.
>>>> 
>>>> I thought I would simply need to create the same destination but define different table names in each, it works, however under load the syslog-ng service starts restarting over and over and eventually MS-SQL errors and alerts that the login packet is structurally invalid.
>>>> 
>>>> Is it possible to send to different table names in this manner or is there another way to do it?
>>>> 
>>>> Thanks!
>>>> 
>>>> ______________________________________________________________________________
>>>> 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/20190206/275df29d/attachment.html>