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!
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@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
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@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@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
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@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@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@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
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@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@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@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@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
Not being able to send to multiple sources can definitely be a bug in the version you are using. I had to go to a later version that the one packaged with centos7 to get multiple destinations working. We use Elasticsearch too, we take events and pass them through json-format inside of an amqp configuration using syslog-ng's amqp plugin to a rabbit cluster. Then we pick up the events from rabbit using workers that do any normalization and enrichment needed before passing to Elasticsearch. All of our data is then ingested by ES already structured. Alicia On Wed, Feb 6, 2019, 12:55 PM Garridan <garridan@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@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@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
On 2/6/19 12:42 PM, Garridan 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.
trolling? -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013
That response isn't really helpful, Ruben. New users most certainly will have questions like these. Please add to the conversation so that we can all increase our knowledge. Respectfully, Alicia On Wed, Feb 6, 2019, 7:31 PM Ruben Safir <ruben@mrbrklyn.com wrote:
On 2/6/19 12:42 PM, Garridan 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.
trolling?
-- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://www.nylxs.com - Leadership Development in Free Software http://www.brooklyn-living.com
Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013
______________________________________________________________________________ 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
I agree, I am not sure about Ruben's intent, but even with the best assumptions, this comment doesn't help much in the conversation. On Feb 7, 2019 02:37, "Alicia Smith" <asmith@mozilla.com> wrote: That response isn't really helpful, Ruben. New users most certainly will have questions like these. Please add to the conversation so that we can all increase our knowledge. Respectfully, Alicia On Wed, Feb 6, 2019, 7:31 PM Ruben Safir <ruben@mrbrklyn.com wrote:
On 2/6/19 12:42 PM, Garridan 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.
trolling?
-- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://www.nylxs.com - Leadership Development in Free Software http://www.brooklyn-living.com
Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013
______________________________________________________________________________ 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
participants (6)
-
Alicia Smith
-
Balazs Scheidler
-
Garridan
-
Harridan
-
Ruben Safir
-
Scheidler, Balázs