Issues with sql driver
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
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@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
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@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@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
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/a725a578b06459e96a3bc... 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@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@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@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
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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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@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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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@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@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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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@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@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@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@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/a725a578b06459e96a3bc...
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@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@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@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
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@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@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@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@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@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@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@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/a725a578b06459e96a3bc... > > 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@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@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@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
participants (3)
-
N. Max Pierson
-
Péter, Kókai
-
Scheidler, Balázs