Hi, I was thinking about a more generic approach, but this could solve your problem: https://github.com/balabit/syslog-ng/pull/1367 This creates a create-statement-append() option to the sql destination where you can specify a string that gets appended to the CREATE SQL statement. If it indeed solves your problem, I'd welcome feedback on the #1367 github issue. This is just a quick and dirty patch from me, without tests etc. -- Bazsi On Mon, Mar 6, 2017 at 7:58 PM, Nik Ambrosch <nik@ambrosch.com> wrote:

Table name (macros) are not the issue, here is how I’m currently creating a table:

destination mysql { sql(type(mysql) host("localhost") username("syslog") password("xxx") database("syslog") table("syslog_${R_YEAR}${R_MONTH}${R_DAY}") columns("id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null") values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG") indexes("id","host","priority")); };

This creates a table like this:

syslog_20170306 | CREATE TABLE `syslog_20170306` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `host` varchar(40) NOT NULL, `facility` varchar(20) DEFAULT NULL, `priority` varchar(10) DEFAULT NULL, `level` varchar(10) DEFAULT NULL, `program` text, `date` date NOT NULL, `time` time NOT NULL, `message` text NOT NULL, PRIMARY KEY (`id`), KEY `syslog_20170306_id_idx` (`id`), KEY `syslog_20170306_host_idx` (`host`), KEY `syslog_20170306_priority_idx` (`priority`) ) ENGINE=MyISAM AUTO_INCREMENT=10485923 <(1)%20048%205923> DEFAULT CHARSET=latin1

That is the output from my old syslog server, the new will be using InnoDB tables, not MyISAM, as listed. If you notice the last parameters, starting with “ENGINE” - that is where the additional parameters can be added. in a compressed table you would append a ROW_FORMAT variable. Here is a little information if you’re curious:

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html https://signalvnoise.com/posts/3571-scaling-your- database-via-innodb-table-compression

On Mar 6, 2017, at 1:46 PM, Fekete, Róbert <robert.fekete@balabit.com> wrote:

Hi,

syslog-ng can automatically create tables if you use macros in the table names. As for additional options, newer syslog-ng versions have a dbd-option() that can be used to set database options when syslog-ng connects the database (it might or might not be enough for your needs), see https://www.balabit.com/documents/syslog-ng-ose- latest-guides/en/syslog-ng-ose-guide-admin/html/ reference-destination-sql.html

HTH,

Robert

____________________________________________________________ __________________ 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